1 / 9

Normalization of database model

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}

yin
Download Presentation

Normalization of database model

An Image/Link below is provided (as is) to download presentation 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Normalization of database model

  2. 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+

  3. 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!

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

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

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

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

  8. 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!

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

More Related