Normalization of database model

1 / 9

# Normalization of database model - PowerPoint PPT Presentation

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}

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Normalization of database model' - yin

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### 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 Form

A 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 I

Consider 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 Form

A 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);

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);