Database-Management-System
Question 1 |
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 _________.
A | 52 |
B | 53 |
C | 54 |
D | 55 |
Block size = 512 bytes
Block pointer = 2 bytes
Search key = 8 bytes
Let Maximum order of B+ tree = n
n(Pb) + (n – 1)(k) ≤ 512
n(2) + (n – 1)(8) ≤ 512
2n + 8n – 8 ≤ 512
10n ≤ 520
n ≤ 520/10
(n = 52)
Question 2 |
Which one of the following statements is NOT correct about the B+ tree data structure used for creating an index of a relational database table?
A | Each leaf node has a pointer to the next leaf node |
B | Non-leaf nodes have pointers to data records |
C | B+ Tree is a height-balanced tree |
D | Key values in each node are kept in sorted order |
In B+ trees non-leaf nodes do not have pointers to data records.
Question 3 |
I. Strict two-phase locking protocol generates conflict serializable schedules that are also recoverable.
II. Timestamp-ordering concurrency control protocol with Thomas Write Rule can generate view serializable schedules that are not conflict serializable.
Which of the above statements is/are TRUE?
A | Both I and II |
B | Neither I nor II |
C | II only |
D | I only |
In strict 2PL, a transaction T does not release any of its exclusive (write) locks until after it commits or aborts.
Hence, no other transaction can read or write an item that is written by T unless T has committed, leading to a strict schedule for recoverability.
(Ref: Fundamentals of Database Systems by Elmasri and Navathe, 7e Pg. No. 789)
By ignoring the write, Thomas write rule allows schedules that are not conflict serializable but are nevertheless correct.
Those non-conflict-serializable schedules allowed satisfy the definition of view serializable schedules.
(Ref: Database System Concepts by Silberschatch, Korth and Sudarshan, 6e Pg No. 686)
Question 4 |

How many tuples will be returned by the following relational algebra query?
∏x(P.Y=R.Y ∧ R.V=V2)(P × R)) – ∏x(Q.Y=R.Y ∧ Q.T>2)(Q × R))
A | 0 |
B | 1 |
C | 2 |
D | 3 |

∏x(σ(P.Y = R.Y ∧ R.V = V2)(P × R))

σ(Q.Y = R.Y ∧ Q.T>2)(Q × R)


∏x(σ(P.Y = R.Y ∧ R.V = V2)(P × R)) – ∏x(σ(Q.Y = R.Y ∧ Q.T>2)(Q × R))

Question 5 |
A relational database contains two tables Student and Performance as shown below:

The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together from the primary key. Consider the SQL query given below:
SELECT S.Student_name, sum (P.Marks) FROM Student S, Performance P WHERE P.Marks > 84 GROUP BY S.Student_name;
The number of rows returned by the above SQL query is _____.
A | 0 |
B | 9 |
C | 7 |
D | 5 |
SQL> SELECT S.Student_name,sum(P.Marks)
2 FROM Student S,Performance P
3 WHERE P.Marks>84
4 GROUP BY S.Student_name;

Question 6 |
Let the set of functional dependencies F = {QR → S, R → P, S → Q} hold on a relation schema X = (PQRS). X is not in BCNF. Suppose X is decomposed into two schemas Y and Z, where Y = (PR) and Z = (QRS).
Consider the two statements given below.
-
- I. Both Y and Z are in BCNF
- II. Decomposition of X into Y and Z is dependency preserving and lossless
Which of the above statements is/are correct?
A | I only |
B | Neither I nor II |
C | II only |
D | Both I and II |
R → P
R+ = RP
* In R → P, ‘R’ is a super key. So, Y is in BCNF.
Z = (QRS)
QR → S
S → Q
CK’s = QR, RS
* In, S → Q, ‘S’ is not a super key. So, Z is not in BCNF.
* Y is in BCNF and Z is not in BCNF.
* ‘R’ is common attribute in the relations Y and Z. and R is candidate key for Y. So, the decomposition is lossless.
* The FD, R → P is applicable on Y and QR → S, S → Q are applicablein 2.
So, the decomposition is dependency preserving.
* Hence, Statement II is correct.
Question 7 |
In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in R and that the cardinality of E1 is greater than the cardinality of E2.
Which one of the following is true about R?
A | Every entity in E1 is associated with exactly one entity in E2. |
B | Some entity in E1 is associated with more than one entity in E2. |
C | Every entity in E2 is associated with exactly one entity in E1. |
D | Every entity in E2 is associated with at most one entity in E1.
|

