9. Consider the following relation: Visitors (ano, artist, mno, museum, price, v
ID: 3816532 • Letter: 9
Question
9. Consider the following relation: Visitors (ano, artist, mno, museum, price, visitDate)
Each tuple in the table satisfies the following properties: the artist with ano and visited mno on the visitDate.
The FDs are: ano artist; mno museum, price.
A) specify all keys for relation Visitors.
B) if Visitors is not in BCNF, decompose into BCNF relations. Underline keys of each relation. (Specify cadidate keys for each relation.
C) Now add the following assumptions: 1) an artist cannot visit different museums on same dates, and 2) an artist does not visit the same museum twice. Specify FDs that satisfy the above assumptions.
D) Based on all FDs (the original and the additional assumptions) together, specify all keys for the relation. If Visitors is not in BCNF based on all the FDs, decompose into BCNF relations. Underline keys of each relation. (Specify cadidate keys for each relation.)
Explanation / Answer
Given FDs for the relation Visitors (ano, artist, mno, museum, price, visitDate) are -
ano artist
mno museum, price
a) As we see in the FDs,
- combining {ano,mno} will functionally determine (ano, artist, mno, museum, price).
- visitDate is left out.
Since any key should functionally determine all the parameters, we should combine visitDate with the key.
Therefore the key here is {ano,mno,visitDate}
b) Since we have only 1 key possible here and that key functionally determines all the parameters,
the relation is in BCNF.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.