1 / 16

Relational Design Theory

Relational Design Theory. Multivalued Dependencies & 4 th Normal Form. MVDs & 4NF. 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

nevan
Download Presentation

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 Multivalued Dependencies & 4th Normal Form

  2. MVDs & 4NF 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. MVDs & 4NF Example: College application info. Apply(SSN,cName,hobby) FDs? Keys? BCNF? Good design?

  4. MVDs & 4NF Multivalued Dependency • Based on knowledge of real world • All instances of relation must adhere

  5. MVDs & 4NF Apply(SSN,cName,hobby)

  6. MVDs & 4NF Modified example Apply(SSN,cName,hobby) Reveal hobbies to colleges selectively MVDs? Good design?

  7. MVDs & 4NF Expanded example Apply(SSN,cName,date,major,hobby) Reveal hobbies to colleges selectively Apply once to each college May apply to multiple majors

  8. MVDs & 4NF Trivial Multivalued Dependency Nontrivial MVD

  9. MVDs & 4NF Rules for Multivalued Dependencies FD-is-an-MVD rule

  10. MVDs & 4NF Rules for Multivalued Dependencies Intersection rule Transitive rule

  11. MVDs & 4NF Fourth Normal Form Relation R with MVDs is in 4NF if: For each nontrivialA B,Ais a key

  12. MVDs & 4NF Fourth Normal Form Relation R with MVDs is in 4NF if: For each nontrivialA B,Ais a key

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

  14. MVDs & 4NF 4NF Decomposition Example #1 Apply(SSN,cName,hobby)

  15. MVDs & 4NF 4NF Decomposition Example #2 Apply(SSN,cName,date,major,hobby)

  16. MVDs & 4NF Relational design • Functional dependencies & Boyce-Codd Normal Form • Multivalued dependences & Fourth Normal Form

More Related