DatabaseManagementSystem
Question 1 
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?
Each leaf node has a pointer to the next leaf node  
Nonleaf nodes have pointers to data records  
B^{+} Tree is a heightbalanced tree  
Key values in each node are kept in sorted order 
In B+ trees nonleaf nodes do not have pointers to data records.
Question 2 
Consider the following two statements about database transaction schedules:
I. Strict twophase locking protocol generates conflict serializable schedules that are also recoverable. II. Timestampordering 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?
Both I and II  
Neither I nor II  
II only  
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 nonconflictserializable schedules allowed satisfy the definition of view serializable schedules.
(Ref: Database System Concepts by Silberschatch, Korth and Sudarshan, 6e Pg No. 686)
Question 3 
Consider the following relations P(X,Y,Z), Q(X,Y,T) and R(Y,V).
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))
0  
1  
2  
3 
∏_{x}(σ_{(P.Y=R.Y ∧ R.V=V2)}(P × R))
σ_{(Q.Y=R.Y ∧ Q.T>2)}(Q × R)
∏_{x}(σ_{(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 4 
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 _____.
0  
9  
7  
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 5 
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 losslessWhich of the above statements is/are correct?
I only  
Neither I nor II  
II only  
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 6 
In an EntityRelationship (ER) model, suppose R is a manytoone 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?
Every entity in E1 is associated with exactly one entity in E2.
 
Some entity in E1 is associated with more than one entity in E2.
 
Every entity in E2 is associated with exactly one entity in E1.
 
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 7 
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?
Query 1
 
Query 2  
Query 3  
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 8 
Consider the following four relational schemas. For each schema, all nontrivial functional dependencies are listed. The underlined attributes are the respective primary keys.
Schema I: Registration(rollno, courses) Field ‘courses’ is a setvalued attribute containing the set of courses a student has registered for. Nontrivial functional dependency rollno → courses Schema II: Registration (rollno, coursid, email) Nontrivial functional dependencies: rollno, courseid → email email → rollno Schema III: Registration (rollno, courseid, marks, grade) Nontrivial functional dependencies: rollno, courseid, → marks, grade marks → grade Schema IV: Registration (rollno, courseid, credit) Nontrivial functional dependencies: rollno, courseid → credit courseid → credit
Which one of the relational schemas above is in 3NF but not in BCNF?
Schema I  
Schema II  
Schema III  
Schema IV 
Registration (rollno, courses) rollno → courses
For the given schema Registration ‘rollno’ is a primary key.
Leftside 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 nonprime 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 nonprime attributes.
So, the schema is in 2NF.
In the FD marks → grade, both the attributes marks and grade are nonprime.
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 nonprime.
So, it is a partial dependency.
The schema is violating 2NF.
Question 9 
Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query

Q: r⋈(σ_{B<5}(s))
Let LOJ denote the natural left outerjoin operation. Assume that r and s contain no null values.
Which one of the following is NOT equivalent to Q?
σ_{B<5} (r ⨝ s)
 
σ_{B<5} (r LOJ s)
 
r LOJ (σ_{B<5}(s))
 
σ_{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 10 
The following functional dependencies hold true for the relational schema {V, W, X, Y, Z} :

V → W
VW → X
Y → VX
Y → Z
Which of the following is irreducible equivalent for this set of functional dependencies?
V→W V→X Y→V Y→Z  
V→W W→X Y→V Y→Z  
V→W V→X Y→V Y→X Y→Z  
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 11 
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 ___________.
2.6  
2.7  
2.8  
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 12 
Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId), and DEPT(DeptName, DeptId). Note that the DeptId can be permitted to a NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

(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?
(I) and (II) only  
(I) and (III) only  
(II) and (III) only  
(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 13 
In a database system, unique timestamps are assigned to each transaction using Lamport’s logical clock. Let TS(T_{1}) and TS(T_{2}) be the timestamps of transactions T_{1} and T_{2} respectively. Besides, T_{1} holds a lock on the resource R and T_{2} 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_{2})<TS(T_{1}) then T_{1} is killed else T_{2} 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?
The database system is both deadlockfree and starvationfree.  
The database system is deadlockfree, but not starvationfree.  
The database system is starvationfree, but not deadlockfree.  
The database system is neither deadlockfree nor starvationfree. 
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(T_{2}) < TS(T_{1})
then T_{1} is killed
else
T_{2} will wait
So, in both the cases, it will be deadlock free and there will be no starvation.
Question 14 
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 ____________.
4  
5  
6  
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 15 
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?
Relationship R is onetomany and the participation of A in R is total.  
Relationship R is onetomany and the participation of A in R is partial.  
Relationship R is manytoone and the participation of A in R is total.  
Relationship R is onetomany and the participation of A in R is partial. 
The relational table for R be merged that of A, if the relationship R is Manytoone and the participation of A in R is total.
Question 16 
In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with ondelete cascade and onupdate cascade. In table T2, R is the primary key and S is the foreign key referencing P in table T1 with ondelete set NULL and onupdate 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 _________.
0  
1  
2  
3 
Therefore, no. of additional records deleted from the table T1 is 0 (zero).
Question 17 
Two transactions T_{1} and T_{2} are given as

T_{1}: r_{1}(X)w_{1}(X)r_{1}(Y)w_{1}(Y)
T_{2}: r_{2}(Y)w_{2}(Y)r_{2}(Z)w_{2}(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_{1} and T_{2} is ____________.
54  
55  
56  
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 18 
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 _____.
7  
8  
9  
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 19 
In a B^{+} tree, if the searchkey 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 _________.
52  
53  
54  
55 
Block size = 512 bytes
Block pointer = 2 bytes
Search key = 8 bytes
Let Maximum order of B^{+} tree = n
n(P_{b}) + (n  1)(k) ≤ 512
n(2) + (n  1)(8) ≤ 512
2n + 8n  8 ≤ 512
10n ≤ 520
n ≤ 520/10
(n = 52)
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?
VXYZ  
VWXZ  
VWXY  
VWXYZ 
Any superset of “VY” is a super key. So, option (B) does not contain “Y”.
Question 21 
NOT a part of the ACID properties of database transactions?
Atomicity  
Consistency  
Isolation  
Deadlockfreedom 
So, Deadlock – freedom is not there in the ACID properties.
Question 22 
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 satisﬁes, but the old one does not?
1NF  
2NF  
3NF  
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 nonprime 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 R_{1} and R_{2} are in BCNF.
Therefore, 2NF is the weakest normal form that the new database satisfies, but the old one does not.
Question 23 
Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects{O_{1},...,O_{k}}. This is done in the following manner:

Step1. T acquires exclusive locks to O_{1},...,O_{k} in increasing order of their addresses.
Step2. The required operations are performed.
Step3. All locks are released.
This protocol will
guarantee serializability and deadlockfreedom  
guarantee neither serializability nor deadlockfreedom  
guarantee serializability but not deadlockfreedom  
guarantee deadlockfreedom 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
the lengths of the paths from the root to all leaf nodes are all equal.  
the lengths of the paths from the root to all leaf nodes differ from each other by at most 1.  
the number of children of any two nonleaf sibling nodes differ by at most 1.  
the number of records in any two leaf nodes differ by at most 1. 
Question 25 
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 conﬂicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?
Topological order  
Depthﬁrst order  
Breadthﬁrst order  
Ascending order of transaction indices 
But BFS and DFS are also potssible 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 
Consider the following database schedule with two transactions, T_{1} and T_{2}.

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?
S is nonrecoverable  
S is recoverable, but has a cascading abort  
S does not have a cascading abort  
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 T_{2} which written the value of x which is written by T_{1} before T_{1} 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
2  
3  
4  
5 
The name assigned to the subquery 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, 3^{rd} 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 
the selection operation in relational algebra  
the selection operation in relational algebra, except that SELECT in SQL retains duplicates
 
the projection operation in relational algebra  
the projection operation in relational algebra, except that SELECT in SQL retains duplicates

Question 30 
2  
3  
4  
5 
Question 31 
Atomicity  
Consistency  
Isolation  
Durability 
Question 33 
R contains a,b,e,f,g but not c, d.
 
R contains all of a,b,c,d,e,f,g
 
R contains e,f,g but not a,b
 
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 
Undo T_{3}, T_{1}; Redo T_{2}
 
Undo T_{3}, T_{1}; Redo T_{2}, T_{4}
 
Undo: none; redo: T_{2}, T_{4}, T_{3}, T_{1}
 
Undo T_{3}, T_{1}; T_{4}; Redo: T_{2}

Question 35 
{P,R}→{S,T}  
{P,R}→{R,T}  
{P,S}→{S}  
{P,S,U}→{Q} 
Question 36 
WHERE P1.capacity >= All (select P2.capacity from Cinema P2)  
WHERE P1.capacity >= Any (select P2.capacity from Cinema P2)  
WHERE P1.capacity > All (select max(P2.capacity) from Cinema P2)  
WHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2) 
So the theatres which are having maximum capacity will satisfy the condition.
Question 38 
0.75  
0.76  
0.77  
0.78 
Pr(Y=0/ X_{3}=0) = Pr(Y=0 ∩ X_{3}=0)/ Pr(X_{3}=0)
= 3/8 / 4/8 = 3/4 = 0.75
Question 39 
{E,F}  
{E,F,H}  
{E,F,H,K,L}  
{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 
S1 is TRUE and S2 is FALSE.  
Both S1 and S2 are TRUE.  
S1 is FALSE and S2 is TRUE.  
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 
r_{1} (x); r_{2} (x); w_{1} (x); r_{3} (x); w_{2} (x)  
r_{2} (x);r_{1} (x);w_{2} (x);r_{3} (x);w_{1} (x)  
r_{3} (x);r_{2} (x);r_{1} (x);w_{2} (x);w_{1} (x)  
r_{2} (x);w_{2} (x);r_{3} (x);r_{1} (x);w_{1} (x) 
 Polygraph contains cycle. So, not a conflict serializable.
Option: B
Cyclic
Option: C
 Cyclic
Option: D
 Acyclic, so conflict serializable.
Question 42 
S1 is TRUE and S2 is FALSE.  
Both S1 and S2 are TRUE.  
S1 is FALSE and S2 is TRUE.  
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 nontrivial Functional Dependency} then default BCNF.
Hence it’s proved that a Relation with two singlevalued 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 
It executes but does not give the correct result.  
It executes and gives the correct result.  
It generates an error because of pairwise comparison.  
It generates an error because the GROUP BY clause cannot be used with table joins in a subquery. 
Question 44 
8  
9  
10  
11 
Here, n = 4.
So, the possible super keys = 2^{41} = 8
The super keys are: E, EH, EG, EF, EGH, EFH, EFG, EFGH.
Question 45 
19  
20  
21  
22 
Question 46 
S is conflictserializable but not recoverable  
S is not conflictserializable but is recoverable  
S is both conflictserializable and recoverable  
S is neither conflictserializable nor is it recoverable 
In the precedence graph, there are no cycles. So, it is conflict serializable and recoverable also.
Question 47 
relation r(R) is in the outer loop.  
relation s(S) is in the outer loop.  
join selection factor between r(R) and s(S) is more than 0.5.  
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 (n_{r}*b_{s}+b_{r} )
block transfer and (n_{r}+b_{r} ) seeks.
where,
b_{r} → no. of blocks in R
b_{s} → no. of blocks in S
h_{r} → 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 
select R.* from R,S where R.a=S.a  
select distinct R.* from R,S where R.a=S.a  
select R.* from R,(select distinct a from S) as S1 where R.a=S1.a  
select R.* from R,S where R.a=S.a and is unique R 
Question 49 
π_{A1} (σ_{(F1∧F2)} (r))  
π_{A1} (σ_{(F1∨F2)} (r))  
π_{A2} (σ_{(F1∧F2)} (r))  
π_{A2} (σ_{(F1∨F2)} (r)) 
Two Selects with Boolean expression can be combined into one select with AND of two Boolean expressions.
Question 50 
in all candidate keys of R.  
in some candidate key of R.  
in a foreign key of R.  
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.
Question 51 
some dependent.  
all dependents.  
some of his/her dependents.  
all of his/her dependents. 
Question 52 
Names of all the employees with at least one of their customers having a ‘GOOD’ rating.  
Names of all the employees with at most one of their customers having a ‘GOOD’ rating.  
Names of all the employees with none of their customers having a ‘GOOD’ rating.  
Names of all the employees with all their customers having a ‘GOOD’ rating. 
The inner query i.e., ② represents all customers having other than ‘GOOD’ while the entire query represents name of all employees with all their customers having a ‘good rating’.
Question 53 
it is on a set of fields that form a candidate key.  
it is on a set of fields that include the primary key.  
the data records of the file are organized in the same order as the data entries of the index.  
the data records of the file are organized not in the same order as the data entries of the index. 
Basically, Indexed column is used to sort the rows of table. Whole data record of file is sorted using index so the correct option is (C).
Question 54 
I, II, III and IV  
I, II and III only  
I, II and IV only  
II, III and IV only 
Question 55 
3  
4  
5  
6 
Now D+ = {D}.
Hence we have to add A,B,C,E,F,G,H to D and check which of them are Candidate keys of size 2.
AD^{+} = {ABCDEFGH}
BD^{+} = {ABCDEFGH}
ED^{+} = {ABCDEFGH}
FD^{+} = {ABCDEFGH}
But CD^{+}, GD^{+} and HD^{+} does not give all the attributes hence CD, GD and HD are not candidate keys.
Hence no. of candidate keys are 4: AD, BD, ED, FD.
Question 56 
in 1NF, but not in 2NF.  
in 2NF, but not in 3NF.  
in 3NF, but not in BCNF.  
in BCNF. 
Now D+ = {D}.
Hence we have to add A,B,C,E,F,G,H to D and check which of them are Candidate keys of size 2.
AD^{+} = {ABCDEFGH}
BD^{+} = {ABCDEFGH}
ED^{+} = {ABCDEFGH}
FD^{+}= {ABCDEFGH}
But CD^{+}, GD^{+} and HD^{+} does not give all the attributes hence CD, GD and HD are not candidate keys.
Here Candidate keys are AD, BD, ED and FD.
A → BC, B → CFH and F → EG etc are partial dependencies.
So given relation is in 1NF, but not in 2NF.
Question 57 
Every relation in 3NF is also in BCNF  
A relation R is in 3NF if every nonprime attribute of R is fully functionally dependent on every key of R  
Every relation in BCNF is also in 3NF  
No relation can be in both BCNF and 3NF 
Question 58 
An attribute of an entity can have more than one value  
An attribute of an entity can be composite  
In a row of a relational table, an attribute can have more than one value  
In a row of a relational table, an attribute can have exactly one value or a NULL value 
Option (B): In ER model, the attribute which can be further broken down into some other attributes is called composite attribute.
Option (C): In Relational model, the intersection of one row and column should contain only one value. So, option (C) is INCORRECT.
Option (D): In Relational model, the intersection of one row and column should contain either exactly one value or NULL.
Question 59 
P and R  
P and S  
Q and R  
Q and S 
The HAVING Clause enables you to specify conditions that filter which group results appear in the results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. So, we cannot use HAVING clause without GROUP BY clause.
Question 60 
a serializable schedule  
a schedule that is not conflict serializable  
a conflict serializable schedule  
a schedule for which a precedence graph cannot be drawn 
The above schedule is not conflict serializable.
Question 61 
∏_{B} (r_{1})  ∏_{C} (r_{2}) = ∅  
∏_{C} (r_{2})  ∏_{B} (r_{1}) = ∅  
∏_{B} (r_{1}) = ∏_{C} (r_{2})  
∏_{B} (r_{1})  ∏_{C} (r_{2}) ≠ ∅ 
So we can say that r_{2}(C) is the superset of r_{1}(B).
So (subset  superset) is always empty.
Question 62 
7  
4  
5  
9 
Performs the cross product and selects the tuples whose A∙Id is either greater than 40 or C∙Id is less than 15. It yields:
Question 63 
4  
3  
0  
1 
First query (2) will be executed and 0 (no) rows will be selected because in relation B there is no Name ‘Arun’.
The outer query (1) results the follow and that will be the result of entire query now. (Because inner query returns 0 rows).
Question 64 
BankAccount_Num is a candidate key  
Registration_Num can be a primary key
 
UID is a candidate key if all students are from the same country  
If S is a superkey such that S∩UID is NULL then S∪UID is also a superkey 
Question 65 
Ordered indexing will always outperform hashing for both queries  
Hashing will always outperform ordered indexing for both queries  
Hashing will outperform ordered indexing on Q1, but not on Q2  
Hashing will outperform ordered indexing on Q2, but not on Q1. 
For example, consider B^{+} tree, once you have searched a key in B^{+}; you can find range of values via the block pointers pointing to another block of values on the leaf node level.
Question 68 
1  
2  
3  
4 
― Here,
p = order
key = order – 1 = p – 1
― In the nonroot node, the minimum no. of keys =⌈p/2⌉1
― So, key = 5
order = 6
― So, minimum no. of keys in root node =⌈6/2⌉1=2
Question 69 
1, 0  
1, 2  
1, 3  
1, 5 
― 1, 3 Pids are returned
Question 70 
I only
 
II only  
Both I and II  
Neither I nor II 
― Timestamp ordering protocol ensures conflict serializability and free from deadlock.
Question 71 
T1 → T3 → T2  
T2 → T1 → T3  
T2 → T3 → T1  
T3 → T1 → T2 
― Precedence graph for the above schedule is,
― From the precedence graph the correct serialization is,
Question 72 
100  
200  
300  
2000 
R(A, B, C) – 200 tuples
S(B, D, E) – 100 tuples
FD’s:
B → A
A → C
― ‘B’ is primary key for R and foreign key of S from the given FDs.
― Maximum tuples in natural join of R and S is min(200, 100) = 100.
Question 73 
S_{1} and S_{2}  
S_{2} and S_{3}  
S_{3} only
 
S_{4} only 
Dependency graph is,
So, there is no cycle.
Schedule S3,
Dependency graph is,
S_{4} also has a cycle T_{1}→T_{2} and T_{2}→T_{1}.
So, S_{2} and S_{3} are conflict serializable.
Question 74 
2  
3  
4  
5 
Insert 3:
Insert 6:
Insert 4:
Insert 2:
Insert 1:
So, total splits are 3.
Question 75 
I and II
 
I and III
 
II and IV  
III and IV 
S={c}
It looks like Division operator. Since a division operator in E(A,B)/ P(B) will be equal to
Now replacing A=RS P=r
B=S
E=r
we will get,
equivalent to I
∴ It is equivalent to division operator.
⇒ r(RS,S)/r(S)
This logical statement means that
① Select t(RS) from r such that
② for all tuples U in S,
③ there exists a tuple V in r, such that
④ U=V[S] & t=V[RS]
A(x,y) & B(y)
A/B = {(x)  ∃(x,y)∈A(y)∈B}
which means that A/B contains all x tuples, such that for every tuple in B, there is an xy tuple in A.
So, this is just equivalent to I.
This logical statement means that
① Select t(RS) from r such that
② for all tuples V in r,
③ there exists a tuple U in r, such that
④ U=V[S] & t=V[RS]
⇒ Select (RS) values from r, where the S value is in (r/r), which will be true only if S in r is a foreign key referring to S is r.
This selects (a,b) from all tuples from r which has an equivalent value in S.
Question 76 
Find the names of all suppliers who have supplied a nonblue part.
 
Find the names of all suppliers who have not supplied a nonblue part.  
Find the names of all suppliers who have supplied only blue parts.
 
Find the names of all suppliers who have not supplied only blue parts.

If we execute the given query the output will be S3 and S4 i.e., names of all suppliers who didn’t supply blue parts which is option (A).
Option (D) says names of suppliers who didn’t supply only blue parts that means, supplier should supply all other parts for sure and shouldn’t supply blue part.
Question 77 
The schema is in BCNF  
The schema is in 3NF but not in BCNF
 
The schema is in 2NF but not in 3NF  
The schema is not in 2NF 
(Sid, Street) → Sname
As Sid is a primary key, then
(Sid, Street) will be super key.
Hence, it is in BCNF.
Question 78 
I only  
II only  
III only  
III and IV only 
∀xP(x)≡∼∃x(∼P(x))
∼∀x(∼P(x))≡∃x(P(x))
Given: ∀t ∈ r(P(t)) (1)
As per Demorgan law
(1) ⇒ ∼∃t ∈ r(∼P(t))
which is option (III).
Question 79 
nonkey and ordering
 
nonkey and nonordering
 
key and ordering
 
key and nonordering

Question 80 
3  
4  
5  
6 
{1,2,3,...,10} which can cause maximum possi ble splits.
1, 2, 3 →
4 →
5 →
6 →
7 →
8 →
9 →
10 →
Question 81 
Only I and II
 
Only I and III
 
Only I, II and III  
Only I, III and IV 
We have two common columns in 'R' and 'S' which are 'P' and 'Q'.
(I) Both P and Q are used while doing the join, i.e., both P and Q are used to filter.
(II) Q is not used here for filtering. Natural join is done on all P's from R and all P's from S. So different from option (I).
(III) Through venn diagram it can be proved that A∩B = A  (AB).
So through above formula we can say that (III) and (IV) are equivalent.
So finally (I), (III) and (IV) are equivalent.
Question 82 
Both Book and Collection are in BCNF
 
Both Book and Collection are in 3NF only
 
Book is in 2NF and Collection is in 3NF
 
Both Book and Collection are in 2NF only

Book(Title, Author, Catalog_no, Publisher, Year, Price)
Collection(Title, Author, Catalog_no)
I) Title Author ⟶ Catalog_no ⟶ BCNR
II) Catalog_no ⟶ Title, Author, Publisher, Year ⟶ 3NF
III) Publisher Title Year ⟶Price ⟶ 2NF Book’s in 2NF
Collection is in 3NF.
Question 83 
8 and 0  
128 and 6  
256 and 4
 
512 and 5 
Record size = 32 bytes
Key size = 6 bytes
Block pointer size = 10 bytes
Block size of file system = 1024 bytes
Record (or) index entry size = 10+6 = 16 bytes
In first level no. of blocks =No. of records in a file/Block size=16384 * 16/1024=256
In second level, it have = 256*16 entries
In second level, no. of blocks =No. of entries/Block size =256*16/1024=4
Question 84 
2  
3  
4  
5 
➝ Here N is a Weak entity, but it need to modify the primary key of P such as P1
M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
➝ Relationship set has its own attribute, then no need to create a separate table.
➝ Finally we require 3 minimum tables to represent M,P,N,R1,R2.
Question 85 
{M1, M2, M3, P1}  
{M1, P1, N1, N2}
 
{M1, P1, N1}  
{M1, P1}

For M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
Question 86 
gives a lossless join, and is dependency preserving  
gives a lossless join, but is not dependency preserving  
does not give a lossless join, but is dependency preserving  
does not give a lossless join and is not dependency preserving

(A, B, C) (B, D)  common attributes is B and B→D is a FD (via B→C, C→D), and hence, B is a key for (B, D). So, decomposition of (A, B, C, D) into (A, B, C) (B, D) is lossless.
Thus the given decomposition is lossless.
The given decomposition is also dependency preserving as the dependencies A→B is present in (A, B), B→C is present in (B, C), D→B is present in (B, D) and C→D is indirectly present via C→B in (B, C) and B→D in (B, D).
Question 87 
in BCNF  
in 3NF, but not in BCNF  
in 2NF, but not in 3NF  
not in 2NF 
Since there is a partial dependency B→G.
So the relational schema R is Not in 2NF.
Question 88 
S1, S2 and S3 are all conflict equivalent to each other  
No two of S1, S2 and S3 are conflict equivalent to each other  
S2 is conflict equivalent to S3, but not to S1  
S1 is conflict equivalent to S2, but not to S3 
For S1:
For S2:
For S3:
Hence, S1 is conflict equivalent to S2, but not to S3.
Question 89 
for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students  
for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students  
for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam  
nothing; the query has a syntax error

Question 90 
The empty set  
schools with more than 35% of its students enrolled in some exam or the other  
schools with a pass percentage above 35% over all exams taken together  
schools with a pass percentage above 35% over each exam 
{ x  x ∈ Enrolment ∧ x . schoolid = t }  * 100 > 35 }
This is school with enrollment % is 35 or above.
Question 91 
Courses in which all the female students are enrolled.
 
