1 / 31

Final Exam Revision

Final Exam Revision. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. What You Learned. Data Models Entity-Relationship Model & ERD Relational Model Conversion between the data models Relational Algebra & Operators Structured Query Language SQL DML: Data Manipulation Language

isolde
Download Presentation

Final Exam Revision

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. Final Exam Revision Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

  2. What You Learned • Data Models • Entity-Relationship Model & ERD • Relational Model • Conversion between the data models • Relational Algebra & Operators • Structured Query Language SQL • DML: Data Manipulation Language • DDL: Data Definition Language

  3. What You Learned (Cont’d) • Advanced SQL • Triggers, Views, Cursors, Stored Procedures and Functions • PL/SQL • Functional Dependencies • Normalization Rules

  4. In Advanced Courses Things get more interesting • Indexing Techniques • Transaction Management • Query Optimization • Handling of Big Data • And many more …

  5. Final Exam Details • Time and date • Final exam will start on Feb 28th (Thursday) @ 11:00am • The exam will be 80 mins • Included material • SQL commands (Select, update, delete, insert) • Advanced SQL (Views, Triggers, Stored functions, Cursors) • Functional Dependencies • Normalization       • Closed book and closed notes • Allowed only ONE page (front and back) to write anything you want. • You answer in the same sheet 

  6. Functional Dependencies & Normalization

  7. Key Points • Identifying the candidate keys of relations • Use of Transitive,and Union properties to generate more FDs • Computing the attribute closure • A is a key if all attributes are in its closure • A candidate key is also a super key (But it is minimal) • Remember the rules for BCNF and 3NF • Test which FD is violating the rules • Decompose based on this rule

  8. Question 1 • Given relation R= (A, B, C, D, E) with the following FDs: • F = {AB  C, C  D, BD, CD  E, AB  E} Compute the canonical (minimal) cover of F = G • Use Union property: AB  CE, CD, B D, CD E • Take the shortest L.H.S first {CD, BD, …} • D in CD E is not needed • We have {CDE, BD, AB CE} • AB determines C, and C determines E…So no need for ABE • Canonical cover G = {C DE, B  D, AB  C}

  9. Question 2 • Given relation R= (A, B, C, D, E) with the following FDs: • F = {AB  C, C  D, BD, CD  E, AB  E} What are the candidate keys of R? • Is there any FD that determines A No (then A must be part of the candidate key) • Is there any FD that determines B No (then B must be part of the candidate key) • Lets check {AB}+= {AB …} • {ABCD…} • {ABCDE}  Then AB is the only candidate key

  10. Question 3 • Given relation R = (A, B, C, D) with the following FDs: • F = {B  C, B D} • What are the candidate keys of R? • Report FDs violating BCNF (if any)? • Decompose R to be in BCNF (if not already)? • {AB}+= {ABCD} • AB is the only candidate key • B  C • B  D

  11. Question 3 (Cont’d) • Given relation R = (A, B, C, D) with the following FDs: • F = {B  C, B D} • What are the candidate keys of R? • Report FDs violating 3NF (if any)? • Decompose R to be in 3NF (if not already)? • AB • B  C • B  D

  12. Question 4 • Given R = (A, B, C, D, E, F), FDs: • F = {AB  DE, CD  E, B  EF, DF  AC, BD  AF} • Is ABF candidate key for R? Is it a superkey (Yes or No)? • Compute {ABF}+ = {ABF…} • {ABDEF…} • {ABCDEF}  This means ABF is a superkey • Is ABF minimal key? • {AF}+= {AF}  That is not a key • {BF}+= {BEF}  That is not a key • {AB}+= {ABCDEF}  That is a key (minimal one) • So ABF is not a candidate key

  13. Question 5 • Given R = (A, B, C, D, E, F), FDs: • F = {AB  DE, CD  E, B  EF, DF  AC, BD  AF} • If R is decomposed into two relations R1(A, B, D, F) and R2(C, D, E, F) Is this decomposition dependency preserving or not? • Step 1: Find the local dependencies to R1 and R2 • Step 2: Check each of the original FDs on R, whether you can get it from the local FDs + the global derived ones

  14. Question 5 (Cont’d) • Given R = (A, B, C, D, E, F), FDs: • F = {AB  DE, CD  E, B  EF, DF  AC, BD  AF} • If R is decomposed into two relations R1(A, B, D, F) and R2(C, D, E, F) Is this decomposition dependency preserving or not? • Local to R1: {AB  D, B F, DF  A, BD A F } • Local to R2: {CD  E, DF  C}

  15. Question 5 (Cont’d) • Given R = (A, B, C, D, E, F), FDs: • F = {AB  DE, CD  E, B  EF, DF  AC, BD  AF} • If R is decomposed into two relations R1(A, B, D, F) and R2(C, D, E, F) Is this decomposition dependency preserving or not? • Local to R1: {AB  D, B F, DF  A, BD A F } • Local to R2: {CD  E, DF  C} • Check original ones: • AB  DE – Preserved using (ABD) from R1 and (AB E) global one • CD  E -- Preserved from (CD  E) in R2 • B  F -- Preserved from (B F) in R1 • B  E -- Lost • DF  AC -- Preserved from (DF  A) from R1, and (DF  C) from R2 • BD  AF -- Preserved from (BD  AF) from R1 Decomposition is NOT dependency preserving

  16. Question 6 • Given R = (A, B, C, D, E, F), FDs: • F = {AB  DE, CD  E, B  EF, DF  AC, BD  AF} • If R is decomposed into R1 = (A, B, D, F) and R2 = (C, D, E, F) Is this decomposition lossless or lossy? Indicate why? • Step 1: Find the common attributes between R1 and R2 (these are the ones used in natural join) • Step 2: Check if the common attributes are candidate keys in either of R1 or R2 • If yes  Lossless • If no  lossy

  17. Question 6 (Cont’d) • Given R = (A, B, C, D, E, F), FDs: • F = {AB  DE, CD  E, B  EF, DF  AC, BD  AF} • If R is decomposed into R1 = (A, B, D, F) and R2 = (C, D, E, F) Is this decomposition lossless or lossy? Indicate why? • Common attributes are {DF} • In R1: {DF}+ = {DFA}  So DF is not a key in R1 • In R2: {DF}+ = {DFCE}  So DF is a key in R2 Decomposition is lossless

  18. Question 7 • Given R = (A, B, C, D, E, F), FDs: • F = {AB  DE, CD  E, B  EF, DF  AC, BD  AF} • Which of the five given FDs violates the BCNF (if any)? • Step 1: Before you check a normal form, you need to find the keys • Step 2: Check each dependency against the normal form rules

  19. Question 7 (Cont’d) • Given R = (A, B, C, D, E, F), FDs: • F = {AB  DE, CD  E, B  EF, DF  AC, BD  AF} • Which of the five given FDs violates the BCNF (if any)? Using (CD  E) to divide R R1 = (C, D, E), R2 = (A, B, C, D, F) R2 is still not in BCNF and violated by: B  F and DF  AC Using (B  F) to divide R2 R1 = (C, D, E), R3 = (B, F), R4 = (A, B, C, D) Now R1, R3, R4 are in BCNF • Candidate keys of R: {AB} {BD} • Violation to BCNF: • CD  E • B  EF • DF  AC

  20. SQL Commands

  21. Question 1

  22. Question 2 Delete prescription lines for prescriptions written on date ‘Jan-01-2010’ Delete From Prescription_Medicine Where prescription_id in ( Select id From Prescription Where date = ‘Jan-01-2010’);

  23. Question 3 Delete prescriptions that have no lines (no records in prescription_medicine) Delete From Prescription Where id not in ( Select prescription_id From prescription_medicine);

  24. Question 4 Select patients who have no primary doctors Select * From Patient Where primaryDoctor_SSN is null;

  25. Question 5 Report the prescription id and its total cost of prescriptions having total cost between $100 and $200. Sort ascending based on the total cost Select prescription_id, sum(unitPrice * NumOfUnits) As totalCost From Medicine M, Prescription_Medicine PM Where M.TradeName = PM.TradeName Group By prescription_id Having totalCost > 100 And totalCost < 200 Order By totalCost;

  26. Advanced SQL Commands

  27. Question 1 Create a view that reports the trade name, unit price, and the generic flag of the most expensive and cheapest medicines.

  28. Question 2 Create a stored function that takes a date as a parameter and returns the number of prescriptions on that date • Create Function NumPrescriptions (inDate IN date) Return int As • temp int; • Begin • Select count(*) into temp • From prescription • where date = inDate; • return temp; • End;

  29. Question 3 Using the function created in Question 15, report the prescriptions written on a date in which more than 10 prescriptions have been written Select * From prescription Where NumPrescriotions(date) > 10;

  30. Question 4 Create trigger that ensures that if the medicine is generic, then its unit price is below $100, and if it is not generic then its unit price >= $100 Create Trigger UnitPrice Before Insert Or Update On Medicine For Each Row Begin IF (:new.GenericFlag = ‘T’ and :new.UnitPrice >= 100) Then RAISE_APPLICATION_ERROR(-20004, ‘Price should be < $100’); ELSIF (:new.GenericFlag = ‘F’ and :new.UnitPrice < 100) Then RAISE_APPLICATION_ERROR(-20004, ‘Price should be >= $100’); END IF; End;

  31. End of Revision

More Related