Computer Science And Engineering - Database Management System (DBMS) PYQ

Q1.MCQ

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

Q2.M-MCQ

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?

Q3.MCQ

A schedule of three database transactions $T1$, $T2$, and $T3$ is shown. $Ri(A)$ and $Wi(A)$ denote read and write of data item $A$ by transaction $Ti, i = 1,2,3$. The transaction $T1$ aborts at the end. Which other transaction(s) will be required to be rolled back? $$R1(X)\ W1(Y)\ R2(X)\ R2(Y)\ R3(Y)\ ABORT(T1)$$

Q4.NUMERICAL

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. !image(https://uadmin.udsfgecw.tech/file/download/public/public:90929af6-06bc-450f-9e03-e6d904c2f68c.png) sql 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 )

Q5.M-MCQ

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? image needed to be inserted here

Q6.NUMERICAL

Consider the following two relations, $R(A,B)$ and $S(A,C)$: image needed to be inserted here The total number of tuples obtained by evaluating the following expression $$\sigma{B < C}(R \bowtie{R.A = S.A} S)$$ is \\\\\\\\\\\

Q7.M-MCQ

The symbol $\rightarrow$ indicates functional dependency in the context of a relational database. Which of the following options is/are TRUE?

Q8.M-MCQ

Consider the following read-write schedule $S$ over three transactions $T1$, $T2$, and $T3$, where the subscripts in the schedule indicate transaction IDs: $$S: r1(z); w1(z); r2(x); r3(y); w3(y); r2(y); w2(x); w2(y);$$ Which of the following transaction schedules is/are conflict equivalent to $S$ ?