DBMS Questions Gate 2006 – CSE

Question 1 : Consider the following log sequence of two transactions
             on a bank account, with initial balance 12000, that
             transfer 2000 to a mortgage payment and then apply a 5%
             interest.

          1. T1 start
          2. T1 B old=12000 new=10000
          3. T1 M old=0 new=2000
          4. T1 commit
          5. T2 start
          6. T2 B old=10000 new=10500
          7. T2 commit

             Suppose the database system crashes just before log
             record 7 is written. When the system is restarted, 

             which one statement is true of the recovery procedure?

             a) We must redo log record 6 to set B to 10500
             b) We must undo log record 6 to set B to 10000 and then
                redo log records 2 and 3.
             c) We need not redo log records 2 and 3 because
                transaction T1 has committed.
             d) We can apply redo and undo operations in arbitrary
                order because they are idempotent
Solution : 
           Redo : All work done by transactions that completed
                  successfully prior to the crash.
           Undo : All work done by transactions that started but
                  did not complete prior to the crash.

           In database transaction system, if a transaction is commit
           then it becomes permanent. There is no effect of any 
           failure. So we need not redo log records 2 and 3 because 
           transaction T1 has committed.
Question 2 : Consider the relation account (customer, balance) where
             customer is a primary key and there are no null values.
             We would like to rank customers according to decreasing
             balance. The customer with the largest balance gets
             rank 1. ties are not broke but ranks are skipped: if
             exactly two customers have the largest balance they each
             get rank 1 and rank 2 is not assigned

             Query1:
             select A.customer, count(B.customer)
             from account A, account B
             where A.balance <=B.balance
             group by A.customer

             Query2:
             select A.customer, 1+count(B.customer)
             from account A, account B
             where A.balance < B.balance
             group by A.customer

             Consider these statements about Query1 and Query2.
          1. Query1 will produce the same row set as Query2 for some
             but not all databases.
          2. Both Query1 and Query2 are correct implementation of the
             specification
          3. Query1 is a correct implementation of the specification
             but Query2 is not
          4. Neither Query1 nor Query2 is a correct implementation of
             the specification
          5. Assigning rank with a pure relational query takes less
             time than scanning in decreasing balance order assigning
             ranks using ODBC.

             Which two of the above statements are correct?

          a) 2 and 5
          b) 1 and 3
          c) 1 and 4
          d) 3 and 5
Solution :
           QA21 DBMS Questions Gate 2006

           QA21A DBMS Questions Gate 2006
Question 3 : Consider the relation "enrolled(student, course)" in 
             which (student, course) is the primary key, and the
             relation "paid(student, amount)" where student is the
             primary key. Assume no null values and no foreign keys
             or integrity constraints. Given the following four
             queries:

             Query1: select student from enrolled where
                     student in (select student from paid)
             Query2: select student from paid where
                     student in (select student from enrolled)
             Query3: select E.student from enrolled E, paid P
                     where E.student = P.student
             Query4:  select student from paid where exists
                     (select * from enrolled where enrolled.student
                                                     = paid.student)

             Which one of the following statements is correct?

          a) All queries return identical row sets for any database
          b) Query2 and Query4 return identical row sets for all
             databases but there exist databases for which Query1
             and Query2 return different row sets.
          c) There exist databases for which Query3 returns strictly
             fewer rows than Query2
          d) There exist databases for which Query4 will encounter an
             integrity violation at runtime.
Solution :
         QA DBMS Questions Gate 2006
Question 4 : Consider the relation enrolled(student, course) in which
             (student, course) is the primary key, and the relation
             paid(student, amount), where student is the primary key.
             Assume no null values and no foreign keys or integrity
             constraints. Assume that amounts 6000, 7000, 8000, 9000
             and 10000 were each paid by 20% of the students.
             Consider these query plans (Plan 1 on left, Plan 2 on
             right) to
             "list all courses taken by students who have paid more 
              than x". 
             A disk seek takes 4ms, disk data transfer bandwidth is 
             300 MB/s and checking a tuple to see if amount is
             greater than x takes 10 micro-seconds.

             Which of the following statements is correct?

          a) Plan 1 and Plan 2 will not output identical row sets for
             all databases.
          b) A course may be listed more than once in the output of
             Plan 1 for some databases
          c) For x = 5000, Plan 1 executes faster than Plan 2 for all
             databases.
          d) For x = 9000, Plan I executes slower than Plan 2 for all
             databases.
Solution :
          There are two plans mainly plan 1 and plan 2.
          In plan 1 first all the records paid is selected and then
          they are joined.
          But in plan 2, all paid records are joined first and then 
          the records are checked.
          
          The seek time of disk = 4ms and
          Data transfer rate    = 300 MB/S
          So, if x = 5000, then plan 1 executes faster than plan 2
          for all databases.
Question 5 : The following functional dependencies are given:
             AB → CD, 
             AF → D, 
             DE → F, 
              C → G , 
              F → E, 
              G → A

             Which one of the following options is false?

          a) CF+ = {ACDEFG}
          b) BG+ = {ABCDG}
          c) AF+ = {ACDEFG}
          d) AB+ = {ABCDFG}
Solution :
         QA24 DBMS Questions Gate 2006
     

Leave a Reply