**DBMS |Gate-2015| Database Management System**

**1. **SELECT operation in SQL is equivalent to : **[GATE – 2015]**

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 : d)*

**2. **Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are connected by an m : n relationship R_{12}, E1 and E3 are connected by a 1 : n (1 on the side of E1 and n on the side of E3) relationship R_{13}. E1 has two single-valued attributes a_{11} and a_{12} of which a_{11} is the key attribute. E2 has two single-valued attributes a_{21} and a_{22} is the key attribute. E3 has two single-valued attributes a_{31} and a_{32} of which a_{31} 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 ___________. **[GATE – 2015]**

a. 4

b. 6

c. 7

d. 8

*Answer : a)*

**3. **Consider the following relations:

Roll no. | Student name |

1 | Raj |

2 | Rohit |

3 | Raj |

Roll no. | Course | Marks |

1 | Math | 80 |

1 | English | 70 |

2 | Math | 75 |

3 | English | 80 |

2 | Physics | 65 |

3 | Math | 80 |

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 _________ : **[GATE – 2015]**

a. 2

b. 3

c. 4

d. 5

*Answer : a)*

**4. **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 : **[GATE – 2015]**

a. Atomicity

b. Consistency

c. Isolation

d. Durability

*Answer : b)*

**5. **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 searcj key grater than or equal to 7 and less than 15″ is ___________ **[GATE – 2015]**

a. 5

b. 6

c. 7

d. 9

*Answer : a)*

**6. **Consider two relations R_{1}(A,B) with the tuples (1,5), (3,7) and R_{2}(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R_{1} and R_{2}. Consider the following tuples of the form (A,B,C): a = (1.5,null) , b = (1,null,7), c = (3,null,9), d = (4,7,null), e = (1,5,7), f = (3,7,null) , g = (4,null,9). Which one of the following statements is correct? **[GATE – 2015]**

a. R contains a,b,e,f,g but not c, d.

b. R contains all of a,b,c,d,e,f,g

c. R contains e,f,g but not a,b

d. R contains e but not f,g

*Answer : c)*

**7. **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+ is closure of F? **[GATE – 2015]**

a. {P,R}→{S,T}

b. {P,S}→{S}

c. {P,R}→{R,T}

d. {P,S,U}→{Q}

*Answer : b)*

DBMS |Gate-2015|

**8.** 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? **[GATE – 2015]**

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 : a)*

DBMS |Gate-2015|