1 / 17

Lecture Week 14

Review for Final Exam. Lecture Week 14. Problems on Functional Dependencies and Normal Forms. IS -> ISBQO IS is the only key. No, because of I->B and B->O. Problem 1. R = {Broker (B), Office (O), Investor (I), Stock (S), Quantity (Q)} F = { I -> B, IS -> Q, B -> O}

cicily
Download Presentation

Lecture Week 14

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Review for Final Exam Lecture Week 14

  2. Problems on Functional Dependencies and Normal Forms

  3. IS -> ISBQO IS is the only key No, because of I->B and B->O Problem 1 R = {Broker (B), Office (O), Investor (I), Stock (S), Quantity (Q)} • F = { I -> B, IS -> Q, B -> O} • a.       What are the keys of the relation? • b.      Is the relation in BCNF?

  4. Problem 1 (contd.) R1=(I,B), R2 = (I,S,Q,O) R2 is not BCNF (I->O) R21= (I,S,Q), R22= (I,O) No, because of B->O is not preserved. You need a join to enforce it! F = { I -> B, IS -> Q, B -> O} • Produce a lossless-join, BCNF decomposition of the original relation • Is your decomposition dependency-preserving?

  5. Problem 2 Consider the relation R=(A,B,C,D,E) and F ={ A->BC, CD->E, B-> D, E-> A} a.       Is this schema in 3NF? Note: no attributes appear only on left sides or only on right sides. We start with computing the closure of single attributes: A+ = ABCDE; B+ = BD; C+ = C; D+ = D; E+ = EABCD. It follows that A and E are candidate keys. Now we consider combinations of two attributes: BC+ = BCDEA, BD+ = BD, CD+ = CDEAB. It follows that BC and CD are candidate keys. There are no more combinations to be considered. The candidate keys are: A, E, BC, and CD.

  6. Problem 2 (contd.) Consider the relation R=(A,B,C,D,E) and F ={ A->BC, CD->E, B-> D, E-> A} a.       Is this schema in 3NF? The candidate keys are: A, E, BC, and CD. We reduce F in canonical form: F={A->B, A->C, CD->E, B->D, E->A} A->B is in 3NF since A is a key; A->C is in 3NF since A is a key; CD->E is in 3NF since CD is a key; B->D is in 3NF since D is part of a key; E->A is in 3NF since E is a key. It follows that the given schema is in 3NF.

  7. ABCDE Also BCNF BD ABCE Problem 2 (contd.) F ={ A->BC, CD->E, B-> D, E-> A} • Give a lossless-join, decomposition into BCNF • (The relation is not in BCNF, since B is not a key and B->D) The decomposition is lossless-join: R1  R2 = {B} key of R1=BD. But it is not d.p. CD->E requires a join!

  8. R1  R2 = A, which is a key of both! So, the decomposition is lossless-join But the dependency B->D is not preserved. Problem 2 (contd.) F ={ A->BC, CD->E, B-> D, E-> A} Show that the decomposition R1= (A,B,C), R2 = (A,D,E) is lossless-join, but not dependency-preserving

  9. R1  R2 = {C} which is not a key of R1 or R2, so the decomposition is not lossless-join. Problem 2 (contd.) F ={ A->BC, CD->E, B-> D, E-> A} Show that the decomposition R1= (A,B,C), R2 = (C,D,E) is not lossless-join.

  10. We need to check if any of the given functional dependencies violates the BCNF: AB+ = ABCD Thus AB->C is in BCNF. C+ = CDA Thus C->D violates the BCNF. D+ = DA Thus D->A violates the BCNF. It follows that R in not in BCNF. Problem 3 • R(A,B,C,D), F={AB->C, C->D, D->A} • Is R in BCNF?

  11. Problem 3 (contd.) From previous analysis: C->D and D->A violate the BCNF. Using C->D, we decompose R into CD and ABC. We need to check whether ABC is in BCNF. We observe that C->A is in F+, and in the projection of F onto ABC. C is not a (super)key for ABC, thus ABC is not in BCNF. We decompose it further into CA and BC. The resulting decomposition CD, CA, and BC is a lossless join decomposition of ABCD into BCNF relations. The decomposition is not dependency preserving. E.g., AB->C is not preserved. • R(A,B,C,D), F={AB->C, C->D, D->A} • Decompose R into relations that are in BCNF using a lossless join decomposition.

  12. Both dependencies in F violate the BCNF condition since B is not a (super)key. One possible decomposition is R1=BC and R2=ABD (using B->C).B->D holds on R2, and B is not a superkey for R2, so we decompose it further in: R23=BD and R24=AB. The resulting decomposition is: BC, BD, and AB. This decomposition preserves dependencies: (F1  F23)+ = F+ Problem 4 • R(A,B,C,D) F = {B->C, B->D} • Is R in BCNF? If not, compute a lossless join decomposition of R into BCNF relations.

  13. Problem 5 Answer: B and C are consistent with Country -> Capitol

  14. Final Exam • Comprehensive, closed books; • Topics: • ER Diagrams; • Relational Database schemas; • Key constraints, participation constraints, weak entities, etc; • Relational algebra; • SQL (including GROUP BY and HAVING clauses, aggregate operators, set comparison operators, nested queries, etc.); • Functional dependencies; • Normal forms (BCNF, 3NF) and related decomposition techniques.

  15. Discussion of Problems in Sample Final

  16. Problem 2 (4) SELECT C.Instructor-SSn, sum(decode(S.Status,’Grad’,1,0))/count(*) as Percent FROM Class C, Enrollment E, Student S WHERE (C.Class-no=E.Class-no) AND (E.Student-Ssn=S.Ssn) GROUP BY C.Instructor-SSn

  17. Problem 2 (5) SELECT C.D-code, C.num-st FROM (SELECT D.D-code, count(*) as num-st FROM Department D, Student S WHERE D.D-code=S.Major GROUP BY D.D-code) C WHERE C.num-st <= ALL (SELECT C.num-st FROM C)

More Related