Normalization Part II cs 3431 Attribute Closure Example

Normalization Part II cs 3431

Attribute Closure : Example l l l l Consider R (A, B, C, D, E) with FDs A B, B C, CD E Does A E hold ? (Is A E in F+ ? ) Rephrase as : Is E in A+ ? Let us compute {A}+ = {A, B, C} Therefore, A E is false cs 3431

Decomposition: Must be Lossless (no spurious tuples!) cs 3431

Decomposing Relations Student. Prof s. Number s. Name p. Number p. Name s 1 Dave p 1 MM s 2 Greg p 2 MM FDs: p. Number p. Name Student Professor s. Number s. Name p. Number p. Name s 1 Dave p 1 MM s 2 Greg p 2 MM cs 3431

Decomposition: Lossless Join Property Student Professor s. Number s. Name p. Number p. Name S 1 Dave MM p 1 MM S 2 Greg MM p 2 MM Student. Prof s. Number s. Name p. Number p. Name s 1 Dave p 1 MM s 1 Dave p 2 MM s 2 Greg p 1 MM s 2 Greg p 2 MM cs 3431 Spurious Tuples

Normalization l What is the algorithm for correct (lossless) decomposition ?

Normalization Step : Decompose l Consider relation R with set of attributes AR. Consider a FD : A B (such that no other attribute in (AR – A – B) is functionally determined by A). l If A is not a superkey for R, we may decompose R as: l l Create R’ (AR – B) Create R’’ with attributes A B Key for R’’ = A Foreign key : R’ (A) references R’’ (A) cs 3431

Example Decomposition Revisited Student. Prof s. Number s. Name p. Number p. Name s 1 Dave p 1 MM s 2 Greg p 2 MM FDs: p. Number p. Name Student Professor s. Number s. Name p. Number p. Name s 1 Dave p 1 MM s 2 Greg p 2 MM FOREIGN KEY: Student (PNum) references Professor (PNum) cs 3431

Schema Refinement : Normal Forms l Question : How decide if any refinement of schema is needed ? l If a relation is in a certain normal form, then it is known that certain kinds of problems are avoided or minimized.

Normal Form: BCNF l Boyce Codd Normal Form (BCNF): l For every non-trivial FD X B in R, X is a superkey of R. cs 3431

BCNF Example Relation: SCI (student, course, instructor) FDs: student, course instructor course Decomposition: SI (student, instructor) Instructor (instructor, course) cs 3431

Decomposition Algo into BCNF l Repeated application of decomposition will result in: l relations that are in BCNF; l lossless join decomposition, l and guaranteed to terminate.

Decomposition : Dependency Preserving ? l l l l Intuition: Can we locally in each decomposed relation check the functional dependencies ? Consider relation CSJDPQV, C is key, JP C and SD P. Decomposition: CSJDQV and SDP Is it lossless ? Yes ! Is it in BCNF ? Yes ! Problem: Checking JP C requires a join!

Dependency Preserving Decomposition l Intuition : l l Formal Definition : l l If R is decomposed into X, Y and Z, and we enforce FDs that hold on X, on Y and on Z, then all FDs that were given to hold on R must also hold. Decomposition of R into X and Y is dependency preserving if (FX union FY ) + = F + Projection of set of FDs F: l If R is decomposed into X, Y, . . . , then projection of F onto X (denoted FX ) is the set of FDs U -> V in F+ (closure of F ) such that U, V are in X.

Dependency Preserving Decompositions l Decomposition of R into X and Y is dependency preserving if (FX union FY ) + = F + l Important to consider F +, not F, in this definition: l l l ABC, A B, B C, C A, decomposed into AB and BC. Is this dependency preserving? Is C A preserved ?

BCNF and Dependency Preservation l In general, a dependency preserving decomposition into BCNF may not exist ! l Example : l Not in BCNF. Can’t decompose while preserving 1 st FD. l CSZ, CS Z, Z C

Dependency Preservation BCNF does not necessarily preserve FDs. But 3 NF is guaranteed to be able to preserve FDs. cs 3431

Normal Form : 3 NF l Third Normal Form (3 NF): l For every non-trivial FD X B in R, either X is a superkey of R, or B is a prime attribute (B is part of a key). cs 3431

3 NF vs BCNF ? l l l If R is in BCNF, obviously R is in 3 NF. If R is in 3 NF, R may not be in BCNF. If R is in 3 NF, some redundancy is possible. 3 NF is a compromise used when BCNF not achievable, i. e. , when no ``good’’ decomp exists Important: Lossless-join, dependency-preserving decomposition of R into a collection of 3 NF relations always possible !

Algorithm : Decomposition into 3 NF l l l Decomposition algorithm again used, but typically can stop earlier). But how to ensure dependency preservation? Idea 1: l l l If X Y is not preserved, add relation XY. Problem is that XY may violate 3 NF! Idea 2 : Instead of the given set of FDs F, use a minimal cover for F.

Minimal Cover for a Set of FDs l Minimal cover G for a set of FDs F: l l l Closure of F = closure of G. Right hand side of each FD in G is single attribute. If we modify G by deleting a FD or by deleting attributes from an FD in G, the closure changes. Intuition: every FD in G is needed, and ``as small as possible’’ in order to get the same closure as F. Example : If both J C and JP C, then only keep the first one.

Minimal Cover for a Set of FDs l Theorem : l l l Example : Given : l l Use minimum cover of FD+ in decomposition guarantees that the decomposition is lossless-join and dependency-preserving. A B, ABCD E, EF GH, ACDF EG Then the minimal cover is: l A B, ACD E, EF G and EF H

Algorithm for Minimal Cover l l Decompose FD into one attribute on RHS Minimize left side of each FD l Check each attribute on LHS to see if deleted while still preserving the equivalence to F+. l Delete redundant FDs. l Note: Several minimal covers may exist.

3 NF Decomposition Algorithm l l Compute minimal cover G of F Decompose R using minimal cover G of FD into lossless decomposition of R. l l Each Ri is in 3 NF Fi is projection of F onto Ri Identify dependencies in F not preserved now, X Create relation XA : l l l A New relation XA preserves X A X is key of XA, because G is minimal cover. Hence no Y subset X exists, with Y A If another dependency exists in XA; can only imply attribute of X.

Summary l Step 1: BCNF is a good form for relation l If a relation is in BCNF, it is free of redundancies that can be detected using FDs. l Step 2 : If a relation is not in BCNF, we can try to decompose it into a collection of BCNF relations. l Step 3: If a lossless-join dependency-preserving decomposition into BCNF is not possible (or unsuitable given typical queries), consider decomposition into 3 NF. l Note: Decompositions should be carried out while keeping performance requirements in mind.