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

**1. **Which of the following is **NOT** a superkey in a relational schema with attributes *V, W, X, Y, Z* and primary key *VY*? **[GATE – 2016]**

a. VXYZ

b. VWXZ

c. VWXY

d. VWXYZ

*Answer : b)*

**2. **NOT a part of the **ACID** properties of database transactions? **[GATE – 2016]**

a. Atomicity

b. Isolation

c. Consistency

d. Deadlock-freedom

*Answer : d)*

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

(VOLUME, NUMBER, STARTPAGE, 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 satisﬁes, but the old one does not? **[GATE – 2016]**

a. 1NF

b. 2NF

c. BCNF

d. 3NF

*Answer : b)*

**4. **Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects{O_{1},…,O_{k}}. This is done in the following manner:

**Step1.** T acquires exclusive locks to O_{1},…,O_{k} in increasing order of their addresses.**Step2.** The required operations are performed.**Step3.** All locks are released.

This protocol will : **[GATE – 2016]**

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

*Answer : a)*

**5**. B^{+} Trees are considered **BALANCED** because: **[GATE – 2016]**

a. the lengths of the paths from the root to all leaf nodes are all equal.

b. the lengths of the paths from the root to all leaf nodes differ from each other by at most 1

c. the number of children of any two non-leaf sibling nodes differ by at most 1.

d. the number of records in any two leaf nodes differ by at most 1.

*Answer : a)*

DBMS |Gate-2016|

**6. **Suppose a database schedule *S* involves transactions *T _{1}, …, T_{n}*. Construct the precedence graph of

*S*with vertices representing the transactions and edges representing the conﬂicts. If

*S*is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?

**[GATE – 2016]**

a. Topological order

b. Depth-ﬁrst order

c. Breadth-ﬁrst order

d. Ascending order of transaction indices

*Answer : a)*

**7. **Consider the following database schedule with two transactions, T_{1} and T_{2}.

S = r_{2}(X); r_{1}(X); r_{2}(Y); w_{1}(X); r_{1}(Y); w_{2}(X); a_{1}; a_{2}

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**? **[GATE – 2016]**

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

*Answer : c)*

DBMS |Gate-2016|

**8. **Consider the following database table named water_schemes :

The number of tuples returned by the following SQL query is _________. **[GATE – 2016]**

**with** total(name, capacity) **as****select** district_name, **sum**(capacity)**from **water_schemes**group by** district_name**with** total_avg(capacity) **as****select avg**(capacity)**from** total**select** name**from** total, total_avg**where** total.capacity ≥ total_avg.capacity

a. 2

b. 3

c. 4

d. 5

*Answer : a)*