ER Diagrams Concluded Schema Refinement and Normalization Zachary

ER Diagrams (Concluded), Schema Refinement, and Normalization Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 6, 2005 Some slide content courtesy of Susan Davidson & Raghu Ramakrishnan

Examples of ER Diagrams § Please interpret these ER diagrams: STUDENTS Takes COURSES 2

Converting ER Relationship Sets to Tables: PROFESSORS 1: n Relationships CREATE TABLE Teaches( • “ 1” entity = key of fid INTEGER, serno CHAR(15) , relationship set: • Or embed relationship in “many” entity set: Teaches semester CHAR(4) , PRIMARY KEY (serno), FOREIGN KEY (fid) COURSES REFERENCES PROFESSORS, FOREIGN KEY (serno) REFERENCES Teaches) CREATE TABLE Teaches_Course ( serno INTEGER, subj VARCHAR(30), cid CHAR(15), fid CHAR(15), when CHAR(4), PRIMARY KEY (serno), FOREIGN KEY (fid) REFERENCES PROFESSORS) 3

1: 1 Relationships If you borrow money or have credit, you might get: rid Credit. Report delinquent? Describes debt Borrower ssn name What are the table options? 4

ISA Relationships: Subclassing (Structurally) § Inheritance states that one entity is a “special kind” of another entity: “subclass” should be member of “base class” id People name ISA Employees salary 5

But How Does this Translate into the Relational Model? Compare these options: § § Two tables, disjoint tuples Two tables, disjoint attributes One table with NULLs Object-relational databases (allow subclassing of tables) 6

Weak Entities A weak entity can only be identified uniquely using the primary key of another (owner) entity. § Owner and weak entity sets in a one-to-many relationship set, 1 owner : many weak entities § Weak entity set must have total participation People ssn name Feeds weekly. Cost Pets name species 7

Translating Weak Entity Sets Weak entity set and identifying relationship set are translated into a single table; when the owner entity is deleted, all owned weak entities must also be deleted CREATE TABLE Feed_Pets ( name VARCHAR(20), species INTEGER, weekly. Cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE) 8

N-ary Relationships § Relationship sets can relate an arbitrary number of entity sets: Student Project Indep Study Advisor 9

Summary of ER Diagrams § One of the primary ways of designing logical schemas § CASE tools exist built around ER (e. g. ERWin, Power. Builder, etc. ) § Translate the design automatically into DDL, XML, UML, etc. § Use a slightly different notation that is better suited to graphical displays § Some tools support constraints beyond what ER diagrams can capture § Can you get different ER diagrams from the same data? 10

Schema Refinement & Design Theory § ER Diagrams give us a start in logical schema design § Sometimes need to refine our designs further § There’s a system and theory for this § Focus is on redundancy of data Causes update, insertion, deletion anomalies 11

Not All Designs are Equally Good Why is this a poor schema design? Stuff(sid, name, serno, subj, cid, exp-grade) And why is this one better? Student(sid, name) Course(serno, cid) Subject(cid, subj) Takes(sid, serno, exp-grade) 12

Focus on the Bad Design sid name serno subj cid exp-grade 1 Sam 570103 AI 520 B 23 Nitin 550103 DB 550 A 45 Jill 505103 OS 505 A 1 Sam 505103 OS 505 C § Certain items (e. g. , name) get repeated § Some information requires that a student be enrolled (e. g. , courses) due to the key 13

Functional Dependencies Describe “Key-Like” Relationships A key is a set of attributes where: If keys match, then the tuples match A functional dependency (FD) is a generalization: If an attribute set determines another, written X ! Y then if two tuples agree on attribute set X, they must agree on X: sid ! name What other FDs are there in this data? Ø FDs are independent of our schema design choice 14

Formal Definition of FD’s Def. Given a relation schema R and subsets X, Y of R: An instance r of R satisfies FD X Y if, for any two tuples t 1, t 2 2 r, t 1[X ] = t 2[X] implies t 1[Y] = t 2[Y] § For an FD to hold for schema R, it must hold for every possible instance of r (Can a DBMS verify this? Can we determine this by looking at an instance? ) 15

