GATE Computer Science Database Management Systems Questions

Consider the following B+ tree with 5 nodes, in which a node can store at most 3 key values. The value 23 is now inserted in the B+ tree. Which of the following options(s) is/are CORRECT?

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

Transaction $T_1$

Transaction $T_2$

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)ABORT(T_1)$

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 'MI'?

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?

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.

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)

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?

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?

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?

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)