The M : 1 relationship holds between two entities E1 and E2, in which each tuple from E2 is in relation with many tuples of E1. One tuple from E1 is in relation with only one tuple of E2. It is given that participation from both the sides is total and the cardinality of E1 is greater than E2.

Therefore, every entity E1 is associated with exactly one entity in E2.
Question 8 |
Consider the following two tables and four queries in SQL.
Book (isbn, bname), Stock (isbn, copies)Query 1: SELECT B.isbn, S.copies FROM Book B INNER JOIN Stock S ON B.isbn = S.isbn; Query 2: SELECT B.isbn, S.copies FROM Book B LEFT OUTER JOIN Stock S ON B.isbn = S.isbn; Query 3: SELECT B.isbn, S.copies FROM Book B RIGHT OUTER JOIN Stock S ON B.isbn = S.isbn; Query 4: SELECT B.isbn, S.copies FROM Book B FULL OUTER JOIN Stock S ON B.isbn = S.isbn;
Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?
A | Query 1 |
B | Query 2 |
C | Query 3 |
D | Query 4 |
Book (isbn, bname)
Stock (isbn, copies)
isbn is a primary key of Book and isbn is a foreign key of stock referring to Book table.
For example:

Query 1:
INNER JOIN keyword selects records that have matching values in both tables (Book and Stock).

So, the result of Query 1 is,

Query 2:
The LEFT OUTER JOIN keyword returns all records from the left table (Book) and the matched records from the right table (Stock).
The result is NULL from the right side, if there is no match.

So, the result of Query 2 is,

Query 3:
The RIGHT OUTER JOIN keyword returns all records from the right table (Stock), and the matched records from the left table(BOOK).
The result is NULL from the left side, when there is no match.


Query 4:
The FULL OUTER JOIN keyword return all records when there is a match in either left (Book) or right (Stock) table records.

So, the result of Query 4 is,

Therefore, from the result of above four queries, a superset of the outputs of the Query 1, Query 2 and Query 3 is Query 4.
Note:
If we take isbn as a primary key in both the tables Book and Stock and foreign key, in one of the tables then also will get option (D) as the answer.
Question 9 |
Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.
Schema I: Registration(rollno, courses) Field ‘courses’ is a set-valued attribute containing the set of courses a student has registered for. Non-trivial functional dependency rollno → courses Schema II: Registration (rollno, coursid, email) Non-trivial functional dependencies: rollno, courseid → email email → rollno Schema III: Registration (rollno, courseid, marks, grade) Non-trivial functional dependencies: rollno, courseid, → marks, grade marks → grade Schema IV: Registration (rollno, courseid, credit) Non-trivial functional dependencies: rollno, courseid → credit courseid → credit
Which one of the relational schemas above is in 3NF but not in BCNF?
A | Schema I |
B | Schema II |
C | Schema III |
D | Schema IV |
Registration (rollno, courses) rollno → courses
For the given schema Registration ‘rollno’ is a primary key.
Left-side of the functional dependency is a superkey so, Registration is in BCNF.
Schema II:
Registrstion (rollno, courseid, email)
rollno, courseid → email
email → rollno
From the given schema the candidate key is (rollno + courseid).
There is no part of the key in the left hand of the FD’s so, it is in 2NF.
In the FD email→rollno, email is non-prime attribute but rollno is a prime attribute.
So, it is not a transitive dependency.
No transitive dependencies so, the schema is in 3NF.
But in the second FD email→rollno, email is not a superkey.
So, it is violating BCNF.
Hence, the schema Registration is in 3NF but not in BCNF.
Schema III:
Registration (rollno, courseid, marks, grade)
rollno, courseid → marks, grade
marks → grade
For the schema the candidate key is (rollno + courseid).
There are no part of the keys are determining non-prime attributes.
So, the schema is in 2NF.
In the FD marks → grade, both the attributes marks and grade are non-prime.
So, it is a transitive dependency.
The FD is violating 3NF.
The schema Registration is in 2NF but not in 3NF.
Schema IV:
Registration (rollno, courseid, credit)
rollno, courseid → credit
courseid → credit
The candidate key is (rollno + courseid).
In the FD, courseid → credit, courseid is part of the key (prime attribute) and credit is non-prime.
So, it is a partial dependency.
The schema is violating 2NF.
Question 10 |
Q: r⋈(σ<B<5 (s))
Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.
Which one of the following is NOT equivalent to Q?
A | σB<5 (r ⨝ s) |
B | σB<5 (r LOJ s)
|
C | r LOJ (σB<5(s))
|
D | σB<5(r) LOJ s |
Consider the following tables without NULL values.

