Document Sample

BCNF & Lossless Decomposition Prof. Sin-Min Lee Department of Computer Science Normalization Review on Keys • superkey: a set of attributes which will uniquely identify each tuple in a relation • candidate key: a minimal superkey • primary key: a chosen candidate key • secondary key: all the rest of candiate keys • prime attribute: an attribute that is a part of a candidate key (key column) • nonprime attribute: a nonkey column Normalization Functional Dependency Type by Keys • ‘whole (candidate) key nonprime attribute’: full FD (no violation) • ‘partial key nonprime attribute’: partial FD (violation of 2NF) • ‘nonprime attribute nonprime attribute’: transitive FD (violation of 3NF) • ‘not a whole key prime attribute’: violation of BCNF Functional Dependencies • Let R be a relation schema R and R • The functional dependency holds on R iff for any legal relations r(R), whenever two tuples t1 and t2 of r have same values for , they have same values for . t1[] = t2 [] t1[ ] = t2 [ ] A B 1 4 1 5 3 7 • On this instance, A B does NOT hold, but B A does hold. 1. Closure • Given a set of functional dependencies, F, its closure, F+ , is all FDs that are implied by FDs in F. • e.g. If A B, and B C, • then clearly A C Armstrong’s Axioms • We can find F+ by applying Armstrong’s Axioms: – if , then (reflexivity) – if , then (augmentation) – if , and , then (transitivity) • These rules are – sound (generate only functional dependencies that actually hold) and – complete (generate all functional dependencies that hold). Additional rules • If and , then (union) • If , then and (decomposition) • If and , then (pseudotransitivity) The above rules can be inferred from Armstrong’s axioms. Example • R = (A, B, C, G, H, I) F={ AB AC CG H CG I B H} • Some members of F+ – AH • by transitivity from A B and B H – AG I • by augmenting A C with G, to get AG CG and then transitivity with CG I – CG HI • by augmenting CG I to infer CG CGI, and augmenting of CG H to infer CGI HI, and then transitivity 2. Closure of an attribute set • Given a set of attributes A and a set of FDs F, closure of A under F is the set of all attributes implied by A • In other words, the largest B such that: • AB • Redefining super keys: • The closure of a super key is the entire relation schema • Redefining candidate keys: • 1. It is a super key • 2. No subset of it is a super key Computing the closure for A • Simple algorithm • 1. Start with B = A. • 2. Go over all functional dependencies, , in F+ • 3. If B, then • Add to B • 4. Repeat till B changes • R = (A, B, C, G, H, I) Example F={ AB AC CG H CG I B H} • (AG) + ? • 1. result = AG 2. result = ABCG (A C and A B) 3. result = ABCGH (CG H and CG AGBC) 4. result = ABCGHI (CG I and CG AGBCH Is (AG) a candidate key ? 1. It is a super key. 2. (A+) = BC, (G+) = G. Uses of attribute set closures • Determining superkeys and candidate keys • Determining if A B is a valid FD • Check if A+ contains B • Can be used to compute F+ Database Normalization Functional dependency (FD) X Y means that if there is only one possible value of Y for every value of X, then Y is Functionally dependent on X. Is the following FDs hold? X Y Z 10 B1 C1 X Y Y Z 10 B2 C2 11 B4 C1 Z Y Y X 12 B3 C4 13 B1 C1 14 B3 C4 Database Normalization • Functional Dependency is “good”. With functional dependency the primary key (Attribute A) determines the value of all the other non-key attributes (Attributes B,C,D,etc.) • Transitive dependency is “bad”. Transitive dependency exists if the primary/candidate key (Attribute A) determines non-key Attribute B, and Attribute B determines non-key Attribute C. • If a relation schema has more than one key, each is called a candidate key • An attribute in a relation schema R is called prim if it is a member of some candidate key of R First Normal Form (1NF) Each attribute must be atomic (single value) • No repeating columns within a row (composite attributes) • No multi-valued columns. 1NF simplifies attributes • Queries become easier. 1NF Deptno Dname Location 10 IT Leeds, Bradford, Kent 20 Research Hundredfold 30 Marketing Leeds Deptno Location Deptno Dname 10 Leeds 10 Bradfprd 10 IT 10 Kent 20 Research 20 Hundredfold 30 Marketing 30 Leeds Second Normal Form (2NF) Each attribute must be functionally dependent on the primary key. • If the primary key is a single attribute, then the relation is in 2NF • The test for 2NF involves testing for FDs whose left-hand-side attribute are part of the primary key • Disallow partial dependency, where non-keys attributes depend on part of a composite primary key • In short, remove partial dependencies 2NF improves data integrity. • Prevents update, insert, and delete anomalies. 2NF PNo PName PLoc EmpNo EName Salary Address HoursNo Given the following FDs: PNo , Em pNo HoursNo PNo Dnam e Loc , Em pNo Nam e, Salary, Address Assuming all attributes are atomic, is the above relation in the 1NF, 2NF ? Relation X1 Relation X3 PNo PName PLoc PNo EmpNo HoursNo Relation X2 EmpNo EName Salary Address Third Normal Form (3NF) Remove transitive dependencies. Transitive dependency A non-prime attribute is dependent on another, non-prime attribute or attributes Attribute is the result of a calculation Examples: Area code attribute based on City attribute of a customer Total price attribute of order entry based on quantity attribute and unit price attribute (calculated value) Solution: • Any transitive dependencies are moved into a smaller table. Transitive Dependence Give a relation R, EmpNo EName Salary Address Assume the following FD hold: Ename Address Note : Both Ename and Address attributes are non-key attributes in R, and since Address depends on a non-Prime attribute Name, which depends on the primary key(EmpNo), a transitive dependency exists EmpNo Ename, Ename Addresst EmpNo Address , R1 R2 EmpNo EName Salary Ename Address Note : If address is a prime attribute Then R is in 3NF Modification Anomalies Patron Patron Book Book Book Borrow Due Return Name Address ID Title Author Date Date Date Smith 12 Elk AAA Peace Bart 2/4 2/18 2/15 Jones 25 Sun BBB War Hine 2/4 2/18 2/19 Hart 73 Sera CCC System Vang 2/5 2/19 2/23 Hicks 22 Main AAA Peace Bart 2/12 2/25 2/28 Rice 69 Witt DDD Spring Lyon 2/6 2/20 2/8 Jones 25 Sun CCC System Vang 1/26 2/7 2/6 • What happens when you want to – add a new book? – change the address of a patron? – delete a patron record? Modification Anomalies • Deletion anomaly – deleting one fact about an entity deletes a fact about another entity • Insertion anomaly – cannot insert one fact about an entity unless a fact about another entity is also added • Update anomaly – changing one fact about an entity requires multiple changes to a table Referential Integrity Constraint • When we split a relation, we must pay attention to the references across the newly formed relations • E.g., a book must exist before it can be checked out: – CHECKOUT [BookID] Í BOOK [BookID] • The DBMS or the applications will have to check/enforce constraints Boyce-Codd Normal Form • Every determinant is a candidate key – ADVISER(SID,Major,Fname) – STU-ADV(SID,Fname) ADV-SUBJ(Fname,Subject) Multi-valued Dependency • Two or more functionally independent multi- valued attributes are dependent on another attribute – EMPLOYEE(Name,Dependent,Project) • Data redundancy and modification anomalies • 4NF: BCNF & no multi-valued dependencies – EMPLOYEE(Name,Dependent) – EMPLOYEE(Name, Project) Database Normalization • Boyce-Codd Normal Form (BCNF) – A relation is in Boyce-Codd normal form (BCNF) if every determinant in the table is a candidate key. (A determinant is any attribute whose value determines other values with a row.) – If a table contains only one candidate key, the 3NF and the BCNF are equivalent. – BCNF is a special case of 3NF. A Table That Is In 3NF But Not In BCNF Figure 5.7 The Decomposition of a Table Structure to Meet BCNF Requirements Figure 5.8 Lossless-join Decomposition ● For the case of R = (R1, R2), we require that for all possible relations r on schema R r = R1 (r ) |X| R2 (r ) ● A decomposition of R into R1 and R2 is lossless join if and only if at least one of the following dependencies is in F+: ● R1 R2 R1 ● R1 R2 R2 ● R = (A, B, C) F = {A B, B C) ● Can be decomposed in two different ways ● R1 = (A, B), R2 = (B, C) ● Lossless-join decomposition: R1 R2 = {B} and B BC ● Dependency preserving ● R1 = (A, B), R2 = (A, C) ● Lossless-join decomposition: R1 R2 = {A} and A AB ● Not dependency preserving (cannot check B C without computing R1 |X| R2) Dependency Preservation ● Let Fi be the set of dependencies F + that include only attributes in Ri. ● A decomposition is dependency preserving, if (F1 F2 … Fn )+ = F + ● If it is not, then checking updates for violation of functional dependencies may require computing joins, which is expensive. Dependency Preservation ● To check if a dependency is preserved in a decomposition of R into R1, R2, …, Rn we apply the following test (with attribute closure done with respect to F) ● result = while (changes to result) do for each Ri in the decomposition t = (result Ri)+ Ri result = result t ● If result contains all attributes in , then the functional dependency is preserved. Dependency Preservation ● We apply the test on all dependencies in F to check if a decomposition is dependency preserving ● This procedure takes polynomial time, instead of the exponential time required to compute F+ and (F1 F2 … Fn)+ FD Example ● R = (A, B, C ) F = {A B, B C} Key = {A} ● R is not in BCNF ● Decomposition R1 = (A, B), R2 = (B, C) ● R1 and R2 now in BCNF ● Lossless-join decomposition ● Dependency preserving A Lossy Decomposition Aim of Normalization • Goal for a relational database design is: – BCNF. – Lossless join. – Dependency preservation. • If we cannot achieve this, we accept one of – Lack of dependency preservation – Redundancy due to use of 3NF Sample Data for a BCNF Conversion Table 5.2 Decomposition into BCNF Perform lossless-join decompositions of each of the following scheme into BCNF schemes: R(A, B, C, D, E) with dependency set {AB CDE, C D, D E} A B C D A B C D C D A B C E D E A B C D D E A B C C D A B C Given the FDs {B D, AB C, D B} and the relation {A, B, C, D}, give a two distinct lossless join decomposition to BNCF indicating the keys of each of the resulting relations. A B C D A B C D B D A B C B D A C D Definition of MVD • A multivalued dependency (MVD) X ->->Y is an assertion that if two tuples of a relation agree on all the attributes of X, then their components in the set of attributes Y may be swapped, and the result will be two tuples that are also in the relation. Example • The name-addr-phones-beersLiked example illustrated the MVD name->->phones and the MVD name ->-> beersLiked. Picture of MVD X ->->Y X Y others equal exchange MVD Rules • Every FD is an MVD. – If X ->Y, then swapping Y ’s between two tuples that agree on X doesn’t change the tuples. – Therefore, the “new” tuples are surely in the relation, and we know X ->->Y. • Complementation : If X ->->Y, and Z is all the other attributes, then X ->->Z. 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. 4NF Definition • A relation R is in 4NF if whenever X ->->Y is a nontrivial MVD, then X is a superkey. – “Nontrivial 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. BCNF Versus 4NF • Remember that every FD X ->Y is also an MVD, X ->->Y. • Thus, if R is in 4NF, it is certainly in BCNF. – Because any BCNF violation is a 4NF violation. • But R could be in BCNF and not 4NF, because MVD’s are “invisible” to BCNF. Normalization Good Decomposition • dependency preserving decomposition - it is undesirable to lose functional dependencies during decomposition • lossless join decomposition - join of decomposed relations should be able to create the original relation (no spurious tuples) 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. Example Drinkers(name, addr, phones, beersLiked) FD: name -> addr MVD’s: name ->-> phones name ->-> beersLiked • Key is {name, phones, beersLiked}. • All dependencies violate 4NF. Example, Continued • Decompose using name -> addr: 1. Drinkers1(name, addr) In 4NF, only dependency is name -> addr. 2. Drinkers2(name, phones, beersLiked) Not in 4NF. MVD’s name ->-> phones and name ->-> beersLiked apply. No FD’s, so all three attributes form the key. Example: Decompose Drinkers2 • Either MVD name ->-> phones or name ->-> beersLiked tells us to decompose to: – Drinkers3(name, phones) – Drinkers4(name, beersLiked) BCNF • Given a relation schema R, and a set of functional dependencies F, if every FD, A B, is either: • 1. Trivial • 2. A is a superkey of R • Then, R is in BCNF (Boyce-Codd Normal Form) BCNF • What if the schema is not in BCNF ? • Decompose (split) the schema into two pieces. • Careful: you want the decomposition to be lossless Achieving BCNF Schemas • For all dependencies A B in F+, check if A is a superkey • By using attribute closure • If not, then • Choose a dependency in F+ that breaks the BCNF rules, say A B • Create R1 = A B • Create R2 = A (R – B – A) • Note that: R1 ∩ R2 = A and A AB (= R1), so this is lossless decomposition • Repeat for R1, and R2 • By defining F1+ to be all dependencies in F that contain only attributes in R1 • Similarly F2+ • Example 1 R = (A, B, C) • F = {A B, B C} • Candidate keys = {A} • BCNF = No. B C violates. BC • R1 = (B, C) • R2 = (A, B) • F1 = {B C} • F2 = {A B} • Candidate keys = {B} • Candidate keys = {A} • BCNF = true • BCNF = true • Example 2-1 R = (A, B, C, D, E) • F = {A B, BC D} • Candidate keys = {ACE} • BCNF = Violated by {A B, BC D} etc… • From A B and BC AB D by pseudo-transitivity • R1 = (A, B) • R2 = (A, C, D, E) • F1 = {A B} • F2 = {AC D} • Candidate keys = {A} • Candidate keys = {ACE} • BCNF = true • BCNF = false (AC D) • Dependency preservation ??? AC D • We can check: • R4 = (A, C, E) • A B (R1), AC D • F4 = {} [[ only (R3), • R3 = (A, C, D) trivial ]] • but we lost BC D • F3 = {AC D} • Candidate keys = • So this is not a dependency • Candidate keys = {AC} {ACE} • -preserving decomposition • BCNF = true • BCNF = true • Example 2-2 R = (A, B, C, D, E) • F = {A B, BC D} • Candidate keys = {ACE} • BCNF = Violated by {A B, BC D} etc… BC D • R1 = (B, C, D) • R2 = (B, C, A, E) • F1 = {BC D} • F2 = {A B} • Candidate keys = {BC} • Candidate keys = {ACE} • BCNF = true • BCNF = false (A B) • Dependency preservation ??? AB • We can check: • R3 = (A, B) • R4 = (A, C, E) • BC D (R1), A • F3 = {A B} • F4 = {} [[ only B (R3), • Candidate keys = {A} trivial ]] • Dependency-preserving • BCNF = true • Candidate keys = {ACE} • decomposition • BCNF = true Example 3 • R = (A, B, C, D, E, H) • F = {A BC, E HA} • Candidate keys = {DE} • BCNF = Violated by {A BC} etc… A BC • R1 = (A, B, C) • R2 = (A, D, E, H) • F1 = {A BC} • F2 = {E HA} • Candidate keys = {A} • Candidate keys = {DE} • BCNF = true • BCNF = false (E HA) • Dependency preservation ??? E HA • R4 = (ED) • We can check: • F4 = {} [[ only • R3 = (E, H, A) • A BC (R1), E trivial ]] • F3 = {E HA} HA (R3), • Candidate keys = • Candidate keys = {E} • Dependency-preserving {DE} • BCNF = true • decomposition • BCNF = true

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 274 |

posted: | 2/16/2012 |

language: | English |

pages: | 67 |

OTHER DOCS BY 3dG73t6

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.