Relational Database Design Algorithms and Further Dependencies

1. Relational Database Design Algorithms and Further Dependencies 　　教育輪講 Chapter16 2011/05/26 cindy

2. Outline • Further topics in Functional Dependencies: Inference Rules, Equivalence, and Mining Cover • Properties of Relational Decompositions • Algorithms for Relational Database schema Design • About Nulls, Dangling Tuples and Alternative Relational Designs • Further Discussion of Multivalued Dependencies and 4NF • Other Dependencies and Normal Forms • Summary

3. Goal of this chapter • Discuss further the multivalued dependency (MVD) concept we introduced in Chapter 15 • Briefly point out other types of dependencies that have been identified.

4. Further topics in Functional Dependencies • Inference Rules for Functional Dependencies • Equivalence of Sets of Functional Dependencies • Minimal Sets of Functional Dependencies

5. Inference Rules for Functional Dependencies • Closure? Definition: Formally, the set of all dependencies that include F as well as all dependencies that can be inferred from F is called the closure of F, it is denoted by F+ . F: the set of functional dependencies specified on relation schema R. Dept_no Mgr_ssn Mgr_phone

6. Inference Rules for Functional Dependencies • Additional FDs example We specify the set F of obvious FD on the relation schema in Figure15.3(a) F ={Ssn {Ename, Bdate, Address, Dnumber},Dnumber {Dname, Dmgr_ssn}} Ssn{Dname, Dmgr_ssn},SsnSsn, DnumberDname

7. Inference Rules for Functional Dependencies • Inference rules: to infer new dependencies from a given set of dependencies. • We use the notation F|=XY to denote FD XY is inferred from F • The FD {X,Y} Z is abbreviated to XYZ • The FD {X,Y,Z} {U,V} is abbreviated to XYZUV • Six rules • IR1(reflexive rule): if X⊇Y, then XY • IR2 (augmentation rule): {XY}|=XZYZ • IR3 (transitive rule): {XY, YZ}|=XZ • IR4 (decomposition rule): {XYZ}|=XY • IR5 (union, ore additive rule): {XY, XZ}|=XYZ • IR6 (pseudotransitiverule):{XY, WYZ}|=WXZ Armstrong’s inference rules Additional rules

8. Inference Rules for Functional Dependencies • IR1 through IR3 are sound and complete • Sound • they generate only functional dependencies in the closure of a set of functional dependencies (denoted as F+) when applied to that set (denoted as F). • Complete • repeated application of these rules will generate all functional dependencies in the closure F+. • Definition: • For each set of attributes X, we determine the set X+ of attributes that are functionally determined by X based on F;X+ is called the closure of X under F.

9. Inference Rules for Functional Dependencies • Algorithm of closure(16.1) • Input: A set of FDs on a relation schema R, and a set of attributes X, which is a subset of R. • X+ :=X; • repeat • oldX+ :=X+ ; • for each functional dependency YZ in F do • if X+ ⊇Y then X+ :=X+∪Z; • Until (X+ =oldX+);

10. Example of closure • {Ssn,Pnumber}+ ? • A set of attribute • SSn,Pnumber, Hours, Ename,Fname,Plocation • A set of FDs • FD1, FD2,FD3 X+=X={Ssn,Pnumber} FD1 Ssn,PnumberHours add Hours to X X={Ssn, Pnumber, Hours} FD2 SsnEname, add Ename to X X={Ssn,Pnumber,Hours,Ename} FD3 PnumberPname, Plocation, add Pname, Plocation to X X={Ssn,Pnumber,Hours,Ename,Pname,Plocation} Cannot add any more {Ssn, Pnumber}+=X

11. Equivalence of Sets of Functional Dependencies • Definition1: • A set of FDs F is said to cover another set of FDs E if every FD in E is also in F+; if every dependency in E can be inferred from F, we can say E is covered by F • Definition 2: • Two sets of FDs E and F are equivalent if E+=F+. E is equivalent to F if both E covers F and F covers E

12. E={AB, BC, ACD} F={AB, BC, AD} • All FDs in F can be inferred using FDs in E vice versa A+using F= A B C D . AA;AB;AC;AD A+using E= A B C D . AA;AB;AC;AD B+using F= B C . BB;BC B+using E= B C. BB;BC AC+using F=A B C D. ACA,ACB,ACC,ACD E and F are equivalent