Q: r⨝(σB<5(S))
The result of σB<5(S) is

The result of σB<5(S) is

Option (A):
The result of r⨝S is

The result of σB<5(r⨝S) is

Option (B):
The result of r LOJ S is

The result of σB<5(r LOJ S) is

Option (C):
The result of σB<5(S) is

Now, the result of r LOJ(σB<5(S))

Option (D):
The result of σB<5(r) is

Now, the result of σB<5(r) LOJ S is

Therefore, from the output of above four options, the results of options, the results of options (A), (B) and (D) are equivalent to Q.
Question 11 |
V → W
VW → X
Y → VX
Y → Z
Which of the following is irreducible equivalent for this set of functional dependencies?
A | V→W V→X Y→V Y→Z |
B | V→W W→X Y→V Y→Z |
C | V→W V→X Y→V Y→X Y→Z |
D | V→W W→X Y→V Y→X Y→Z |
V → W, VW → X, Y → V, Y → X, Y→ Z
Step 2:
V → W, VW → X, Y → V, Y → X, Y→ Z
(V)+ = V ×
(VW)+ = VW ×
(Y)+ = YXZ
(Y)+ = YVW ×
(Y)+ = YVWX
Without Y → X, the closure of Y is deriving ‘X’ from the remaining attributes.
So, we can remove Y → X as its redundant.
Step 3:
V → W, VW → X, Y → V, Y → Z
(V)+ = VW, the closure of V is deriving W from the remaining FD’s.
So, W is redundant. We can remove it.
So, the final canonical form is
V→W, V→X, Y→V, Y→Z
⇾ So, option (A) is correct.
Question 12 |
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 given below.
The output of executing the SQL query is ___________.
A | 2.6 |
B | 2.7 |
C | 2.8 |
D | 2.9 |

⇾ We start evaluating from the inner query.
The inner query forms DeptName wise groups and counts the DeptName wise EmpIds.
⇾ In inner query DeptName, Count(EmpId) is the alias name DeptName, Num.
So, the output of the inner query is,

The outer query will find the
Avg(Num) = (4+3+3+2+1)/5 = 2.6
Question 13 |
(I) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∀v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(II) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(III) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] = v[DeptId]))}
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 (III) |
(I) Gives EmpNames who do not belong to any Department. So, it is going to be a finite number of tuples as a result.
(II) Gives EmpNames who do not belong to some Department. This is also going to have finite number of tuples.
(III) Gives EmpNames who do not belong to same Department. This one will also give finite number of tuples.
All the expressions I, II and III are giving finite number of tuples. So, all are safe.
Question 14 |
if TS(T2)<TS(T1) then
T1 is killed
else T2 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 deadlocks?
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 database system is neither deadlock-free nor starvation-free. |
So, there will be no equal timestamps.
Lamport’s logical clock:
In this timestamps are assigned in increasing order only.
According to the given algorithm,
TS(T2) < TS(T1)
then T1 is killed
else
T2 will wait
So, in both the cases, it will be deadlock free and there will be no starvation.
Question 15 |
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.
T1 ← πCourseName(σStudentName='SA'(CR)) T2 ← CR ÷ T1
The number of rows in T2 is ____________.
A | 4 |
B | 5 |
C | 6 |
D | 7 |
The σStudentName = ‘SA’(CR) will produce the following

