Problem Axiom of addition If X Y and

Problem • Axiom of addition: – If X Y and W V then XW YV • Prove this axiom using Armstrong’s Axioms or show that it is not sound 1. X ® Y (given) 2. XW ® YW (augmentation of 1) 3. W ® V (given) 4. YW ® YV (augmentation of 4) 5. XW ® YV (transitivity of 2, 4) 1

Problem • Axiom of confusion: – If XZ Y, YZU X, W V, V WU then XW YV • Prove this axiom using Armstrong’s Axioms or show that it is not sound U V W X Y Z u v w x y 1 z 1 u v w x y 2 z 2 2

Problem • R = (A, B, C, D). For each of the following: – find all keys – determine the normal form of R 1. C → D, C → A, B → C B, not 3 NF 2. ABC → D, D → A ABC, DBC 3 NF 3. A → B, BC → D, A → C A, not 3 NF 4. AB → C, AB → D, C → A, D → B AB, CB, AD, CD, 3 NF 3

Problem • Proposition: Let R be a relation containing exactly two attributes. Let F be a set of functional dependencies. Then, R is in BCNF. • Prove or give a counter-example. 4

• Proof: Let R(A, B) be a relation with 2 attributes and let F be functional dependencies over R. Every dependency other than A B, B A, A AB and B BA is trivial. In all the dependencies detailed, the left side is a key. 5

Problem • Proposition: Let R be a relation and F be a set of functional dependencies. Suppose that every key in F has a single attribute. Then, R is in BCNF. • Prove or give a counter-example. • Counter Example: R(A, B, C) A B, B C 6

Problem • Proposition: Let R be a relation and F be a set of functional dependencies. Suppose that every key in F has a single attribute. Then, R is in BCNF if and only if R is in 3 NF. • Prove or give a counter-example. 7

• Correct, by an easy case analysis. If R is in BCNF, then it is in 3 NF by definition. Suppose R is in 3 NF. Let X A be a dependency in R. If this dependency is trivial or X is a super key, then this dependency does not contradict the BNCF requirement. If A is a field in a key, then A is a key, and hence, X is a superkey 8

Problem • Proposition: Let R be a relation and F be a set of functional dependencies. Suppose that every dependency in F has one attribute on the left side. Then, R is in BCNF if and only if R is in 3 NF • Prove or show a simple counter example. • Counter example: R(A, B, C) A B, B A 9

Problem • Find small(est) examples of R and F such that: – R is in BCNF R(A), F is empty – R is in 3 NF, but not in BCNF R(A, B, C), F = {A B, B A} – R is not in 3 NF R(A, B, C), F={A B} 10

Problem • R=(ABCD) • F={A->B, C->D, A->D} • What is the normal form of R? Not 3 NF • We have a decomposition R 1=(ABC), R 2=(BCD) • Is this a lossless join decomposition? Yes 11

Problem • R=(ABC) • F={A->B, BC->A} • What is the normal form of R? 3 NF • We have a decomposition R 1=(AB), R 2=(AC) • Is this a lossless join decomposition? Yes 12

Problem • Let R=ABCDEG • Let F={C->D, E->C, EG->A, G->B} • What are all the keys of R? EG • What is the normal form of R? Not 3 NF • Is the following decomposition lossless? Yes – R 1=AEG, R 2=CE, R 3=BG, R 4=DEG 13