GATE CS Databases Previous Year Questions

GATE CS 2025 Set 1 | Question 15

MCQ · 1 mark · −0.33

A schedule of three database transactions \(T_1\), \(T_2\), and \(T_3\) is shown. \(R_i(A)\) and \(W_i(A)\) denote read and write of data item \(A\) by transaction \(T_i, i = 1,2,3\). The transaction \(T_1\) aborts at the end. Which other transaction(s) will be required to be rolled back?

\(R_1(X)\) \(W_1(Y)\) \(R_2(X)\) \(R_2(Y)\) \(R_3(Y)\) \(\mathit{ABORT}(T_1)\)

Only \(T_2\) Only \(T_3\) Both \(T_2\) and \(T_3\) Neither \(T_2\) nor \(T_3\)

Explanation

Answer: (C)

When \(T_1\) aborts, every transaction that read a value written by \(T_1\) (a dirty read) must also be rolled back; this is a cascading rollback. \(T_1\) wrote only \(Y\). Both \(R_2(Y)\) and \(R_3(Y)\) come after \(W_1(Y)\) with no other write to \(Y\) in between, so \(T_2\) and \(T_3\) both read the uncommitted value. \(R_2(X)\) does not matter, because \(T_1\) only read \(X\).

Answer: C

GATE CS 2025 Set 1 | Question 39

MCQ · 2 marks · −0.66

Consider two relations describing \(teams\) and \(players\) in a sports league:

\(teams(tid, tname)\): \(tid\), \(tname\) are team-id and team-name, respectively
\(players(pid, pname, tid)\): \(pid\), \(pname\), and \(tid\) denote player-id, player-name and the team-id of the player, respectively

Which ONE of the following tuple relational calculus queries returns the name of the players who play for the team having \(tname\) as \(\text{‘MI’}\)?

\(\{\, p.pname \mid p \in players \land \exists t\, (t \in teams \land p.tid = t.tid \land t.tname = \text{‘MI’}) \,\}\) \(\{\, p.pname \mid p \in teams \land \exists t\, (t \in players \land p.tid = t.tid \land t.tname = \text{‘MI’}) \,\}\) \(\{\, p.pname \mid p \in players \land \exists t\, (t \in teams \land t.tname = \text{‘MI’}) \,\}\) \(\{\, p.pname \mid p \in teams \land \exists t\, (t \in players \land t.tname = \text{‘MI’}) \,\}\)

Explanation

Answer: (A)

The result must list players, so \(p\) has to range over \(players\) (the relation that has \(pname\)), and there must exist a team \(t\) with the same \(tid\) whose \(tname\) is ‘MI’.

(A) does exactly this: \(p \in players\), \(t \in teams\), joined by \(p.tid = t.tid\) and filtered by \(t.tname = \text{‘MI’}\). Correct.
(B) and (D) take \(p\) from \(teams\) and \(t\) from \(players\), but \(teams\) has no \(pname\) and \(players\) has no \(tname\), so they are wrong.
(C) has no join condition \(p.tid = t.tid\): as long as some team is called ‘MI’, it returns every player in the league.

Answer: A

GATE CS 2025 Set 1 | Question 47

MSQ · 2 marks

Consider a relational schema \(team(name, city, owner)\), with functional dependencies \(\{name \to city,\ name \to owner\}\).

The relation \(team\) is decomposed into two relations, \(t1(name, city)\) and \(t2(name, owner)\). Which of the following statement(s) is/are TRUE?

The relation \(team\) is NOT in BCNF. The relations \(t1\) and \(t2\) are in BCNF. The decomposition constitutes a lossless join. The relation \(team\) is NOT in 3NF.

Explanation

Answer: (B), (C)

Key: \(name^+ = \{name, city, owner\}\), so \(name\) is a candidate key of \(team\). Both FDs have this key on the left, so \(team\) is already in BCNF, and therefore in 3NF too. So (A) and (D) are false.
(B) True: in \(t1\) the only FD is \(name \to city\) and in \(t2\) it is \(name \to owner\); in each, \(name\) is a key. (Any relation with just two attributes is in BCNF anyway.)
(C) True: the common attribute of \(t1\) and \(t2\) is \(name\), which is a key of \(t1\) (and of \(t2\)), so joining them on \(name\) gives back exactly the original relation.

Answer: B, C

GATE CS 2025 Set 1 | Question 55

NAT · 2 marks

Consider the following database tables of a sports league.

player(pid,pname,age)          team(tid,tname,city,cid)
coach(cid,cname)               members(pid,tid)

An instance of the table and an SQL query are given.

player
pid	pname	age
1	Jasprit	31
2	Atharva	24
3	Ishan	26
4	Axar	30

coach
cid	cname
101	Ricky
102	Mark
103	Trevor

team
tid	tname	city	cid
10	MI	Mumbai	102
20	DC	Delhi	101
30	PK	Mohali	103

members
pid	tid
1	10
2	30
3	10
4	20

