1 / 15

Relational Design Theory

Relational Design Theory. Boyce- Codd Normal Form. BCNF. Relational design by decomposition “Mega” relations + properties of the data System decomposes based on properties Final set of relations satisfies normal form No anomalies, no lost information

vic
Télécharger la présentation

Relational Design Theory

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. Relational Design Theory Boyce-Codd Normal Form

  2. BCNF Relational design by decomposition • “Mega” relations + properties of the data • System decomposes based on properties • Final set of relations satisfies normal form • No anomalies, no lost information • Functional dependencies  Boyce-Codd Normal Form • Multivalued dependences  Fourth Normal Form

  3. BCNF Decomposition of a relational schema

  4. BCNF Decomposition Example #1 Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority)

  5. BCNF Decomposition Example #2 Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority)

  6. BCNF Relational design by decomposition • “Mega” relations + properties of the data • System decomposes based on properties • “Good” decompositions only • Into “good” relations

  7. BCNF Boyce-Codd Normal Form Relation R with FDs is in BCNF if: For eachᾹ B,Ᾱis a key

  8. BCNF BCNF? Example #1 Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority) SSN  sName,address,GPA GPA  priority HScode  HSname,HScity

  9. BCNF BCNF? Example #2 Apply(SSN,cName,state,date,major) SSN,cName,state  date,major

  10. BCNF Relational design by decomposition • “Mega” relations + properties of the data • System decomposes based on properties • “Good” decompositions only • Into “good” relations

  11. BCNF BCNF decomposition algorithm Input: relation R + FDs for R Output: decomposition of R into BCNF relations with “lossless join” Compute keys for R Repeat until all relations are in BCNF: Pick any R’ with A  B that violates BCNF Decompose R’ into R1(A, B) and R2(A, rest) Compute FDs for R1 and R2 Compute keys for R1and R2

  12. BCNF BCNF Decomposition Example Student(SSN,sName,address, HScode,HSname,HScity, GPA,priority) SSNsName,address,GPA GPApriority HScodeHSname,HScity

  13. BCNF BCNF decomposition algorithm Input: relation R + FDs for R Output: decomposition of R into BCNF relations with “lossless join” Compute keys for R Repeat until all relations are in BCNF: Pick any R’ with A  B that violates BCNF Decompose R’ into R1(A, B) and R2(A, rest) Compute FDs for R1 and R2 Compute keys for R1and R2

  14. BCNF Does BCNF guarantee a good decomposition? • Removes anomalies? • Can logically reconstruct original relation? Too few or too many tuples?

  15. BCNF Does BCNF guarantee a good decomposition? • Removes anomalies? • Can logically reconstruct original relation? Too few or too many tuples? • Some shortcomings discussed in later video

More Related