1.14k likes | 1.3k Views
Design Theory. Some of the slides on this topic were adapted from slides of Yehoshua Sagiv. 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
E N D
Design Theory Some of the slides on this topic were adapted from slides of Yehoshua Sagiv
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
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
Example of a “Bad” SchemaIntuitive Example • Suppose that each department has a single head Why is this bad? 3 Reasons
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?)
Why is there duplication? • A department has one head, but many students • Information on head is stored for each student Student Department Head
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
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?
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
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
Several Fields on Both Sides • The functional dependency SC → G holds • Does the functional dependency SC → GC hold? Student Course Grade
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
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
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
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…)
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] ?
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?
Example • Which of the following functional dependencies hold in this relation? • AC → BA • ABD → CFE • AC → BF
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
Proposition • Prove: A functional dependency X! Y is trivial if and only if Yµ X Can you fill in the proof?
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
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
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…
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
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
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
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?
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
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
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
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?
You Try It • Prove that {AB® BC, C® B} 2 A® C
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
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
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
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 ?)
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
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…
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
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
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!
Using Armstrong’s Axioms:Determining Implication by Computing Closures
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?)
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
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)