⇾ The result of T1 ← πCourseName(σStudentName=’SA’(CR)) is

(2) T2 ← CR÷T1
⇾ We see that SA is enrolled for CA, CB and CC.
⇾ T2 will give the StudentNames those who have enrolled for all the CA, C, CC courses. So, the following Students are enrolled for the given 3 courses.

⇾ So, the output of T2 will have 4 rows.
Question 16 |
An ER model of a database consists of entity types A and B. These are connected by a relationship R which does not have its own attribute. Under which one of the following conditions, 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 one-to-many and the participation of A in R is partial. |

The relational table for R be merged that of A, if the relationship R is Many-to-one and the participation of A in R is total.
Question 17 |
Consider 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 cascade and on-update cascade. In table T2, R is the primary key and S is the foreign key referencing 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 _________.
A | 0 |
B | 1 |
C | 2 |
D | 3 |

Therefore, no. of additional records deleted from the table T1 is 0 (zero).
Question 18 |
T1: r1(X)w1(X)r1(Y)w1(Y)
T2: r2(Y)w2(Y)r2(Z)w2(Z)
where ri(V) denotes a read operation by transaction Ti on a variable V and wi(V) denotes a write operation by transaction Ti on a variable V. The total number of conflict serializable schedules that can be formed by T1 and T2 is ____________.
A | 54 |
B | 55 |
C | 56 |
D | 57 |
= 8!/(4×3×2×4×3×2)
= (8×7×6×5×4×3×2×1)/(4×3×2×4×3×2)
= 70
Following two conflict actions are possible:


∴# Permutations = 4 × 3 = 12

#permutations = 4 × 1 = 4
∴ Total no. of conflict serial schedules possible = 70 – 12 – 4 = 54
Question 19 |
Consider the following database table named top_scorer.

SELECT ta.player FROM top_scorer AS ta WHERE ta.goals > ALL (SELECT tb.goals FROM top_scorer AS tb WHERE tb.country = 'Spain') AND ta.goals > ANY (SELECT tc.goals FROM top_scorer AS tc WHERE tc.country = 'Germany')
The number of tuples returned by the above SQL query is _____.
A | 7 |
B | 8 |
C | 9 |
D | 10 |

In the given database table top_scorer no players are there from ‘Spain’.
So, the query (1) results 0 and ALL (empty) is always TRUE.
The query (2) selects the goals of the players those who are belongs to ‘Germany’.
So, it results in ANY (16, 14, 11, 10).
So, the outer most query results the player names from top_scorer, who have more goals.
Since, the minimum goal by the ‘Germany’ player is 10, it returns the following 7 rows.

Question 20 |
Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key VY?
A | VXYZ |
B | VWXZ |
C | VWXY |
D | VWXYZ |
Any superset of “VY” is a super key. So, option (B) does not contain “Y”.
Question 21 |
A | Atomicity |
B | Consistency |
C | Isolation |
D | Deadlock-freedom |
So, Deadlock – freedom is not there in the ACID properties.
Question 22 |
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 |
V – VOLUME
N – NUMBER
S – STARTPAGE
E – ENDPAGE
T – TITLE
Y – YEAR
P – PRICE
Primary key: (V, N, S, E)
FD set:
(V, N, S, E) → T
(V, N) → Y
(V, N, S, E) → P
In (V, N) → Y; V, N is a part of the key and Y is non-prime attribute.
So, it is a partial dependency.
Now, the schema “Journal” is in 1NF but not in 2NF.
The database is redesigned as follows:

