This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: Closing and Projecting FDs Anomalies BCNF Properties of BCNF Decompositions Design of Relational Database Schemas T. M. Murali October 27, November 1, 2010 T. M. Murali October 27, November 1, 2010 CS 4504: Design of Relational Database Closing and Projecting FDs Anomalies BCNF Properties of BCNF Decompositions Plan Till Thanksgiving I What are the typical problems or“anomalies”in relational designs? I Introduce the idea of decomposing a relation schema into two smaller schemas. I Introduce“BoyceCodd normal form”(BCNF), a condition on relational schemas that eliminates anomalies. I BCNF stated using the concept of FDs. I Use decomposition of schemas to bring them to BCNF. I Define another type of constraint called Multivalued Dependencies (MDs). I Define normal forms that eliminate MDs. T. M. Murali October 27, November 1, 2010 CS 4504: Design of Relational Database Closing and Projecting FDs Anomalies BCNF Properties of BCNF Decompositions Closures of FDs I Given a relation R and I a set F of FDs that hold in R I the closure {F} + is the set of all FDs that follow from R . T. M. Murali October 27, November 1, 2010 CS 4504: Design of Relational Database Closing and Projecting FDs Anomalies BCNF Properties of BCNF Decompositions Closures of FDs I Given a relation R and I a set F of FDs that hold in R I the closure {F} + is the set of all FDs that follow from R . I Recall: An FD S follows from a set of FDs T if every relation instance that satisfies all the FDs in T also satisfies S . I S = { A → C } follows from T = { A → B , B → C } . T. M. Murali October 27, November 1, 2010 CS 4504: Design of Relational Database Closing and Projecting FDs Anomalies BCNF Properties of BCNF Decompositions Computing Closures of FDs I To compute the closure of a set of FDs, repeatedly apply Armstrong’s Axioms until you cannot find any new FDs: T. M. Murali October 27, November 1, 2010 CS 4504: Design of Relational Database Closing and Projecting FDs Anomalies BCNF Properties of BCNF Decompositions Computing Closures of FDs I To compute the closure of a set of FDs, repeatedly apply Armstrong’s Axioms until you cannot find any new FDs: Reflexivity : If Y ⊆ X , then X → Y Augmentation : If X → Y then XZ → YZ for any attribute Z . Transitivity : If X → Y and Y → Z then X → Z . T. M. Murali October 27, November 1, 2010 CS 4504: Design of Relational Database Closing and Projecting FDs Anomalies BCNF Properties of BCNF Decompositions Examples of Computing Closures of FDs I Let us include only completely nontrivial FDs in these examples, with a single attribute on the right. I Assume that there are no attributes other than those mentioned in the FDs....
View
Full Document
 Fall '08
 CMMALIK
 Relational Database, Relational model, BCNF, T. M. Murali, BCNF decompositions

Click to edit the document details