DBMS |Gate-2006| Previous Year Questions| Set-15

Set-15 GATE-2006 DBMS

DBMS |Gate-2006| Database Management System

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? [GATE – 2006]

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.

Answer : b)


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? [GATE – 2006]

a. 2 and 5
b. 1 and 3
c. 1 and 4
d. 3 and 5

Answer : c)


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? [GATE – 2006]

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.

Answer : b)


4. Consider the relations r1(P, Q, R) and r2(R, S, T) with primary keys P and R respectively. The relation r1 contains 2000 tuples and r2 contains 2500 tuples. The maximum size of the join r1⋈ r2 is : [GATE – 2006]

a. 2000
b. 2500
c. 4500
d. 5000

Answer : a)


5. Which of the following relational query languages have the same expressive power? [GATE – 2006]

  1. Relational algebra
  2. Tuple relational calculus restricted to safe expressions
  3. Domain relational calculus restricted to safe expressions

a. 2 and 3 only
b. 1 and 2 only
c. 1 and 3 only
d. 1, 2 and 3

Answer : d)


6. In a database file structure, the search key field is 9 bytes long, the block size is 512 bytes, a record pointer is 7 bytes and a block pointer is 6 bytes. The largest possible order of a non-leaf node in a B+ tree implementing this file structure is : [GATE – 2006]

a. 23
b. 24
c. 34
d. 44

Answer : c)
DBMS |Gate-2006|


7. Consider a relation R with five attributes V, W, X, Y, and Z. The following functional dependencies hold: VY→ W, WX → Z, and ZY → V. Which of the following is a candidate key for R? [GATE – 2006]

a. VXZ
b. VXY
c. VWXY
d. VWXYZ

Answer : b)
DBMS |Gate-2006|


Back to GATE-HOME


Spread the love

Leave a Comment

Your email address will not be published. Required fields are marked *