Relational Database Design Theory

1. Relational Database Design Theory Lecture 6

2. Relational Database Design • Features of Good Relational Design • First Normal Form • Decomposition Using Functional Dependencies • Functional Dependency Theory • Algorithms for Functional Dependencies and Dependency preserving Decompositions • BCNF and 3D Normal Form • Decomposition Using Multivalued Dependencies and 4th Normal Form • Database Design Process • Modeling Temporal Data

3. University Schema • Classroom (bldg, rid, capacity) • Department (dname, bldg, budget) • Course (cid, title, dname, credits) • Instructor (ID, name, dname, salary) • Section (cid, sid, semester, year, bldg, rid, timeslotId) • Teaches (ID, cid, sid, semester, year) • Student (ID, name, dname, totCredit) • Takes (ID, cid, sid semester, year, grade) • Advisor (stID, inID) • Timeslot (tid, day, startH, startM, endH, endM) • Prereq (cid, prereqID)

4. Combine Schemas? • Suppose we combine instructor and department into inst_dept as shown in a table below • Result is possible repetition of information (see CS department) • Furthermore if a new department is created but staff is not hired yet, nulls must be introduced

5. A Combined Schema Without Repetition • Consider combining relations • sec_class(sec_id, building, room_number) and • section(course_id, sec_id, semester, year) into one relation • section(course_id, sec_id, semester, year, building, room_number) • No repetition in this case Why?

6. A Combined Schema Without Repetition • Consider combining loan_branch and loan loan_amt_br = (loan_number, amount, branch_name) • No repetition (as suggested by example below) Why?

7. What About Smaller Schemas? • Suppose we had started with inst_dept. How would we know to split up (decompose) it into instructor and department? • Denote as a functional dependency: dnamebuilding, budget • In inst_dept, because dname is not a candidate key, the building and budget of a department may have to be repeated. • This indicates the need to decompose inst_dept • Not all decompositions are good. Suppose we decomposeemployee(ID, name, street, city, salary) into employee1 (ID, name) employee2 (name, street, city, salary) • The next slide shows how we lose information -- we cannot reconstruct the original employee relation -- and so, this is a lossy decomposition.

8. A Lossy Decomposition

9. Example of Lossless-Join Decomposition • Lossless join decomposition • Decomposition of R = (A, B, C) R1 = (A, B) R2 = (B, C) A B C A B B C   1 2 A B   1 2 1 2 A B r B,C(r) A,B(r) A B C A (r) B (r)   1 2 A B

10. First Normal Form • Domain is atomic if its elements are considered to be indivisible units • Examples of non-atomic domains: • Set of names, composite attributes • Identification numbers like CS101 that can be broken up into parts • A relational schema R is in first normal form if the domains of all attributes of R are atomic • Non-atomic values complicate storage and encourage redundant (repeated) storage of data • Example: Set of accounts stored with each customer, and set of owners stored with each account • We assume all relations are in first normal form

11. First Normal Form • Atomicity is actually a property of how the elements of the domain are used. • Example: Strings would normally be considered indivisible • Suppose that students are given roll numbers which are strings of the form CS0012 or EE1127 • If the first two characters are extracted to find the department, the domain of roll numbers is not atomic. • Doing so is a bad idea: leads to encoding of information in application program rather than in the database.

12. Goal — Devise a Theory for the Following • Decide whether a particular relation R is in “good” form. • In the case that a relation R is not in “good” form, decompose it into a set of relations {R1, R2, ..., Rn} such that • each relation is in good form • the decomposition is a lossless-join decomposition • Our theory is based on: • functional dependencies • multivalued dependencies

13. Functional Dependencies • Constraints on the set of legal relations. • Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. • A functional dependency is a generalization of the notion of a key.

14. Functional Dependencies (Cont.) • Let R be a relation schema   R and   R • The functional dependency  holds onR if and only if for any legal relations r(R), whenever any two tuples t1and t2 of r agree on the attributes , they also agree on the attributes . That is, t1[] = t2 []  t1[ ] = t2 [ ] • Example: Consider r(A,B ) with the following instance of r. • On this instance, AB does NOT hold, but BA does hold. • 4 • 1 5 • 3 7

