How are SQL queries tested in GATE DBMS questions?

GATE tests SQL SELECT with WHERE, GROUP BY, HAVING, ORDER BY, nested subqueries, correlated subqueries, EXISTS/NOT EXISTS, and set operations (UNION, INTERSECT, EXCEPT). Questions ask you to find the output of a query or write an equivalent relational algebra expression.

What is lossless join decomposition in GATE DBMS?

A decomposition of relation R into R1 and R2 is lossless if R1 ⋈ R2 = R — no spurious tuples are generated. GATE tests the condition for lossless decomposition: the common attributes of R1 and R2 must form a superkey of either R1 or R2. This is tested using functional dependencies.

What is two-phase locking and how is it tested in GATE CS?

Two-phase locking (2PL) ensures conflict serializability by requiring all lock acquisitions to happen before any lock release (growing phase followed by shrinking phase). GATE tests strict 2PL (holds all exclusive locks until commit), which guarantees recoverability, and whether given schedules follow 2PL.

How is relational algebra division operator tested in GATE DBMS?

The relational algebra division operator (R ÷ S) retrieves tuples in R that are associated with all tuples in S. GATE tests division for "find all X who are associated with every Y" queries, such as finding employees who own all brands, and its equivalent SQL using NOT EXISTS or double negation.

CS/IT Gate Subject

Aptitude and Reasoning
Engineering Mathematics
C Programming
Data Structure
Design Algorithm Analysis
Operating System
Computer Network
Digital Logic Design
Compiler Design
Database Management System
Theory of Computation

DBMS GATE previous year questions with answer

Ques 14 Gate 2017 Set-2

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.

(ER model) is the correct answer.

Ques 15 Gate 2017 Set-1

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.

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

.
.
Assume any transactions that is not killed terminates eventually. Which of the following is TRUE about the database system that uses the above algorithm to prevent deadlocks?

The database system is both deadlock-free and starvation- free.

The database system is deadlock- free, but not starvation-free.

The database system is starvation-free but not deadlock- free.

The database system is neither deadlock- free nor starvation-free.

(a) is the correct answer.

PROCESSED

Ques 16 Gate 2017 Set-1

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.

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

I and II only

I and III only

II and III only

I, II, and III

(Relational Algebra) is the correct answer.

Ques 17 Gate 2017 Set-1