Courses in which a proper subset of female students are enrolled.
 
Courses in which only male students are enrolled.
 
None of the above

Option B: Yes, True. It selects the proper subset of female students which are enrolled because in the expression we are performing the cartesian product.
Option C: False. It doesn’t shows (or) display the males students who are enrolled.
Question 92 
Names of employees with a male supervisor.
 
Names of employees with no immediate male subordinates.  
Names of employees with no immediate female subordinates.  
Names of employees with a female supervisor.

Question 93 
Any relation with two attributes is in BCNF  
A relation in which every key has only one attribute is in 2NF
 
A prime attribute can be transitively dependent on a key in a 3NF relation  
A prime attribute can be transitively dependent on a key in a BCNF relation 
i) It is in 3NF.
ii) For any dependency X→ Y
where X is a super key.
iii) Functional dependency has been removed.
Option D is false.
→ Because a prime attribute can’t be transitive dependent on a key in a BCNF relation.
Question 94 
63  
64  
67  
68 
Data recorder pointer size, r = 7 bytes
Value size, v = 9 bytes
Disk Block pointer, P = 6 bytes
⇒ Order of leaf be m, then
r*m+v*m+p <= 1024
7m+9m+6 <= 1024
16m <= 1024  6
m <= 63
Question 95 
Both S_{1} and S_{2} are conflict serializable.
 