Both R1 and R2 are in BCNF.
Therefore, 2NF is the weakest normal form that the new database satisfies, but the old one does not.
Question 23 |
Step1. T acquires exclusive locks to O1,…,Ok 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 |
In conservative 2PL protocol, a transaction has to lock all the items before the transaction begins execution.
Advantages of conservative 2PLP:
• No possibility of deadlock.
• Ensure serializability.
The given scenario (Step1, Step2 and Step3) is conservative 2PLP.
Question 24 |
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. |
Question 25 |
Suppose a database schedule S involves transactions T1, …, Tn. 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 |
But BFS and DFS are also possible for cyclic graphs.
And topological sort is not possible for cyclic graph.
Moreover option (D) is also wrong because in a transaction with more indices might come before lower one.
Question 26 |
S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2
where ri(Z) denotes a read operation by transaction Ti on a variable Z, wi(Z) denotes a write operation by Ti on a variable Z and ai denotes an abort by transaction Ti.
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 |

Now let’s check statements one by one,
A) False, because there is no dirty read. So, it is recoverable.
B) False, because there is to dirty read. So, no cascading aborts.
C) True.
D) False, because there is Transaction T2 which written the value of x which is written by T1 before T1 has aborted. So, not strict.
Question 27 |
Consider the following database table named water_schemes :

The number of tuples returned by the following SQL query is _________.
with total(name, capacity) as select district_name, sum(capacity) from water_schemes group by district_name with total_avg(capacity) as select avg(capacity) from total select name from total, total_avg where total.capacity ≥ total_avg.capacity
A | 2 |
B | 3 |
C | 4 |
D | 5 |
The name assigned to the sub-query is treated as though it was an inline view or table.
• First group by district name is performed and total capacities are obtained as following:

• Then average capacity is computed,
Average Capacity = (20 + 40 + 30 + 10)/4
= 100/4
= 25
• Finally, 3rd query will be executed and it’s tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25.
• Then average capacity is computed,
Average Capacity = (20 + 40 + 30 + 10)/4
= 100/4
= 25
• Finally, 3rd query will be executed and it’s tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25.

Question 28 |
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
|
Question 29 |
Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are connected by an m:n relationship R12, E1 and E3 are connected by a 1:n (1 on the side of E1 and n on the side of E3) relationship R13.
E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two single-valued attributes a21 and a22 is the key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any attributes.
If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is ___________.
A | 4 |
B | 6 |
C | 7 |
D | 8 |


Question 30 |
Consider the following relations:

Consider the following SQL query.
SELECT S. Student_Name, sum(P.Marks) FROM Student S, Performance P WHERE S.Roll_No = P.Roll_No GROUP BY S.Student_Name
The number of rows that will be returned by the SQL query is _________.
A | 2 |
B | 3 |
C | 4 |
D | 5 |

Question 31 |
Consider the following transaction involving two bank account 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 |
Question 32 |
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 with a search key grater than or equal to 7 and less than 15″ is ___________.

A | 6 |
B | 7 |
C | 5 |
D | 9 |

Question 33 |
Consider two relations R1(A,B) with the tuples (1,5), (3,7) and R2(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R1 and R2. 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 |

⋆ So, from the above resultant table, R contains e, f, g only but not a, b.
Question 34 |
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 and 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
|
Question 35 |
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+ is closure of F?
A | {P,R}→{S,T} |
B | {P,R}→{R,T} |
C | {P,S}→{S} |
D | {P,S,U}→{Q} |
Question 36 |
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) from Cinema P2) |
D | WHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2) |
So the theaters which are having maximum capacity will satisfy the condition.
Question 37 |
The value of is
A | 0 |
B | 1/2 |
C | 1 |
D | ∞ |

Question 38 |
Suppose Xi for i = 1, 2, 3 are independent and identically distributed random variables whose probability mass functions are Pr[Xi = 0] = Pr[Xi = 1]=1/2 for i = 1, 2, 3. Define another random variable Y = X1X2 ⊕ X3, where ⊕ denotes XOR. Then Pr[Y = 0|X3 = 0] = _______________.
A | 0.75 |
B | 0.76 |
C | 0.77 |
D | 0.78 |

