Design Theory

1. Design Theory Some of the slides on this topic were adapted from slides of Yehoshua Sagiv

2. Two Goals • To be able to determine whether or not a schema is “well-designed” (i.e., “good”) • This depends on constraints that we know hold in the world • Types of constraints: function dependencies, join dependencies, multi-valued dependencies • To be able to “fix” a “bad” schema

3. Determining whether a schema is “GOOD” Formal definition of a constraint (FD) Deriving implicit FDs Normal Forms (BCNF, 3NF) Fixing a “BAD” schema Decompositions and their properties Finding 3NF Decompositions Finding BCNF Decompositions

4. Intuitive Discussion

5. Example of a “Bad” SchemaIntuitive Example • Suppose that each department has a single head Why is this bad? 3 Reasons

6. Schema Problems • Waste of space • May cause database to become inconsistent when updating or inserting (examples?) • May not allow certain data to be stored (examples?)

7. Why is there duplication? • A department has one head, but many students • Information on head is stored for each student Student Department Head

8. Functional Dependancy • The constraint that a department has a single head is called a functional dependency, and is written as D → H Student Department Head

9. Function Dependencies: Semi-Formal • The functional dependency D → Hholds in a relation if every two rows that have the same D value, also have the same H value • Do D → Hand H → D hold in the relations below?

10. Functional Dependencies that Imply Functional Dependencies • We know that D → H holds • We know that S → Dholds • Must S → H also hold? Student Department Head

11. Function Dependencies with Several Fields • The functional dependency SC → G holds • The functional dependency S → G does not hold • The functional dependency C → G does not hold Student Course Grade

12. Several Fields on Both Sides • The functional dependency SC → G holds • Does the functional dependency SC → GC hold? Student Course Grade

13. Deducing Functional Dependencies • Given an instance of a relation, we cannot deduce which functional dependencies will always hold in the relation • Depends on knowledge of the world • We can deduce functional dependencies that do not hold in the instance of the relation

14. Using Functional Dependencies • 4 Steps: • Based on our knowledge of the world, define functional dependencies for a given schema • Determine which other functional dependencies are impliedfrom these • Determine how and if they create redundancy (i.e., duplication) • Decide how to fix the schema, if redundancy is created • We will study formal, mathematical characterizations for each of the problems above

15. Determining whether a schema is “GOOD” Formal definition of a constraint (FD) Deriving implicit FDs Normal Forms (BCNF, 3NF) Fixing a “BAD” schema Decompositions and their properties Finding 3NF Decompositions Finding BCNF Decompositions

16. Formal Definitions

17. Notation: Sets of Attributes • Formal notation for attributes and sets of attributes: • Letters at the beginning of the alphabet (e.g., A, B, C ...) denote a singleattribute • Letters at the end of the alphabet (e.g., X, Y, Z ...) denote sets of attributes • Shorthand notation for unions of sets of attributes • Use XYfor X U Y • Use ABC for {A, B, C} • Use XA for (fill in the answer here…)

18. Notation: Tuples and their Values • We uses, t, to denote rows (tuples) • We use t[X] to denote the set of values of t in the fields of X • We write s[X]=t[X] if they are equal on all corresponding values. Otherwise, s[X] ¹ t[X] Is t1[A] = t2[A] ? Is t1[B] = t2[B] ? t1 Is t1[AC] = t2[AC] ? t2 Is t1[AB] = t2[AB] ? t3 Is t2[AC] = t3[AC] ?

19. Formal Definition of a Functional Dependency (FD) • Let R be a schema of a relation, that contains the sets of attributes X and Y • Let r be an instance of R • Then, X! Y holds in r if for every two tuples s and t in r, • if s[X]=t[X] then s[Y]=t[Y] • Question: When does X! Y not hold in r?

20. Example • Which of the following functional dependencies hold in this relation? • AC → BA • ABD → CFE • AC → BF

21. Trivial Functional Dependencies

22. Trivial Functional Dependencies • A functional dependency X! Y is trivialif it holds in every (instance of every) relation that contains the attributes X and Y • Which of the following are trivial? • D → D • SD → S • S → SD

23. Proposition • Prove: A functional dependency X! Y is trivial if and only if Yµ X Can you fill in the proof?

24. Determining whether a schema is “GOOD” Formal definition of a constraint (FD) Deriving implicit FDs Normal Forms (BCNF, 3NF) Fixing a “BAD” schema Decompositions and their properties Finding 3NF Decompositions Finding BCNF Decompositions

25. “Implies” and “Can be Proven”נובע ויכיח

26. Goal • We want to be able to show statements such as: • Suppose that A! B and B! C hold in r. Then, A ! C must also hold in r. • For this purpose we must have a systematic method to “prove” the correctness of functional dependencies, given that other dependencies are known to hold • We would like both the following to hold: • We would only like to prove things that are correct • We would like to be able to proveeverything that is correct

27. Provable and Correct • Provable and correct are two distinct terms • In general, depending on the proof system, i.e., on the set of rules available for our proofs • it may be possible to prove things that are incorrect • It may not be possible to prove things that are correct • We give some intuition behind these notations…

28. Follows נובע • Correct refers to what is actually possible • Suppose that we have a triangle, and we are given the following information about its angles 80o What is the correct size of this angle?or, in other words,What follows from the given information? 50o

