### SET 1

Question 1

**Select operation in SQL is equivalent to**

A : the selection operation in relational algebra

B : the selection operation in relational algebra, except that SELECT in SQL retains duplicates

C : the projection operation in relational algebra

D : the projection operation in relational algebra, except that SELECT in SQL retains duplicates

Answer Discuss it!

.

Correct answer is :D

Solution :

SELECT operation in SQL perform vertical partitioning which is performed by projection operation in relational calculus but SQL is multi sets; hence (D).

Question 2

**A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called**

A : Dense

B : Sparse

C : Clustered

D : Unclustered

Answer Discuss it!

.

Correct answer is :A

Solution :

According to the given question, we can say that each data record in the data file has one entry in the index file. So it must be dense index.

Question 3

**Consider an Entity-Relationship (ER) model in which entity sets E1and E2 are connected by an m : n relationship R12, E1 and E3 are connected by a 1 : n (1 on the side of E1 and n on the side of E3) relationship R13. E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two singlevalued attributes a21 and a22 of which a21 is the key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any attributes. If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is ___________.**

Answer Discuss it!

.

Correct answer is :5

Solution :

E1(a11,a12) , E2(a21,a22) , E3 and R13 (a11,a31,a32) , R12(a11,a21)

But in table (a11,a31,a32) there may be transitive dependency between a11 and a32 so we should decompose this table into 2 more tables

=> 5 tables

Question 4

**Consider the following relations:**

Consider the following SQL query. SELECT S. Student_Name, sum (P.Marks) FROM Student S, Performance P WHERE S. Roll_No =P.Roll_No GROUP BY S.Student_Name The number of rows that will be returned by the SQL query is _________.

Answer Discuss it!

.

Correct answer is :2

Solution :

Output :

Raj 310

Rohit 140

Question 5

**Consider the following transaction involving two bank account x and y.**

read (x) ; x : = x – 50; write (x) ; read (y); y : = y + 50 ; write (y)The constraint that the sum of the accounts x and y should remain constant is that of

A : Atomicity

B : Consistency

C : Isolation

D : Durability

Answer Discuss it!

.

Correct answer is :B

Solution :

