1 / 17

Chapter 16: Relational Database Design and Further Dependencies

Chapter 16: Relational Database Design and Further Dependencies. TA: Zhe Jiang zhe@cs.umn.edu. Ref: Elmasri, Navathe, Fundamentals of Database Systems, 6th, Addison Wesley, ISBN-10: 0-13-608620-9. Outline. Big picture & motivation Simple case algorithm (part of 16.3.3) Formal algorithm

rocio
Download Presentation

Chapter 16: Relational Database Design and Further Dependencies

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. Chapter 16: Relational Database Design and Further Dependencies TA: Zhe Jiang zhe@cs.umn.edu Ref: Elmasri, Navathe, Fundamentals of Database Systems, 6th, Addison Wesley, ISBN-10: 0-13-608620-9.

  2. Outline • Big picture & motivation • Simple case algorithm (part of 16.3.3) • Formal algorithm • Basic concepts (16.1): • General case algorithm (16.3.3)

  3. Big Picture: Database Design Phases ER-Diagram Relational Tables Which choice is good? How to guarantee it? Formal Norm Theory

  4. Motivation We have: • Universal relational schema U(A1,A2, … An). • A set of functional dependencies (FDs) from domain knowledge. Question: How do we decompose U into sub-relations, so as to satisfy 3NF?

  5. Simple Case Decomposition Algorithm • Motivation: • Decompose universal relational schema into sub relations which satisfy 3NF • Properties: • Preserve dependencies (nonlossy design) • Non-additive join property (no spurious tuples) • Resulting relational schemas are in 3NF • Problem Definition: • Input: Universal Relation R and a set of functional dependencies F on the attributes of R • Output: Sub-relations, FDs. • Constraint: the three properties above

  6. Simple Case Decomposition Algorithm • Suppose the FD set given is already “good”minimal cover (defined later) • Approach: • For each LHS X in F, create a relation schemain D {X U {A1} U {A2} … U {Ak} }.where XAi only dependency with X as LHS. • If none of the relation schemas in D contains a key of R, create one relation with key. (How? Introduce later) • Eliminate redundant relations.

  7. Simple Case Decomposition Algorithm • Exercise: • Universal relation • FD: {PLC, LCAP, AC} • Q: Does it satisfy 1NF, 2NF, 3NF? • Q: How to decompose the relation to satisfy 3NF? • Solution: • R1(P,L,C); R2(L,C,A,P); R3(A,C) • Already contains key. • Remove redundant relations R1 and R3, final answer is R2(L,C,A,P).

  8. General Case Decomposition Algorithm • New info: Transform the given FD set into minimal cover • New info: If no key exists, find key of U, then create a relation contain key • We will introduce some basic concepts, then formal algorithm

  9. Basic Concept • Inference rules: One FD could infer another • trivial: IR1: IR1 (reflexive rule) • If X Y, then X Y. • non-trivial: IR2-IR4 • {XY} |= XZYZ • {XY, YZ} |= XZ • {XYZ} |=XY • Closure of set of dependencies • Closure of F: F+, set of all FDs could be inferred. • Use IR1 to IR3;

  10. Basic Concepts • Closure of left-hand-side under dependency set Algorithm 16.1 • Start: X+={X} • Grow X+ with new attributes determined by elements in X+ • Repeat 2 until can’t grow any more. Exercise: Given: F={XYZ, XW, WU, YV}, U(X,Y,Z,W,U,V) Find: X+ ?

  11. Basic Concepts • Equivalence of functional dependencies sets • Definition • Cover: F covers E if F+ contains E. • Equivalent FD sets: • Algorithm • Check if all left-hand-sides’ closures are same • Minimal Cover of dependency set F • definition: Can’t find subset that is equivalent to F

  12. Basic Concept • Minimal Cover of dependency set F • break down right-hand-side, X{A1,A2,…An} to XA1, XA2, …XAn • Try reduce size of LHS X in F, e.g. changing X into {X-B} still equivalent to F? • Try reduce unnecessary FD in F, e.g. remove XA in F, if result still equivalent to F. • Example: • F={PLCA, LCAP, AC} • What is “minimal cover” of F?

  13. Basic Concepts • Algorithm to find key of R&F • Start with K=R. • Find A in R such that (K-A)+ contain all attributes. • Repeat until size of K is as small as possible • Example: • U(Emp_ssn, Pno, Esal, Ephone, Dno, Pname, Plocation) • F={Emp_ssnEsal, Ephone, Dno; PnoPname, plocation}; • What is the key?

  14. Decomposition Algorithm: Exercise FD3 FD1: Property_id Lot#, County, Area FD2: Lot#, County Area, Property_id FD3: AreaCounty Simpler Version: F={PLCA, LCAP, AC} • What is the minimal cover G? • Decompose G

  15. Decomposition Algorithm Example • Simpler Version: • F={PLCA, LCAP, AC} • First Case: • Minimal cover GX: • F: {PL, PC, PA, LCA, LCP,AC} • Minimal cover GX: {PLC, LCAP, AC} • Design X: • 3. R1(P,L,C), R2(L,C,A,P), and R3(A,C) • 4. R2(L,C,A,P)

  16. Decomposition Algorithm Example • Simpler Version: • F={PLCA, LCAP, AC} • Second Case: • Minimal cover GX: • F: {PL, PC, PA, LCA, LCP,AC} • Minimal cover GX: {PLA, LCP, AC} • Design Y: • 3. S1(P,A,L), S2(L,C,P), and S3(A,C) • 4. No redundant relations.

  17. Exercise • Given: • Universal relation U(A,B,C,D,E,F,G,H,I,J) • Functional dependencies F={ {A,B}{C}, {B,D}{E,F}, {A,D}{G,H}, {A}{I}, {H}{J} }. • Decompose it into 3NF?

More Related