SELECT MIN(P.age)
FROM player P
WHERE P.pid IN (
    SELECT M.pid
    FROM team T, coach C, members M
    WHERE C.cname = 'Mark'
          AND T.cid = C.cid
          AND M.tid = T.tid
    )

The value returned by the given SQL query is ______ . (Answer in integer)

Your answer

Explanation

Answer: 26

The subquery joins coach, team and members to find the players in teams coached by Mark.

coach: Mark has cid = 102.
team: the team with cid = 102 is MI, with tid = 10.
members: tid = 10 gives pid 1 and pid 3.

The outer query takes the smaller age of pid 1 (Jasprit, 31) and pid 3 (Ishan, 26), which is 26.

Answer: 26

GATE CS 2025 Set 2 | Question 27

MSQ · 1 mark

An audit of a banking transactions system has found that on an earlier occasion, two joint holders of account \(A\) attempted simultaneous transfers of Rs. 10000 each from account \(A\) to account \(B\). Both transactions read the same value, Rs. 11000, as the initial balance in \(A\) and were allowed to go through. \(B\) was credited Rs. 10000 twice. \(A\) was debited only once and ended up with a balance of Rs. 1000.

Which of the following properties is/are certain to have been violated by the system?

Atomicity Consistency Isolation Durability

Explanation

Answer: (B), (C)

Each transaction read \(A = 11000\), wrote \(A = 1000\) and added Rs. 10000 to \(B\). The second write to \(A\) overwrote the first: a lost update.

Isolation is violated. Run one after the other, the second transfer would have seen only Rs. 1000 in \(A\); no serial order gives the observed result.
Consistency is violated. A transfer must keep the total \(A + B\) unchanged, but the total grew by Rs. 10000.
Atomicity is not shown to fail: each transaction carried out all of its steps, and nothing was left half done.
Durability is not shown to fail: the committed writes all survived.

Answer: B, C

GATE CS 2025 Set 2 | Question 46

MSQ · 2 marks

Consider the following relational schema along with all the functional dependencies that hold on them.

R1(A, B, C, D, E): \(\{D \to E,\ EA \to B,\ EB \to C\}\)

R2(A, B, C, D): \(\{A \to D,\ A \to B,\ C \to A\}\)

Which of the following statement(s) is/are TRUE?

R1 is in 3NF R2 is in 3NF R1 is NOT in 3NF R2 is NOT in 3NF

Explanation

Answer: (C), (D)

A relation is in 3NF if, for every non-trivial FD \(X \to Y\), either \(X\) is a superkey or every attribute of \(Y\) is prime (part of some candidate key).

R1: \(A\) and \(D\) appear on no right-hand side, so every key contains both, and \(\{A, D\}^{+} = \{A, B, C, D, E\}\). The only key is \(AD\). In \(D \to E\), \(D\) is not a superkey and \(E\) is not prime, so R1 is NOT in 3NF (it is not even in 2NF).
R2: \(C\) appears on no right-hand side and \(C^{+} = \{A, B, C, D\}\), so \(C\) is the only key. In \(A \to D\), \(A\) is not a superkey and \(D\) is not prime: the transitive dependency \(C \to A \to D\). So R2 is NOT in 3NF.

Answer: C, D

GATE CS 2025 Set 2 | Question 53

MSQ · 2 marks

Consider the database transactions T1 and T2, and data items X and Y. Which of the schedule(s) is/are conflict serializable?

Transaction T1
R1(X)
W1(Y)
R1(X)
W1(X)
COMMIT(T1)

Transaction T2
W2(X)
W2(Y)
COMMIT(T2)

R1(X), W2(X), W1(Y), W2(Y), R1(X), W1(X), COMMIT(T2), COMMIT(T1) W2(X), R1(X), W2(Y), W1(Y), R1(X), COMMIT(T2), W1(X), COMMIT(T1) R1(X), W1(Y), W2(X), W2(Y), R1(X), W1(X), COMMIT(T1), COMMIT(T2) W2(X), R1(X), W1(Y), W2(Y), R1(X), COMMIT(T2), W1(X), COMMIT(T1)

Explanation

Answer: (B)

Build the precedence graph. Two operations conflict when they come from different transactions, touch the same item and at least one is a write; the earlier one's transaction gets an edge to the later one's. Commits add no edges.

(A) R1(X) before W2(X) gives T1 → T2, but W2(X) before the second R1(X) gives T2 → T1. Cycle.
(B) W2(X) comes before both R1(X) operations and W1(X), and W2(Y) comes before W1(Y). Every edge is T2 → T1, so the graph is acyclic and the schedule is equivalent to the serial order T2, T1.
(C) W1(Y) before W2(Y) gives T1 → T2, while W2(X) before the second R1(X) gives T2 → T1. Cycle.
(D) W2(X) before R1(X) gives T2 → T1, while W1(Y) before W2(Y) gives T1 → T2. Cycle.

