Questions & Answers of Databases

The following functional dependencies hold true for the relational schema R {V, W, X, Y, Z}:

                                                                                $\style{font-family:'Times New Roman'}{\begin{array}{l}V\rightarrow W\\VW\rightarrow X\\Y\rightarrow VX\\Y\rightarrow Z\end{array}}$

Which of the following  is irreducible equivalent  for this  set of functional dependencies ?

•  (A)  $\style{font-family:'Times New Roman'}{\begin{array}{l}V\rightarrow W\\V\rightarrow X\\Y\rightarrow V\\Y\rightarrow Z\end{array}}$
•  (B)  $\style{font-family:'Times New Roman'}{\begin{array}{l}V\rightarrow W\\W\rightarrow X\\Y\rightarrow V\\Y\rightarrow Z\end{array}}$
•  (C)  $\style{font-family:'Times New Roman'}{\begin{array}{l}V\rightarrow W\\V\rightarrow X\\Y\rightarrow V\\Y\rightarrow X\\Y\rightarrow Z\end{array}}$
•  (D)  $\style{font-family:'Times New Roman'}{\begin{array}{l}V\rightarrow W\\W\rightarrow X\\Y\rightarrow V\\Y\rightarrow X\\Y\rightarrow Z\end{array}}$

Consider a database that has the relation schema EMP (EmpID, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are below.

The output of executing the SQL query is_______________.

Consider  a database that has the relation schems EMP (Empld, EmpName, DeptId), and DEPT (DeptName, DeptId). Note that the DeptId can be permited to be NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

$\style{font-family:'Times New Roman'}{\begin{array}{l}(\mathrm I)\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\forall\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack\neq\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\\(\mathrm{II})\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\exists\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack\neq\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\\(\mathrm{III})\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\exists\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack=\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\end{array}}$ 

Which of the above queries are safe?

•  (A)  (I) and (II) only
•  (B)  (I) and (III) only
•  (C)  (II) and (III) only
•  (D)  (I) ,(II) and (II)

In a database system, unique timestamps are assigned to each transaction using Lamport's logical clock. Let TS(T₁) and TS(T₂) be the timestamps of transaction T₁ and T₂ respectively. Besides T₁ holds a lock on the resource R, and T₂ has requested a conflicting lock on the same resource R. The following algorithm is used to prevent deadlocks in the database system assuming that a killed transaction is restarted with the same timestamp.

if TS(T₂)<TS(T₁)then

T₁ is killed

else T₂ waits.

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

•  (A)  The database system is both deadlock-free and starvation-free.
•  (B)  The database system is deadlock-free, but not starvation-free.
•  (C)  The database system is starvation-free, but not deadlock-free
•  (D)  The databasre system is nither deadlock-free nor starvation-free.

Consider a database that has the relation schema CR (StudentName, CourseName).An instance of the schema CR is as given below.

The following query is made on the database.

$\style{font-family:'Times New Roman'}{\begin{array}{l}T1\leftarrow{\mathrm\pi}_{CourseName}\;({\mathrm\sigma}_{StudentName\mathit=\mathit'SA\mathit'}(\mathrm{CR}))\\T2\leftarrow CR\;\div T1\end{array}}$

The number of rows in T2 is__________

An ER model of a database consists of entity types A and B. These are connected by a relationship R which dose not have its own attribute. Under which one of the following condiditons, can the relational table for R be merged with that of A?

•  (A)  Relationship R is one-to-many and the participation of A in R is total.
•  (B)  Relationship R is one-to-many and the participation of A in R is partial.
•  (C)  Relationship R is many-to-one and the participation of A in R is total.
•  (D)  Relationship R is many-to-one and the participation of A in R is partial.

Consiider the following tables T1 and T2

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascabe and on-update cascade. In table T2, R is the primary key and S is the forign key refrencing P in table T1 with on-delete set NULL and ON-update cascade. In order to delete record <3,8> from table T1, the number of additional records that need to be deleted from table T1 is _____________________.

Two transaction T₁ and T₂ are given as

T₁ : r₁(X)w₁(X)r₁(Y)w₁(Y)

T₂ : r₂(Y)w₂(Y)r₂(Z)w₂(Z)

Where r_i (V) denotes a read operation by transaction T_i on a variable V and w_i(V) denotes a write operation by transaction T_i on a variable V. The total number of conflict serializable schedules that can be formed by T₁ and T₂ is ___________.

Consider the following database table named top_scorer.

top_scorer

Consider the following SQL query:

In a B+ tree, if the search-key value is 8 bytes long, the block size is 512 bytes and the block pointer size is 2 bytes, then the maximum order of the B+ tree is ___________.

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

•  (A)  VXYZ
•  (B)  VWXZ
•  (C)  VWXY
•  (D)  VWXYZ

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

•  (A)  Atomicity
•  (B)  Consistency
•  (C)  Isolation
•  (D)  Deadlock-freedom

A database of research articles in a journal uses the following schema.
     (VOLUME, NUMBER, STARTPAGE, 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?

•  (A)  1NF
•  (B)  2NF
•  (C)  3NF
•  (D)  BCNF

Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects{0₁,...,0_k}. This is done in the following manner:

Step1. T acquires exclusive locks to 0₁, ..., 0_k in increasing order of their addresses.

Step2. The required operations are performed.

Step3. All locks are released.

This protocol will

•  (A)  guarantee serializability and deadlock-freedom
•  (B)  guarantee neither serializability nor deadlock-freedom
•  (C)  guarantee serializability but not deadlock-freedom
•  (D)  guarantee deadlock-freedom but not serializability

B+ Trees are considered BALANCED because

•  (A)  the lengths of the paths from the root to all leaf nodes are all equal.
•  (B)  the lengths of the paths from the root to all leaf nodes differ from each other by at most 1.
•  (C)  the number of children of any two non-leaf sibling nodes differ by at most 1.
•  (D)  the number of records in any two leaf nodes differ by at most 1

Suppose a database schedule S involves transactions $T_1,. . . , T_n$. 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?

•  (A)  Topological order
•  (B)  Depth-first order
•  (C)  Breadth-first order
•  (D)  Ascending order of transaction indices

Consider the following database schedule with two transactions, T1 and T2.

                                           S = $r_2$(X); $r_1$(X); $r_2$(Y); $w_1$(X); $r_1$(Y); $w_2$(X); $a_1$; $a_2$

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?

•  (A)  S is non-recoverable
•  (B)  S is recoverable, but has a cascading abort
•  (C)  S does not have a cascading abort
•  (D)  S is strict

Consider the following database table named water_schemes :

SELECT operation in SQL is equivalent to

•  (A) the selection operation in relational algebra
•  (B) the selection operation in relational algebra, except that SELECT in SQL retains duplicates
•  (C) the projection operation in relational algebra
•  (D) the projection operation in relational algebra, except that SELECT in SQL retains duplicates

A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called

•  (A) Dense
•  (B) Sparse
•  (C) Clustered
•  (D) Unclustered

Consider an Entity-Relationship(ER) model in which entity sets E₁ and E₂ are connected by an m:n relationship R₁₂. E₁ and E₃ are connected by a 1: n (1 on the side of E₁ and n on the side of E₃) relationship R₁₃.

E₁ has two single-valued attributes a₁₁ and a₁₂ of which a₁₁ is the key attribute. E₂ has two single-valued attributes a₂₁ and a₂₂ of which is the key attribute. E₃ has two single-valued attributes a₃₁ and a₃₂ of which a₃₁ is the key attribute. The relationships do not have any attributes.

If a relational model is the derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is _______.

Consider the following relations:

Consider the following transaction involving two bank accounts x and y.
read(x); x : = x–50; write(x); read(y); y:=y+50; write(y)
The constraint that the sum of the accounts x and y should remain constant is that of

•  (A) Atomicity
•  (B) Consistency
•  (C) Isolation
•  (D) Durability

With reference to the B+ tree index of order 1 shown below, the minimum number of nodes (including the Root node) that must be fetched in order to satisfy the following query: “Get all records a search key greater than or equal to 7 and less than 15” is ____.

Consider a simple checkpointing protocol and the following set of operations in the log.
(start, T4); (write, T4, y, 2, 3); (start, T1); (commit, T4); (write, T1, z, 5, 7);
(checkpoint);
(start, T2); (write, T2, x, 1, 9); (commit, T2); (start, T3), (write, T3, z, 7, 2);
If a crash happens now the system tries to recover using both undo and redo operations, what are the contents of the undo list and the redo list?

•  (A) Undo: T3, T1; Redo: T2
•  (B) Undo: T3, T1; Redo: T2, T4
•  (C) Undo: none; Redo; T2, T4, T3, T1
•  (D) Undo: T3, T1, T4; Redo: T2

Consider two relations R₁(A,B) with the tuples(1, 5), (3, 7) and R₂(A, C) = (1, 7), (4, 9). Assume that R(A,B,C) is the full natural outer join of R₁ and R₂. Consider the following tuples of the form (A, B, C): a =(1, 5, null), b =(1, null, 7), c = (3, null, 9), d = (4, 7, null), e = (1, 5, 7), f = (3, 7, null), g = (4, null, 9). Which one of the following statements is correct?

•  (A) R contains a, b, e, f, g but not c, d.
•  (B) R contains all of a, b, c, d, e, f, g.
•  (C) R contains e, f, g but not a, b.
•  (D) R contains e but not f,g.

Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies

F = {
          {P, R} → {S, T},
          {P, S, U} → {Q, R}
      }

Which of the following is the trivial functional dependency in F⁺, where F⁺ is closure of F?

•  (A) {P, R} → {S, T}
•  (B) {P, R} → {R, T}
•  (C) {P, S} → {S}
•  (D) {P, S, U} → {Q}

Consider the following relation
        Cinema (theater, address, capacity)
Which of the following options will be needed at the end of the SQL query
        SELECT P1. address
        FROM Cinema P1
Such that it always finds the addresses of theaters with maximum capacity?

•  (A) WHERE P1. capacity >= All (select P2.capacity from Cinema P2)
•  (B)  WHERE P1. capacity >= Any (select P2. capacity from Cinema P2)
•  (C) WHERE P1. capacity > All (select max(P2. capacity) form Cinema P2)
•  (D) WHERE P1. capacity > Any (select max (P2. capacity) from Cinema P2)

Consider a B+ tree in which the search key is 12 bytes long, block size is 1024 bytes, record pointer is 10 bytes long and block pointer is 8 bytes long. The maximum number of keys that can be accommodated in each non – leaf node of the tree is ______.

Consider the following partial Schedule S involving two transactions T1and T2. Only the read and the write operations have been shown. The read operation on data item P is denoted by read (P) and the write operation on data item P is denoted by write (P).

Suppose that the transaction T1 fails immediately after time instance 9. Which one of the following statements is correct?

•  (A) T2 must be aborted and then both T1 and T2 must be re – started to ensure transaction atomicity
•  (B) Schedule S is non – recoverable and cannot ensure transaction atomicity
•  (C) Only T2 must be aborted and then re – started to ensure transaction atomicity
•  (D) Schedule S is recoverable and can ensure atomicity and nothing else needs to be done

Consider the relation scheme R = (E,F, G, H, I, J, K, L, M, N) and the set of functional dependencies {{E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, {K} → {M}, {L} → {N}} on R. What is the key for R ?

•  (A) {E,F}
•  (B) {E,F,H}
•  (C) {E,F,H,K,L}
•  (D) {E}

Given the following statements:

S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL.

S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.
CREATE TABLE S (

      a INTEGER,
      d INTEGER,
      e INTEGER,
      PRIMARY KEY (d),
      FOREIGN KEY (a) references R)

Which one of the following statements is CORRECT?

•  (A) S1 is TRUE and S2 is FALSE.
•  (B) Both S1 and S2 are TRUE.
•  (C) S1 is FALSE and S2 is TRUE.
•  (D) Both S1 and S2 are FALSE.

Consider the following four schedules due to three transactions (indicated by the subscript) using read and write on a data item x, denoted by r(x) and w(x) respectively. Which one of them is conflict serializable?

•  (A) r₁(x); r₂(x); w₁(x); r₃(x); w₂(x)
•  (B) r₂(x);r₁(x);w₂(x);r₃(x);w₁(x)
•  (C) r₃(x);r₂(x);r₁(x);w₂(x);w₁(x)
•  (D) r₂(x);w₂(x);r₃(x);r₁(x);w₁(x)

Given the following two statements:
S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF.
S2: AB→C, D→E, E→C is a minimal cover for the set of functional dependencies AB→C, D→E, AB→E, E→C.
Which one of the following is CORRECT?

•  (A) S1 is TRUE and S2 is FALSE.
•  (B) Both S1 and S2 are TRUE.
•  (C) S1 is FALSE and S2 is TRUE.
•  (D) Both S1 and S2 are FALSE.

Given the following schema:
                 employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
                 departments(dept-id, dept-name, manager-id, location-id)
You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:
                 SQL>SELECT last-name, hire-date
                                FROM employees
                                WHERE (dept-id, hire-date) IN
                                (SELECT dept-id, MAX(hire-date)
                                FROM employees JOIN departments USING(dept-id)
                                WHERE location-id = 1700
                                GROUP BY dept-id);

What is the outcome?

•  (A) It executes but does not give the correct result.
•  (B) It executes and gives the correct result.
•  (C) It generates an error because of pairwise comparison.
•  (D) It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.

The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is _____.

Given an instance of the STUDENTS relation as shown below:

For (StudentName,StudentAge) to be a key for this instance,the value X should NOT be equal to ________.

Consider the following schedule S of transactions T1, T2, T3, T4:

Which one of the following statements is CORRECT?

•  (A) S is conflict-serializable but not recoverable
•  (B) S is not conflict-serializable but is recoverable
•  (C) S is both conflict-serializable and recoverable
•  (D) S is neither conflict-serializable nor is it recoverable

Consider a join (relation algebra) between relations r(R) and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size (r(R))<size(s(S)), the join will have fewer number of disk block accesses if

•  (A) relation r(R) is in the outer loop.
•  (B) relation s(S) is in the outer loop.
•  (C) join selection factor between r(R) and s(S) is more than 0.5
•  (D) join selection factor between r(R) and s(S) is less than 0.5.

SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:

select * from R where a in (select S.a from S)

•  (A) select R.* from R, S where R.a=S.a
•  (B) select distinct R.* from R,S where R.a=S.a
•  (C) select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
•  (D) select R.* from R,S where R.a=S.a and is unique R

What is the optimized version of the relation algebra expression ${\pi }_{A1}\left({\mathrm{\pi }}_{A2}\left({\mathrm{\sigma }}_{F1}\left({\mathrm{\sigma }}_{F2}\left(r\right)\right)\right)\right),$ where A1, A2 are sets of attributes in r with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?

•  (A) ${\pi }_{A1}\left({\mathrm{\sigma }}_{\left(F1\Lambda F2\right)}\left(r\right)\right)$
•  (B) ${\pi }_{A1}\left({\mathrm{\sigma }}_{\left(F1\vee F2\right)}\left(r\right)\right)$
•  (C) ${\pi }_{A2}\left({\mathrm{\sigma }}_{\left(F1\wedge F2\right)}\left(r\right)\right)$
•  (D) ${\pi }_{A2}\left({\mathrm{\sigma }}_{\left(F1\vee F2\right)}\left(r\right)\right)$

A prime attribute of a relation scheme R is an attribute that appears

•  (A) in all candidate keys of R.
•  (B) in some candidate key of R.
•  (C) in a foreign key of R.
•  (D) only in the primary key of R.

Consider the transactions T1, T2, and T3 and the schedules S1 and S2 given below.

T1: r1(X); r1(Z); w1(X); w1(Z)
T2: r2(Y); r2(Z); w2(Z)
T3: r3(Y); r3(X); w3(Y)
S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)
S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)

Which one of the following statements about the schedules is TRUE?

•  (A) Only S1 is conflict-serializable.
•  (B) Only S2 is conflict-serializable.
•  (C) Both S1 and S2 are conflict-serializable.
•  (D) Neither S1 nor S2 is conflict-serializable.

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge)
Consider the following relational algebra query:

