DBMS |Gate-2017| Previous Year Questions| Set-4

Set-15 GATE-2006 DBMS

DBMS |Gate-2017| Database Management System

1. The following functional dependencies hold true for the relational schema {V, W, X, Y, Z} :

V → W
VW → X
Y → VX
Y → Z

Which of the following is irreducible equivalent for this set of functional dependencies? [GATE – 2017]

a. V→W
    V→X
    Y→V
    Y→Z

b. V→W
    W→X
    Y→V
    Y→Z

c. V→W
    V→X
    Y→V
    Y→X
    Y→Z

d. V→W
   W→X
   Y→V
   Y→X
   Y→Z

Answer : a)
DBMS |Gate-2017|


2. Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

The output of executing the SQL query is ___________. [GATE – 2017]

a. 2.7
b. 2.6
c. 2.8
d. 2.9

Answer : b)


3. Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId), and DEPT(DeptName, DeptId). Note that the DeptId can be permitted to a NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

(I) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∀v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(II) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(III) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] = v[DeptId]))}

Which of the above queries are safe? [GATE – 2017]

a. (I) and (II) only
b. (II) and (III) only
c. (I) and (III) only
d. (I), (II) and (III)

Answer : d)
DBMS |Gate-2017|


4. In a database system, unique timestamps are assigned to each transaction using Lamport’s logical clock. Let TS(T1) and TS(T2) be the timestamps of transactions T1 and T2 respectively. Besides, T1 holds a lock on the resource R and T2 has requested a conflicting lock on the same resource R. The following algorithm is used to prevent deadlocks in the database system assuming that a killed transaction is restarted with the same timestamp. [GATE – 2017]

if TS(T2)<TS(T1) then
T1 is killed
else T2 waits.

Assume any transaction that is not killed terminates eventually. Which of the following is TRUE about the database system that uses the above algorithm to prevent deadlocks? [GATE – 2017]

a. The database system is deadlock-free, but not starvation-free.
b. The database system is neither deadlock-free nor starvation-free.
c. The database system is both deadlock-free and starvation-free.
d. The database system is starvation-free, but not deadlock-free.

Answer : c)


5. Consider a database that has the relation schema CR(StudentName, CourseName). An instance of the schema CR is as given below.

The following query is made on the database.

T1 ← πCourseNameStudentName=’SA’(CR))
T2 ← CR ÷ T1

The number of rows in T2 is ____________. [GATE – 2017]

a. 4
b. 5
c. 6
d. 7

Answer : a)
DBMS |Gate-2017|


6. An ER model of a database consists of entity types A and B. These are connected by a relationship R which does not have its own attribute. Under which one of the following conditions, can the relational table for R be merged with that of A? [GATE – 2017]

a. Relationship R is one-to-many and the participation of A in R is total.
b. Relationship R is one-to-many and the participation of A in R is partial.
c. Relationship R is many-to-one and the participation of A in R is total.
d. Relationship R is one-to-many and the participation of A in R is partial.

Answer :c)


7. Consider the following tables T1 and T2.

T1                                                     

PQ
22
38
73
58
69
85
98

T2

RS
22
83
32
97
57
72

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascade and on-update cascade, In table T2, R is the primary key and S is the foreign key referencing P in table T1 with on-delete set NULL and on-update cascade. In order to delete record 〈3,8〉 from table T1, the number of additional records that need to be deleted from table T1 is _________. [GATE – 2017]

a. 0
b. 1
c. 2
d. 3

Answer : a)


8.  Two transactions T1 and T2 are given as

T1: r1(X)w1(X)r1(Y)w1(Y)
T2: r2(Y)w2(Y)r2(Z)w2(Z)

where ri(V) denotes a read operation by transaction Ti on a variable V and wi(V) denotes a write operation by transaction Ti on a variable V. The total number of conflict serializable schedules that can be formed by T1 and T2 is ____________. [GATE – 2017]

a. 57
b. 55
c. 56
d. 54

Answer : d)


9.  Consider the following database table named top_scorer.

                                                      Top_score

playercountryGoals
KloseGermany16
RonaldoBrazil15
G MullerGermany14
FontaineFrance13
PeleBrazil12
KlinsmannGermany11
KocsisHungary11
BatistutaArgentina10
CubillasPeru10
LatoPoland10
LinekerEngland10
T MullerGermany10
RahnGermany10

SELECT ta.player FROM top_scorer AS ta

WHERE ta.goals > ALL (SELECT tb.goals

                  FROM top_scorer AS tb

                  WHERE tb.country = ‘Spain’)

AND ta.goals > ANY (SELECT tc.goals

                  FROM top_scorer AS tc

                  WHERE tc.country = ‘Germany’)

The number of tuples returned by the above SQL query is _____. [GATE – 2017]

a. 7
b. 8
c. 9
d. 10

Answer : a)


10. In a B+ tree, if the search-key value is 8 bytes long, the block size is 512 bytes and the block pointer size is 2 bytes, then the maximum order of the B+ tree is _________. [GATE – 2017]

a. 51
b. 53
c. 52
d. 55

Answer : c)


OS – GATE Previous Year Questions
DS – GATE Previous Year Questions


Back to GATE-HOME


Spread the love

Leave a Comment

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