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 AC A C D E E A H C D EC E C A H D DE D E A H C A C AC D E AD EC DH DE CH RHS G is covered by F A A C D E E A D C H A CD 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 DERMOTT
View Full Document
Unlocking...