1 / 20

Relational Design Theory

Relational Design Theory. Functional Dependencies. Functional Dependencies. 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

belita
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 Functional Dependencies

  2. Functional Dependencies 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 Functional dependencies are generally useful concept • Data storage – compression • Reasoning about queries – optimization

  3. Functional Dependencies Example: College application info. Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority) Apply(SSN,cName,state,date,major)

  4. Functional Dependencies Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority) Suppose priority is determined by GPA Two tuples with sameGPAhave samepriority

  5. Functional Dependencies Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority) Two tuples with sameGPAhave samepriority

  6. Functional Dependencies Functional Dependency • Based on knowledge of real world • All instances of relation must adhere

  7. Functional Dependencies Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority)

  8. Functional Dependencies Apply(SSN,cName,state,date,major)

  9. Functional Dependencies Functional Dependencies and Keys • Relation with no duplicates • Suppose Ᾱ all attributes

  10. Functional Dependencies Trivial Functional Dependency Nontrivial FD Completely nontrivial FD

  11. Functional Dependencies Rules for Functional Dependencies Splitting rule Can we also split left-hand-side?

  12. Functional Dependencies Rules for Functional Dependencies Combining rule

  13. Functional Dependencies Rules for Functional Dependencies Trivial-dependency rules

  14. Functional Dependencies Rules for Functional Dependencies Transitive rule

  15. Functional Dependencies Closure of Attributes • Given relation, FDs, set of attributesᾹ • Find all B such that Ᾱ B

  16. Functional Dependencies Closure Example Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority) SSN sName,address,GPA GPA  priority HScode  HSname,HScity

  17. Functional Dependencies Closure and Keys IsᾹa key for R ? How can we find all keys given a set of FDs?

  18. Functional Dependencies Specifying FDs for a relation • S1 and S2 sets of FDs • S2 “follows from” S1 if every relation instance satisfying S1 also satisfies S2 How to test? Does A  B follow from S ?

  19. Functional Dependencies Specifying FDs for a relation Want:Minimal set of completely nontrivial FDs such that all FDs that hold on the relation follow from the dependencies in this set

  20. Functional Dependencies Functional dependencies are generally useful concept • Relational design by decomposition Functional dependencies  Boyce-Codd Normal Form • Data storage – compression • Reasoning about queries – optimization

More Related