|
Project 6 — Database Design SOLUTIONS
Academic Integrity: I completed this project without the assistance of others. I understand that cheating, helping others to cheat, or failing to report such actions is dishonest and wrong. Such acts could result in disciplinary action against me.
· Email your solution in an attached PDF file to both the instructor and the TA. · The subject should be “Project6 - " + Your First Name + Last Name e.g. Project6 - JohnDoe · Your file name should be “Project6 - " + Your First Name + Last Name + ".PDF” e.g. Project6 - JohnDoe.pdf · Include your name inside the PDF file. · For full credit, your assignment must be received by 11:59 pm on the due date.
Given R = (A, B, C, G, H, I) and F = {A ® B, A ® C, CG ® H, CG ® I, B ® H}
1. Compute the canonical cover of F (Fc) using Armstrong’s Axioms.
Union: A ® BC, CG ® HI, B ® H
2. Identify all candidate keys for R.
Compute closure of attributes: A+ ® ABCH B+ ® BH C+ ® C AB+ ® ABCH AC+ ® ABCH CG+ ® CGHI AG+ ® ACGBHI
AG is the ONLY candidate key for R.
Given R = (A, B, C) and F = {A ® BC, B ® C, A ® B, AB ® C}
3. Compute the canonical cover of F (Fc) using Attribute Closure. Show your work.
Reduce RHSs to singletons and remove duplicate FDs {A ® B, A ® C, B ® C, A ® B, AB ® C} {A ® B, A ® C, B ® C, AB ® C} Remove extraneous attributes from LHS A+ ® ABC Since A+ includes B, we don’t need B B+ ® BC Remove B from AB®C {A ® B, A ® C, B ® C, A ® C} - remove redundant A ® C {A ® B, A ® C, B ® C } Remove redundant FDs A ® B A+ ® AC Not redundant A ® C A+ ® ABC Redundant B ® C B+ ® B Not Redundant
Fc = { A ® B, B ® C }
4. Explain why R is not in BCNF. Only candidate key is A B ® C FAILS 1. B ® C is not trivial 2. B is not a superkey
5. Decompose R into 2 schemas that are in BCNF. Show how the decomposition will result in lossless join and dependency preservation.
1. Decomposition a. R1 = (A,B) R2 = (B,C) 2. Lossless a. R1 ∩ R2 = {B } b. B is superkey for R2 3. Dependency Preserving a. A ® B holds on R1 b. B ® C holds on R2
Given R = (J, K, L ) and F = { JK ® L, L ® K }
6. Compute the canonical cover of F (Fc).
F = { JK ® L, L ® K } – that is Fc
7. Identify all candidate keys for R.
Compute closure of attributes: J+ ® J K+ ® K L+ ® LK JK+ ® JKL JL+ ® JLK KL+ ® KL
JL and KL are candidate keys for R.
8. Explain why R is not in BCNF?
L ® K is not trivial L is not a superkey
9. Decompose R into BCNF. Show how you arrived at that decomposition. Explain whether it is a lossless join and whether dependencies are preserved.
BCNF decomposition Violation is L ® K Decompose Ri by Removing b attributes from Ri Create new relation using (a, b) (J,L) and (L,K)
Lossless: Yes => R1 ∩ R2 = { L } and L is superkey for R2 Dep Pres: No => JK ® L is not preserved in either R1 or R2
10. Explain whether or not R is in 3NF.
1. Compute a+ for each FD in F 2. FD passes the test if ONE of the following is true a. FD is trivial b. a is a superkey for R c. b ® a is contained in a candidate key for R
R is in 3NF
Explain what is wrong with the following statement
11. A decomposition of R into R1 and R2 (with corresponding F decomposed into F1 and F2) preserves dependencies if and only if ( F1 ∪ F2 ) = F.
It is not when the union of the decomposed F’s is the same as F, but the when the closure of their unions equals the closure of F. ( F1 ∪ F2 )+ = F+
F’ may not equal F, but F’+ may equal F+
|