The following functional dependencies hold true for the relational schema R{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?

V→W
V→X
Y→V
Y→Z

V→W
W→X
Y→V
Y→Z

V→W
V→X
Y→V
Y→X
Y→Z

V→W
W→X
Y→V
Y→X
Y→Z

(Functional Dependency) is the correct answer.

Ques 18 Gate 2016 Set-2

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?

S is non-recoverable

S is recoverable, but has a cascading abort

S does not have a cascading abort

S is strict

(Transactions) is the correct answer.

PROCESSED

Ques 19 Gate 2016 Set-2

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?

Topological order

Depth-first order

Breadth-first order

Ascending order of transaction indices

(Transactions) is the correct answer.

PROCESSED

Ques 20 Gate 2016 Set-1

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

guarantee serializability and deadlock-freedom

guarantee neither serializability nor deadlock-freedom

guarantee serializability but not deadlock-freedom

guarantee deadlock-freedom but not serializability

(T) is the correct answer.

Given:
A protocol where a transaction acquires exclusive locks on objects in increasing order of their addresses.
All required operations are executed after getting the locks.
All locks are released after the operations are completed.
To find:
Whether this protocol guarantees serializability and/or deadlock-freedom.
Solution:
First, let me check the locking and unlocking phases.
All locks are acquired in Step 1, and no lock is released until Step 3.
Since all lock requests precede all unlock requests, this protocol follows the Two-Phase Locking (2PL) rule.
I know that any 2PL protocol guarantees conflict serializability.
Now let me check the deadlock condition.
Here, transactions acquire locks on items strictly in increasing order of their addresses.
This creates a global total ordering on all resources.
Because every transaction requests objects according to the same global order, circular wait is impossible.
Since circular wait cannot happen, deadlock can never occur.
So this protocol guarantees both serializability and deadlock-freedom.
Answer: (A) guarantee serializability and deadlock-freedom

Ques 21 Gate 2016 Set-1

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?

1NF

2NF

3NF

BCNF

(N) is the correct answer.

Given:
Original schema: R(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)
Primary Key: (VOLUME, NUMBER, STARTPAGE, ENDPAGE)
Functional Dependencies:
FD1: (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE
FD2: (VOLUME, NUMBER) → YEAR
FD3: (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE
Redesigned schemas:
R₁(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
R₂(VOLUME, NUMBER, YEAR)
To find:
The weakest normal form that the new database satisfies, but the old one does not.
Solution:
First, let me analyze the normal form of the original schema R.
The candidate key for R is (VOLUME, NUMBER, STARTPAGE, ENDPAGE).
So the prime attributes are: VOLUME, NUMBER, STARTPAGE, ENDPAGE.
The non-prime attributes are: TITLE, YEAR, PRICE.
Looking at FD2: (VOLUME, NUMBER) → YEAR.
Here, (VOLUME, NUMBER) is a proper subset of the candidate key, and YEAR is a non-prime attribute.
This means there is a partial dependency, so R violates 2NF.
Thus, the original schema is only in 1NF.
Now, let me check the redesigned schemas.
For relation R₁:
The candidate key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE).
The functional dependencies are:
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE
In both FDs, the left-hand side is a superkey. So R₁ is in BCNF.
For relation R₂:
The candidate key is (VOLUME, NUMBER).
The functional dependency is:
(VOLUME, NUMBER) → YEAR
Here too, the left-hand side is a superkey. So R₂ is in BCNF.
Since both R₁ and R₂ are in BCNF, the new database satisfies 2NF, 3NF, and BCNF.
The old database does not satisfy 2NF, 3NF, or BCNF.
Among the normal forms that the new database satisfies but the old one does not (2NF, 3NF, BCNF), the weakest one is 2NF.
Answer:
(B) 2NF

Ques 22 Gate 2016 Set-1

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

Atomicity

Consistency

Isolation

Deadlock-freedom

(T) is the correct answer.

Given:
Four properties related to database transactions: Atomicity, Consistency, Isolation, and Deadlock-freedom.
To find:
The property that is NOT a part of the ACID properties.
Solution:
Let me recall what the acronym ACID stands for in database systems.
A stands for Atomicity, meaning all operations in a transaction succeed, or none do.
C stands for Consistency, ensuring the database moves from one valid state to another.
I stands for Isolation, ensuring concurrent transactions do not interfere with each other.
D stands for Durability, ensuring that committed changes are saved permanently.
Now looking at the options, Deadlock-freedom is given for letter D instead of Durability.
So Deadlock-freedom is definitely not an ACID property.
Answer: (D) Deadlock-freedom

Ques 23 Gate 2016 Set-1

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

V X Y Z

V W X Z

V W X Y

V W X Y Z

(S) is the correct answer.

Given:
Relation schema attributes = {V, W, X, Y, Z}
Primary key = V Y
To find:
Which of the given options is NOT a superkey.
Solution:
Okay, let's recall the basic definition of a superkey.
A superkey is simply any set of attributes that contains a candidate key (or primary key) as a subset.
Here, the primary key is given as {V, Y}.
So, any attribute set that contains both V and Y will act as a superkey.
Now let me check each option one by one.
Checking option A: {V, X, Y, Z}.
It contains both V and Y, so this is a superkey.
Checking option B: {V, W, X, Z}.
Wait, Y is missing here! It only has V, not Y.
Since it does not contain the primary key {V, Y}, it cannot be a superkey.
Let me just quickly check the remaining options to be sure.
Checking option C: {V, W, X, Y}.
Contains both V and Y, so it is a superkey.
Checking option D: {V, W, X, Y, Z}.
Contains both V and Y, so it is also a superkey.
Therefore, option B is definitely not a superkey.
Answer: (B)

Total Unique Visitors

Loading......

CS/IT Gate Subject

DBMS GATE previous year questions with answer

Question Options

Sign in to ExamPrev