General Thoughts on Good Schemas We want all attributes in every tuple to be determined by the tuple’s key attributes, i. e. part of a superkey (for key X Y, a superkey is a “non-minimal” X) What does this say about redundancy? But: § What about tuples that don’t have keys (other than the entire value)? § What about the fact that every attribute determines itself? 16

Armstrong’s Axioms: Inferring FDs Some FDs exist due to others; can compute using Armstrong’s axioms: § Reflexivity: If Y X then X Y (trivial dependencies ) name, sid name § Augmentation: If X Y then XW YW serno subj so serno, exp-grade subj, exp-grade § Transitivity: If X Y and Y Z then X Z serno cid and cid subj so serno subj 17

Armstrong’s Axioms Lead to… If X Y and X Z then X YZ § Pseudotransitivity: If X Y and WY Z then XW Z § Decomposition: If X Y and Z Y then X Z § Union: Let’s prove these from Armstrong’s Axioms 18

Closure of a Set of FD’s Defn. Let F be a set of FD’s. Its closure , F +, is the set of all FD’s: {X Y | X Y is derivable from F by Armstrong’s Axioms} Which of the following are in the closure of our Student. Course FD’s? name cid subj serno subj cid, sid subj cid sid 19

Attribute Closures: Is Something Dependent on X? Defn. The closure of an attribute set X, X+, is: X+ = {Y | X Y F +} § This answers the question “is Y determined (transitively) by X? ”; compute X+ by: closure : = X; repeat until no change { if there is an FD U V in F such that U is in closure then add V to closure} § Does sid, serno subj, exp-grade? 20

Equivalence of FD sets Defn. Two sets of FD’s, F and G, are equivalent if their closures are equivalent, F + = G + e. g. , these two sets are equivalent: {XY Z, X Y} and {X Z, X Y} § F + contains a huge number of FD’s (exponential in the size of the schema) § Would like to have smallest “representative” FD set 21

Minimal Cover we express Defn. A FD set F is minimal if: each FD in 1. Every FD in F is of the form X A, simplest form where A is a single attribute 2. For no X A in F is: in a sense, F – {X A } equivalent to F each FD is “essential” 3. For no X A in F and Z X is: to the cover F – {X A } {Z A } equivalent to F Defn. F is a minimum cover for G if F is minimal and is equivalent to G. e. g. , {X Z, X Y} is a minimal cover for {XY Z, X Y} 22

More on Closures If F is a set of FD’s and X Y F + then for some attribute A Y, X A F + Proof by counterexample. Assume otherwise and let Y = {A 1, . . . , An} Since we assume X A 1, . . . , X An are in F + then X A 1. . . An is in F + by union rule, hence, X Y is in F + which is a contradiction 23

Why Armstrong’s Axioms? Why are Armstrong’s axioms (or an equivalent rule set) appropriate for FD’s? They are: § Consistent : any relation satisfying FD’s in F will satisfy those in F + § Complete : if an FD X Y cannot be derived by Armstrong’s axioms from F, then there exists some relational instance satisfying F but not X Y Ø In other words, Armstrong’s axioms derive all the FD’s that should hold 24

Proving Consistency We prove that the axioms’ definitions must be true for any instance, e. g. : § For augmentation (if X Y then XW YW): If an instance satisfies X Y, then: § For any tuples t 1, t 2 r, if t 1[X] = t 2[X] then t 1[Y] = t 2[Y] by defn. § If, additionally, it is given that t 1[W] = t 2[W], then t 1[YW] = t 2[YW] 25

Proving Completeness Suppose X Y F + and define a relational instance r that satisfies F + but not X Y: § Then for some attribute A Y, X A F + § Let some pair of tuples in r agree on X + but disagree everywhere else: X A X+ –X R – X+ – {A} x 1 x 2. . . xn a 1, 1 v 2. . . vm w 1, 1 w 2, 1. . . x 1 x 2. . . xn a 1, 2 v 1 v 2. . . vm w 1, 2 w 2, 2. . . 26

Proof of Completeness cont’d § Clearly this relation fails to satisfy X A and X Y. We also have to check that it satisfies any FD in F +. § The tuples agree on only X +. Thus the only FD’s that might be violated are of the form X’ Y’ where X’ X+ and Y’ contains attributes in R – X + – {A}. § But if X’ Y’ F+ and X’ X+ then Y’ X+ (reflexivity and augmentation). Therefore X’ Y’ is satisfied. 27

