DATABASE PROBLEMS: 1. Consider the following relation R(A,B,C,D) {C->D, BC->A} (
ID: 3710769 • Letter: D
Question
DATABASE PROBLEMS:
1. Consider the following relation R(A,B,C,D) {C->D, BC->A}
(a) Show all candidate keys.
(b) What is the highest normal form (up to BCNF)? Why?
(c) If it is not in BCNF, can you losslessly decompose R into component relations in BCNF while preserving functional dependencies? If yes, how. If no, why?
2. (a) Provide the simplest example of R that is in BCNF by listing its attributes and functional dependencies.
(b) It is known that for R(A,B,C,D,E):
1. R has exactly 2 candidate keys.
2. AB is the first candidate key.
3. C and D are non-prime attributes.
4. ACE is not a superkey.
What is the second candidate key besides AB?
Explanation / Answer
DATABASE PROBLEMS:
1. Consider the following relation R(A,B,C,D) {C->D, BC->A}
(a) Show all candidate keys.
Answer)
B C are the candidate keys.
As we can reach all the A,B,C,D using B and C.
(b) What is the highest normal form (up to BCNF)? Why?
Answer)
The highest Normal form is upto 1NF.
The candidates keys are { B,C}, The set of key attributes are: { B,C }.
For functional dependency C --> D,
violates definition of 2NF -- LHS is a proper subset of some candidate key
(c) If it is not in BCNF, can you losslessly decompose R into component relations in BCNF while preserving functional dependencies? If yes, how. If no, why?
Answer)
Convering the above Relation into BCNF.
The Functional Dependency C --> D violates BCNF as the LHS is not superkey. Table is split into the two below:
Relation 1 = (A,B,C,D)
Relation 2= (C,D )
With FD:
C-->D
Relation 3= (A,B,C )
With FD:
BC-->A
The above Relation 2 and 3 are in BCNF.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.