S_{1} is conflict serializable and S_{2} is not conflict serializable.  
S_{1} is not conflict serializable and S_{2} is conflict serializable.
 
Both S_{1} and S_{2} are not conflict serializable.

In precedence graph of S_{1} since cycle is formed so not conflict serializable.
But in precedence graph of S_{2} No cycle is formed so it is conflict serializable.
Question 96 
Consider a selection of the form σ_{A≤100}(r), where r is a relation with 1000 tuples. Assume that the attribute values for A among the tuples are uniformly distributed in the interval [0, 500]. Which one of the following options is the best estimate of the number of tuples returned by the given selection query ?
50  
100  
150  
200 
Values for A among the tuples are uniformly distributed in the interval [0, 500]. This can be split to 5 mutually exclusive and exhaustive intervals of same width of 100 ([0100], [101200], [201300], [301400], [401500], 0 makes the first interval larger  this must be typ0 in this question) and we can assume all of them have same number of values due to uniform distribution. So no. of tuples with A value in first interval should be,
Total no. of tuples/5 = 1000/5 = 200
Question 97 
i) Exclusive locks should be released after the commit.
ii) No locking can be done after the first unlock.
(A) Wrong because to write x you need Exclusive Lock.
(B) Wrong because violating the 1^{st} requirement.
(C) Correct.
(D) Wrong because violating the 1^{st} requirement.
Question 98 
0  
1  
2  
3 
if A→B then A→→B holds but reverseis not true.
So,
(i) False.
(ii) True.
(iii) False.
(iv) True.
Question 99 
П_{cname} (σ_{bal < 0} (σ_{bcity = “Agra”} branch ⋈ account) ⋈ depositor)  
П_{cname} (σ_{bcity = “Agra”}branch ⋈ (σ_{bal < 0} account ⋈ depositor))  
П_{cname} (σ_{bcity = “Agra”} branch ⋈ σ_{bcity = “Agra” ⋀ bal < 0} account) ⋈ depositor)  
П_{cname} (σ_{bcity = “Agra” ⋀ bal < 0} account ⋈ depositor)) 
Options (C) and (D) are invalid as there is no bcity column in aschema.
Question 100 
1  
2  
3  
4 
After inserting K15 we get,
Now, we insert K25, which gives
So, we see in the final tree only (K20, K25) is present. Hence, 1 (Answer).
Question 101 
Statements (i) and (ii) are true  
Statements (ii) and (iii) are true  
Statements (iii) and (i) are true  
All the statements are false 
Now after deleting 50 we get B^{+} tree as
Hence, correct statement is only (i) & (ii).
Question 102 
We must redo log record 6 to set B to 10500.
 
