CS 6360.004/005 - Assignment 4 Ask150930 Avaljot Khurana 1. Are the following sets of FDs equivalent? Explain why. F = {A->C, AC->D, E->AD, EC->DH, DE->CH} G = {A->CD, E->AH} For FD’s equivalent, F is covered by G and G is covered by F LHS: F is covered by G: {A}+ = {A, C, D} => A C {AC}+ = {A, C, D} => AC D {E}+ = {E, A, H, C, D} => E AD {EC}+ = {E, C, A, H, D} => EC DH {DE}+ = {D, E, A, H, C} => RHS: G is covered by F: DE CH {A}+ = {A, C, D} => A CD {E}+ = {E, A, D, C, H} => E AH Hence, F and G are equivalent. 2. Find a 3NF decomposition of a relation R(ABCDEFGHIJ) that satisfies the following FDs: { AB->C, BD->EF, AD->GH, A->I, H->J, GD->ABH } Using inference rules of Functional Dependencies, minimal cover is as follows, {AB -> C, BD -> EF, AD -> G, A->I, H-> J, GD -> ABH} 3NF relation, R1 (A, B, C) R2 (B, D, E, F) R3 (A, D, G) R4 (A, I) R5 (H, J) R6 (G, D, A, B, H)3. Find a minimal cover of the following set of dependencies: {AB->CDE, C->BD, CD-> E, DE->B} Reducing the FDs into standard canonical form: AB->C; AB->D; AB->E C->B; C->D CD->E DE->B Using various inference rules Minimal cover: {AB -> C, C -> DE, DE -> B} 4. Consider a relation R(ABCDEFGHIJ) satisfying the following FDs: FI→EHJC H→GB F→EA HI→FGD A→C (a) Find the list of all prime attributes of R. Show the steps. Using applying various inference rules, we get minimal cover, {FI -> HJ, H -> BG, F -> AE, HI -> FD, A -> C} {FI}+ = {F, I, H, J, B, G, A, E, D, C} {FI}+ includes all attributes. Thus FI is one of the candidate key. {H}+ = {H, G, B} {F}+ = {F, E, A, C} {HI}+ = {A,B,C,D,E,F,G,H,I,J} {HI}+ includes all attributes. Thus HI is another candidate key. {A}+ = {A, C} Thus prime attributes are {F, H, I}(b) Based on given functional dependencies and candidate keys that you have found, find a 3NF decomposition of R. Reducing the FDs into standard canonical form, FI->E; FI->H; FI->J; FI->C H->G; H->B F->E; F->A HI->F; HI->G; HI->D A->C Using various inference rules, minimal cover is, {FI -> HJ, H -> BG, F -> AE, HI -> FD, A -> C} The 3NF relations are, Relations: R1 (F, I, H, J) R2 (H, G, B) R3 (F, E, A) R4 (H, I, F, D) R5 (A, C) 5. Find a lossless (non-additive), dependency preserving 3NF decomposition of R (EFGHI) using the minimal cover method. R satisfies the following dependencies: FG→E HI→E F→G FE→H H→I After applying various inference rules, We get H -> E, F -> G, F -> H, H -> I as minimal cover. This is not in 3NF as it has transitive dependencies, Decomposing it into 3NF, we get R1 (F, G, H) R2 (H, I, E)6. Write a PL/SQL program that raises the salary of the employees in ‘Sales’ department by 25%. DECLARE SALES_SALARY EMPLOYEE.SALARY%TYPE; CURSOR C_SALES IS SELECT SALARY FROM EMPLOYEE WHERE DNO IN (SELECT DNO FROM DEPARTMENT WHERE DNAME ='SALES') FOR UPDATE; BEGIN OPEN C_SALES; LOOP FETCH C_SALES INTO SALES_SALARY; EXIT WHEN (C_SALES%NOTFOUND); UPDATE EMPLOYEE SET SALARY = SALES_SALARY * 1.25 WHERE CURRENT OF C_SALES; END LOOP; CLOSE C_SALES; END; 7. Write a stored procedure in PL/SQL that prints out the list of books that have been borrowed from a particular branch in last 30 days and haven’t been returned yet. Procedure should accept the Branch-name as an input parameter. Along with the book title, display also the borrower’s name and phone number. CREATE OR REPLACE PROCEDURE LIBDUE (LIB_BRANCH IN LIBRARY_BRANCH.BRANCH_NAME%TYPE) AS BOOK_VAR BOOK_LOANS%ROWTYPE; TITLE_VAR BOOK.TITLE%TYPE; NAME_VAR BORROWER.NAME%TYPE; PHONE_VAR BORROWER.PHONE%TYPE;CURSOR BOOKNAME_CURSOR IS SELECT * FROM BOOK_LOANS WHERE DATE_OUT >= (TRUNC (SYSDATE) - 30) AND RETURN_DATE IS NULL AND BRANCH_ID IN (SELECT BRANCH_ID FROM LIBRARY_BRANCH WHERE BRANCH_NAME = LIB_BRANCH); BEGIN OPEN BOOKNAME_CURSOR; LOOP FETCH BOOKNAME_CURSOR INTO BOOK_VAR; EXIT WHEN BOOKNAME_CURSOR%NOTFOUND; SELECT B.TITLE, BW.NAME, BW.PHONE INTO TITLE_VAR, NAME_VAR, PHONE_VAR FROM BOOK B, BORROWER BW WHERE B.BOOK_ID = BOOK_VAR.BOOK_ID AND BW.CARD_NO = BOOK_VAR.CARD_NO; DBMS_OUTPUT.PUT_LINE (TITLE_VAR ||' '|| NAME_VAR||' '|| PHONE_VAR); END LOOP; CLOSE BOOKNAME_CURSOR; END; SET SERVEROUTPUT ON; EXEC LIB_DUE ('MC
View Full Document