BCNF Decomposition of a Relational Schema: A Fall 2002 CS 186 Class Solution - Prof. M. Fr | Assignments Computer Science

Fall2002–CS186  9/17/02

9/17/02-Version4-AS Page1



Solutions:

QuestionsontheOriginalSchema

1.Ifsay,concert1325nowhasJoeHendersonastheArtist,weneedtoupdateallthethreerecordswith

concert1325inthetable.Notupdatingallthethreerecordswillcauseananomaly.

2.EventhoughConcertdeterminesArtist,wecannotinsertanewticketholderunlessweknowbothConcert

andArtist.WealsocannotaddaConcertwithouthavingatleastoneticketholder.

3.WewillloseinformationsuchastheArtistforConcert1234andtheTheaterforDavidMurrayonJune7,

7:00PM(adeletionanomaly).

BCNFTesting&Decomposition

ToworkwithBCNF,weneedtoknowthesuperkeysofthisschema.

Abbreviatetheattributesby:

C–Concert,A–Artist,T–Theater,D–Date,H–Ticketholder,S–Seat

DenoteouroriginalrelationBaseasCATDHS.Wehavefunctionaldependencies:

C->A,CH->S,DH->C,DA->T,DT->C

NoticethatthereisnoDandHontheright-handsideofanyfunctionaldependencies.ThisimpliesthatifDand

Hcannotbedeterminedbyanyotherattributes,DHmustbepartofanycandidatekey.Indeed,DHistheonly

candidatekeyofthisrelation.

Wecancheck:DH->DH

DH->DHC(DH->C)

DH->DHAC(C->A)

DH->DACHS(CH->S)

DH->DACHST(DA->T)

SoanysetofattributescontainingDHisasuperkeyofthisrelation.

4.TocheckifDA->TviolatesBCNF,weneedtolookat2statements:

1)WhetherT



DA

2)WhetherDAisasuperkey.

Sincebothstatementsarefalse,weconcludethatDA->TviolatesBCNF.

5.WedecomposeCATDHSintoCADHSandATD.Base1representsthenewrelationATD.Base2represents

thenewrelationCADHS.Base1–Base2=TbecauseTcanbedeterminedbyAD.Base1 Base2=AD.

Partial preview of the text

Download BCNF Decomposition of a Relational Schema: A Fall 2002 CS 186 Class Solution - Prof. M. Fr and more Assignments Computer Science in PDF only on Docsity!

Solutions : Questions on the Original Schema

If say, concert 1325 now has Joe Henderson as the Artist, we need to update all the three records with concert 1325 in the table. Not updating all the three records will cause an anomaly.
Even though Concert determines Artist, we cannot insert a new ticket holder unless we know both Concert and Artist. We also cannot add a Concert without having at least one ticket holder.
We will lose information such as the Artist for Concert 1234 and the Theater for David Murray on June 7, 7:00PM (a deletion anomaly). BCNF Testing & Decomposition To work with BCNF, we need to know the superkeys of this schema. Abbreviate the attributes by: C – Concert, A – Artist, T – Theater, D – Date, H – Ticketholder, S – Seat Denote our original relation Base as CATDHS. We have functional dependencies: C -> A, CH -> S, DH -> C, DA -> T, DT -> C Notice that there is no D and H on the right-hand side of any functional dependencies. This implies that if D and H cannot be determined by any other attributes, DH must be part of any candidate key. Indeed, DH is the only candidate key of this relation. We can check: DH -> DH DH -> DHC (DH -> C) DH -> DHAC (C -> A) DH -> DACHS (CH -> S) DH -> DACHST (DA -> T) So any set of attributes containing DH is a superkey of this relation.
To check if DA -> T violates BCNF, we need to look at 2 statements:
1. Whether T DA
2. Whether DA is a superkey. Since both statements are false, we conclude that DA -> T violates BCNF.
We decompose CATDHS into CADHS and ATD. Base1 represents the new relation ATD. Base2 represents the new relation CADHS. Base1 – Base2 = T because T can be determined by AD. Base1 Base2 = AD.

Testing Properties of the Decomposition

Yes. We need to check if F+ contains either the FD Base1 Base2 -> Base1 or the FD Base1 Base2 -> Base Base1 Base2 -> Base1 Base1 Base2 -> Base ATD CADHS = AD -> ATD? ATD CADHS = AD -> CADHS? Ans: yes Ans: No Since one of the answers is yes, the decomposition is lossless-join. Note that the decomposition is done by following the observation below (*): If an FD X -> Y holds over a relation R and X Y is empty, the decomposition of R into R-Y and XY is lossless (Page 436 R&G). CATDHS is decomposed to CATDHS – T and ADT. So it is guaranteed to be lossless.
Yes. Although intuitively it seems like DT->C cannot be enforced unless we do a join, this is misleading since we have to consider the closure of the union of the two functional dependency sets.

A more rigorous proof is to check if (FCADHS FATD)+ = F+

F+ = {C->A, CH->S, DH->C, DA->T, DT->C, DH->A, DH->T, DH->S, DT->A, DA->C}

FCADHS = {C->A, CH->S, DH->C, DH->A, DA->C, DH->S}, FATD = {DA->T, DT->A}.

We have omitted trivial FDs for brevity.

Note that (FCADHS FATD) FCATDHS. However, (FCADHS FATD)+ = FCATDHS+

(We can derive DT->C from DT->A and DA->C, and DH->T from DH->A and DA->T). Therefore, we conclude that the decomposition is dependency-preserving. Note that although we cannot explicitly enforce DT->C, it is implicitly enforced by enforcing DT->A and DA->C.

DA->T can be enforced in Base1, which has all three attributes {D, A, T}. To enforce the FD we need to declare DA as a candidate key of Base1. Completion of the BCNF Decomposition We have decomposed the relation into CADHS and ATD using the FD AD->T. Examine other functional dependencies one by one. C->A: Need to check only CADHS.

1) A C.

C is not a superkey of CADHS. So C->A violates BCNF. Decompose CADHS into CDHS and CA. This is lossless because we construct this decomposition by following the observation (*). FCDHS = {CH->S, DH->C, DH->S}, FCA = {C->A}, FCADHS = {C->A, CH->S, DH->C, DH->A, DA->C, DH->S},

(FCDHS FCA)+^ FCADHS+, since DA->C is not in (FCDHS FCA)+.

The decomposition is not dependency-preserving.

BCNF Decomposition of a Relational Schema: A Fall 2002 CS 186 Class Solution - Prof. M. Fr, Assignments of Computer Science

Related documents