We must undo log record 6 to set B to 10000 and then redo log records 2 and 3.
 
We need not redo log records 2 and 3 because transaction T1 has committed.
 
We can apply redo and undo operations in arbitrary order because they are idempotent.

So from above theory we can say that option (B) is the correct answer.
Question 103 
Plan 1 and Plan 2 will not output identical row sets for all databases.
 
A course may be listed more than once in the output of Plan 1 for some databases.
 
For x = 5000, Plan 1 executes faster than Plan 2 for all databases.
 
For x = 9000, Plan I executes slower than Plan 2 for all databases.

While analyze the plan1 and plan2 does lesser number of comparisons compared to plan1.
i) The join table consists of two tables will have more rows. So comparisons are needed to find amount greater than x.
ii) Join operation consists of more number of comparisons as the second table will have more rows in plan2 compared to plan1.
Question 104 
{CF}^{+} = {ACDEFG}  
{BG}^{+} = {ABCDG}
 
{AF}^{+} = {ACDEFG}  
{AB}^{+} = {ABCDFG}

AF → D
DE → F
C → G
F → E
G → A
CF^{+} = {G,E,A,D,C,F} = {A,C,D,E,F,G} (✔️)
BG^{+} = {B,G,A,C,D} = {A,B,C,D,G} (✔️)
AF^{+} = {D,E,A,F} = {A,D,E,F} (❌)
AB^{+} = {A,B,C,D,G} (❌)
Question 105 
2 and 5
 
