Final Exam Revision Instructor Mohamed Eltabakh meltabakh cs wpi

Final Exam Revision Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu 1

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

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

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

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

Functional Dependencies & Normalization 6

Key Points l Identifying the candidate keys of relations l l Use of Transitive, and Union properties to generate more FDs Computing the attribute closure l l l 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 3 NF l l Test which FD is violating the rules Decompose based on this rule 7

Question 1 l Given relation R= (A, B, C, D, E) with the following FDs: l 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} 8

Question 2 l Given relation R= (A, B, C, D, E) with the following FDs: l 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 …} {ABCDE} Then AB is the only candidate key 9

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

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

Question 4 l Given R = (A, B, C, D, E, F), FDs: l l 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 12

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

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

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

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

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

Question 7 l Given R = (A, B, C, D, E, F), FDs: l l 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 18

Question 7 (Cont’d) l Given R = (A, B, C, D, E, F), FDs: l l 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 Candidate keys of R: {AB} {BD} R 1 = (C, D, E), R 2 = (A, B, C, D, F) Violation to BCNF: CD E B EF DF AC R 2 is still not in BCNF and violated by: B F and DF AC Using (B F) to divide R 2 R 1 = (C, D, E), R 3 = (B, F), R 4 = (A, B, C, D) Now R 1, R 3, R 4 are in BCNF 19

SQL Commands 20

Question 1 21

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’); 22

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); 23

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

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(unit. Price * Num. Of. Units) As total. Cost From Medicine M, Prescription_Medicine PM Where M. Trade. Name = PM. Trade. Name Group By prescription_id Having total. Cost > 100 And total. Cost < 200 Order By total. Cost; 25

Advanced SQL Commands 26

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

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

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 Num. Prescriotions(date) > 10; 29

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 Unit. Price Before Insert Or Update On Medicine For Each Row Begin IF (: new. Generic. Flag = ‘T’ and : new. Unit. Price >= 100) Then RAISE_APPLICATION_ERROR(-20004, ‘Price should be < $100’); ELSIF (: new. Generic. Flag = ‘F’ and : new. Unit. Price < 100) Then RAISE_APPLICATION_ERROR(-20004, ‘Price should be >= $100’); END IF; End; 30

End of Revision 31