29. Provable יכיח • Provable refers to what can be proven • Depends on the proof system • Example proof system 1:Sum of angles is always 180o 80o What can we prove about the size of this angle? 50o

30. Provable יכיח • Provable refers to what can be proven • Depends on the proof system • Example proof system 2:Sum of angles is always 170o 80o What can we prove about the size of this angle? 50o

31. Sound Proof System • A proof system is sound if everything that can be proven using the proof system is correct, i.e., actually follows • Is proof system 1 sound? • Is proof system 2 sound?

32. Complete Proof System • A proof system is complete if everything that is correct (i.e., that actually follows) can be proven • Is proof system 1 complete with respect to determining angle sizes? • Is proof system 2 complete with respect to determining angle sizes? 130o

33. Think About It • What is the simplest proof system that is sound? • What is the simplest proof system that is complete? • Can a proof system be sound and not complete? • Can a proof system be complete and not sound? We now return to functional dependencies and discuss the notions of יכיח and נובע in this setting

34. Implication of FDs • Example of a kind of questions of interest: • Suppose that A! B and B! C hold in r, what other functional dependencies must hold? • Formally, let F be a set of functional dependencies. We write F² X! Y if, for all instances r, whenever all the functional dependencies in F hold in r, then also X! Y holds in r • We say: F נובע מ-X! Y

35. Implication of FDs (cont) • In order to show that F2 X! Y, we must find a relation in which F holds, but X! Y does not hold • Example: Which of the following proves that A! B, C! B 2 C! A?

36. You Try It • Prove that {AB® BC, C® B} 2 A® C

37. How can we derive FDs? • Given F and X! Y, how can we show that • F2 X! Y ? • F² X! Y ? • Why is the second problem so difficult? • We will present a proof system that we will use to answer the above questions • Note: We will have to show that this proof system is sound and complete! Why? You answer this question

38. A Proof System: Armstrong's Axioms • Reflexivity: If Yµ X, then X! Y תלות טריואלית • Augmentation: If X! Y, then XZ! YZ הוספה • Transitivity: If X! Y and Y! Z, then X! Z • Armstrong's axioms can be used to "prove" that a dependency must hold by replacing X, Y, Z with specific attributes

39. Proofs using Armstrong's Axioms • Proofs using Armstrong's axioms are simply a series of functional dependencies, each of which either appears in the given set F, or follows from previous function dependencies using Armstrong's axioms • If we can prove that X! Y follows from F using Armstrong's axioms, we write: • F` X! Y • and we say: F יכיח מ-X! Y

40. Example • Suppose that F = {A! C, B! D} • Prove that F`AB ! ABCD using Armstrong's axioms • A! C (given) • AB! ABC (follows from ?) • B! D (given) • ABC! ABCD (follows from ?) • AB! ABCD (follows from ?)

41. Reminder: Difference Between נובע and יכיח • What is the difference between F² X! Y and F` X! Y? • true in every possible relation versus • syntactic proof technique • Is it true that F² X! Y , F`X! Y? • If F ` X! Y ) F² X! Y, then Armstrong's axioms are sound • If F² X! Y ) F` X! Y, then Armstrong's axioms are complete

42. Soundness and Completeness of Armstrong's Axioms

43. Proof of Soundness • Show that each of the axioms is sound • Which one did we already prove? • Can you prove the others? Completeness is a lot harder to show! The next two slides define things that will make it a bit easier to show…

44. Additional Axioms • Union: If X! Y and X! Z, then X! YZ • Decomposition: If X! YZ, then X! Y and X! Z • Pseudo Transitivity: If X! Y and YW! Z, then XW ! Z • Soundness of these axioms can be proved • directly, like before or • by deriving them from Armstrong's axioms • Exercise: Derive each of these rules from Armstrong's axioms

45. The Closure of a set of Attributes • The closure of the attributes X, with respect to the FDs F is denoted X+F = {A | F` X! A} • Note: ` means provable using Armstrong’s Axioms • If F is clear from the context, we simply write X+ • Lemma: Let Y be a set of attributes. Then, Yµ X+ if and only if F` X! Y • Proof: By definition of closure and by the axioms of decomposition and union

46. Completeness of the Axioms • Completeness of the axioms means that everything that נובע is also יכיח, i.e., • Equivalently, completeness means that everything that is not יכיח is also not נובע • We prove that Armstrong's axioms are complete by using the second definition of completeness • On the blackboard!

47. Using Armstrong’s Axioms:Determining Implication by Computing Closures

48. Review • Given F and X ® Y • What are the difficulties in showing that F² X ® Y • What are the difficulties in showing that F` X ® Y • If we have an efficient algorithm for the second problem, we get and efficient algorithm for the first (Why?)

49. Strategy • The closure of the attributes X, with respect to the FDs F is denoted X+F = {A | F` X ® A} • We will learn an efficient algorithm that computes the closure of a set of attributes. • Then, instead of checking whether F`X ® Y, we will check if YÍ X+F • This is sufficient according to the closure lemma • This will also prove to us that F² X ® Y

50. Algorithm that Computes X+F V:=X While there is a Y ® ZÎ F such that • YÍ V and • ZÍ V do V:=VÈ Z Return V • What is the runtime of this algorithm? • Algorithm can be improved to run in time O(X+F) • Correctness of the algorithm can be shown similarly to Armstrong’s axioms (proof omitted)