Announcements

Announcements#

  • Hw#1 due today at midnight

  • Lecture exercise #7 out at 2pm today, due midnight on saturday

  • Hw#2 to be handed out tomorrow, due next thursday at midnight

  • Exam #1 on Monday September 29 and 12pm

    • Please send all accommodations by next week monday midnight

  • Exam #2 to be moved from November 6 Thursday to November 10 Monday (during class)

Decomposition of relations#

A decomposition of a relation R with functional dependencies F is given by a set of relations R1, R2,…, Rn such that all attributes in R1, R2,…, Rn are a subset of attributes in R

If you had an instance of R(A,B,C,D,E), and you decompose into R1(A,B,C) and R2(C,D,E)

Find R1 = Project_(A,B,C) ®
R2 = Project_(C,D,E) ®

A decomposition is lossless if R1*R2 = R (natural join of all decomposed relations is guaranteed to be identical to the original relation.

CHASE Algorithm for checking lossless decompositions#

Given the decomposed relations, is it possible to reconstruct the original relation?

  • For each decomposed relation, assume a tuple in the “canonical database” where the missing attributes are variables (variables: have subscripts, known values: constant, no subscript)

  • Apply functional dependencies X->Y to fill in missing values

    • If two tuples have the same value for X, if Y is known in one tuple, make the other value also known (otherwise set the two to the same variable)

  • Continue until

    1. There is a tuple with no subscripts, all variavles are known. Then this is a lossless decomposition.

    2. No more changes are possible and there is no tuple without a subscript, then this is a lossy decomposition and the resulting relation is a counter example of why it is lossy!

R(A,B,C,D,E,F) F={D->A, AD->E, AE->F, BC->A} R1(A,B,C)
R2(B,C,D)
R3(D,E,F)

Is it lossless?

A B C D E F
a b c d1 e1 f1 #for R1
a2 b c d e2 f2 #for R2
a3 b3 c3 d e f #for R3

Given D->A

A B C D E F
a b c d1 e1 f1 #for R1
a2 b c d e2 f2 #for R2
a2 b3 c3 d e f #for R3

Given AD->E

A B C D E F
a b c d1 e1 f1 #for R1
a2 b c d e f2 #for R2
a2 b3 c3 d e f #for R3

Given AE->F

A B C D E F
a b c d1 e1 f1 #for R1
a2 b c d e f #for R2
a2 b3 c3 d e f #for R3

Given BC->A

A B C D E F
a b c d1 e1 f1 #for R1
a b c d e f #for R2
a2 b3 c3 d e f #for R3

R(A,B,C) F={A->B}

R1(A,C)
R2(B,C)

A B C
a b1 c a2 b c

No further changes, this decomposition is lossy!

R1(A,C)

A C
a c
a2 c

R2(B,C)

B C
b1 c
b c

R’ = R1*R2

R’
A B C
a b1 c
a b c
a2 b1 c
a2 b c

Since R’ is not equal to R, this decomposition is lossy!

Dependency preserving decompositions#

R(A,B,C) F={A->B}

R1(A,C) F1={}
R2(B,C) F2={}

R(A,B,C,D) F={A->B,B->C,C->D}

R1(A,C) F1={A->C}
R2(B,D) F2={B->D}
R3(B,C) F3={B->C}

F1 union F2 union F3 ?equivalent= F F’= {A->C,B->D,B->C}

Is \(F'\equiv F\)?

We already know, everything in F’ is in F+.

Is everything in F implied by F’?

A->B, A+ in F’ = {A,C} so no, B is not in A+ B->C, in F’
C->D, C+ in F’= {C} so no, D is not in C+

Hence, \(F'\not\equiv F\)? and This is NOT a dependency preserving decomposition

R(A,B,C,D) F={A->B,B->C,C->D}
R4(A,B) F5={A->B}
R5(B,C) F6={B->C}
R6(C,D) F7={C->D}

F5 UNION F6 UNION F7 = F, hence This is a dependency preserving decomposition

Projecting functional dependencies to a decomposition#

Given a relation R and functional dependency F, the projection of F onto a decomposed relation R1 is the set F1 of all functional dependendices in F+ that only include attributes in R1.

R(A,B,C,D) F={A->B,B->C,C->D}

R1(A,B,D) A->ABD
B->BD
D->D
AB->ABD
AD->ABD
BD->BD

F1={A->B, B->D} #projection of F into R1

Dependency preserving decompositions#

Suppose you are given a relation R and functional dependency F, a decomposition of R into R1, …Rn where F1,…Fn are the projection of F onto R1,…,Rn, then this decomposition is dependency preserving if

(F1 union F2 union … union Fn)+ = F+.

Decompositions should always be lossless. It is desirable (but not necessary) that they are also dependency preserving.

3NF Decomposition#

Given a relation R and a set of functional dependencies F in minimal form, the 3NF decomposition is computed as follows:

  • For each fd X->Y, create a new relation with attributes (X union Y)

  • If there is no relation with all the attributes in one of the keys, then create one relation.

  • If one resulting relation R1 has all the attributes in R2 (and more), then remove R2.

3NF Decomposition results in relations in 3NF and is lossless and functional dependency preserving.

MusicGroups(Artist, DoB, Group, DateFormed, DJoined, DLeft, Genre) Artist -> DoB
Group -> DateFormed
Artist Group -> DJoined DLeft

Key: Artist, Group, Genre

3NF Decomposition:

R1(Artist, DoB) F={Artist->DoB}
R2(Group, DateFormed) F2={Group -> DateFormed}
R3(Artist, Group, DJoined, DLeft) F3 = {Artist Group -> DJoined DLeft }
R4(Artist, Group, Genre) F={}

R(A,B,C,D,E) F={AB->C, C->A, CD->E} Keys: ABD, BCD

R1(A,B,C) {AB->C, C->A}
R2(A,C) {C->A} <– subset of R1, get rid of it! R3(C,D,E) {CD->E}
R4(B,C,D) {}

R1(A,B,C) {AB->C, C->A} Key: AB, BC, not in BCNF
R3(C,D,E) {CD->E} Key: CD, in BCNF
R4(B,C,D) {} Key: B,C,D , in BCNF

R(A,B,C,D) {A->B, A->C, A->D}

(A,B)
(A,C)
(A,D)

R(A,B,C,D) {A->BCD} !in 3NF

BCNF decomposition#

Suppose a relation R and set F is not in BCNF, find X->Y in F that violates BCNF! Create two relations:

  1. Take out X->Y by constructing a new relation with attributes X+

  2. Create a new relation containing all attributes in R except for (X+ - X)

Check if the resulting relations are in BCNF, if not apply BCNF decomposition recursively to each relation that is not in BCNF form.

BCNF Decomposition is always lossless but not necessarily dependency preserving.

R(A,B,C,D) F={A->B,B->C,C->D} Key: A

B->C violates BCNF, take it out!

B+ = {B,C,D}

R1(B,C,D) F1={B->C, C->D}
Key: B not in BCNF (C is not a superkey,C->D not in BCNF)

R2(A,B) F2={A->B} (all attributes of R except B±B={C,D})
Key: A, in BCNF

Decompose R1 using C->D

R3(C,D) {C->D}, key: C in BCNF R4(B,C} {B->C}, key: B in BCNF

Done!

(A,B)
(B,C)
(C,D)

What if we did not use the closure rule!

R(A,B,C,D) F={A->B,B->C,C->D} Key: A
Took B->C (not using closure)

(B,C) {B->C} in BCNF (A,B,D) {A->B, B->D} not in BCNF B->D Take out B->D (A,B) {A->B}
(B,D) {B->D}

Final result: (B,C) {B->C} (A,B) {A->B}
(B,D) {B->D}

Not dependency preserving!

Contents