6. Consider the relational schema R(ABCDEG) with the set of functional dependenc
ID: 3907977 • Letter: 6
Question
6. Consider the relational schema R(ABCDEG) with the set of functional dependencies F = {D?C, A?BE, BC?D, E?C, AC?B, AB?C}.
a) Find all the candidate keys for R.
b) Determine what normal form this relation is in.
c) Find a minimal cover for F. You must show your work.
d) Using 3NF synthesis, find a decomposition of R that is at least in 3NF and is both lossless and dependency-preserving. You must show the projections of functional dependencies on the resulting relations.
7. Consider the relational schema R(ABCDEG) with the set of functional dependencies
F = {A?D, AC?G, EG?D, AG?C, E?G, C?DE}
a) Find all the candidate keys for R.
b) Determine what normal form the relation is in.
c) Using any of the techniques discussed in class, find a set of decomposed relations which are lossless, dependency preserving and hold to the strongest possible normal form. Be sure to identify the projections of the FD’s onto the resulting relations and state what normal form they are in.
8. Consider the following SQL query:
SELECT E.SID
FROM ENROLLED E
WHERE 50 <= E.GRADE AND E.GRADE < 60;
Describe, with the use of a diagram, which index you would create in order to make this query faster.
9. Consider the following SQL query:
SELECT S.SID, S.NAME, E.GRADE
FROM STUDENT S, ENROLLED E
WHERE S.SID = E.SID AND E.OID = 7359 AND E.GRADE < 50;
Explanation / Answer
Please Note: As per Chegg Answering Guidelines, I have answered the first question. Please Post Separate for other Questions.
6. Consider the relational schema R(ABCDEG) with the set of functional dependencies F = {D?C, A?BE, BC?D, E?C, AC?B, AB?C}.
a) Find all the candidate keys for R.
b) Determine what normal form this relation is in.
c) Find a minimal cover for F. You must show your work.
d) Using 3NF synthesis, find a decomposition of R that is at least in 3NF and is both lossless and dependency-preserving. You must show the projections of functional dependencies on the resulting relations.
Answer)
Relational schema R = (ABCDEG)
Dependencies F = {D->C, A->BE, BC->D, E->C, AC->B, AB->C}
a) Find all the candidate keys for R.
The candidate keys for R are:
A,G
b) Determine what normal form this relation is in.
The normal form this relation is: 1NF
c) Find a minimal cover for F. You must show your work.
Rewrite the FD into those with only one attribute on RHS:
D->C
A->B
A->E
BC->D
E->C
AC->B
AB->C
Remove trivial FDs:
D->C
A->B
A->E
BC->D
E->C
AC->B
AB->C
Minimize LHS of each FD:
D->C
A->B
A->E
BC->D
E->C
A->B
A->C
Remove redundant FDs and finally:
D->C
A->E
BC->D
E->C
A->B
d) Using 3NF synthesis, find a decomposition of R that is at least in 3NF and is both lossless and dependency-preserving. You must show the projections of functional dependencies on the resulting relations.
FDs:
D --> C
A --> E
B,C --> D
E --> C
A --> B
candidates keys : (A,G)
Merging FDs with same LHS and whose RHS are non-key attributes::
D --> C
A --> B,E
B,C --> D
E --> C
Table 1: D,C FD: D --> C
Table 2: A,B,E FD : A --> E,B
Table 3: B,C,D FD : B,C --> D, D --> C
Table 4: E,C FD : E --> C
Table 5: A,G
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.