The consistency property ensures that the database remains in a consistent state before the (start of the transaction and after the transaction is over. Here sum of the accounts x & y should remain same before & after execution of the given transactions which refers to the consistency of the sum.

Question 6

**With reference to the B+ tree index of order 1 shown below, the minimum number of nodes (including the Root node) that must be fetched in order to satisfy the following query: “Get all records with a search key greater than or equal to 7 and less than 15” is _________**

Answer Discuss it!

.

Correct answer is :6

Question 7

**Consider a simple checkpointing protocol and the following set of operations in the log : **

(Start , T4) ; (write , T4,y,2,3) ; (Start ,T1); (commit , T4);(write , T1,z,5,7);

(checkpoint);

(Start , T2);(write ,T2,x,1,9);(commit ,T2) ; (start T3),(write,T3,z,7,2);

If a crash happens now and the system tries to recover using both undo and redo operations, what are the contents of the undo lists and the redo list?

A : Undo T3,T1; Redo T2

B : Undo T3,T1; Redo T2,T4

C : Undo: none; redo:T2,T4,T3,T1

D : Undo T3,T1; T4; Redo:T2

Answer Discuss it!

.

Correct answer is :A

Solution :

As 1 3 T &T are not yet committed they must be undone. The transactions which are after the latest checkpoint must be redone. So T2 must be redone. No need to redo the records which are before last checkpoint, so T4 need not be redone.

Question 8

**Consider the relation X(P,Q,R,S,T,U) with the following set of functional dependencies**

F {

{ P,R } -> {S,T}

{P,S,U} -> {Q,R}

}

Which of the following is the trivial functional dependency in F^{+}, where F^{+} is closure of F?

A : {P,R} -> {S,T}

B : {P,R} -> {R,T}

C : {P,S} -> {S}

D : {P,S,U} -> {Q}

Answer Discuss it!

.

Question 9

**Consider the following relation**

Cinema (theater, address, capacity)

Which of the following options will be needed at the end of the SQL query

SELECT P1. address

FROM Cinema P1

Such that it always finds the addresses of theaters with maximum capacity?

A : WHERE P1. Capacity> = All (select P2. Capacity from Cinema P2)

B : WHERE P1. Capacity> = Any (select P2. Capacity from Cinema P2)

C : WHERE P1. Capacity> = All (select max(P2. Capacity) from Cinema P2)

D : WHERE P1. Capacity> = Any (select max (P2. Capacity) from Cinema P2)

Answer Discuss it!

.

Correct answer is :A

Solution :

Inner query collects capacities of all the theatres and in outer query we are filtering the tuples with the condition “capacity>=All”. So the theatres which are having maximum capacity will be satisfy the conductivity and they will.

Question 10

**Consider the following partial Schedule S involving two transactions T1 and T2. Only the read and write operations have been shown. The read operation on data item P is denoted by read(P) and the write operation on data item P is denoted by write(P).**

Suppose that the transaction T1 fails immediately after time instance 9. Which one of the following statements is correct?

A : T2 must be aborted and then both T1 and T2 must be re-started to ensure transaction atomicity

B : Schedule S is non-recoverable and cannot ensure transaction atomicity

C : Only T2 must be aborted and then re-started to ensure transaction atomicity

D : Schedule S is recoverable and can ensure atomicity and nothing else needs to be done

Answer Discuss it!

.

Correct answer is :B

Solution :

T2 is reading the value written by T1 and getting committed before T1 commits. So it is nonrecoverable schedule.

Question 11

**Consider a B+ tree in which the search Answeris 12 bytes long, block size is 1024 bytes, record pointer is 10 bytes long and block pointer is 8 bytes long. The maximum number of keys that can be accommodated in each non-leaf node of the tree is ____ .**

Answer Discuss it!

.

Correct answer is :50

Solution :

Suppose that ‘k’ is order of the non-leaf node

k(8)+(k-1)12 <= 1024

20k<=1036

k<=|_1036/20_| => k<=51

As the order is 51, maximum we can store 50 keys.

Question 1

.

Correct answer is :D

Solution :

SELECT operation in SQL perform vertical partitioning which is performed by projection operation in relational calculus but SQL is multi sets; hence (D).

Question 2

.

Correct answer is :A

Solution :

According to the given question, we can say that each data record in the data file has one entry in the index file. So it must be dense index.

Question 3

.

Correct answer is :5

Solution :

E1(a11,a12) , E2(a21,a22) , E3 and R13 (a11,a31,a32) , R12(a11,a21)

But in table (a11,a31,a32) there may be transitive dependency between a11 and a32 so we should decompose this table into 2 more tables

=> 5 tables

Question 4

Consider the following SQL query. SELECT S. Student_Name, sum (P.Marks) FROM Student S, Performance P WHERE S. Roll_No =P.Roll_No GROUP BY S.Student_Name The number of rows that will be returned by the SQL query is _________.

.

Correct answer is :2

Solution :

Output :

Raj 310

Rohit 140

Question 5

read (x) ; x : = x – 50; write (x) ; read (y); y : = y + 50 ; write (y)The constraint that the sum of the accounts x and y should remain constant is that of

.

Correct answer is :B

Solution :

The consistency property ensures that the database remains in a consistent state before the (start of the transaction and after the transaction is over. Here sum of the accounts x & y should remain same before & after execution of the given transactions which refers to the consistency of the sum.

Question 6

.

Correct answer is :6

Question 7

(Start , T4) ; (write , T4,y,2,3) ; (Start ,T1); (commit , T4);(write , T1,z,5,7);

(checkpoint);

(Start , T2);(write ,T2,x,1,9);(commit ,T2) ; (start T3),(write,T3,z,7,2);

If a crash happens now and the system tries to recover using both undo and redo operations, what are the contents of the undo lists and the redo list?

.

Correct answer is :A

Solution :

As 1 3 T &T are not yet committed they must be undone. The transactions which are after the latest checkpoint must be redone. So T2 must be redone. No need to redo the records which are before last checkpoint, so T4 need not be redone.

Question 8

F {

{ P,R } -> {S,T}

{P,S,U} -> {Q,R}

}

Which of the following is the trivial functional dependency in F

^{+}, where F

^{+}is closure of F?

.

Question 9

Cinema (theater, address, capacity)

Which of the following options will be needed at the end of the SQL query

SELECT P1. address

FROM Cinema P1

Such that it always finds the addresses of theaters with maximum capacity?

.

Correct answer is :A

Solution :

Inner query collects capacities of all the theatres and in outer query we are filtering the tuples with the condition “capacity>=All”. So the theatres which are having maximum capacity will be satisfy the conductivity and they will.

Question 10

Suppose that the transaction T1 fails immediately after time instance 9. Which one of the following statements is correct?

.

Correct answer is :B

Solution :

T2 is reading the value written by T1 and getting committed before T1 commits. So it is nonrecoverable schedule.

Question 11

.

Correct answer is :50

Solution :

Suppose that ‘k’ is order of the non-leaf node

k(8)+(k-1)12 <= 1024

20k<=1036

k<=|_1036/20_| => k<=51

As the order is 51, maximum we can store 50 keys.