DBMS Gate Previous Year Questions

Which one of the option given below refers to the degree of relation in relational database systems?

a) Number of distinct domains of its relation schema.
b) Number of attributes of its relation schema.
c) Number of entries in the relation.
d) Number of tuples stored in the relation.

Let Ri(z) and Wi(z) denote read and write operations on a data element z by a transaction Ti, respectively. Consider the schedule S with four transactions.

S: R4(x)R2(x)R3(x)R1(y)W1(y)W2(x)W3(y)R4(y)

Which one of the following serial schedules is conflict equivalent to S?

a) T₁→T₃→T₄→T₂
b) T₁→T₄→T₃→T₂
c) T₄→T₁→T₃→T₂
d) T₃→T₁→T₄→T₂

In a relational data model, which one of the following statements is TRUE?

a) A relation with only two attributes is always in BCNF.
b) If all attributes of a relation are prime attributes, then the relation is in BCNF.
c) Every relation has at least one non-prime attribute.
d) BCNF decompositions preserve functional dependencies.

Consider the following three relations in a relational database. Employee (eId, Name ), Brand (bId, bName), Own (eId, bId ) Which of the following relational algebra expressions return the set of eIds who own all the brands?

a) π_eld(π_eld,bld(own)/π_brand))
b) π_eld(own)-π_eld((π_eld(own)xπ_bld(brand))-π_bld,eld(own))
c) π_eld(π_eld,bld(own)/π_own))
d) π_eld((π_eld(own)xπ_bld(own))/π_bld(brand))

Consider two files systems A and B , that use contiguous allocation and linked allocation, respectively. A file of size 100 blocks is already stored in A and also in B. Now, consider inserting a new block in the middle of the file (between 50th and 51st block), whose data is already available in the memory. Assume that there are enough free blocks at the end of the file and that the file control blocks are already in memory. Let the number of disk accesses required to insert a block in the middle of the file in A and B are n_A and n_B respectively, then the value of n_A + n_B is_________.

Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE ?

a) R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute.
b) R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is not a proper subset of any key.
c) R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is a proper subset of some key.
d) A cell in R holds a set instead of an atomic value.

Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram ?

a) Diamonds with double/bold border
b) Rectangles with double/bold border
c) Ovals with double/bold border
d) Ovals that contain underlined identifiers

Consider the following two statements about database transaction schedules:

a) I only
b) II only
c) Both I and II
d) Neither I nor II

Let the set of functional dependencies F = {QR → S, R → P, S → Q} hold on a relation schema X = (PQRS). X is not in BCNF. Suppose X is decomposed into two schemas and Z where Y = (PR) and Z = (QRS).
Consider the two statements given below:

a) Both I and II
b) Neither I nor II
c) I only
d) II only

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 of the following conditions, can the relational table for R be merged with that of A?

(A) Relation R is one-to-many and the participation of A in R is total.
(B) Relation R is one-to-many and the participation of A in R is partial.
(C) Relation R is many-to-one and the participation of A in R is total.
(D) Relation R is many-to-one and the participation of A in R is partial.

a) A
b) B
c) C
d) D

In a database system, unique time stamps are assigned to each transaction using Lamport’s logical clock. Let TS(T1) and TS(T2) be the time stamps 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 assuming that a killed transaction is restarted with the same timestamp.

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

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

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

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

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

Consider the following database schedule with two transactions, T₁ and T₂.

S = r₂(X); r₁(X); r₂(Y); w₁(X); r₁(Y); w₂(X); a₁; a₂;

where r_i(Z) denotes a read operation by transaction T_i on a variable Z, w_i(Z) denotes a write operation by T_i on a variable Z and a_i denotes an abort by transaction T_i . Which one of the following statements about the above schedule is TRUE?

a) S is non-recoverable
b) S is recoverable, but has a cascading abort
c) S does not have a cascading abort
d) S is strict

Suppose a database schedule S involves transactions T1, ....Tn. Construct the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?

a) Topological order
b) Depth-first order
c) Breadth-first order
d) Ascending order of transaction indices

Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O₁,...,O_k}. This is done in the following manner:

Step 1. T acquires exclusive locks to O₁,...,O_k in increasing order of their addresses.
Step 2. The required operations are performed.
Step 3. All locks are released.

This protocol will

a) guarantee serializability and deadlock-freedom
b) guarantee neither serializability nor deadlock-freedom
c) guarantee serializability but not deadlock-freedom
d) guarantee deadlock-freedom but not serializability

A database of research articles in a journal uses the following schema.

(VOLUME, NUMBER, STARTPGE, ENDPAGE, TITLE, YEAR, PRICE)

The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> TITLE
(VOLUME, NUMBER) -> YEAR
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> PRICE

The database is redesigned to use the following schemas.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database satisfies, but the old one does not?

a) 1NF
b) 2NF
c) 3NF
d) BCNF

Which one of the following is NOT a part of the ACID properties of database transactions?

a) Atomicity
b) Consistency
c) Isolation
d) Deadlock-freedom

Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key V Y ?

a) V X Y Z
b) V W X Z
c) V W X Y
d) V W X Y Z

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?

a) {P,R}→{S,T}
b) {P,R}→{R,T}
c) {P,S}→{S}
d) {P,S,U}→{Q}

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)

Consider the following transaction involving two bank accounts 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

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

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