Lecture 5 In Class review
September 14, 2020

Announcements:
-----------------------
-Hw1 due tonight at midnight

-Hw2 will be out tomorrow morning, due next monday at midnight

-Almost done with normalization, thursday we are starting to talk
 about ER diagrams


Concepts to review:
------------------------
- Any from last week?

- Decompositions
   - Lossless decompositions
   - Dependency preserving decompositions

- 3NF decomposition
- BCNF decomposition

-------------

Decompositions:

R(A,B,C,D,E,F,G)

ACD->E
EC->F
AE->BC

R1(A,B,C,D,E)
R2(A,E,C,F)
R3(B,C,G)

Rnew = R1*R2*R3 

A   B   C   D   E   F   G
a   b   c   d   e   f1  g1
a   b2  c   d2  e   f   g2
a3  b   c   d3  e3  f3  g

EC->F

A   B   C   D   E   F   G
a   b   c   d   e   f  g1
a   b2  c   d2  e   f   g2
a3  b   c   d3  e3  f3  g

AE->BC

A   B   C   D   E   F   G
a   b   c   d   e   f   g1
a   b   c   d2  e   f   g2
a3  b   c   d3  e3  f3  g

This is a lossy decomposition!

and this a counter example.


R(A,B,C)
A->B

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

A   B  C
a   b  c1
a2  b  c

Lossy!

R1 (a,b) (a2,b)
R2 (b,c) (b,c1)

R1*R2

A   B  C
a   b  c1
a   b  c
a2  b  c
a2  b  c1


R(A,B,C)
A->B

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

A   B   C
a   b   c1
a   b2  c

A->B

A   B   C
a   b   c1
a   b   c  <- no subscript!
Not lossy

R1: (a,b)
R2: (a,c1), (a,c)

R1*R2 = R

----------------
3NF Decomposition:

R(A,B,C,D,E,F,G)

ACD->E
EC->F     x
AE->BC    x

Keys: ACDG, AEDG

in 3NF?

R1(A,C,D,E)    {ACD->E}   key: ACD
R2(E,C,F)      {EC->F}    key: EC
R3(A,B,C,E)    {AE->BC}   key: AE
R4(A,C,D,G)    {}         key: ACDG


Chase!

A  B  C  D  E  F  G
a  b1 c  d  e  f1 g1
a2 b2 c  d2 e  f  g2
a  b  c  d3 e  f3 g3
a  b4 c  d  e4 f4 g

ACD->E

A  B  C  D  E  F  G
a  b1 c  d  e  f1 g1
a2 b2 c  d2 e  f  g2
a  b  c  d3 e  f3 g3
a  b4 c  d  e  f4 g

EC->F    

A  B  C  D  E  F  G
a  b1 c  d  e  f  g1
a2 b2 c  d2 e  f  g2
a  b  c  d3 e  f  g3
a  b4 c  d  e  f  g

AE->BC   

A  B  C  D  E  F  G
a  b  c  d  e  f  g1
a2 b2 c  d2 e  f  g2
a  b  c  d3 e  f  g3
a  b  c  d  e  f  g   <--- no subscript!

Lossless decomposition!

----------------

BCNF Decomposition!

Students(RIN, Name, Email, Major)

RIN->Name Email  x violates BCNF

Key: RIN, Major


RIN+ = {RIN, Name, Email)

S1(RIN, Name, Email)   {RIN->Name Email} Key: RIN, in BCNF

S2(RIN, Major)         {}  Key: RIN Major, in BCNF

-----------

MusicGroups(GroupName, ArtistName, DoB, YearFounded, YearJoined)
GroupName -> YearFounded     x violates BCNF
ArtistName -> DoB            x violates BCNF
GroupName ArtistName -> YearJoined  ok

Key: GroupName ArtistName

GroupName -> YearFounded
GroupName+  = {GroupName, YearFounded}


MG1(GroupName, YearFounded)
     GroupName -> YearFounded      Key: GroupName, in BCNF

MG2(GroupName, ArtistName, DoB, YearJoined)
     ArtistName -> DoB            X violates BCNF
     GroupName ArtistName -> YearJoined  ok

     Key: ArtistName, GroupName
     Not in BCNF

ArtistName -> DoB

MG21(ArtistName, DoB)
     ArtistName -> DoB
     Key: ArtistName, in BCNF

MG22(GroupName, ArtistName, YearJoined)
     GroupName ArtistName -> YearJoined
     Key: GroupName ArtistName
     in BCNF


Result:

MG1(GroupName, YearFounded)
MG21(ArtistName, DoB)
MG22(GroupName, ArtistName, YearJoined)

-------------------

R(A,B,C,D,E,F,G)

AC->DEF
AF->BG  x violates BCNF
BG->D   x violates BCNF

Keys: AC

Take out! AF->BG

AF+ = {A,F,B,G,D}

R1(A,B,D,F,G)
     {AF->BG, BG->D},  Key: AF, not in BCNF 

R2(A,C,E,F)
     {AC->EF}, Key: AC, in BCNF


Take out BG->D

R11(B,D,G)   BG->D in BCNF

R12(A,B,F,G)  AF->BG  in BCNF


Result:

R2(A,C,E,F)
     {AC->EF}, Key: AC, in BCNF
R11(B,D,G)   {BG->D} in BCNF
R12(A,B,F,G)  {AF->BG}  in BCNF


Is this dependency preserving?
 
Original F = {AC->DEF, AF->BG, BG->D}

FD = {AC->EF,BG->D,AF->BG}

In FD, AC+ = {A,C,E,F,B,G,D}

since DEF in AC+, then AC->DEF is preserved!