$\style{font-family:'Courier New'}{{\mathrm\pi}_\mathrm{empId}\left(\mathrm{employee}\right)-{\mathrm\pi}_\mathrm{empId}\left(\mathrm{employee}\bowtie_{\left(\mathrm{empId}=\mathrm{eID}\right)\wedge\left(\mathrm{empAge}\leq\mathrm{depAge}\right)}\mathrm{Dependent}\right)}$

The above query evaluates to the set of empIds of employees whose age is greater than that of

•  (A) some dependent.
•  (B) all dependents.
•  (C) some of his/her dependents.
•  (D) all of his/her dependents.

Consider the following relational schema:

employee(empId,empName,empDept)
customer(custId,custName,salesRepId,rating)
salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
FROM employee E
WHERE NOT EXISTS (SELECT custId
                  FROM customer C
                  WHERE C.salesRepId = E.empId
                  AND C.rating <> ’GOOD’);

•  (A) Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
•  (B) Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
•  (C) Names of all the employees with none of their customers having a ‘GOOD’ rating.
•  (D) Names of all the employees with all their customers having a ‘GOOD’ rating.

An index is clustered, if

•  (A) it is on a set of fields that form a candidate key.
•  (B) it is on a set of fields that include the primary key.
•  (C) the data records of the file are organized in the same order as the data entries of the index.
•  (D) the data records of the file are organized not in the same order as the data entries of the index.