13. Minimal sets of Functional Dependencies • A minimal cover of a set of FDs E is a set of FDs F that satisfies the property that every dependency in E is in the closure of F+of F. • A minimal set of dependencies as being a set of dependencies in a standard or canonical from and no redundancies. • Definition • A minimal cover of a set of FDs E is a minimal set of dependencies that is equivalent to E.

14. Set F:=E. Replace each FD X{A1,A2,…An} in F by the n FD XA,XA2,…XAn. For each FD XA in F for each attribute B that is an element of X If {F-{XA}}∪{(X-{B}) A}} is equivalent to F then replace XA with (X-{B}) A in F. 4. For each remaining FD XA in F if {F-{XA}} is equivalent to F, then remove XA from F. • Algorithm 16.2 Finding a Minimal Cover F for a Set of FD E • Input: A set of FD E. Set of FDs be E: {BA, DA, ABD}, find the minimal cover of E. BD,DA BD,DABA By augmenting with B BA BBAB, or BAB ABD DA BD

15. Properties of Relational Decompositions • Relation Decomposition and Insufficiency of Normal Forms • Universal relation schema • A relation schema R = {A1, A2, …, An} that includes all the attributes of the database. • Universal relation assumption • Every attribute name is unique

16. Properties of Relational Decompositions • Relation Decompositions • Decomposition • The process of decomposing the universal relation schema R into a set of relation schemas D = {R1,R2, …, Rm} that will become the relational database schema by using the functional dependencies. D is called a decomposition of R. • Attribute preservation condition • Each attribute in R will appear in at least one relation schema Riin the decomposition so that no attributes are lost. So, R1∪R2∪...,∪Rm =R

17. Properties of Relational Decompositions • Insufficiency of Normal Forms • Another goal of decomposition is to have each individual relation Ri in the decomposition D be in BCNF or 3NF. • Additional properties of decomposition are needed to prevent from generating spurious tuples.

18. Definition of Projection • Given a set of dependencies F on R, the projection of F on Ri ,denoted by πRi(F) where Ri is a subset of R, is the set of dependencies XY in F+ such that the attributes in X∪ Y are all contained in Ri • Hence, the projection of F on each relation schema Riin the decomposition D={Ri,R2…Rm} is the set of functional dependencies in F+ . The closure of F, such that all their left-and right-hand –side attributes are in Ri • Informally, we want each dependency in R to be directly from one of the dependencies Ri or can be inferred from dependencies in some Ri, This is dependency preservation condition.

19. Projection Example: R=EMP_DEPT • Relation R (Ssn, Ename, Bdate, Address, Dnumber, Dname,Dmgr_ssn)’s F is as follows. F={Ssn{Ename, Bdate, Address, Dnumber}, Dnumber{Dname, Dmgr_ssn}} We will have F above plus the following in F+ SsnDnumber Ssn{Dname,Dmgr_ssn} DnumberDname DnumberDmgr_ssn Ssnevery_combination_else For subset relation R1(Dnumber, Dname,Dmgr_ssn), the projection of F on R1 is the subset of FDs in F+such that left and right hand side attribtues of the FDs are in R1 . So, πR1(F) = { Dnumber {Dname, Dmgr_ssn}, DnumberDname, DnumberDmgr_ssn }

20. Dependency Preservation Property of a Decomposition • A decomposition D = {R1, R2, ..., Rm} of R is dependency-preserving with respect to F if the union of the projections of F on each Ri in D is equivalent to F; that is ((R1(F)) υ . . . υ (Rm(F)))+ = F+ • If a decomposition is not dependency-preserving, some dependency is lost in the decomposition. Example(p.529&p.527) • Claim1: it is always possible to find a dependency-preserving decomposition D with respect to F such that each relation Riin D is in 3NF.

21. Properties of Relational Decompositions • Nonadditive (Lossless ) Join Property of a Decomposition • It ensures that no spurious tuples are generated when a NATURAL JOIN operation is applied to the relations resulting from the decomposition. Relaiton R (A,B,C) Two projections R1(A,B), R2(B,C).R’ =R1*R2 ? R1 A B R’ A B C R A B C a 1 a 1 x a 1 xb 1 a 1 y b 1 yc 2 b 1 x c 2 z R2 B C b 1 y 1 xc 2 z 1 y 2 z Suprious tuples

