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?

    Requires Free Membership to View

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.

This was first published in March 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: