CS 440 Database Management Systems

CS 440 Database Management Systems Practice Session on Functional Dependencies and Normalization

Functional Dependency (FD) • A form of constraint in the database • Given attributes in relation R, the functional dependency means that all tuples in R that agree on attributes must also agree on . • Employee (Employee_ID, Employee_DOB) {Employee_ID} → {Employee_DOB}

Armstrong’s Axioms • Reflexivity: generally, if , we have (trivial FD) • Augmentation: If , then • Transitivity: If and then

Question 1 Consider a relation R(A,B,C,D,E) with FD's, S={AB  C, CD  E, C A, C  D, D  B}: a) Determine all the keys of relation R. Do not list super keys that are not a minimal key. Solution: • Keys: AB, AD, C • To get the key AB, we can do the following: • From AB  C and C D, we obtain AB D. • From AB  C and AB  D, we obtain AB  CD. • From AB  CD and CD  E, we obtain AB  E.

Question 1 (Solution Contd..) • To get the key AD, we can do the following: • From D  B, we can get AD  AB. • From AB, we can obtain the rest of the attributes. • To get the key C, we can do the following: • From C  A and C  B, we obtained C  AB. • From AB, we can obtain the rest of the attributes.

Question 2 Consider a relation R(A, B, C, D, E, F) with the following set of FD’s : S:{ ABC, CDE, B D, EA, CFB} a) Give an example of FD that follows from S and explain your answer. Solution: AB  D, D is in the closure of AB. Because A B and B D Thus AB  D is a valid FD that follows S.

Question 2(Solution Contd..) Consider a relation R(A, B, C, D, E, F) with the following set of FD’s : S:{ ABC, CDE, B D, EA, CFB} b) Give an example of FD that does not follow from S and explain your answer. Solution: • B  C, C is not in the closure of B. B doesn’t uniquely identify C accordance to S. So, BC is not valid accordance to S.

Minimal Basis • The FD sets U1 and U2 are equivalent if and only if U1+ = U2 +. • U2 is a minimal basis for U1 iff: • U2 and U1 are equivalent. • The FDs in U2 have only one attribute in their right hand side. • If we remove an FD from U2 or an attribute from an FD (left-hand side) in U2, U2 will not be equivalent to U1.

Question 3 Consider R(A, B, C, D) has FD’s A B, B C, and C D. Find FD’s for R1. Comment about minimal basis for R1. Solution: • Finding closure of all attributes of R we get: • {A} + = {A ,B ,C ,D }, FD’s A  C and A D hold in R1 • {C} + = {C,D}, an additional FD, CD for R1. • The FD’s discovered are AC, C  D, and A  D. • A  D follows from the other two bytransitivity. • So {A  C, CD} is a minimal basis for the FD’s of R1.

3rd Normal Form • A relation R is in 3rd normal form if for each non-trivial FD AB in R • A is a super-key or • B is a part of a key. • Every attribute depends on a key or is in a key. • Every non-key attribute must depend on a key, the whole key, and nothing but the key ( E. Codd ) .

3NF Synthesizing Algorithm Input: relation R and set of FDs U. Output: Normalized schema S • Find a minimal basis M for U. • For each FD AB in M, if AB is not covered by any relation in S, add Ri = (AB) to S. • If none of the relations in S contain a super-key for R, add a relation to S whose attributes form a key for R.

Question 4 Consider the relation R (A ,B ,C ,D ,E ) with FD's A B  C, C  B, and A  D. Convert the Relation into 3NF. Solution: • R is not in 3NF. • Given FD's are their own minimal basis. • Each FD as a relation schema: R1(A ,B ,C ), R2(B, C), and R3(A,D ).

Question 4 (Contd..) Consider the relation R (A ,B ,C ,D ,E ) with FD's A B  C, C  B, and A  D. Convert the Relation into 3NF. Solution: • R has two keys: { A ,B ,E } and {A ,C ,E }, neither of these keys is a subset of the relations chosen so far. Thus, we must add one of them, R4(A ,B ,E ). • We drop R2(B,C), since its schema is a subset of R1(A ,B ,C ). • The final decomposition: R1(A ,B ,C ), R3 (A,D ), and R4 (A ,B ,E ).

Boyce-Codd Normal Form • Relation R is in BCNF, if and only if: • For each non-trivial FD A B, A is a super-key of R. • If a set of attributes determines another attribute, it determines all attributes. • Every attribute depends only on super-keys.

BCNF Decomposition • Pick an FD A B that violates the BCNF condition in relation R. • Select the largest possible B. • Decompose R to R1 and R2 • Repeat until there is no BCNF violation left. Other AttributesA A B R1 R2

The Complete Algorithm (R, U) • Compute U+ using FD closure Algorithm. • Find the keys for R using U+. • Pick FD A B in U+ that violates BCNF. • Update B to A+ • Replace R with R1= (A,B ) and R2 = (A, others). • Compute keys and FDs for R1 and R2 from U+. • Repeat the above step for R1 and R2until there is no violation left.

BCNF versus 3NF • Relation R (A, B, C) with a set of FDs F={AB → C, C→A}. • The keys are AB and BC. • R is not in BCNF since LHS of every FD is not a super key. • R is in 3NF since in FD AB→C the LHS is a super key, and in FD C→A the RHS is part of the key a key (A is part of the key AB).

Question 5 Consider the relation schema R(A, B, C, D, E) with FD’s, A BCDE, C D, and CE  B . Decompose the relation till it follows BCNF . Solution: • R is not in BCNF because CE B and CE is not a super key. • Decompose R: R1= {CEB}, R2={ACDE} • R1 is in BCNF • R2 is not in BCNF, because C  D and C is not a super key • Decompose R2: R21= {C,D} , R22={A,C,E} • R1,R21,R22 are in BCNF.

Question 6 Consider a relation R=(A,B,C,D,E) with the following functional dependencies, S= {BC  ADE, D  B}. a) Find all candidate keys. Solution: The keys are {B,C} and {C,D}. • {B,C} is a key from BC ADE. • To get the key {C,D}: from D B we get B, with B and C we have BC  ADE.

Question 6 (Contd..) Consider a relation R=(A,B,C,D,E) with the following functional dependencies, S= {BC  ADE,D  B}. b) Identify a normal form that R satisfies (e.g.: 3NF, or BCNF). Solution: The relation is in 3NF. If the left hand side (LHS) of every FD is a super key or the right hand side (RHS) is a part of a key. But not BCNF because D is not a super key which violates BCNF.

Question 7 Consider a relation R = (A,B,C,D,E) with the following functional dependencies, S= {CE  D,D  B,C  A}. a) Find all candidate keys. Solution: The only key is {C,E} • To get the key CE, we can do the following: • From CE  D and D B, we obtain CE B. • From CE  D and C  A, we obtain CE  AD.

Question 7 (Contd..) Consider a relation R = (A,B,C,D,E) with S = {CE  D,D  B,C  A}. b) If the relation is not in BCNF, decompose it until it becomes BCNF. Solution: Relation R is not in BCNF. Step 1: Decomposes R into R1=(A,C) and R2=(B,C,D,E). Resulting R1 is in BCNF. R2 is not. Step 2: Decompose R2 into, R21=(C,D,E) and R22=(B,D). Both relations are in BCNF.

CS 440 Database Management Systems