DOC PREVIEW
UT Dallas CS 6360 - assignment-4

This preview shows page 1-2 out of 5 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 5 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 5 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 5 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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

UT Dallas CS 6360 - assignment-4

Documents in this Course
Ch22(1)

Ch22(1)

44 pages

Ch21

Ch21

38 pages

Ch19

Ch19

46 pages

Ch18

Ch18

25 pages

Ch17

Ch17

21 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch05

Ch05

34 pages

Ch22

Ch22

45 pages

Ch21

Ch21

38 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch16

Ch16

17 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

34 pages

Ch06

Ch06

43 pages

Ch05

Ch05

34 pages

Ch04

Ch04

39 pages

Ch03(2)

Ch03(2)

36 pages

Ch02

Ch02

33 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

lab-manual

lab-manual

215 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch17

Ch17

25 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch04(1)

Ch04(1)

43 pages

Ch07

Ch07

40 pages

Ch03

Ch03

42 pages

Ch01

Ch01

36 pages

Ch02

Ch02

38 pages

Ch05

Ch05

41 pages

Ch06

Ch06

47 pages

Ch08

Ch08

39 pages

Ch17

Ch17

25 pages

Ch18

Ch18

24 pages

Ch09

Ch09

42 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04(1)

Ch04(1)

43 pages

Ch03

Ch03

42 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Load more
Download assignment-4
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view assignment-4 and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view assignment-4 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?