Pr(Y=0/ X3=0) = Pr(Y=0 ∩ X3=0)/ Pr(X3=0)
= 3/8 / 4/8 = 3/4 = 0.75
Question 39 |
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} |
B) (EFH)+ = EFHGIJKLMN, EFH is deriving all the attributes of R. So, EFH is a key.
C) If EFH is a key, then EFHKL will become the super key.
D) (E)+ = E
Question 40 |
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. |
Using a check constraint, we can have the same effect as foreign key while adding elements to the child table. But while deleting elements from the parent table the referential integrity constraint is no longer valid. So, a check constraint cannot replace a foreign key.
S2: False:
Foreign key in one table should be defined as a primary key in other table. In above table definition, table S has a foreign key that refers to field ‘a’ of R. The field ‘a’ in table S is part of the primary key and part of the key cannot be declared as a foreign key.
Question 41 |
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 | r1 (x); r2 (x); w1 (x); r3 (x); w2 (x) |
B | r2 (x);r1 (x);w2 (x);r3 (x);w1 (x) |
C | r3 (x);r2 (x);r1 (x);w2 (x);w1 (x) |
D | r2 (x);w2 (x);r3 (x);r1 (x);w1 (x) |

– Polygraph contains cycle. So, not a conflict serializable.
Option: B

-Cyclic
Option: C

– Cyclic
Option: D

– Acyclic, so conflict serializable.
Question 42 |
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. |
If we can prove the relation is in BCNF then by default it would be in 1NF, 2NF, 3NF also.
Let R(AB) be a two attribute relation, then
If {A→B} exists then BCNF since {A}+ = AB = R
If {B→A} exists then BCNF since {B}+ = AB = R
If {A→B, B→A} exists then BCNF since A and B both are Super Key now.
If {No non-trivial Functional Dependency} then default BCNF.
Hence it’s proved that a Relation with two single-valued attributes is in BCNF hence it’s also in 1NF, 2NF, 3NF.
S2: False
The canonical cover for the given FD set is {AB→C, D→E, AB→E, E→C}. As we can see AB→E is not covered in minimal cover since {AB}+ = ABC in the given cover {AB→C, D→E, E→C}
Question 43 |
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. |
Question 44 |
The maximum number of superkeys for the relation schema R(E, F, G, H) with E as the key is _____.
A | 8 |
B | 9 |
C | 10 |
D | 11 |
Here, n = 4.
So, the possible super keys = 24-1 = 8
The super keys are: E, EH, EG, EF, EGH, EFH, EFG, EFGH.
Question 45 |
Given the STUDENTS relation as shown below.
For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to
A | 19 |
B | 20 |
C | 21 |
D | 22 |
Question 46 |
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 |

In the precedence graph, there are no cycles. So, it is conflict serializable and recoverable also.
Question 47 |
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. |
For each tuple r in R do
For each tuple s in S do
If r and s satisfy the join condition then
output the tuple
The above algorithm involves (nr*bs+br)
block transfer and (nr+br) seeks.
where,
br → no. of blocks in R
bs → no. of blocks in S
hr → no. of tuples in R
To have less block accesses in nr should be less and in question it is given that size(r(R)) < size(s(S)).
To have fewer no. of disk block accesses the relation r(R) should be in outer loop.
Question 48 |
SQL allows 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 |
Question 49 |
What is the optimized version of the relation algebra expression πA1(πA2(σF1(σF2(r)))), where A1, A2 are sets of attributes in with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?
A | πA1 (σ(F1∧F2) (r)) |
B | πA1 (σ(F1∨F2) (r)) |
C | πA2 (σ(F1∧F2) (r)) |
D | πA2 (σ(F1∨F2) (r)) |
Two Selects with Boolean expression can be combined into one select with AND of two Boolean expressions.
Question 50 |
A prime attribute of a relation scheme 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 . |
Ex: AB, BC, CD are candidate keys of R(ABCD). In the FDs set one attribute may not be part of all the FDs.