Q1 by yantingting

VIEWS: 2 PAGES: 4

• pg 1
```									           Solution for final exam (CS6400, Spring 2002)
Q1.
(a) (AC)+ = {A, B, C, D, E, H}
Reasoning: Since A->B, AC->BC by augmentation
But BC->DE (given), hence we have AC->DE.
A->AB->E. Hence AC -> E
A->AE->H. Hence AC -> H

(b) Minimum Cover:
{ A->C, C->D, C->I, EC->A, EC->B, EI->C}
Reasoning: (i) AB->C is implied by A->C and is redundant.
(ii) C -> DI means C-> D and C-> I.
C-> I makes CD->I redundant.
(iii) EC->AB can be rewritten as EC->A, EC->B

Q2.
(a) i) Candidate Key:
Key 1: {Flight#, Date, Seat#}
Key 2: {Flight#, Date, Pass_name}
Reason: each of the above functionally determines the rest of the attributes

ii) Violation of 2NF: Flight# -> City_of_origin
and/or Pass_name -> FFI, Pass_phone if we use Key 2.

Violation of 3NF: Pass_name ->FFI, Pass_phone if use Key 1.
[ Hence if we apply the general definition of 3NF, we see that it fails due to the
above two f.d.s where the l.h.s . is not a key for each of them.]
(b)
Book_title Authname Book_type ListPrice Author_affil Publisher               %effort
B1 A1             B12            A3            B14\A4 B15               A6         B17
B2 B21            B22            A3            A4         B25           B26        B27
B3 B31            A2             B33           B34        A5            B36        B37
B4 A1             A2             B43 \ A3      B44 \A4 B45 \ A5         B46 \ A6   A7
Step1: Apply FD1 from B1, we have B43->A3, B46->A6
Step 2: Apply FD2 from B2, we have B44->A4, B14->A4
Step 3: Apply FD3, B45->A5
 B4 is all A’s
 We know it is lossless by algorithm 15.2

It is dependency preserving decomposition since all FDs can be found in one of
the relations.

(c) i) School ->> webmaster and webmaster->> school.
Trivial MVD since any binary relation has a trivial MVD.
ii) FDs: Sites -> (Webmaster, School) and webmaster -> (Sites, School)
Hence the relation is in 3NF, with Sites or Webmaster as candidate keys.
GT                             Jim                          URL2
GT                             John                         URL1
UNC                            Larry                        URL4
UNC                            Mike                         URL3

Q3:
a-1:

П   Book#, price, Author_type, ccity, order_date

σcstate=‘GA’   and order_date > ‘990101’ and price>50

X

X
Author

X                               Book

Customer                         Order

a-2:
П   Book#, price, Author_type, ccity, order_date

*
П   Author, Author_type

*
Author
П   Book#, ccity, order_date

*                                      П    Book#, price, Author

П cid, ccity              П                                    σ price>50
Book#, cid, order_date

Book
σcstate=‘GA’             σ order_date > ‘990101’

Customer                         Order
Note: J stands for an equality join with appropriate join condition in the above tree. It
may be replaced by the natural join (*) operator.

b) We have 4.5 M / 45,000 = 100 books for each topic.
Since the clustering index is in memory, we can know the block address without
block access. The books in the same topic occupy one block worth of storage but at
most may be distributed over 2 blocks, so we need to access 1 or at most 2 contiguous
blocks.

c) i) JS = |Author join Book| / ( |Author| * |Book| )= |Book| / (|Author|*|Book|)
= 1 / |Author| = 10-5
ii) Total number of blocks for author ba = 100,000 / 1,000 = 100 blocks
Total number of blocks for book bb = 4.5 * 106 / 100 = 45,000 blocks

 Total number of blocks read = ba + ((ba / 10)* bb)
= 100 + ((100/10)*45,000
= 450,100

Q4. a) When transactions read “dirty data” or data written by uncommitted transactions

b)

T2

X
Y
Z

T1
T3

It is not serializable since we have a loop in the above figure.
It is recoverable since each transaction Ti committed only after the commit of another
transaction Tj that modified the items Ti read. (i<>j and i,j є {1,2,3})

Q5.
a) No. Because unlock(Y) appears before write_lock(Z)
b) Yes. Exchange the position of write_lock(Z) and unlock(Y)
c) Conservative 2PL: Get all locks before the transaction.
Write_lock(Y)
Write_lock(Z)
Y= Y+50
Write(Y)
Unlock(Y)
Z = Z+Y
Write(Z)
Unlock(Z)

d) Strict 2PL: Release write locks after commit.
Y= Y+50
Write_lock(Y)

Write(Y)
Write_lock(Z)