Answer: B

GATE CS 2025 Set 2 | Question 54

MSQ · 2 marks

Consider the following relational schema:

Students (rollno: integer, name: string, age: integer, cgpa: real)

Courses (courseno: integer, cname: string, credits: integer)

Enrolled (rollno: integer, courseno: integer, grade: string)

Which of the following options is/are correct SQL query/queries to retrieve the names of the students enrolled in course number (i.e., courseno) 1470?

SELECT S.name
FROM Students S
WHERE EXISTS (SELECT * FROM Enrolled E
              WHERE E.courseno = 1470
                      AND E.rollno = S.rollno);

SELECT S.name
FROM Students S
WHERE SIZEOF (SELECT * FROM Enrolled E
              WHERE E.courseno = 1470
                      AND E.rollno = S.rollno) > 0;

SELECT S.name
FROM Students S
WHERE  0 < (SELECT COUNT(*)
            FROM Enrolled E
            WHERE E.courseno = 1470
                      AND E.rollno = S.rollno);

SELECT S.name
FROM Students S NATURAL JOIN Enrolled E
WHERE E.courseno = 1470;

Explanation

Answer: (A), (C), (D)

(A) For each student, the correlated EXISTS subquery is true exactly when an Enrolled row has that student's roll number and course 1470. Correct.
(B) SIZEOF is not an SQL operator or aggregate function, so this is not a valid query.
(C) The correlated COUNT(*) counts the student's enrolments in course 1470, and requiring it to be greater than 0 is the same test as (A). Correct.
(D) rollno is the only attribute common to Students and Enrolled, so the natural join pairs each student with their enrolments and the filter keeps course 1470. Because (rollno, courseno) is the key of Enrolled, each such student appears once. Correct.

Answer: A, C, D

GATE CS 2025 Set 2 | Question 57

NAT · 2 marks

In a B⁺- tree where each node can hold at most four key values, a root to leaf path consists of the following nodes:

A = (49, 77, 83, -), B = (7, 19, 33, 44), C = (20*, 22*, 25*, 26*)

The *-marked keys signify that these are data entries in a leaf.

Assume that a pointer between keys \(k_1\) and \(k_2\) points to a subtree containing keys in \([k_1, k_2)\), and that when a leaf is created, the smallest key in it is copied up into its parent.

A record with key value 23 is inserted into the B⁺- tree.

The smallest key value in the parent of the leaf that contains 25* is __________. (Answer in integer)

Your answer

Explanation

Answer: 33

Key 23 goes to leaf C: B is the child of A for keys below 49, and C is B's child for \([19, 33)\). C is full, so the five keys 20, 22, 23, 25, 26 split into two leaves, and the smallest key of the new right leaf (23 for a 2 + 3 split, 25 for a 3 + 2 split) is copied into B.

B now has five keys, one too many, so it splits as well: its middle key (the one just copied up) moves to A, which has room, leaving (7, 19) on the left and (33, 44) on the right. The leaf holding 25* lies to the right of the promoted key, so its parent is (33, 44).

Both ways of splitting the leaf give the same parent, whose smallest key is 33.

Answer: 33

GATE CS 2024 Set 1 | Question 20

MCQ · 1 mark · −0.33

Let S be the specification: "Instructors teach courses. Students register for courses. Courses are allocated classrooms. Instructors guide students." Which one of the following ER diagrams CORRECTLY represents S?

ER diagrams (i) and (ii), with entity sets in rectangles and relationships in diamonds. In (i), Instructor–Teaches–Course and Classroom–Allocation–Course, a Registration rectangle is attached directly to Course, and a Student diamond joins Course to a Guides diamond that joins Instructor; in (ii), Instructor–Teaches–Course, Classroom–Allocation–Course, Course–Registration–Projects and Instructor–Guides–Projects.

ER diagrams (iii) and (iv), with entity sets in rectangles and relationships in diamonds. In (iii), Instructor–Teaches–Course, Course–Registration–Student and Instructor–Guides–Student, while Classroom is joined to Course by a plain line and the Allocation diamond hangs from Course alone; in (iv), Instructor–Teaches–Course, Classroom–Allocation–Course, Course–Registration–Student and Instructor–Guides–Student.

(i) (ii) (iii) (iv)

Explanation

Answer: (D)

The specification has four entity sets (Instructor, Course, Student and Classroom) and four relationships: Teaches (Instructor–Course), Registration (Student–Course), Allocation (Course–Classroom) and Guides (Instructor–Student). In an ER diagram entity sets are rectangles, relationships are diamonds, and two entity sets are linked only through a relationship.

(i) swaps the roles: Student is drawn as a relationship and Registration as an entity set.
(ii) has no Student entity set at all; Projects, which the specification never mentions, takes its place.
(iii) joins Classroom straight to Course with a plain line, and its Allocation diamond is connected to Course only.
(iv) shows exactly the four entity sets joined through the four relationships.

Answer: D

Topics