(Consider schema(R, F), where R=ABCDE, F={E?DB, D?A, CA?E, BE?D} Find all the ke
ID: 649367 • Letter: #
Question
(Consider schema(R, F), where R=ABCDE, F={E?DB, D?A, CA?E, BE?D}
Find all the keys of the relation, and for each one, prove it is indeed a key.
Decompose the schema into a set of BCNF schemas. Is this decomposition lossless? Is it dependency-preserving? Explain your answer to get full credits.
Write an SQL CREATE ASSERTION statement to enforce E?DB.
What is the minimal cover of F? Describe each step for full credits.
Decompose the schema into a set of 3NF schemas. Is this decomposition lossless? Is it dependency-preserving? Explain your answer to get full credits.
Explanation / Answer
Answer:
Given: The following set F of functional dependencies
for Relation schema R = (A, B, C, D, E).
E ? DB
D ? A
CA ? E
BE ? D
List for the candidate keys for R.
Note: Some shorthand representation of the result should be acceptable as long as the nontrivial
members of F + are found.
Starting with E ? DB, we can conclude: E ? D and E ? B.
Since E ? D and D ? A, E ? A (decomposition, transitive)
Since E ? A and CA ? E, BE ? D (union, decomposition, transitive)
Since D ? A, (transitive)
Since E ? E we have (reflexive)
A ? ABCDE from the above steps (union)
Since E ? D, E ? ABCDE (transitive)
Since CA ? E, CD ? ABCDE (transitive)
Since D ? A and DB ? CD, DB ? ABCDE (augmentative, transitive)
Also, C ? C, D ? D, CD ? D, etc
Therefore, any functional dependency with A, E, DB, or CD on the left hand side of the arrow is in F +, no matter which other attributes appear in the FD.
Allow * to represent any set of attributes in R, then F + is CD ? B, CD ? D,
C ? C, D ? D, CD ? CD, B ? D, B ? B, B ? CD, and all FDs of the form A ? ? ?, DB ? ? ?, CD ? ? ?, E ? ? ? where ? is any subset of {A, B, C, D, E}.
The candidate keys are A, BC, CD, DB and E.
We know that CA ? E is nontrivial and the left hand side is not a superkey.
We derive the relations {( B, C, D, E), (C, E)}. This is in BCNF
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.