Q1 by yantingting

VIEWS: 2 PAGES: 4

									           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.
   iii) Additional tuples:
   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
cascaded rollback can occur.

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)
                                           Read(Y)
                                           Y= Y+50
                                   Write(Y)
                                   Unlock(Y)
                                    Read(Z)
                                    Z = Z+Y
                                    Write(Z)
                                   Unlock(Z)

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

                                    Write(Y)
                                  Write_lock(Z)
                                    Read(Z)
                                    Z = Z+Y
                                    Write(Z)
                                    Commit
                                   Unlock(Y)
                                   Unlock(Z)

e) Only conservative 2PL will be deadlock-free.

								
To top