csce520-normalization

# csce520-normalization - Normalization Closures Closure of...

This preview shows page 1. Sign up to view the full content.

This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Normalization Closures Closure of attributes (A+): find keys Closure of FDs (S+): projection of FDs to decompositions of schema Canonical cover: minimize the number of functional dependencies Important for updates Farkas CSCE 520 2 Canonical Cover Combine FDs if possible and eliminate extraneous attributes: Given a set of FDs S, and an FD XY Attribute A is extraneous in X if S logically implies (S­{XY}) ∪ {(X­A)Y}. Attribute A is extraneous in Y if the set of FDs (S­{XY}) ∪ {X(Y­A)} logically implies S. Farkas CSCE 520 3 Canonical Cover No functional dependency in Sc contains an extraneous attribute Each left side of a functional dependency in Sc is unique. Farkas CSCE 520 4 Example Canonical Cover Given: 1. 2. 3. 4. Farkas A BC B C A B AB C Combine 1 and 3 into: A BC From 2 and 4: A is extraneous in 4: BC C is extraneous in 1: A B Result: AB, BC CSCE 520 5 Problems of Relational Database Design Loss of information (lossless­join) Inability to represent certain information (dependency preservation) Repetition of information (normal forms) Farkas CSCE 520 6 Desirable Properties of Decomposition – Lossless­join Lossless­Join: Let R be a relation schema, S a set of FDs on R, R1 and R2 a decomposition of R. R1 and R2 form a lossless­join decomposition if at least one of the following functional dependencies are in S+ R1 ∩ R2 → R1 R1 ∩ R2 → R2 Farkas CSCE 520 7 Create Lossless­Join Decomposition Let R be the DB Schema and f: X Y an FD in S+ Decompose R into two relations R1(X,Y) R2(R­Y) Continue for each decomposed relation until cannot be decomposed any more Farkas CSCE 520 8 Desirable Properties of Decomposition – Dependency Preserving Dependency Preservation: all dependencies that hold on the original schema should be able to test on individual schemas after decomposition. Testing dependency preservations on projections is straight forward. Farkas CSCE 520 9 Testing dependency preservations on decomposition Let S be the original set of FDs on R, and S’ the union of FDs on projections R1, R2, …,Rn. A decomposition is dependency preserving if S’+=S+ Farkas CSCE 520 10 Desirable Properties of Decomposition – Avoid Redundancy Owner Name Owner Address Owner Phone Dog Name Dog Breed Dog Color Dog Age Viki 13 Lake Rd. Irmo, SC 1-803-777-8989 Pepper G.S. Black 3 Viki 13 Lake Rd. Irmo, SC 1-803-777-8989 Buddy G.S. Brown 1 Viki 13 Lake Rd. Irmo, SC 1-803-777-8989 Alexandra Mix Gray 5 Viki 13 Lake Rd. Irmo, SC 1-803-777-8989 Missy Labrador Brown 5 Pete 300 Main St. Columbia, SC 1-803-657-5678 Hunter Vizsla Brown 8 Paul 99 Apple St Irmo, SC 1-803-899-8524 Giant Kuvasz White 4 FD: Farkas O.Name O.Address, O.Phone D.Name, D.Breed D.Color, D.Age CSCE 520 11 Decomposition and FDs Functional dependencies: can be used in designing a relational database to remove the undesired properties Normalization using FDs: Boyce­Codd Normal Form (BCNF) 3rd Normal Form (3NF) Farkas CSCE 520 12 Boyce­Codd Normal Form A relation is in Boyce­Codd Normal Form if for all FDs X A in S+ over R at least one of the followings hold: X A is a trivial FD if X A is a nontrivial FD then X is a superkey for schema R Example: R(Name,Breed,Date, Kennel) FD: Name,Breed,Date Kennel R is in BCNF Farkas CSCE 520 13 Decomposition into BCNF Compute S+ (for FDs in S); if there is a R that is not BCNF then ­ let XA a non­trivial FD on R s.t. XR is not in S+ and X ∩ A = ∅; ­ Decompose R into: R1(R­A) R2(X,A) Farkas CSCE 520 14 BCNF Not every BCNF decomposition is dependency preserving Example: FDs: AB C and C B Keys: {A,B} and {A,C} C B is BCNF violation, therefore need to decompose to R1=(AC) and R2=(BC) Decomposition cannot enforce AB C! Farkas CSCE 520 15 Third Normal Form Modifies BCNF conditions so no need to decompose in this problem situation An attribute is prime if it is member of any key X A violates 3NF if and only if X is not a superkey and also A is not a prime. Farkas CSCE 520 16 3NF Conditions A relation is in 3NF if for all FDs X A in S+ over R at least one of the followings hold: X A is a trivial FD if X A is a nontrivial FD then X is a superkey for schema R Each attribute B in A­X is contained in a candidate key for R Farkas CSCE 520 17 3NF and BCNF BCNF gives Lossless­join No­redundancy Dependency preservation not always possible 3NF gives Lossless­join Dependency preservation May have null values (transitive dependencies) Farkas CSCE 520 18 Definition of MVD A multivalued dependency (MVD) on R, X ­>­>Y , says that if two tuples of R agree on all the attributes of X, then their components in Y may be swapped, and the result will be two tuples that are also in the relation. i.e., for each value of X, the values of Y are independent of the values of R­X­Y. 19 Picture of MVD X ­>­>Y X Y others equal exchange 20 Fourth Normal Form The redundancy that comes from MVD’s is not removable by putting the database schema in BCNF. There is a stronger normal form, called 4NF, that (intuitively) treats MVD’s as FD’s when it comes to decomposition, but not when determining keys of the relation. 21 4NF Definition A relation R is in 4NF if: whenever X ­>­>Y is a nontrivial MVD, then X is a superkey. Nontrivial MVD means that: 1. Y is not a subset of X, and 2. X and Y are not, together, all the attributes. Note that the definition of “superkey” still depends on FD’s only. 22 Decomposition and 4NF If X ­>­>Y is a 4NF violation for relation R, we can decompose R using the same technique as for BCNF. 1. XY is one of the decomposed relations. 2. All but Y – X is the other. 23 3NF, BCNF, 4NF 3NF BCNF 4NF 24 Properties Property 3NF BCNF 4NF Eliminates redundancy due to FDs No Yes Yes Eliminates redundancy due to MVDs No No Yes Preserves FDs Yes No No Preserves MVDs No No No 25 ...
View Full Document

## This note was uploaded on 12/13/2011 for the course CSCE 520 taught by Professor Farkas during the Spring '11 term at South Carolina.

Ask a homework question - tutors are online