tailieunhanh - Lecture Database Systems - Lecture 29

After completing this chapter, students will be able to: Basic concepts, tree-structure diagrams, data-retrieval facility, update facility, virtual records, mapping of hierarchies to files, the IMS database system. | CSC271 Database Systems Lecture # 29 Summary: Previous Lecture The normalization process 1NF, 2NF, 3NF Inference rules for FDs BCNF Summary: Normalization First normal form (1NF) A relation in which the intersection of each row and column contains one and only one value Second normal form (2NF) A relation that is in 1NF and every non-PK attribute is fully functionally dependent on the primary key (CKs) Third normal form (3NF) A relation that is in 1NF and 2NF and in which no non-primary-key attribute is transitively dependent on the primary key (CKs) Boyce–Codd normal form (BCNF) A relation is in BCNF if and only if every determinant is a candidate key Example: Normalization Example: Normalization FDs Example: Normalization fd1 propertyNo, iDate → iTime, comments, staffNo, sName, carReg (Primary key) fd2 propertyNo→ pAddress (Partial dependency) fd3 staffNo→ sName (Transitive dependency) fd4 staffNo, iDate→ carReg fd5 carReg, iDate, iTime→ propertyNo, pAddress, comments, staffNo, sName (Candidate key) fd6 staffNo, iDate, iTime→ propertyNo, pAddress, comments (Candidate key) Example: Normalization INF to 2NF Property (propertyNo, pAddress) PropertyInspection (propertyNo, iDate, iTime, comments, staffNo, sName, carReg) Example: Normalization Property fd2 propertyNo → pAddress PropertyInspection fd1 propertyNo, iDate → iTime, comments, staffNo, sName, carReg fd3 staffNo → sName fd4 staffNo, iDate → carReg fd5′ carReg, iDate, iTime → propertyNo, comments, staffNo, sName fd6′ staffNo, iDate, iTime → propertyNo, comments Example: Normalization 2NF to 3NF Property (propertyNo, pAddress) Staff (staffNo, sName) PropertyInspect (propertyNo, iDate, iTime, comments, staffNo, carReg) Example: Normalization Property fd2 propertyNo → pAddress Staff fd3 staffNo → sName PropertyInspect fd1′ propertyNo, iDate → iTime, comments, staffNo, carReg fd4 staffNo, iDate → carReg fd5′ carReg, iDate, iTime → propertyNo, comments, staffNo fd6′ staffNo, iDate, iTime → propertyNo, comments . | CSC271 Database Systems Lecture # 29 Summary: Previous Lecture The normalization process 1NF, 2NF, 3NF Inference rules for FDs BCNF Summary: Normalization First normal form (1NF) A relation in which the intersection of each row and column contains one and only one value Second normal form (2NF) A relation that is in 1NF and every non-PK attribute is fully functionally dependent on the primary key (CKs) Third normal form (3NF) A relation that is in 1NF and 2NF and in which no non-primary-key attribute is transitively dependent on the primary key (CKs) Boyce–Codd normal form (BCNF) A relation is in BCNF if and only if every determinant is a candidate key Example: Normalization Example: Normalization FDs Example: Normalization fd1 propertyNo, iDate → iTime, comments, staffNo, sName, carReg (Primary key) fd2 propertyNo→ pAddress (Partial dependency) fd3 staffNo→ sName (Transitive dependency) fd4 staffNo, iDate→ carReg fd5 carReg, iDate, iTime→ propertyNo, pAddress, comments, staffNo, .