Normalization of database model

Closure an attribute set

Given a set of attributes α define the closure of attribute set α under F (denoted as α+) as the set of attributes that are functionally depend on α under F.

Example R(A,B,C,G,H,I)

F={A->B,A->C,CG->H,CG->I,B->H}

(AG) + => AG trivial

ABCG => (A->B,A->C)

ABCGH => ( CG->H )

ABCGHI => (CG->I)

Is AG is a superkey? Does AG->R ?

Is AG a candidate key? A->R? Or G->R ?

Compute A + and G+

Why to normalize?Teacher(T-Name,T-No,U-Name,U-No)Students(S-Name,S-No,U-No)Location(U-No,Room,Time);

If all teacher who teach a particulas unit leavem the information about the unit (U-Name) is lost.

If a teacher teaches many units then information on the teacher is unnecessarily replicated. Similary information about students attending to many units is unnecessarily duplicated.

To update the U-Name, one may have to update many Teacher records.

Normalization removes such problems!

First Normal Form

A relation is in 1NF if it does not contain multivalued field or nested relations, but all the fields are atomic.

Eg:

Teacher(T-Name, T-No,Units(U-No,U-Name));

Teacher(T-No,T-Name,U-No,U-name);

Second Normal FormA relation R is in 2NF if it is in 1NF and each non-prime attribute of R is fully

functionally dependent on each candidate key of R.

Full Functional Dependency:X,Y-->Z; X-\->Z and Y-/->Z than X,Y-fully->Z

Eg: Teacher(T-No,T-Name,U-No,U-Name);F={T-No->T-Name, U-No->U-Name};

Is in 2NF?U-Name is a non-prime attribute, but it does not fully functionally depend on the

primary key, since U-No->U-Name;

Solution:Teacher(T-No,T-Name);Unit(U-No,U-name);Teaches(U-No,T-No);

Example IConsider the following schema:

Source(Supp-No,Part-No,Supp-Details,Supp-Name,Price);F={Supp-No->Supp-Details,

Supp-No,Part-No->Price,Supp-No->Supp-Name};

Is in 2NF?

No, since eg. Supp-Details is not prime attribute, but is depends only on Supp-No but not on Part-No!

Solution:Suppliers(Supp-No,Supp-Details,Sup-Name);Cost(Supp-No,Part-No,Price);

Third Normal FormA relation R is in 3NF if it is in 2NF and non-prime attribute of R is not

transitively depend on the primary key.

Recall transitive dependency:A->B, B->C, => A->C;

R(A,B,C) would not be in 3NF;

Eg: Employee(E-No,E-Name,Dept-No,Salary,Location)F={E-No->E-Name,E-No->Dept-No,E-No->Salary,

E-No->Location,Dept-No->Location}

Location transitively depends on E-No, through Dept-No.

Solution: Employee(E-No,E-Name,Dept-No,Salary) Department(Dept-No,Location);

Example II

Timtetable(S-No,U-No,Time,S-Name,U-Name,Room-No);F={S-No->S-Name,U-No->U-Name,

S-No,Time->RoomNo}

Is in 3NF?

No, it is not even in 2NF!

Student(S-No,S-Name); Unit(U-No,U-Name);Location(S-No,Time,Room-No); Studies(S-No,U-No,Time);

It is already in 3NF!

Example III

Stock(Bin-No,Part-No,Bin-Quantity,Re-Order-Level);

F={Bin-No->Part-No,Bin-No->Bin-Quantity,Part-No->Re-Order-Level}

Is in 3NF?

It is in 2NF but not in 3NF because of transitive dependency of

Re-Order-Level!

Bin-Stock(Bin-No,Part-No,Bin-Quantity);

Re-Order(Part-No,Re-Order-Level);