Composite keys and normalization

For a good illustration of composite keys, please see the following two-part answer:

• Composite foreign keys, Part 1 (13 September 2002)
• Composite foreign keys, Part 2 (13 September 2002)

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:

• Introduction to Data Modeling by the Information Technology Services department at The University of Texas.

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.