15. Functional Dependencies (Cont.) • Let R be a relation schema   R and   R • The functional dependency  holds onR if and only if for any legal relations r(R), whenever any two tuples t1and t2 of r agree on the attributes , they also agree on the attributes . That is, t1[] = t2 []  t1[ ] = t2 [ ] • Example: Consider r(A,B ) with the following instance of r. • On this instance, AB does NOT hold, but BA does hold. • 4 • 1 5 • 3 7

16. Functional Dependencies • Let R(A1, A2, ….Ak) be a relational schema; X and Y are subsets of {A1, A2, …Ak}. We say that X->Y, if any two tuples that agree on X, then they also agree on Y. • Example: Student(SSN,Name,Addr,subjectTaken,favSubject,Prof) SSN->Name SSN->Addr subjectTaken->Prof Assign(Pilot,Flight,Date,Departs) Pilot,Date,Departs->Flight Flight,Date->Pilot

17. Functional Dependencies • No need for FD’s with more than one attribute on right side. But it maybe convenient: SSN->Name SSN->Addr combine into: SSN-> Name,Addr • More than one attribute on left is important and we may not be able to eliminate it. Flight,Date->Pilot

18. Functional Dependencies • A functional dependency X->Y is trivial if it is satisfied by any relation that includes attributes from X and Y • E.g. • customer-name, loan-number customer-name • customer-name customer-name • In general,   is trivial if   

19. Closure of a Set of Functional Dependencies • Given a set F set of functional dependencies, there are certain other functional dependencies that are logically implied by F. • E.g. If A  B and B  C, then we can infer that A  C • The set of all functional dependencies logically implied by F is the closure of F. • We denote the closure of F by F+.

20. Closure of a Set of Functional Dependencies • An inference axiom is a rule that states if a relation satisfies certain FDs, it must also satisfy certain other FDs • Set of inference rules is sound if the rules lead only to true conclusions • Set of inference rules is complete, if it can be used to conclude every valid FD on R • We can find all ofF+by applying Armstrong’s Axioms: • if   , then   (reflexivity) • if  , then    (augmentation) • if  , and   , then   (transitivity) • These rules are • sound and complete

21. Example • R = (A, B, C, G, H, I)F = { A BA CCG HCG IB H} • some members of F+ • A H • by transitivity from A B and B H • AG I • by augmenting A C with G, to get AG CG and then transitivity with CG I

22. To compute the closure of a set of functional dependencies F: F+ = Frepeatfor each functional dependency f in F+ apply reflexivity and augmentation rules on fadd the resulting functional dependencies to F+for each pair of functional dependencies f1and f2 in F+iff1 and f2 can be combined using transitivitythen add the resulting functional dependency to F+until F+ does not change any further Procedure for Computing F+

23. Closure of Attribute Sets • Given a set of attributes a, define the closureof aunderF (denoted by a+) as the set of attributes that are functionally determined by a under F:ais in F+   a+ • Algorithm to compute a+, the closure of a under F result := a;while (changes to result) do for each in F do begin if  result then result := result end

24. There are several uses of the attribute closure algorithm: Testing for superkey: To test if  is a superkey, we compute +, and check if + contains all attributes of R. Testing functional dependencies To check if a functional dependency    holds (or, in other words, is in F+), just check if   +. That is, we compute + by using attribute closure, and then check if it contains . Is a simple and cheap test, and very useful Computing closure of F For each   R, we find the closure +, and for each S  +, we output a functional dependency   S. Uses of Attribute Closure

25. Example of Attribute Set Closure • R = (A, B, C, G, H, I) • F = {A B, A C, CG H, CG I, B H} • (AG)+ 1. result = AG 2. result = ABCG (A C and A  B) 3. result = ABCGH (CG H and CG  AGBC) 4. result = ABCGHI (CG I and CG  AGBCH) • Is AG a key? • Is AG a super key? • Does AG R? == Is (AG)+  R • Is any subset of AG a superkey? • Does AR? == Is (A)+  R • Does GR? == Is (G)+  R

26. Extraneous Attributes • Consider a set F of functional dependencies and the functional dependency   in F. • Attribute A is extraneous in  if A   and (F – {})  {( – A) } logically implies F,or A  and the set of functional dependencies (F – {})  {(– A)} logically implies F. • Example: Given F = {AC, ABC } • B is extraneous in AB C because {AC} logically implies ABC, A C. • Example: Given F = {AC, ABCD} • C is extraneous in ABCD since {AB  D,A C} implies ABC

