CS186 Fall 2002 - Exercise on Functional Dependencies and BCNF Decomposition - Prof. M. Fr | Assignments Computer Science

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

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

Exercise:

ConsiderthefollowinginstanceofaTicketholderschemathathasTicketHolderTable(Concert,Artist,

Theater,Date,Ticketholder,Seat)asattributesandthefollowingfunctionaldependencies:

o{Concert}->{Artist}

o{Concert,Ticketholder}->{Seat}

o{Date,Ticketholder}->{Concert}

o{Date,Artist}->{Theater}

o{Date,Theater}->{Concert}



Concert



Artist Theater



Date Ticketholder



Seat



1234 DavidMurray 4 June7,7:00PM



2154 47A



1234 DavidMurray 4 June7,7:00PM



1129 47B



1234 DavidMurray 4 June7,7:00PM



8510 12A



1234 DavidMurray 4 June7,7:00PM



4243 11C

7253 JoeHenderson



2 June7,9:00PM



9821 13F

7253 JoeHenderson



2 June7,9:00PM



8435 47H

7253 JoeHenderson



2 June7,9:00PM



2835 1A

1325 JoeyBaron 3 June7,8:00PM



7235 1A

1325 JoeyBaron 3 June8,8:00PM



8510 1B

1325 JoeyBaron 3 June8,8:00PM



2449 1C

7562 SteveColeman



2 June8,7:30PM



9821 1A

7562 SteveColeman



2 June8,7:30PM



8510 1B

7562 SteveColeman



2 June8,7:30PM



2154 1C



QuestionsontheOriginalSchema

1.GiveanexampleofananomalyresultingfromanupdateintheArtistfield.

2.Giveanexampleofaninsertionanomalythatcouldoccur.

3.WhatwouldhappenifalltheticketholdersweretoreturntheirticketstotheDavidMurrayconcert?

BCNFTesting&Decomposition

Decompositionoftheschemacaneliminatetheaboveproblems!

4.ConsidertheFD{Date,Artist}->{Theater}.IsTicketHolderTableinBCNF?Explainwhy.

5.Now,decomposetherelationbasedontheabovefunctionaldependency.ThedecompositionoftheBase

relationresultsintworelations,Base1andBase2.WhatarethecolumnsinBase1–Base2?

WhatarethecolumnsinBase1 Base2?

Partial preview of the text

Download CS186 Fall 2002 - Exercise on Functional Dependencies and BCNF Decomposition - Prof. M. Fr and more Assignments Computer Science in PDF only on Docsity!

Fall 2002 – CS 186 9/17/

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

Exercise : Consider the following instance of a Ticketholder schema that has TicketHolderTable(Concert, Artist, Theater, Date, Ticketholder, Seat) as attributes and the following functional dependencies: o { Concert } -> { Artist } o { Concert , Ticketholder } -> { Seat } o { Date , Ticketholder } -> { Concert } o { Date , Artist } -> { Theater } o { Date , Theater } -> { Concert }

Concert Artist Theater Date Ticketholder Seat 1234 David Murray 4 June 7, 7:00 PM 2154 47A 1234 David Murray 4 June 7, 7:00 PM 1129 47B 1234 David Murray 4 June 7, 7:00 PM 8510 12A 1234 David Murray 4 June 7, 7:00 PM 4243 11C 7253 Joe Henderson 2 June 7, 9:00 PM 9821 13F 7253 Joe Henderson 2 June 7, 9:00 PM 8435 47H 7253 Joe Henderson 2 June 7, 9:00 PM 2835 1A 1325 Joey Baron 3 June 7, 8:00 PM 7235 1A 1325 Joey Baron 3 June 8, 8:00 PM 8510 1B 1325 Joey Baron 3 June 8, 8:00 PM 2449 1C 7562 Steve Coleman 2 June 8, 7:30 PM 9821 1A 7562 Steve Coleman 2 June 8, 7:30 PM 8510 1B 7562 Steve Coleman 2 June 8, 7:30 PM 2154 1C

Questions on the Original Schema

Give an example of an anomaly resulting from an update in the Artist field.
Give an example of an insertion anomaly that could occur.
What would happen if all the ticketholders were to return their tickets to the David Murray concert? BCNF Testing & Decomposition Decomposition of the schema can eliminate the above problems!
Consider the FD { Date , Artist } -> { Theater }. Is TicketHolderTable in BCNF? Explain why.
Now, decompose the relation based on the above functional dependency. The decomposition of the Base relation results in two relations, Base1 and Base2. What are the columns in Base1 – Base2? What are the columns in Base1 Base2?

Fall 2002 – CS 186 9/17/

9/17/02-Version 4-AS Page 2

Testing Properties of the Decomposition Our next task is to ensure that the decomposition we just performed is a lossless-join; if it is not, we will be unable to reconstruct the original relation!

Is the decomposition lossless-join? Prove the answer based on the columns in Base1 and Base2, and the FDs. Now we must check that the decomposition is dependency-preserving. If it is not dependency-preserving, it will be expensive to maintain the dependencies.
Is the decomposition dependency-preserving?
Assuming that { Date, Artist} -> {Theater} were the only FD in this schema, how would you guarantee (by hand) that the decomposition was dependency-preserving? Completion of the BCNF Decomposition Continue decomposing the relation -- one FD at a time -- to decompose the relation into BCNF. As you decompose, check whether or not your decompositions are lossless-join and dependency-preserving.
At some point, your decomposition will not be dependency-preserving. What was your choice of FD to decompose that led to the warning?
What are the set of relations that you arrived at after all decompositions? A 3NF Decomposition Since the previous decomposition was not dependency-preserving, let us consider a 3NF decomposition instead. Using the FDs decompose the relation until you get the new 3NF decomposition.
Is the resulting decomposition lossless-join?
Is it dependency-preserving?
Briefly describe how (if) the resulting schema differs from your original BCNF schema. A Dependency-Preserving, BCNF Decomposition In general, there is no dependency-preserving BCNF decomposition of every schema. However, it happens that there is a dependency-preserving BCNF decomposition of our schema_._
Find a dependency preserving BCNF decomposition for the base relation.

CS186 Fall 2002 - Exercise on Functional Dependencies and BCNF Decomposition - Prof. M. Fr, Assignments of Computer Science

Related documents

Partial preview of the text

Download CS186 Fall 2002 - Exercise on Functional Dependencies and BCNF Decomposition - Prof. M. Fr and more Assignments Computer Science in PDF only on Docsity!