Consider the following relational schema.
Students(rollno: integer, sname: string)
Courses(courseno: integer, cname: string)
Registration(rollno: integer, courseno: integer, percent: real)
Which of the following queries are equivalent to this query in English?
“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I) SELECT DISTINCT S.sname
FROM Students as S, Registration as R
WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent >90

(II) $\prod_\mathrm{sname}({\mathrm\sigma}_{\mathrm{courseno}=107\;\wedge\;\mathrm{percent}>90}(\mathrm{Registration}\;\bowtie\;\mathrm{Students})$

(III) {T | ∃S$\in$ Students, ∃ R$\in$ Registration ( S.rollno=R.rollno ∧ R.courseno=107 ∧ R.percent>90 ∧T.sname=S.sname)}

(IV) {<S_N> | ∃S_R ∃R_P ( <S_R, S_N>$\in$ Students ∧ <S_R, 107, R_P>$\in$ Registration ∧ R_P>90)}

•  (A) I, II, III and IV
•  (B) I, II and III only
•  (C) I, II and IV only
•  (D) II, III and IV only

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F={CH→G, A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F⁺ is exactly the set of FDs that hold for R.
How many candidate keys does the relation R have?

•  (A) 3
•  (B) 4
•  (C) 5
•  (D) 6

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F={CH→G, A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F⁺ is exactly the set of FDs that hold for R.

The relation R is

•  (A) in 1NF, but not in 2NF.
•  (B) in 2NF, but not in 3NF.
•  (C) in 3NF, but not in BCNF.
•  (D) in BCNF.

Which of the following is TRUE?

•  (A) Every relation in 3NF is also in BCNF
•  (B) A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
•  (C) Every relation in BCNF is also in 3NF
•  (D) No relation can be in both BCNF and 3NF

Given the basic ER and relational models, which of the following is INCORRECT?

•  (A) An attribute of an entity can have more than one value
•  (B) An attribute of an entity can be composite
•  (C) In a row of a relational table, an attribute can have more than one value
•  (D) In a row of a relational table, an attribute can have exactly one value or a NULL value

Which of the following statements are TRUE about an SQL query?

P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause
Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause
R : All attributes used in the GROUP BY clause must appear in the SELECT clause
S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

•  (A) P and R
•  (B) P and S
•  (C) Q and R
•  (D) Q and S

Consider the following transactions with data items P and Q initialized to zero:

T₁ :read (P);
      read (Q);
      if P = 0 then Q := Q + 1 ;
      write (Q).

T₂ : read (Q);
    read (P);
    if Q = 0 then P := P + 1 ;
    write (P).

Any non-serial interleaving of T₁ and T₂ for concurrent execution leads to

•  (A) a serializable schedule
•  (B) a schedule that is not conflict serializable
•  (C) a conflict serializable schedule
•  (D) a schedule for which a precedence graph cannot be drawn

Suppose R₁(A, B) and R₂(C, D) are two relation schemas. Let r₁ and r₂ be the corresponding relation instances. B is a foreign key that refers to C in R₂. If data in r₁ and r₂ satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

•  (A) ${\Pi }_B\left(r_1\right)-{\Pi }_C\left(r_2\right)=\varnothing$
•  (B) ${\Pi }_C\left(r_2\right)-{\Pi }_B\left(r_1\right)=\varnothing$
•  (C) ${\Pi }_B\left(r_1\right)={\Pi }_C\left(r_2\right)$
•  (D) ${\Pi }_B\left(r_1\right)-{\Pi }_C\left(r_2\right)\ne \varnothing$

Consider the following relations A, B and C:

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A$\cup$B is the same as that of A.

$\style{font-family:Verdana}{\left(\mathrm A\cup\mathrm B\right)\bowtie_{\mathrm A.\mathrm{Id}>40\vee\mathrm C.\mathrm{Id}<15}\mathrm C}$

•  (A) 7
•  (B) 4
•  (C) 5
•  (D) 9

Consider the following relations A, B and C:

How many tuples does the result of the following SQL query contain?

SELECT A.Id
FROM A
WHERE A.Age > ALL (SELECT B.Age
                   FROM B
                   WHERE B.Name = ‘Arun’)

•  (A) 4
•  (B) 3
•  (C) 0
•  (D) 1

Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Num: Unique registration number of each registered student

2. UID: Unique identity number, unique at the national level for each citizen

3. BdnkAccount_Num: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attribute stores the primary account number.

4. Name: Name of the student

 5. Hostel_Room: Room number of the hostel

Which of the following options is INCORRECT?

•  (A) BankAccount_Num is a candidate key
•  (B) Registration_Num can be a primary key
•  (C) UID is a candidate key if all students are from the same country
•  (D) If S is a superkey such that $S\cap UID$ is NULL then $S\cup UID$ is also a superkey

Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table.

Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change

What will be the output of the following SQL query after the steps mentioned above are carried out?

SELECT Y FROM T WHERE X=7;

•  (A) 127
•  (B) 255
•  (C) 129
•  (D) 257

Consider a relational table r with sufficient number of records, having attributes A₁, A₂,……A_n and

let 1 ≤ p ≤ n. Two queries Q1 and Q2 are given below.

Q1: ${\pi }_{ A_{1......}{A}_p}\left({\sigma }_{A_{p=c}}\left(r\right)\right)$where c is a constant

Q2: ${\pi }_{ A_{1......}{A}_p}\left({\sigma }_{ c_1\le A_p\le c_2}\left(r\right)\right)$ where c₁ and c₂ are constants

The database can be configured to do ordered indexing on A_p or hashing on A_p. Which of the following statements is TRUE?

•  (A) Ordered indexing will always outperform hashing for both queries
•  (B) Hashing will always outperform ordered indexing for both queries
•  (C) Hashing will outperform ordered indexing on Q1, but not on Q2
•  (D) Hashing will outperform ordered indexing on Q2, but not on Q1

Database table by name Loan_Records is given below.

What is the output of the following SQL query?

SELECT count(*)
FROM (
            (SELECT Borrower,Bank_Manager FROM Loan Records)AS S
             NATURAL JOIN
            (SELECT Bank_Manager,Loan_Amount FROM Loan Records)AS T
);

•  (A) 3
•  (B) 9
•  (C) 5
•  (D) 6

Consider a B⁺-tree in which the maximum number of keys in a node is 5. What is the minimum number of keys in any non-root node?

•  (A) 1
•  (B) 2
•  (C) 3
•  (D) 4

A relational schema for a train reservation database is given below

Passenger (pid, pname, age)
Reservation (pid, cass, tid)

What pids are returned by the following SQL query for the above instance of the tables?

SELECT pid
FROM Reservation
WHERE class = 'AC' AND
      EXISTS (SELECT *
              FROM Passenger
              WHERE age 65 AND
              Passenger.pid Reservation.pid)

•  (A) 1, 0
•  (B) 1, 2
•  (C) 1, 3
•  (D) 1, 5

Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock?

I. 2-phase locking
II. Time-stamp ordering

•  (A) I only
•  (B) IIonly
•  (C) BothI and II
•  (D) Neither I norII

Consider the following schedule for transactions T1, T2 and T3:

Which one of the schedules below is the correct serialization of the above?

•  (A) T1 → T3 → T2
•  (B) T2 → T1 → T3
•  (C) T2 → T3 → T1
•  (D) T3 → T1 → T2

The following functional dependencies hold for relations    R   (  A   ,  B ,   C ) and  S  ( B  , D,  E)  
B → A,
A → C
The relation R contains 200 tuples and the relation S contains 100 tuples. What is the maximum number of tuples possible in the natural join R $\bowtie$ S ?

•  (A) 100
•  (B) 200
•  (C) 300
•  (D) 2000

Consider two transactions T₁ and T₂, and four schedules S₁, S₂, S₃, S₄ of T₁ and T₂ as given below:

T₁ : R₁ [x] W₁ [x] W₁ [y]
T₂ : R₂ [x] R₂ [y] W₂ [y]
S₁ : R₁ [x] R₂ [x] R₂ [y] W₁ [x] W₁ [y] W₂ [y]
S₂ : R₁ [x] R₂ [x] R₂ [y] W₁ [x] W₂ [y] W₁ [y]
S₃ : R₁ [x] W₁ [x] R₂ [x] W₁ [y] R₂ [y] W₂ [y]
S₄ : R₂ [x] R₂ [y] R₁ [x] W₁ [x] W₁ [y] W₂ [y]

Which of the above schedules are conflict-serializable?

•  (A) S₁ and S₂
•  (B) S₂ and S₃
•  (C) S₃ only
•  (D) S₄ only

The following key values are inserted into a B+ - tree in which order of the internal nodes is 3, and that of the leaf nodes is 2, in the sequence given below. The order of internal nodes is the maximum number of tree pointers in each node, and the order of leaf nodes is the maximum number of data items that can be stored in it. The B+ - tree is initially empty.

10, 3, 6, 8, 4, 2, 1

The maximum number of times leaf nodes would get split up as a result of these insertions is

•  (A) 2
•  (B) 3
•  (C) 4
•  (D) 5

Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database:

I.  ${\pi }_{R-S}\left(r\right)-{\pi }_{R-S}\left({\pi }_{R-S}\left(r\right)\times S-{\pi }_{R-S,S}\left(r\right)\right)$
II. $\left\{t|t\in {\pi }_{R-S}\left(r\right)\wedge \forall u\in s\left(\exists v\in r\left(u=v\left[s\right]\wedge t=v\left[R-S\right]\right)\right)\right\}$
III. $\left\{t|t\in {\pi }_{R-S}\left(r\right)\wedge \forall v\in r\left(\exists u\in s\left(u=v\left[s\right]\wedge t=v\left[R-S\right]\right)\right)\right\}$
IV. Select R.a, R.b
     from R, S
     where R.c = S.c

Which of the above queries are equivalent?

•  (A) I and II
•  (B) I and III
•  (C) II and IV
•  (D) III and IV

Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

Consider the following relational query on the above database:

SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C.sid
                                FROM Catalog C
                                WHERE C.pid NOT (SELECT P.pid
                                                            FROM Parts P
                                                            WHERE P.color<> 'blue'))

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

•  (A) Find the names of all suppliers who have supplied a non-blue part.
•  (B) Find the names of all suppliers who have not supplied a non-blue part.
•  (C) Find the names of all suppliers who have supplied only blue parts.
•  (D) Find the names of all suppliers who have not supplied only blue parts.

Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?

•  (A) The schema is in BCNF.
•  (B) The schema is in 3NF but not in BCNF.
•  (C) The schema is in 2NF but not in 3NF.
•  (D) The schema is not in 2NF.