27. Testing if an Attribute is Extraneous • Consider a set F of functional dependencies and the functional dependency   in F. • To test if attribute A   is extraneousin • compute ({} – A)+ using the dependencies in F – { }  {( – A) } 2. check that ({} – A)+ contains A; if it does, A is extraneous • To test if attribute A  is extraneous in  • compute + using only the dependencies in F’ = (F – {})  {(– A)}, • check that + contains A; if it does, A is extraneous

28. Canonical Cover • Sets of functional dependencies may have redundant dependencies that can be inferred from the others • Eg: A  C is redundant in: {A  B, B  C, A  C} • Parts of a functional dependency may be redundant • E.g. : {A  B, B  C, A  CD} can be simplified to {A  B, B  C, A  D} • E.g. : {A  B, B  C, AC  D} can be simplified to {A  B, B  C, A  D} • A canonical cover of F is a “minimal” set of functional dependencies equivalent to F, having no redundant dependencies or redundant parts of dependencies

29. Canonical Cover(Formal Definition) • A canonical coverfor F is a set of dependencies Fc such that • F logically implies all dependencies in Fc, and • Fclogically implies all dependencies in F, and • No functional dependency in Fc contains an extraneous attribute, and • Each left side of functional dependency in Fcis unique.

30. Canonical CoverComputation • To compute a canonical cover for F:repeatUse the union rule to replace any dependencies in F11 and 11 with 112 Find a functional dependency  with an extraneous attribute either in  or in  If an extraneous attribute is found, delete it from until F does not change

31. Example of Computing a Canonical Cover • R = (A, B, C)F = {A BC B C A BABC} • Combine A BC and A B into A BC • A is extraneous in ABC • Set is now {A BC, B C} • C is extraneous in ABC • Check if A C is logically implied by A B and the other dependencies • Yes: using transitivity on A B and B  C. • The canonical cover is: A B B C

32. Decomposition • All attributes of an original schema (R) must appear in the decomposition (R1, R2): R = R1  R2 • Lossless-join decomposition.For all possible relations r on schema R r = R1 (r) R2 (r) • A decomposition of R into R1 and R2 is lossless join if and only if at least one of the following dependencies is in F+: • R1 R2R1 • R1 R2R2

33. Normalization Using Functional Dependencies • When we decompose a relation schema R with a set of functional dependencies F into R1, R2,.., Rn we want • Lossless-join decomposition: Otherwise decomposition would result in information loss. • Dependency preservation: Let Fibe the set of dependencies F+ that include only attributes in Ri. (F1 F2  …  Fn)+ = F+ .

34. Example • R = (A, B, C)F = {A B, B C) • Can be decomposed in two different ways • R1 = (A, B), R2 = (B, C) • Lossless-join decomposition: R1  R2 = {B} and B BC • Dependency preserving • R1 = (A, B), R2 = (A, C) • Lossless-join decomposition: R1  R2 = {A} and A  AB • Not dependency preserving (cannot check B C without computing R1 R2)

35. To check if a dependency  is preserved in a decomposition of R into R1, R2, …, Rn we apply the following simplified test (with attribute closure done w.r.t. F) result = while (changes to result) dofor eachRiin the decompositiont = (result  Ri)+  Riresult = result  t If result contains all attributes in , then the functional dependency    is preserved. We apply the test on all dependencies in F to check if a decomposition is dependency preserving This procedure takes polynomial time, instead of the exponential time required to compute F+and(F1 F2  …  Fn)+ Testing for Dependency Preservation

36. Boyce-Codd Normal Form A relation schema R is in BCNF with respect to a set F of functional dependencies if for all functional dependencies in F+ of the form , where  R and  R,at least one of the following holds: •  is trivial (i.e.,  ) •  is a superkey for R

37. Boyce-Codd Normal Form Example schema not in BCNF: instr_dept (ID, name, salary, dname, building, budget ) Functional dependencies: ID → name,salary,dname dnamebuilding, budget holds on instr_dept, but dname is not a superkey It is not in BCNF It preserves functional dependencies It is lossless

