Composite keys and normalization
What is composite key and why do we use it? Why go for normalization?
What is composite key and why do we use it? Why go for normalization? Any good sites where I can get information? Any tool which can be used?
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.