- By
**kylia** - Follow User

- 85 Views
- Uploaded on

Download Presentation
## Design Theory

**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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

**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)

Download Presentation

Connecting to Server..