1 and 3
 
1 and 4
 
3 and 5

The customer with largest balance gets rank 1. Ties are broken with ranks are skipped.
So, both queries may doesn’t give same output. Statement 4 is correct.
Question 106 
All queries return identical row sets for any database.
 
Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.
 
There exist databases for which Query3 returns strictly fewer rows than Query2.
 
There exist databases for which Query4 will encounter an integrity violation at runtime.

Query1 : Output
abcd
abcd
PQRS
Query 2 : Output
abcd
PQRS
Query 3 : Output
abcd
PQRS
Query 4 : Output
abcd
PQRS
Query 2 & Query 4 gives same results but Query 1 & Query 3 gives different results.
Question 107 
Consider the relations r_{1}(P, Q, R) and r_{2}(R, S, T) with primary keys P and R respectively. The relation r_{1} contains 2000 tuples and r_{2} contains 2500 tuples. The maximum size of the join r_{1}⋈ r_{2} is :
2000  
2500  
4500  
5000 
Question 108 
2 and 3 only  
1 and 2 only  
1 and 3 only  
1, 2 and 3 
Question 109 
In a database file structure, the search key field is 9 bytes long, the block size is 512 bytes, a record pointer is 7 bytes and a block pointer is 6 bytes. The largest possible order of a nonleaf node in a B^{+} tree implementing this file structure is
23  
24  
34  
44 
n × p + (n  1) × k ≤ B (for nonleaf node)
Here, n = order, p = tree/block/index pointer, B = size of block
So,
n × p + (n  1) × k ≤ B
n × 6 + (n  1) × 9 ≤ 512
n ≤ 34.77
∴ n = 34
Question 110 
VXZ  
VXY  
VWXY  
VWXYZ 
Candidate keys are
VXY, WXY, ZXY
Question 111 
Karthikeyan, Boris  
Sachin, Salman  
Karthikeyan, Boris, Sachin  
Schumacher, Senna

did = {22, 22, 31, 31, 64}
For colour = "Green"
did = {22, 31, 74}
Intersection of Red and Green will be = {22, 31}, which is Karthikeyan and Boris.
Question 112 
36  40  
44  48  
60  64  
100  104 
red did : 22, 31, 64
green did : 22, 31, 74
(6) for intersection
(1) for searching 22 in driver relation, and (3) for searching 31.
Total: 38 + 6 + 4 = 48
Question 113 
AE, BE
 
AE, BE, DE
 
AEH, BEH, BCH  
AEH, BEH, DEH 
So only option (D) contains E and H as part of candidate key.
Question 114 
Titles of the four most expensive books
 
