Example relations not in BCNF
--------------------------------
Students(RIN, Name, Email, Major)
RIN->Name Email ---- violates BCNF
Not in BCNF ---- violates BCNF
MusicGroups(GroupName, ArtistName, DoB, YearFounded)
GroupName -> YearFounded ---- violates BCNF
ArtistName -> DoB ---- violates BCNF
Key: GroupName ArtistName
Not in BCNF
Courses(CRN,Semester,Year,Section,CourseCode,CourseName)
CRN-> Semester Year Section CourseCode
CourseCode -> CourseName ---- violates BCNF
CourseCode Semester Year Section -> CRN
Key: CRN or CourseCode Semester Year Section
Not In BCNF
------------------------
Decomposition:
A decomposition of R into R1,R2,...,Rn is valid if R1,..,Rn make up all the attributes in R
and is given by:
R1 = project_{attributes of R1} (R)
R2 = project_{attributes of R2} (R)
...
Rn = project_{attributes of Rn} (R)
A good decomposition is:
1. lossless: required property [all decompositions should be lossless!]
2. dependency preserving [desired property!]
Lossless
--------
A decomposition of R into R1,R2,..Rn is lossless iff we are guaranteed for every
possible instance of R that R = R1*R2*...*Rn
Students(RIN, Name, Email, Major)
RIN->Name Email ---- violates BCNF
RIN Name Email Major
a1 b1 c1 d1
a1 b1 c1 d2
a2 b1 c2 d1
S1(RIN, Name, Email)
RIN Name Email
a1 b1 c1
a1 b1 c1
a2 b1 c2
S2(Name, Major)
Name Major
b1 d1
b1 d2
Result = S1 * S2
RIN Name Email Major
a1 b1 c1 d1
a1 b1 c1 d2
a2 b1 c2 d1
a2 b1 c2 d2 -- extra tuple, not in the original student relation
This decomposition is not lossless!
Chase Algorithm For Checking Lossless Decompositions
----------------------------------------------------
Given a relation R a set fds F and its decomposition R1, ...,Rn,
First construct a canonical relation that has a tuple for each
relation in the decomposition:
--- for each tuple for Ri, if the attribute is in Ri,
we represent it as an unsubscripted variable (known value!)
otherwise, we are going to have a subscript (a distinct number
for each tuple!)
Given a canonical relation, apply all possible fd in F, X->Y
such that if you have two tuples with the same X values,
then make the Y values the same:
-- if one tuple has no subscript for an attribute in Y,
then change all the variables to no subscript, otherwise
make all of them the same (same subscript).
Continue until no more changes are possible.
When finished, if there is a tuple with no subscripts, then this relation is
lossless.
If there is no such tuple, then this resulting canonical relation is a
counter example of why this is a lossy decomposition.
(no subscript = known value, has subscript = unknown value/variable!)
Ex:
Students(RIN, Name, Email, Major)
RIN->Name Email
S1(RIN, Name, Email)
S2(Name, Major)
RIN Name Email Major
r n e m1 -- for s1
r2 n e2 m -- for s2
No two tuples have the same rin, so this is the final form.
Lossy decomposition!
S1: (r,n,e), (r2,n,e2)
S2: (n,m1), (n,m)
S1*S2:
RIN Name Email Major
r n e m1
r2 n e2 m
r n e m
r2 n e2 m1
Ex:
Students(RIN, Name, Email, Major)
RIN->Name Email
S1(RIN, Name, Email)
S2(RIN, Major)
RIN Name Email Major
r n e m1
r n2 e2 m
Apply RIN -> name email
RIN Name Email Major
r n e m1
r n e m
Last row has no subscript, so it is lossless!
------------------------
R(A,B,C,D,E,F,G)
AB->C
CD->EF
F->A
R1(A,B,C,D,E)
R2(C,D,F)
R3(A,B,D,G)
R4(A,F)
A B C D E F G
a b c d e f1 g1
a2 b2 c d e2 f g2
a b c3 d e3 f3 g
a b4 c4 d4 e4 f g4
Apply: AB->C
A B C D E F G
a b c d e f1 g1
a2 b2 c d e2 f g2
a b c d e3 f3 g
a b4 c4 d4 e4 f g4
Apply: CD->EF
A B C D E F G
a b c d e f g1
a2 b2 c d e f g2
a b c d e f g
a b4 c4 d4 e4 f g4
Third row has no subscript, this is a lossless decomposition!