All DBMS questions

One or more correct

• AIf $(\{P, Q\} \to \{R\}$ and $\{P\} \to \{R\})$, then $\{Q\} \to \{R\}$
• BIf $\{P, Q\} \to \{R\}$, then $(\{P\} \to \{R\}$ or $\{Q\} \to \{R\})$
• CIf $(\{P\} \to \{R\}$ and $\{Q\} \to \{S\})$, then $\{P, Q\} \to \{R, S\}$
• DIf $\{P\} \to \{R\}$, then $\{P, Q\} \to \{R\}$

One or more correct

• AIt is always possible to obtain a dependency-preserving 3NF decomposition of a relation
• BIt is always possible to obtain a dependency-preserving 1NF decomposition of a relation
• CIt is not always possible to obtain a dependency-preserving BCNF decomposition of a relation
• DIt is not always possible to obtain a dependency-preserving 2NF decomposition of a relation

Single correct

• A$\Pi_P(R \bowtie_{R.P = S.X} S)$
• B$\Pi_P(S \bowtie_{S.X = R.Q} R)$
• C$\Pi_P(R \bowtie_{R.P = S.Y} S)$
• D$\Pi_P(S \bowtie_{S.Y = R.Q} R)$

Numerical answer

T                        S
I:   Logical schema      L: Views
II:  Physical schema     M: File organization and indexes
III: External schema     N: Relations

Single correct

• AI – L, II – M, III – N
• BI – M, II – L, III – N
• CI – N, II – M, III – L
• DI – N, II – L, III – M

Single correct

• ABoth $T_1$ and $T_2$ only read $A$
• B$T_1$ reads $A$ and $T_2$ writes $A$
• C$T_1$ writes $A$ and $T_2$ reads $A$
• DBoth $T_1$ and $T_2$ write $A$

Single correct

• AReflexivity, Augmentation, and Transitivity
• BReflexivity and Augmentation
• CTransitivity
• DAugmentation and Transitivity

Single correct

• ABoth S1 and S2 are true
• BBoth S1 and S2 are false
• CS1 is true and S2 is false
• DS1 is false and S2 is true

Single correct

• AOnly $T_2$
• BOnly $T_3$
• CBoth $T_2$ and $T_3$
• DNeither $T_2$ nor $T_3$

One or more correct

• ANone of the nodes will split.
• BAt least one node will split and redistribute.
• CThe total number of nodes will remain same.
• DThe height of the tree will increase.

Single correct

• A$\{\, p.pname \mid p \in players \wedge \exists t\, (t \in teams \wedge p.tid = t.tid \wedge t.tname = \text{'MI'}) \}$
• B$\{\, p.pname \mid p \in teams \wedge \exists t\, (t \in players \wedge p.tid = t.tid \wedge t.tname = \text{'MI'}) \}$
• C$\{\, p.pname \mid p \in players \wedge \exists t\, (t \in teams \wedge t.tname = \text{'MI'}) \}$
• D$\{\, p.pname \mid p \in teams \wedge \exists t\, (t \in players \wedge t.tname = \text{'MI'}) \}$

One or more correct

• AThe relation $team$ is NOT in BCNF.
• BThe relations $t1$ and $t2$ are in BCNF.
• CThe decomposition constitutes a lossless join.
• DThe relation $team$ is NOT in 3NF.

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

player                  coach            team                         members
pid  pname    age       cid  cname       tid  tname  city    cid      pid  tid
1    Jasprit  31        101  Ricky       10   MI     Mumbai  102      1    10
2    Atharva  24        102  Mark        20   DC     Delhi   101      2    30
3    Ishan    26        103  Trevor      30   PK     Mohali  103      3    10
4    Axar     30                                                      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
    )

Numerical answer

One or more correct

• AAtomicity
• BConsistency
• CIsolation
• DDurability

One or more correct

• AR1 is in 3NF
• BR2 is in 3NF
• CR1 is NOT in 3NF
• DR2 is NOT in 3NF

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

One or more correct

• AR1(X), W2(X), W1(Y), W2(Y), R1(X), W1(X), COMMIT(T2), COMMIT(T1)
• BW2(X), R1(X), W2(Y), W1(Y), R1(X), COMMIT(T2), W1(X), COMMIT(T1)
• CR1(X), W1(Y), W2(X), W2(Y), R1(X), W1(X), COMMIT(T1), COMMIT(T2)
• DW2(X), R1(X), W1(Y), W2(Y), R1(X), COMMIT(T2), W1(X), COMMIT(T1)

Students (rollno: integer, name: string, age: integer, cgpa: real)
Courses (courseno: integer, cname: string, credits: integer)
Enrolled (rollno: integer, courseno: integer, grade: string)

One or more correct

• A

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

• B

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

• C

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

• D

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

Numerical answer

Single correct

• A(i)
• B(ii)
• C(iii)
• D(iv)

Single correct

• AOnly the root node
• BAll leaf nodes
• CAll internal nodes
• DOnly the leftmost leaf node