Decomposition § Consider our original “bad” attribute set Stuff(sid, name, serno, subj, cid, exp-grade) § We could decompose it into Student(sid, name) Course(serno, cid) Subject(cid, subj) § But this decomposition loses information about the relationship between students and courses. Why? 28

Lossless Join Decomposition R 1, … Rk is a lossless join decomposition of R w. r. t. an FD set F if for every instance r of R that satisfies F, ÕR 1(r) ⋈. . . ⋈ ÕRk(r) = r Consider: sid name serno subj cid exp-grade 1 23 Sam Nitin 570103 550103 AI DB 570 550 B A What if we decompose on (sid, name) and (serno, subj, cid, exp-grade)? 29

Testing for Lossless Join R 1, R 2 is a lossless join decomposition of R with respect to F iff at least one of the following dependencies is in F+ (R 1 R 2) R 1 – R 2 (R 1 R 2) R 2 – R 1 So for the FD set: sid name serno cid, exp-grade cid subj Is (sid, name) and (serno, subj, cid, exp-grade) a lossless decomposition? 30

Dependency Preservation Ensures we can “easily” check whether a FD X Y is violated during an update to a database: § The projection of an FD set F onto a set of attributes Z, F Z is {X Y | X Y F +, X Y Z} i. e. , it is those FDs local to Z’s attributes § A decomposition R 1, …, Rk is dependency preserving F + = (F R 1 . . . F R k)+ if The decomposition hasn’t “lost” any essential FD’s, so we can check without doing a join 31

Example of Lossless and Dependency-Preserving Decompositions Given relation scheme R(name, street, city, st, zip, item, price) And FD set name street, city st street, city zip name, item price Consider the decomposition R 1(name, street, city, st, zip) and R 2(name, item, price) Ø Is it lossless? Ø Is it dependency preserving? What if we replaced the first FD by name, street city? 32

Another Example Given scheme: R(sid, fid, subj) and FD set: fid subj sid, subj fid Consider the decomposition R 1(sid, fid) and R 2(fid, subj) Ø Is it lossless? Ø Is it dependency preserving? 33

FD’s and Keys § Ideally, we want a design s. t. for each nontrivial dependency X Y, X is a superkey for some relation schema in R § We just saw that this isn’t always possible § Hence we have two kinds of normal forms 34

Two Important Normal Forms Boyce- Codd Normal Form (BCNF). For every relation scheme R and for every X A that holds over R, either A X (it is trivial) , or or X is a superkey for R Third Normal Form (3 NF). For every relation scheme R and for every X A that holds over R, either A X (it is trivial), or X is a superkey for R, or A is a member of some key for R 35

Normal Forms Compared § BCNF is preferable, but sometimes in conflict with the goal of dependency preservation § It’s strictly stronger than 3 NF § Let’s see algorithms to obtain: § A BCNF lossless join decomposition § A 3 NF lossless join, dependency preserving decomposition 36

BCNF Decomposition Algorithm (from Korth et al. ; our book gives recursive version) result : = {R} compute F+ while there is a schema Ri in result that is not in BCNF { let A B be a nontrivial FD on Ri s. t. A Ri is not in F+ and A and B are disjoint } result: = (result – Ri) {(Ri - B), (A, B)} 37

3 NF Decomposition Algorithm by Phil Bernstein, now @ MS Research Let F be a minimal cover i: =0 for each FD A B in F { if none of the schemas Rj, 1 j i, contains AB { increment i Ri : = (A, B) } } if no schema Rj, 1 j i contains a candidate key for R { increment i Ri : = any candidate key for R } return (R 1, …, Ri) Build dep. preserving decomp. Ensure lossless decomp. 38

Summary § We can always decompose into 3 NF and get: § Lossless join § Dependency preservation § But with BCNF we are only guaranteed lossless joins § BCNF is stronger than 3 NF: every BCNF schema is also in 3 NF § The BCNF algorithm is nondeterministic, so there is not a unique decomposition for a given schema R 39