22. Properties of Relational Decompositions • Nonadditive (Lossless ) Join Property of a Decomposition • Basic definition of loss-less join: A decomposition D = {R1, R2, ..., Rm} of R has the lossless (nonadditive) join property with respect to the set of dependencies F on R if, for every relation r of R that satisfies F, the following holds, where * is the natural join of all the relations in D: * ( R1(r), ..., Rm(r)) = r • The word loss in lossless refers to loss of information, not to loss of tuples.

23. Example lossless-join • Emp_PROJ • F= {SsnEname,Pnumber{Pname, Plocation}, {Ssn, Pnumber} Hours} Ssn Ename Pnumber Pname Plocation Hours R2 R1 Pnumber Pname Plocation Ssn Ename R3 Pnumber Hours Ssn Lossless join

24. Properties of Relational Decompositions Testing algorithm(16.3) input: A relation R, a decomposition D = {R1, R2,..., Rm} of R, and a set F of function dependencies. 1. Create an initial matrix S with one row ifor each relation Ri in D, and one column j for each attribute Aj in R. 2. Set S(i, j) := bijfor all matrix entries. 3. For each row irepresenting relation schema RiDo {for each column j representing Ajdo {if relation Ri includes attribute Ajthen set S(i, j) := aj;} Repeat the following loop until a complete loop execution results in no changes to S.

25. Testing algorithm(cont.) 4. Repeat the following loop until a complete loop execution results in no changes to S. {for each function dependency X  Y in F do for all rows in S which have the same symbols in the columns corresponding to attributes in X do {make the symbols in each column that correspond to an attribute in Y be the same in all these rows as follows: if any of the rows has an “a” symbol for the column, set the other rows to the same “a” symbol in the column. If no “a” symbol exists for the attribute in any of the rows, choose one of the “b” symbols that appear in one of the rows for the attribute and set the other rows to that same “b” symbol in the column;}} 5. If a row is made up entirely of “a” symbols, then the decompo- sition has the lossless join property; otherwise it does not.

26. R1 b16 b15 b12 b11 b13 R1 b14 b21 b22 R2 b23 b25 R2 b26 b24 b32 b33 R3 b35 b31 b36 R3 b34 A6 Hours A1 Ssn A2 Ename A3 Pnumber A4 Pname A5 Plocation a1 a2 b13 b14 b15 b16 b21 b22 a3 a4 a5 b26 a1 b32 a3 b34 b35 a6 R1 =EMP={Ssn,Ename} R2 =PROJ={Pnumber,Pname, Plocation} R3 =WORKS_ON={Ssn, Pnumber, Hours}

27. R1 R1 R2 R2 R3 R3 SsnEname Ssn Ename b13 b15 b14 a2 b16 a1 a3 a4 b26 a5 b22 b21 a3 a2 a1 b34 b35 a6 Pnumber{Pname, Plocation} Plocation Pnumber Pname b13 b15 b14 a2 b16 a1 a3 a4 b26 a5 b22 b21 a3 a2 a1 a4 a5 a6

28. EMP_PROJ R1 R2 R3 R1 =EMP={Ssn,Ename} R2 =PROJ={Pnumber,Pname, Plocation} R3 =WORKS_ON={Ssn, Pnumber, Hours}

29. R1 *R3=R13 R13*R2= Equals with the results in Page 27!!

30. Properties of Relational Decompositions • Testing Binary Decomposition for the Nonadditive Join Property • Binary decomposition: decomposition of a relation R into two relations • Property NJB (Nonadditive Join Test for Binary Decompositions): A decomposition D={R1,R2} of R has the lossless join property with respect to a set of functional dependencies F on R if and only if either • The FD((R1∩R2) (R1-R2)) is in F+, or • The FD((R1∩R2) (R2-R1)) is in F+

31. Binary Decomposition Example Ename Ssn Bdate Address Dnumber Dname Ename Ssn Bdate Address Dnumber Dmgr_ssn FD1 3NF normalization ED2 ED1 Dmgr_ssn Dnumber Dname ED1∩ ED2 = {Dnumber} ED2 - ED1 = {Dname, Dmgr_ssn} And ED1∩ ED2 ED2 –ED1, or Dnumber {Dname, Dmgr_ssn} (FD1) So, the binary decomposition is nonadditive

32. Properties of Relational Decompositions • Successive Nonadditive Join Decompositions • Claim2 (Preservation of Nonadditivity in Successive Decompositions). • If a decomposition D={R1,R2,…Rm} of R has the nonadditive join property with respect to a set of functional dependencies F on R • And if a decomposition Di = {Q1, Q2, ..., Qk} of Ri has the lossless (non-additive) join property with respect to the projection of F on Ri, • Then the decomposition D2={R1,R2,…Ri-1,Q1,Q2,…Qk} of R has the non-additive join property with respect to F

33. Algorithms for Relational Database Schema Design • Dependency-Preserving Decomposition into 3NF Schemas • Create a dependency-preserving decomposition D={R1,R2,…Rm} of a universal relation R based on a set of FD F, each Ri in D is 3NF. • Next algorithm (16.4)shows Relational Synthesis into 3NF with Dependency Preservation

34. Input: A universal relation R and a set of FDs F on the • attributes of R. 1. Find a minimal cover G for F(algorithm16.2) 2. For each left-hand-side X of a FD that appears in G, create a relation schema in D with attributes {X∪{A1}∪{A2}∪{Ak}},where XA1,…XA2,XAk are the only dependencies in G with X as the left-hand-side; 3. Place any remaining attributes (that have not been placed in any relation) in a single relation schema to ensure the attribute preservation property. relational synthesis algorithm.

35. Example of Algorithm 16.4 • U (Emp_ssn, Pno,Esal, Ephone,Dno,Pname,Plocation) • Dependencies are present: • FD1: Emp_ssn{Esal, Ephone,Dno} • FD2: Pno{Pname, Plocation} • FD3:Emp_ssn,Pno{Esal, Ephone,Dno, Pname, Plocation} {Emp_ssn,Pno} represent a key of the universal relation. By using algorithm16.2, Emp_ssn is redundant in Emp_ssn, PnoPname, Plocation. Pno is redundant in Emp_ssn, PnoEsal, Ephone,Dno Hence, the minimal cover G consist FD1 and FD2 only {Emp_ssnEsal,Ephone,Dno; PnoPname,Plocation} By applying algorithm 16.4, get a 3NF design consisting of two relationss with keys Emp_ssn and Pno: R1(Emp_ssn, Esal, Ephone,Dno) R2(Pno,Pname,Plocation)

36. Dependency-Preserving Decomposition into 3NF Schemas • Lossy design. • Two relations have lost the original information. This algorithm preserve the original dependencies but make no guarantee of preserving all the information. • Claim 3: Every relation schema created by Algorithm 16.4 is in 3NF.

37. Nonadditive Join Decomposition into BCNF Schemas • Decompose a universal relation schema R={A1,A2,…An} into a decomposition D ={R1, R2,…Rm} such that each Ri is in BCNF and the decomposition D has the lossless join property with respect to F. • Algorithm 16.5 Relational Decomposition into BCNF with Nonadditive Join Property Input: A universal relation R and a set of FDs on the attributes of R. Set D:= {R}; While there is a relation schema Q in D that is not in BCNF do { choose a relation schema Q in D that is not in BCNF; find a FD XY in Q that violates BCNF; replace Q in D by two relation schema (Q-Y) and (X∪Y); };

38. Example Relation Schema Q FD X(Area)Y(Country_name) Violates BCNF ! Replace Q by two relation Schema (Q-Y) and (X∪Y); X∪ Y Q-Y

39. Dependency-Preserving and Nonadditive Join Decomposition into 3NF Schemas • By now we know that it is not possible to have all three of the following: • Guaranteed nonlossy design (must, can’t be compromised) • Guaranteed dependency preservation (desirable, not a must) • All ralations in BCNF Next algorithm: achieve conditions 1 and 2 only guarantee 3NF

40. Dependency-Preserving and Nonadditive Join Decomposition into 3NF Schemas • Yield a decomposition D of R that does the following: • Preserves dependencies • Has the nonadditive join property • Is such that each resulting relation schema in the decomposition is in 3NF Algorithm 16.6 is preferred over Algorithm 16.4!!

41. Input: A universal relation R and a set of FDs F on the attributes of R. 1. Find a minimal cover G for F (Use Algorithm 16.2). 2. For each left-hand-side X of a functional dependency that appears in G,create a relation schema in D with attributes {X υ {A1} υ {A2} ... υ {Ak}}, where X A1, X  A2, ..., XAkare the only dependencies in G with X as left-hand-side (X is the key of this relation). 3. If none of the relation schemas in D contains a key of R, then create one more relation schema in D that contains attributes that form a key of R 4. Among decomposed relation schemas, if Ri is a subset of Rj, remove Ri Relational Synthesis into 3NF with Dependency Preservation and Nonadditice Join Property

42. Example • U (Emp_ssn, Pno,Esal, Ephone,Dno,Pname,Plocation) • Dependencies are present: • FD1: Emp_ssn{Esal, Ephone,Dno} • FD2: Pno{Pname, Plocation} • FD3:Emp_ssn,Pno{Esal, Ephone,Dno, Pname, Plocation} The minimal cover G holds as before. The second step produces relations R1 and R2 as before. In step3, we generate a relation corresponding to the key{ Emp_ssn, Pno}. Hence, the resulting design contains: R1 (Emp_ssn, Esal, Ephone, Dno) R2 (Pno, Pname, Plocation) R3 (Emp_ssn, Pno)

43. Further Discussion of Multivalued Dependencies and 4NF • Definition: • A multivalueddependency(MVD)X→→Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation state r of R: If two tuplest1 and t2 exist in r such that t1[X] = t2[X], then two tuplest3 and t4 should also exist in r with the following properties, where we use Z to denote (R 2 (XυY)): • t3[X] = t4[X] = t1[X] = t2[X]. • t3[Y] = t1[Y] and t4[Y] = t2[Y]. • t3[Z] = t2[Z] and t4[Z] = t1[Z]. • An MVD X →→Y in R is called a trivial MVD if (a) Y is a subset of X, or (b) XY = R.

44. Multivalued Dependencies • Inference Rules for Functional and Multivalued Dependencies • IR1 (reflexive rule for FDs): If X ⊇Y, then X→Y. • IR2 (augmentation rule for FDs): {X →Y} |= XZ →YZ. • IR3 (transitive rule for FDs): {X →Y, Y →Z}  X→Z. • IR4 (complementation rule for MVDs): {X →→Y} {X →→(R – (X  Y))}. • IR5 (augmentation rule for MVDs): If X →→Y and W⊇Z then WX →→YZ. • IR6 (transitive rule for MVDs): {X →→Y, Y →→Z}  X →→ (Z -Y). • IR7 (replication rule for FD to MVD): {X →Y}  X →→Y. • IR8 (coalescence rule for FDs and MVDs): If X →→Y and there exists W with the properties that (a) W Y is empty, (b) W →Z, and (c) Y ⊇Z, then X→Z.

45. Fourth Normal Form • Definition • A relation schema R is in 4NF with respect to a set of dependencies F if, for every nontrivial MVD X→→Y in F+, X is a superkey for R • Requires that either every multivalued dependency X→→ Y is trivial or for every nontrivial multivalued dependency X→→Y, X is a superkey • All the 4NF are BCNF, but not all the BCNF are 4NF

46. Nonadditive Join Decomposition into 4NF Realtions • Property NJB’ • The relation schema R1 and R2 form a nonadditive join decomposition of R with respect to a set F of functional and multivalued dependencies if and only if • (R1 ∩R2) →→ (R1 - R2) or by symmetry, if and only if • (R1∩R2) →→(R2 - R1)).

47. Algorithm 16.7 Relational Decomposition into 4NF Relations with Nonadditive Join Property Input: A universal relation R and a set of functional and multivalued dependencies F 1.Set D:={R}; 2.While there is a relation schema Q in D that is not in 4NF, do { choose a relation schema Q in D that is not in 4NF;; find a nontrivial MVD X→→Y in Q that violates 4NF; replace Q in D by two relation schemas (Q-Y) and (XY)}

48. Multivalued Dependency example 4NF 2MVDs in this relation: {course} →→ {book} and {course} →→{lecturer}

50. Other Dependencies and Normal Forms • Inclusion Dependencies • Template Dependencies • Functional Dependencies Based on Arithmetic Functions and Procedures • Domain-Key Normal Form