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 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 single-valued attributes a21 and a22 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 ___________. [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 R1(A,B) with the tuples (1,5), (3,7) and R2(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R1 and R2. 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|