Title of the fifth most inexpensive book
 
Title of the fifth most expensive book
 
Titles of the five most expensive books

The where clause of outer query will be true for 5 most expensive books.
Question 115 
(3,4) and (6,4)  
(5,2) and (7,2)
 
(5,2), (7,2) and (9,5)
 
(3,4), (4,3) and (6,4)

Totally (5,2), (7,2), (9,5) are also deleted.
Question 116 
s ⊂ r
 
r ∪ s = r
 
r ⊂ s  
r * s = s

Table r: R(A, B, C, D)
Table r_{1}: Π_{A,B,C}(R)
Table r_{2}: Π_{A,D}(R)
S = r_{1} * r_{2} (* denotes natural join)
Table S:
Table r ⊂ Table S
⇒ r ⊂ S
Question 117 
Database relations have a large number of records  
Database relations are sorted on the primary key
 
B^{+} trees require less memory than binary search trees
 
Data transfer from disks is in blocks 
Thus, the data can be transferred through blocks in B^{+} trees. This can be used for indexing the database relations.
Question 118 
BCNF is stricter than 3NF
 
Lossless, dependencypreserving decomposition into 3NF is always possible
 
Lossless, dependencypreserving decomposition into BCNF is always possible  
Any relation with two attributes is in BCNF

Option B: Lossless, dependency preserving decomposition into 3NF is always possible.
Option C: It is false.
It is not possible to have dependency preserving in BCNF decomposition.
→ Let take an example, 3NF can't be decomposed into BCNF.
Option D: It is true.
Let consider t wo attributes (X, Y).
If (X→Y), X is a candidate key. It is in BCNF and viceversa.
Question 119 
Person  
Hotel Room  
Lodging  
None of these 
Question 120 
1 NF  
2 NF  
3 NF  
None 
F2 → F4 ......(ii)
(F1⋅F2) → F5 .....(iii)
F1F2 is the candidate key.
F1 and F2 are the prime key.
In (i) and (ii) we can observe that the relation from P → NP which is partial dependency. So this is in 1NF.
Question 121 
5  
4  
3  
2 
Here, the tree has 4 levels, then 4+1=5 nodes to be present in the newly created process.
Question 122 
Execute T1 followed by T2 followed by T3  
Execute T2, followed by T3; T1 running concurrently throughout  
Execute T3 followed by T2; T1 running concurrently throughout  
Execute T3 followed by T2 followed by T1

