Q

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:

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close