To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
For a good illustration of composite keys, please see the following
two-part answer:
In Part 1, you will see this:
A major advantage of the composite key structure
is that because of the transitive nature of relations,
you can see that employees are related
not just to their sections, but also to their
departments and divisions, without the
need to declare additional foreign keys -- you could
declare them, but you don't need to.
Why is this an advantage? To find all the employees
in the Information Technology Division, you do not
have to have a four-way join in your query, you can just use --
select EMPNO, EMPNAME
from EMPLOYEE
where DVID = 'it'
Now compare this to the query in Part 2 which shows how to obtain the same
information when surrogate keys are used.
select EMPNO, EMPNAME
from DIVISION
inner
join DEPARTMENT
on DIVISION.DVNO = DEPARTMENT.DVNO
inner
join SECTION
on DEPARTMENT.DPNO = SECTION.DPNO
inner
join EMPLOYEE
on SECTION.SCNO = EMPLOYEE.SCNO
where DIVISION.DVNO = 5
Why go for normalization? The answer is easy: why wouldn't you?
Normalization produces the simplest, "cleanest" data structures,
which are not susceptible to insert, update,
and delete anomalies (for a good
explanation of these, see the Wikipedia
Database normalization article).
One of the best tutorials on the Web for data modelling (of which
normalization is a most important part) is:
As far as tools are concerned, these are really too numerous to mention.
Look for one that can "reverse engineer" a diagram from a database schema,
and that can generate the schema DDL from the diagram.
|