In other cases some people will get two times increment, for example,
if we have T1 followed by T2 and if initial commission is 49500, then he is belonging to <50000.
Hence, 49500 * 1.02 = 50490.
Now again he is eligible for second category. So, he will get again increment as,
50490 * 1.04 = 52509.6
So he will get increment two times, but he is eligible for only one slab of commission.
Question 123 
6  
4  
2  
0 
Total 7 rows are selected.
Where in relational algebra only distinct values of hostels are selected,i.e., 5, 6, 7 (3 rows).
∴ Answer is 7  3 =4
Question 124 
do not supply any item  
supply exactly one item  
supply one or more items  
supply two or more items 
Question 125 
CD → AC  
BD → CD  
BC → CD  
AC → BC 
Option (B):
BD → CD
BD^{+} = BD
i.e., BD cannot derive CD and hence is not implied.
Question 126 
800000  
40080  
32020  
100 
Therefore,
putting 2^{nd} table outside,
for every 400 records {
80 block accesses in first table
}
= 32000 blocks
and also 20 blocks accesses of outer table.
So, answer comes out to be,
32000 + 20 = 32020
Question 127 
0  
30400  
38400  
798400 
400×80+20
= 32020 (calculated in previous question)
In block Nested loop join, the no. of block access will be
20×80+20 = 1620
∴ The difference is = 32020  1620 = 30400
Question 128 
Let R_{1} (A, B, (D)) and R_{2} (D, E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R_{1} referring to R_{2}. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r_{1} and r_{2}. Which one of the following relational algebra expressions would necessarily produce an empty relation?
Π_{D}(r_{2})  Π_{C}(r_{1})
 
Π_{C}(r_{1})  Π_{D}(r_{2})
 
Π_{D}(r_{1}⨝_{C≠D}r_{2})
 
Π_{C}(r_{1}⨝_{C=D}r_{2})

→ Based on referral integrity C is subset of values in R_{2} then,
Π_{C}(r_{1})  Π_{D}(r_{2}) results empty relation.
Question 129 
8, 8  
120, 8
 
960, 8
 
960, 120 
→ In the question only enroll Id's are same with the student table.
→ The no. of minimum and maximum tuples is same i.e., 8, 8.
Question 130 
2 NF
 
3 NF
 
BCNF
 
4NF

name, courseNo → grade →(I)
rollNo, courseNo → grade →(II)
name → rollNo →(III)
rollNo → name →(IV)
Candidate keys: name, courseNo (or) rollNo
Its is not BCNF, because the relation III, there is no relationship from super key.
name → rollNo
It is not BCNF, name is not super key.
It belongs to 3NF, because if X→Y, Y is prime then it is in 3NF.
Question 131 
names of girl students with the highest marks
 
names of girl students with more marks than some boy student
 
names of girl students with marks not less than some boy students
 
names of girl students with more marks than all the boy students

Question 132 
24  
25  
26  
27 
Child pointer = 6 bytes
key size = 14 bytes
Block size = 512 bytes
⇒ 512 = (n1)14 + n(6)
512 = 20n  14
n = 512+14/20 = 526/20 = 26.3
∴ n = 26
Question 133 
the average salary is more than the average salary in the company
 
the average salary of male employees is more than the average salary of all male employees in the company
 
the average salary of male employees is more than the average salary of employees in the same department
 
the average salary of male employees is more than the average salary in the company

This results the employees who having the salary more than the average salary.
Sex = M
Selects the Male employees whose salary is more than the average salary in the company.
Question 134 
Page  
Table  
Row  
Page, table and row level locking allow the same degree of concurrency

Table locking can be used for concurrency control with DDL operations.
In row share table is less restrictive but it consists of highest degree of concurrency compared to page and table.
Question 135 
2  
3  
5  
4 
Then, we get
T1: {A11, A12, A13}  key is A11
T2: {A21, A22, A11}  key is A21
T3: {A21, A23}  key is {A21, A23}
Question 136 
0 row and 4 columns  
3 rows and 4 columns  
3 rows and 5 columns  
6 rows and 5 columns 
rows = 3 * 2 = 6
Columns = 3 + 2 = 5
Question 137 
A relation Empdtl is defined with attributes empcode (unique), name, street, city, state and pincode. For any pincode, there is only one city and state. Also, for any given street, city and state, there is just one pincode. In normalization terms, Empdtl is a relation in
1NF only  
2NF and hence also in 1NF  
3NF and hence also in 2NF and 1NF  
BCNF and hence also in 3NF, 2NF an 1NF 
Here key is empcode and contains only one attribute, hence no partial dependency. But there is transitive dependency in this.
Pincode → city, state, so it is not in 3NF.
Question 138 
18.75  
20  
25  
NULL 
(15+25+35)/3 = 25
Question 139 
Both (i) and (ii) will fail  
(i) will fail but (ii) will succeed  
(i) will succeed but (ii) will fail  
Both (i) and (ii) will succeed 
But in (ii) if we set in Department table, Dept_id = Null, then it will produce inconsistency because in Student table we will still have the tuples containing the Dept_id = 1.
Question 140 
Consider a table T in a relational database with a key field K. A Btree of order p is used as an access structure on K, where p denotes the maximum number of tree pointers in a Btree index node. Assume that K is 10 bytes long; disk block size is 512 bytes; each data pointer P_{D} is 8 bytes long and each block pointer P_{B} is 5 bytes long. In order for each Btree node to fit in a single disk block, the maximum value of p is
20  
22  
23  
32 
r = record_ptr_size
b = block_ptr_size
(p  1) (k + r) + p × b ≤ 512
(p  1) (10 + 8) + p × 5 ≤ 512
23p ≤ 530
p ≤ 23.04
So, maximum value of p possible will be 23.
Question 141 
A transaction writes a data item after it is read by an uncommitted transaction  
A transaction reads a data item after it is read by an uncommitted transaction  
A transaction reads a data item after it is written by a committed transaction
 
A transaction reads a data item after it is written by an uncommitted transaction

Question 142 
Question 143 
in second normal form but not in third normal form  
in third normal form but not in BCNF  
in BCNF
 
in none of the above

Date_of_Birth → Age
Name → Roll_number
Roll_number → Name
Candidate keys for the above are:
(Date_of_Birth, Name) and (Date_of_Birth, Roll_number)
Clearly, there is a partial dependency,
Date_of_Birth → Age
So, it is only in 1NF.
Question 144 
Names of students who have got an A grade in all courses taught by Korth
 
Names of students who have got an A grade in all courses
 
Names of students who have got an A grade in at least one of the courses taught by Korth
 
in none of the above 
Question 145 
The schedule is serializable as T2; T3; T1
 
The schedule is serializable as T2; T1; T3  
The schedule is serializable as T3; T2; T1
 
The schedule is not serializable

Cycle formed so not serializable.
Question 146 
Zero  
More than zero but less than that of an equivalent 3NF decomposition  
Proportional to the size of F^{+}  
Indetermine 
Question 147 
Relational algebra is more powerful than relational calculus.  
Relational algebra has the same power as relational calculus.  
Relational algebra has the same power as safe relational calculus.  
None of the above. 
A query can be formulated in safe Relational Calculus if and only if it can be formulated in Relational Algebra.
Question 148 
A B^{+} tree index is to be built on the Name attribute of the relation STUDENT. Assume that all student names are of length 8 bytes, disk blocks are of size 512 bytes, and index pointers are of size 4 bytes. Given this scenario, what would be the best choice of the degree (i.e. the number of pointers per node) of the B^{+}tree?
16  
42  
43  
44 
Then,
8(P1) + 4P ≤ 512
12P  8 ≤ 512
12P ≤ 520
P ≤ 43.33
P = 43
Question 149 
Relation R is decomposed using a set of functional dependencies, F, and relation S is decomposed using another set of functional dependencies, G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a quaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G are available).
Dependencypreservation  
Losslessjoin  
BCNF definition  
3NF definition 
Question 150 
A functionally determines B and B functionally determines C  
A functionally determines B and B does not functionally determines C  
B does not functionally determines C  
A does not functionally determines B and B does not functionally determines C 
But for the given instance it can be seen that B does not functionally determines C, and it can be concluded for entire relation
Question 151 
Theory Explanation is given below. 
Question 154 
Disk capacities are greater than memory capacities  
Disk access is much slower than memory access  
Disk data transfer rates are much less than memory data transfer rates  
Disks are more reliable than memory 
Question 155 
Department address of every employee  
Employees whose name is the same as their department name  
The sum of all employees’ salaries  
All employees of a given department 
Question 156 
XY → Z and Z → Y  
YZ → X and Y → Z  
YZ → X and X → Z  
XZ → Y and Y → X 
If for t1[A] = t2[A] then t1[Y] = t2[Y].
Question 157 
r has no duplicates and s is nonempty  
r and s have no duplicates  
s has no duplicates and r is nonempty  
r and s have the same number of tuples 
Question 158 
In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent?
x = 5 not AND (not (x = 5)  
x = 5 AND x > 4 and x < 6, where x is an integer  
x ≠ 5 AND not (x = 5)  
None of the above 
Question 159 
Theory Explanation is given below. 
(i)
(ii)
(b)
Question 161 
m + n and 0  
mn and 0  
m + n and m – n  
mn and m + n 
Suppose there is no common attribute in R and S due to which natural join will act as cross product. So then in cross product total no. of tuples will be mn.
For minimum:
Suppose there is common attribute in R and S, but none of the row of R matches with rows of S then minimum no. of tuples will be 0.
Question 162 
σ_{(A=10∨B=20)} (r)  
σ_{(A=10)} (r) ∪ σ_{(B=20)} (r)  
σ_{(A=10)} (r) ∩ σ_{(B=20)} (r)  
σ_{(A=10)} (r)  σ_{(B=20)} (r) 
σ_{(A=10)} (r) ∩ σ_{(B=20)} (r)
Question 163 
CD  
EC  
AE  
AC 
A) (CD)^{+} = cdf
Not a key.
B) (EC)^{+} = ecdabf
Yes, it is a key.
C) (AE)^{+} = aeb
Not a key. D) (AC)^{+} = abcf
Not a key.
Question 164 
Btrees are for storing data on disk and B^{+} trees are for main memory.  
Range queries are faster on B* trees.  
Btrees are for primary indexes and B* trees are for secondary indexes.  
The height of a B* tree is independent of the number of records. 
Question 165 
This schedule is serialized and can occur in a scheme using 2PL protocol  
This schedule is serializable but cannot occur in a scheme using 2PL protocol  
This schedule is not serialiable but can occur in a scheme using 2PL protocol  
This schedule is not seralisable and cannot occur in a scheme using 2PL protocol.