38. Example • R = (A, B, C)F = {AB B  C}Key = {A} • R is not in BCNF • Decomposition R1 = (A, B), R2 = (B, C) • R1and R2 in BCNF • Lossless-join decomposition • Dependency preserving

39. Testing for BCNF • To check if a non-trivial dependency causes a violation of BCNF 1. compute + (the attribute closure of ), and 2. verify that it includes all attributes of R • Using only F is incorrect when testing a relation in a decomposition of R • E.g. Consider R (A, B, C, D), with F = { A B, B C} • Decompose R into R1(A,B) and R2(A,C,D) • Neither of the dependencies in F contain only attributes from (A,C,D) so we might be mislead into thinking R2 satisfies BCNF. • In fact, dependency AC in F+ shows R2 is not in BCNF.

40. BCNF Decomposition Algorithm result := {R};done := false;compute F+;while (not done) do if (there is a schema Riin result that is not in BCNF)then beginlet   be a nontrivial functional dependency that holds on Ri such that  Riis not in F+, and   = ;result := (result – Ri )  (Ri – )  (,  );end else done := true; Each Riis in BCNF, and decomposition is lossless-join.

41. Example of BCNF Decomposition • R = (branch-name, branch-city, assets, customer-name, loan-number, amount) F = {branch-name assets branch-city loan-number amount branch-name} Key = {loan-number, customer-name} • Decomposition • R1 = (branch-name, branch-city, assets) • R2 = (branch-name, customer-name, loan-number, amount) • R3 = (branch-name, loan-number, amount) • R4 = (customer-name, loan-number) • Final decomposition R1, R3, R4

42. BCNF and Dependency Preservation It is not always possible to get a BCNF decomposition that is dependency preserving • R = (A, B, C)F = {AB  C C  B}Two candidate keys = ABand AC • R is not in BCNF • Any decomposition of R will fail to preserve AB C

43. Goals of Normalization • Let R be a relation scheme with a set F of functional dependencies. • Decide whether a relation scheme R is in “good” form. • In the case that a relation scheme R is not in “good” form, decompose it into a set of relation scheme {R1, R2, ..., Rn} such that • each relation scheme is in good form • the decomposition is a lossless-join decomposition • Preferably, the decomposition should be dependency preserving.

44. Third Normal Form: Motivation • There are some situations where • BCNF is not dependency preserving, and • efficient checking for FD violation on updates is important • Solution: define a weaker normal form, called Third Normal Form. • FDs can be checked on individual relations without computing a join. • There is always a lossless-join, dependency-preserving decomposition into 3NF.

45. Third Normal Form • A relation schema R is in third normal form (3NF) if for all:  in F+ at least one of the following holds: • is trivial (i.e.,  ) •  is a superkey for R • Each attribute A in  –  is contained in a candidate key for R. • If a relation is in BCNF it is in 3NF (since in BCNF one of the first two conditions above must hold). • Third condition is a minimal relaxation of BCNF to ensure dependency preservation.

46. Third Normal Form • Example • R = (A,B,C)F = {AB C, C B} • Two candidate keys: AB and AC • R is in 3NF AB C AB is a superkeyC B B is contained in a candidate key • BCNF decomposition has (AC) and (BC) • Testing for AB C requires a join

47. Testing for 3NF • Use attribute closure to check for each dependency   , if  is a superkey. • If  is not a superkey, we have to verify if each attribute in  is contained in a candidate key of R • this test is rather more expensive, since it involve finding candidate keys • testing for 3NF has been shown to be NP-hard • However, decomposition into third normal form can be done in polynomial time

48. 3NF Decomposition Algorithm Let Fcbe a canonical cover for F;i := 0;for each functional dependency in Fcdo if none of the schemas Rj, 1  j  i contains then begini := i + 1;Ri := endif none of the schemas Rj, 1  j  i contains a candidate key for Rthen begini := i + 1;Ri := any candidate key for R;end return (R1, R2, ..., Ri)

49. 3NF Decomposition Algorithm • Decomposition algorithm ensures: • each relation schema Riis in 3NF • decomposition is dependency preserving and lossless-join

50. Example • Relation schema: R(A, B, C, D) • The functional dependencies for this relation schema are:C AD AB C • The keys are: {BC}, {AB}