Since cycle exist so not conflict serializable.
And we know that if the schedule is not serializable then it is not 2PL.
Hence correct option is (D).
Question 166 
not in 2NF  
in 2NF but not 3NF  
in 3NF but not in 2NF  
in both 2NF and 3NF 
And since every attribute is key so the decomposed relation will be in BCNF and hence in 3NF.
Question 167 
A = 0, B = 0, C = 1  
A = 0, B = 1, C = 1  
A = 1, B = 0, C = 1  
A = 1, B = 1, C = 1 
So the above equation is satisfied if either C=0 or A=0 and B=1.
Hence, Option (B) is correct.
Question 168 
XOR gates, NOT gates  
2 to 1 multiplexors  
AND gates, XOR gates  
Threeinput gates that output (A⋅B) + C for the inputs A⋅B and C  
Both B and C 
(B) 2 to 1 multiplexors is functionally complete.
(C) XOR gate can be used to make a NOT gate. So, (AND, NOT) is functionally complete.
(D) With given gates and inputs NOT gate cannot be derived.
Hence, not complete.
Question 169 
An SQL query automatically eliminates duplicates  
An SQL query will not work if there are no indexes on the relations  
SQL permits attribute names to be repeated in the same relation  
None of the above 
→ If there are no indexes on the relation SQL, then also it works.
→ SQL does not permit 2 attributes to have same name in a relation.
Question 170 
R_{1} ∪ R_{2}  
R_{1} × R_{2}  
R_{1}  R_{2}  
R_{1} ∩ R_{2} 
Question 171 
2 NF  
5 NF  
4 NF  
3 NF 
Question 172 
Age  
Name  
Occupation  
Category 
Question 173 
Which of the following query transformations (i.e. replacing the l.h.s. expression by the r.h.s. expression) is incorrect? R_{1} and R_{2} are relations, C_{1}, C_{2} are selection conditions and A_{1}, A_{2} are attributes of R_{1}?
σ_{C1}(σ_{C1}(R_{1})) → σ_{C2}(σ_{C2}(R_{1}))  
σ_{C1}(σ_{A1}(R_{1})) → σ_{A1}(σ_{C1}(R_{1}))  
σ_{C1}(R_{1} ∪ R_{2}) → σ_{C1}(R_{1}) ∪ σ_{C1}(R_{2})  
π_{A1}(σ_{C1}(R_{1})) → σ_{C1}(σ_{A1}(R_{1})) 
Question 174 
in first normal form but not in second normal form  
in second normal form but not in third normal form  
in third normal form  
None of the above 
Since all a, b, c, d are atomic. So the relation is in 1NF.
Checking the FD's
a → c
b → d
We can see that there is partial dependencies. So it is not 2NF.
So answer is option (A).
Question 175 
None of (a), (b), (c) or (d) can cause its violation  
All of (a), (b), (c) and (d) can cause its violation  
Both (a) and (d) can cause its violation  
Both (b) and (c) can cause its violation

Here 'd' is the foreign key of S and let 'a' is the primary key of R.
(A) Insertion into R: will cause no violation.
(B) Insertion into S: may cause violation because there may not be entry of the tuple in relation R. Example entry of 〈S_{4}, __, __〉 is not allowed.
(C) Delete from R: may cause violation. For example, deletion of tuple 〈S_{2}, __, __〉 will cause violation as there is entry of S_{2} in the foreign key table.
(D) Delete from S: will cause no violation as it does not result inconsistency.
Question 176 
True  
False 
Question 177 
Yes  
No 
Question 178 
Out of syllabus (For explanation see below) 
→ No need of using Union operation here. → In question they gave (∪, −) but we don't use both.
→ And also they are saying that only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.
So, the expression is minimal.
Question 179 
True  
False 
Question 180 
dependency preserving and lossless join  
lossless join but not dependency preserving  
dependency preserving but not lossless join  
not dependency preserving and not lossless join 
R_{1}∩R_{2} ≠ 0
Given R_{1}(A,B), R_{2}
R_{1}∩R_{2}= 0
Not lossless.
The given relation decomposed into R_{1}(A,B) and R_{2}(C,D) and there are only two functional dependencies A→B and C→D. So the given decomposition is dependency preserving.
Question 181 
List of all vertices adjacent to a given vertex  
List all vertices which have self loops  
List all vertices which belong to cycles of less than three vertices  
List all vertices reachable from a given vertex 
(b) Finding a self loop is also simple (Oop(X,X))
(c) If a → b, b → c then c!=a, finding this is also simple.
(d) List all the elements reachable from a given vertex is too difficult in Relational Algebra.
Question 182 
σ_{A=a} (r)  
r  
σ_{A=a} (r)⨝s  
None of the above 
(b) Display table
(c) A=a for all Tables r and s
Question 183 
A → B, B → CD  
A → B, B → C, C → D  
AB → C, C → AD  
A → BCD 
Question 184 
{t∃u ∈ R_{1} (t[A] = u[A])∧ ¬∃s ∈ R_{2} (t[A] = s[A])}  
{t∀u ∈ R_{1} (u[A]= "x" ⇒ ∃s ∈ R_{2} (t[A] = s[A] ∧ s[A] = u[A]))}  
{t¬(t ∈ R_{1})}  
{t∃u ∈ R_{1} (t[A] = u[A])∧ ∃s ∈ R_{2} (t[A] = s[A])} 
Question 185 
A tuple (z,w) with z > y is deleted  
A tuple (z,w) with z > x is deleted  
A tuple (z,w) with w < x is deleted  
The deletion of (x,y) is prohibited 
Question 188 
(a)  (q), (b)  (r), (c)  (p), (d)  (s) 
Nonprocedural query language → Domain calculus
Closure of set of attributes → Function dependency
Natural join → Relational algebraic operations
Question 189 
Transitive functional dependencies.  
Nontrivial functional dependencies involving prime attributes on the rightside.
 
Nontrivial functional dependencies involving prime attributes only on the leftside.
 
Nontrivial functional dependencies involving only prime attributes.  
Both (B) and (D). 
B) 3NF because right side is prime attribute.
C) Not in 3NF, because lets suppose ABC is a candidate key. Now consider
AB → Nonprime attribute
which show it is not in 3NF
D) Involves only prime attribute, so right side should definitely contain only prime attribute. So in 3NF.
Question 190 
Indexed sequential file organization.  
Twoway linked list.  
Inverted file organization.  
Sequential file organization. 
→ An index for each secondary key.
→ An index entry for each distinct value of the secondary key.
→ Exhibits better enquiry performance.