DatabaseManagementSystem
Question 1 
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 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?
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 3 
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 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))
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 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 _____.
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 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?
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 7 
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 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?
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 9 
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 10 
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 11 
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 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 ___________.
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 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?
(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 14 
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 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 ____________.
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 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?
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 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 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 18 
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 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 _____.
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 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 
Atomicity  
Consistency  
Isolation  
Deadlockfreedom 
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 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 
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 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 = 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, 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.
• 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 
SELECT operation in SQL is equivalent to
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 29 
Consider an EntityRelationship (ER) model in which entity sets E_{1} and E_{2} are connected by an m:n relationship R_{12}, E_{1} and E_{3} are connected by a 1:n (1 on the side of E_{1} and n on the side of E_{3}) relationship R_{13}.
E_{1} has two singlevalued attributes a_{11} and a_{12} of which a_{11} is the key attribute. E_{2} has two singlevalued attributes a_{21} and a_{22} is the key attribute. E_{3} has two singlevalued attributes a_{31} and a_{32} of which a_{31} 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 ___________.
4  
6  
7  
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 _________.
2  
3  
4  
5 
Question 31 
Consider the following transaction involving two bank account x and y.
read(x); x:= x50; 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
Atomicity  
Consistency  
Isolation  
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 ___________.
6  
7  
5  
9 
Question 33 
Consider two relations R_{1}(A,B) with the tuples (1,5), (3,7) and R_{2}(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R_{1} and R_{2}. 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?
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 
Consider a simple checkpointing protocol and the following set of operations in the log.

(start, T_{4}); (write, T_{4}, y, 2, 3); (start, T_{1}); (commit, T_{4}); (write, T_{1}, z, 5, 7);
(checkpoint);
(start, T_{2}); (write, T_{2}, x, 1, 9); (commit, T_{2}); (start, T_{3}); (write, T_{3}, 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
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 
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?
{P,R}→{S,T}  
{P,R}→{R,T}  
{P,S}→{S}  
{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?
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 theaters which are having maximum capacity will satisfy the condition.
Question 37 
The value of is
0  
1/2  
1  
∞ 
Question 38 
Suppose X_{i} for i = 1, 2, 3 are independent and identically distributed random variables whose probability mass functions are Pr[X_{i} = 0] = Pr[X_{i} = 1]=1/2 for i = 1, 2, 3. Define another random variable Y = X_{1}X_{2} ⊕ X_{3}, where ⊕ denotes XOR. Then Pr[Y = 0X_{3} = 0] = _______________.
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 
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?
{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 
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?
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 
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?
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 
Given the following two statements:
S1: Every table with two singlevalued 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?
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 
Given the following schema:
employees(empid, firstname, lastname, hiredate, deptid, salary) departments(deptid, deptname, managerid, locationid)
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 lastname, hiredate FROM employees WHERE (deptid, hiredate) IN ( SELECT deptid, MAX(hiredate) FROM employees JOIN departments USING(deptid) WHERE locationid = 1700 GROUP BY deptid);
What is the outcome?
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 
The maximum number of superkeys for the relation schema R(E, F, G, H) with E as the key is _____.
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 
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
19  
20  
21  
22 
Question 46 
Consider the following schedule S of transactions T1, T2, T3, T4:
Which one of the following statements is CORRECT?
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 
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
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 n_{r} 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)
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 
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?
π_{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 
A prime attribute of a relation scheme is an attribute that appears
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 
Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.
employee (empId, empName, empAge) dependent(depId, eId, depName, depAge)
Consider the following relational algebra query:
^{∏}_{empId}^{(employee)∏}_{empId}^{(employee}^{⋈}(empId = eID)∧(empAge ≤ depAge)^{dependent)}
The above query evaluates to the set of empIds of employees whose age is greater than that of
some dependent.  
all dependents.  
some of his/her dependents.  
all of his/her dependents. 
Question 52 
Consider the following relational schema:
employee(empId, empName, empDept) customer(custId, custName, salesRepId, rating)
salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?
SELECT empName FROM employee E WHERE NOT EXISTS (SELECT custId FROM customer C WHERE C.salesRepId = E.empId AND C.rating <> `GOOD`);
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 
An index is clustered, if
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 
Consider the following relation schema
 Students(rollno: integer, sname: string)
Courses(courseno: integer, cname: string)
Registration(rollno: integer, courseno: integer, percent: real)
Which of the following queries are equivalent to this query in English?
"Find the distinct names of all students who score more than 90% in the course numbered 107"
(I) SELECT DISTINCT S.sname FROM Students as S,Registration as R WHERE R.rollno = S.roll AND R.courseno = 107 AND R.percent > 90 (II) Π_{sname}(σ_{courseno=107∧percent>90}(Registration ⋈ Students)) (III) {T∃S ∈ Students, ∃R ∈ Registration (S.rollno = R.rollno ∧ R.courseno = 107 ∧ R.percent > 90 ∧ T.sname = S.sname)} (IV) {〈S_{N}〉∃S_{R}∃R_{P}(〈S_{R},S_{N}〉 ∈ Students ∧ 〈S_{R},107,R_{P}〉 ∈ Registration ∧ R_{P}>90)}
I, II, III and IV  
I, II and III only  
I, II and IV only  
II, III and IV only 
Question 55 
Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.
F = {CH→G, A→BC, B→CFG, E→A, F→EG}, is a set of functional dependencies (FDs) so that F^{+} is exactly the set of FDs that hold for R.
How many candidate keys does the relation R have?
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 
Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.
F = {CH→G, A→BC, B→CFG, E→A, F→EG}, is a set of functional dependencies (FDs) so that F^{+} is exactly the set of FDs that hold for R.
The relation R is
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 
Which of the following is TRUE?
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 
Given the basic ER and relational models, which of the following is INCORRECT?
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 
Which of the following statements are TRUE about an SQL query?

P:An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause.
Q:An SQL query can contain a HAVING clause even if it has a GROUP BY clause.
R: All attributes used in the GROUP BY clause must appear in the SELECT clause.
S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
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 
Consider the following transactions with data items P and Q initialized to zero:
T1: read (P) ; read (Q) ; if P = 0 then Q : = Q + 1 ; write (Q) ; T2: read (Q) ; read (P) ; if Q = 0 then P : = P + 1 ; write (P) ;
Any nonserial interleaving of T1 and T2 for concurrent execution leads to
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 
Suppose R_{1}(A, B) and R_{2}(C, D) are two relation schemas. Let r_{1} and r_{2} be the corresponding relation instances. B is a foreign key that refers to C in R_{2}. If data in r_{1} and r_{2} satisfy referential integrity constraints, which of the following is ALWAYS TRUE?
∏_{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 
Consider the following relations A, B, C.
How many tuples does the result of the following relational algebra expression contain? Assume that the schema of AUB is the same as that of A.
(AUB)⋈_{A.Id>40∨C.Id<15} C
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 
Consider the following relations A, B, C.
How many tuples does the result of the following SQL query contain?
SELECT A.id FROM A WHERE A.age > ALL (SELECT B.age FROM B WHERE B. name = "arun")
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 
Consider a relational table with a single record for each registered student with the following attributes.
 1. Registration_Num: Unique registration number
of each registered student
2. UID: Unique identity number, unique at the national level for each citizen
3. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel
Which one of the following option is INCORRECT?
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 
Consider a relational table r with sufficient number of records, having attributes A_{1}, A_{2},…, A_{n} and let 1≤p≤n. Two queries Q1 and Q2 are given below.
 Q1: π_{A1},...,A_{p}(σ_{Ap=c}(r)) where c is a constant
Q2: π_{A1},...,A_{p}(σ_{c1≤Ap≤c2}(r)) where c_{1} and c_{2} is a constants
The database can be configured to do ordered indexing on A_{p} or hashing on A_{p}. Which of the following statements is TRUE?
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 66 
Database table by name Loan_Records is given below.
Borrower Bank_Manager Loan_Amount Ramesh Sunderajan 10000.00 Suresh Ramgopal 5000.00 Mahesh Sunderajan 7000.00What is the output of the following SQL query?
SELECT Count(*) FROM ( (SELECT Borrower, Bank_Manager FROM Loan_Records) AS S NATURAL JOIN (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T );
3  
9  
5  
6 
Question 67 
Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?
SELECT Y FROM T WHERE X=7;
127  
255  
129  
257 
Question 68 
Consider a B^{+}tree in which the maximum number of keys in a node is 5. What is the minimum number of keys in any nonroot node?
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 
A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, tid)
Table: Passenger pid pname age  0 Sachin 65 1 Rahul 66 2 Sourav 67 3 Anil 69 Table : Reservation pid class tid  0 AC 8200 1 AC 8201 2 SC 8201 5 AC 8203 1 SC 8204 3 AC 8202
What pids are returned by the following SQL query for the above instance of the tables?
SELECT pid FROM Reservation , WHERE class ‘AC’ AND EXISTS (SELECT * FROM Passenger WHERE age > 65 AND Passenger. pid = Reservation.pid)
1, 0  
1, 2  
1, 3  
1, 5 
― 1, 3 Pids are returned
Question 70 
Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock?
I. 2phase locking II. TImestamp ordering
I only  
II only  
Both I and II  
Neither I nor II 
― Timestamp ordering protocol ensures conflict serializability and free from deadlock.
Question 71 
Consider the following schedule for transactions T1, T2 and T3:
Which one of the schedules below is the correct serialization of the above?
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 
Which of the following functional dependencies hold for relations R(A, B, C) and S(B, D, E):
B > A A > C
The relation R contains 200 tuples and the rel ation S contains 100 tuples. What is the maximum number of tuples possible in the natural join of R and S (R natural join S)
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 
Consider two transactions T1 and T2, and four schedules S1, S2, S3, S4 of T1 and T2 as given below:
 T1 = R1[X] W1[X] W1[Y]
T2 = R2[X] R2[Y] W2[Y]
S1 = R1[X] R2[X] R2[Y] W1[X] W1[Y] W2[Y]
S2 = R1[X] R2[X] R2[Y] W1[X] W2[Y] W1[Y]
S3 = R1[X] W1[X] R2[X] W1[Y] R2[Y] W2[Y]
S1 = R1[X] R2[Y]R2[X]W1[X] W1[Y] W2[Y]
Which of the above schedules are conflictserializable?
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 
The following key values are inserted into a B+  tree in which order of the internal nodes is 3, and that of the leaf nodes is 2, in the sequence given below. The order of internal nodes is the maximum number of tree pointers in each node, and the order of leaf nodes is the maximum number of data items that can be stored in it. The B^{+}tree is initially
10, 3, 6, 8, 4, 2, 1
The maximum number of times leaf nodes would get split up as a result of these insertions is
2  
3  
4  
5 
Insert 3:
Insert 6:
Insert 4:
Insert 2:
Insert 1:
So, total splits are 3.
Question 75 
Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database:
I. π_{RS}(r)π_{RS}(π_{RS}(r)×sπ_{RS,S}(r)) II. {tt∈ π_{RS}(r) ∧ ∀u ∈ s(∃v ∈ r(u = v[s] ∧ t = v[R  S]))} III. {tt∈ π_{RS}(r) ∧ ∀v ∈ r(∃u ∈ s(u = v[s] ∧ t = v[R  S]))} IV. Select R.a, R.b from R, S where R.c = S.c
Which of the above queries are equivalent?
I and II  
I and III  
II and IV  
III and IV 
S={c}
I. π_{RS}(r)π_{RS}(π_{RS}(r)×sπ_{RS,S(r)) 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. IV. Select R.a, R.b From R, S Where R.c = S.c This selects (a,b) from all tuples from r which has an equivalent value in S.}
Question 76 
Consider the following relational schema:
Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real)
Consider the following relational query on the above database:
SELECT S.sname FROM Suppliers S WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color <> 'blue'))
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
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 
Consider the following relational schema:
Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real)
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema??
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 
Which of the following tuple relational calculus expression(s) is/are equivalent to ∀t ∈ r(P(t))?
 I. ¬∃t ∈ r(P(t))
II. ∃t ∉ r(P(t))
III. ¬∃t ∈ r(¬P(t))
IV. ∃t ∉ r(¬P(t))
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 
A clustering index is defined on the fields which are of type
nonkey and ordering  
nonkey and nonordering  
key and ordering
 
key and nonordering

Question 80 
A Btree of order 4 is built from scratch by 10 successive insertions. What is the maximum number of node splitting operations that may take place?
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 
Let R and S be two relations with the following schema
 R(P,Q,R1,R2,R3)
S(P,Q,S1,S2)
Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?
 I. Π_{P}(R ⨝ S)
II. Π_{P}(R) ⨝ Π_{P}(S)
III. Π_{P}(Π_{P,Q}(R) ∩ Π_{P,Q}(S))
IV. Π_{P}(Π_{P,Q}(R)  (Π_{P,Q}(R)  Π_{P,Q}(S)))
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 
Consider the following relational schemes for a library database:

Book(Title, Author, Catalog_ no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)
with in the following functional dependencies:

I. Title Author → Catalog_no
II. Catalog_no → Title Author Publisher Year
III. Publisher Title Year → Price
Assume {Author, Title} is the key for both schemes. Which of the following statements is true?
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 
Consider a file of 16384 records. Each record is 32 bytes long and its key field is of size 6 bytes. The file is ordered on a nonkey field, and the file organization is unspanned. The file is stored in a file system with block size 1024 bytes, and the size of a block pointer is 10 bytes. If the secondary index is built on the key field of the file, and a multilevel index scheme is used to store the secondary index, the number of firstlevel and secondlevel blocks in the multilevel index are respectively
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 
Consider the following ER diagram
The minimum number of tables needed to represent M, N, P, R1, R2 is
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 
Consider the following ER diagram
Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?
{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 
Information about a collection of students is given by the relation studinfo(studId, name, sex). The relation enroll(studId, courseId) gives which student has enrolled for (or taken) that course(s). Assume that every course is taken by at least one male and at least one female student. What does the following relational algebra expression represent?
Π_{courseId}((Π_{studId}(σ_{sex="female"}(studInfo))×Π_{courseId}(enroll))enroll)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 87 
Consider the relation employee(name, sex, supervisorName) with name as the key. supervisorName gives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce?
{e.nameemployee(e)∧} (∀x)[¬employee(x) ∨ x.supervisorName ≠ e.name ∨ x.sex = "male"]}
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 88 
Consider the table employee(empId, name, department, salary) and the two queries Q_{1},Q_{2} below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?
Q_{1} : Select e.empId From employee e Where not exists (Select * From employee s where s.department = “5” and s.salary >= e.salary) Q_{2} : Select e.empId From employee e Where e.salary > Any (Select distinct salary From employee s Where s.department = “5”)
Q_{1} is the correct query  
Q_{2} is the correct query  
Both Q_{1} and Q_{2} produce the same answer  
Neither Q_{1} nor Q_{2} is the correct query 
Query 1: Results the empId's which have higher salary than anyone in the department 5.
Query 2: Results the empId's which have higher salary than atleast one employee of department 5.
Question 89 
Which one of the following statements if FALSE?
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 90 
The order of a leaf node in a B^{+} tree is the maximum number of (value, data record pointer) pairs it can hold. Given that the block size is 1K bytes, data record pointer is 7 bytes long, the value field is 9 bytes long and a block pointer is 6 bytes long, what is the order of the leaf node?
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 91 
Consider the following schedules involving two transactions. Which one of the following statements is TRUE?
 S_{1}: r_{1}(X); r_{1}(Y); r_{2}(X); r_{2}(Y); w_{2}(Y); w_{1}(X)
S_{2}: r_{1}(X); r_{2}(X); r_{2}(Y); w_{2}(Y); r_{1}(Y); w_{1}(X)
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 92 
Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortgage payment and then apply a 5% interest.
1. T1 start 2. T1 B old=12000 new=10000 3. T1 M old=0 new=2000 4. T1 commit 5. T2 start 6. T2 B old=10000 new=10500 7. T2 commit
Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure?
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 93 
Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. ties are not broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.
Query1: select A.customer, count(B.customer) from account A, account B where A.balance <=B.balance group by A.customer Query2: select A.customer, 1+count(B.customer) from account A, account B where A.balance < B.balance group by A.customer
Consider these statements about Query1 and Query2.
1. Query1 will produce the same row set as Query2 for some but not all databases. 2. Both Query1 and Query2 are correct implementation of the specification 3. Query1 is a correct implementation of the specification but Query2 is not 4. Neither Query1 nor Query2 is a correct implementation of the specification 5. Assigning rank with a pure relational query takes less time than scanning in decreasing balance order assigning ranks using ODBC.
Which two of the above statements are correct?
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 94 
Consider the relation "enrolled(student, course)" in which (student, course) is the primary key, and the relation "paid(student, amount)" where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries:
Query1: select student from enrolled where student in (select student from paid) Query2: select student from paid where student in (select student from enrolled) Query3: select E.student from enrolled E, paid P where E.student = P.student Query4: select student from paid where exists (select * from enrolled where enrolled.student = paid.student)
Which one of the following statements is correct?
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 95 
Consider the relation enrolled(student, course) in which (student, course) is the primary key, and the relation paid(student, amount), where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts 6000, 7000, 8000, 9000 and 10000 were each paid by 20% of the students. Consider these query plans (Plan 1 on left, Plan 2 on right) to "list all courses taken by students who have paid more than x".
A disk seek takes 4ms, disk data transfer bandwidth is 300 MB/s and checking a tuple to see if amount is greater than x takes 10 microseconds. Which of the following statements is correct?
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 96 
The following functional dependencies are given:
AB → CD, AF → D, DE → F, C → G, F → E, G → A.
Which one of the following options is false?
{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 97 
Which one of the following is a key factor for preferring B^{+} trees to binary search trees for indexing database relations?
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 98 
Which one of the following statements about normal forms is FALSE?
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 two attributes (X, Y).
If (X→Y), X is a candidate key. It is in BCNF and viceversa.
Question 99 
Let r be a relation instance with schema R = (A, B, C, D). We define r_{1} = Π_{A,B,C}(R) and r_{2} = Π_{A,D}(R). Let s = r_{1}*r_{2} where * denotes natural join. Given that the decomposition of r into r_{1} and r_{2} is lossy, which one of the following is TRUE?
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 100 
The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with ondelete cascade.
A C  2 4 3 4 4 3 5 2 7 2 9 5 6 4
The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:
(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 101 
select title from book as B where (select count(*) from book as T where T.price > B.price) < 5
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 102 
Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A → B, BC → D, E → C, D → A}. What are the candidate keys of R?
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 103 
Let R_{1}(A,B,C) 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 104 
Consider the following relation schema pertaining to a students database:
Student (rollno, name, address) Enroll (rollno, courseno, coursename)
Where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?
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 105 
The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:
name, courseNo → grade rollNo, courseNo → grade name → rollNo rollNo → name
The highest normal form of this relation scheme is
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 106 
Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: p is the rename operator).
The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"
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 107 
The order of an internal node in a B^{+} tree index is the maximum number of children it can have. Suppose that a child pointer takes 6 bytes, the search field value takes 14 bytes, and the block size is 512 bytes. What is the order of the internal node?
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 108 
The employee information in a company is stored in the relation
Employee (name, sex, salary, deptName)Consider the following SQL query
Select deptName From Employee Where sex = 'M' Group by deptName Having avg (salary) > (select avg (salary) from Employee)
It returns the names of the department in which
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 109 
Which of the following scenarios may lead to an irrecoverable error in a database system?
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 110 
Consider the following SQL query
select distinct a_{l}, a_{2},........., a_{n} from r_{l}, r_{2},........, r_{m} where P
For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?
Question 111 
Consider the following functional dependencies in a database:
Data_of_Birth → Age Age → Eligibility Name → Roll_number Roll_number → Name Course_number → Course_name Course_number → Instructor (Roll_number, Course_number) → Grade
The relation (Roll_number, Name, Date_of_birth, Age) is:
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 112 
Consider the set of relations shown below and the SQL query that follows.
Students: (Roll_number, Name, Date_of_birth) Courses: (Course number, Course_name, Instructor) Grades: (Roll_number, Course_number, Grade) select distinct Name from Students, Courses, Grades where Students. Roll_number = Grades.Roll_number and Courses.Instructor = Korth and Courses.Course_number = Grades.Course_number and Grades.grade = A
Which of the following sets is computed by the above query?
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 113 
Consider three data items D1, D2 and D3 and the following execution schedule of transactions T1, T2 and T3. In the diagram, R(D) and W(D) denote the actions reading and writing the data item D respectively.
Which of the following statements is correct?
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 114 
Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is
Zero  
More than zero but less than that of an equivalent 3NF decomposition  
Proportional to the size of F^{+}  
Indetermine 
Question 115 
With regard to the expressive power of the formal relational query languages, which of the following statements is true?
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 116 
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 117 
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 118 
From the following instance of a relation scheme R(A,B,C), we can conclude that:
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 119 
A university placement center maintains a relational database of companies that interview students on campus and make job offers to those successful in the interview. The schema of the database is given below:
COMPANY (cname, clocation) STUDENT (scrollno, sname, sdegree) INTERVIEW (cname, srollno, idate) OFFER (cname,srollno, osalary)
The COMPANY relation gives the name and location of the company. The STUDENT relation gives the student’s roll number, name and the degree program for which the student is registered in the university. The INTERVIEW relation gives the date on which a students is interviewed by a company. The OFFER relation gives the salary offered to a student who is successful in a company’s interview. The key for each relation is indicated by the underlined attributes.
(a) Write relational algebra expressions (using only the operator ⨝,σ,π,∪,−) for the following queries:
 (i) List the rollnumbers and names of those students who attended at least one interview but did not receive any job offer.
(ii) List the rollnumbers and names of students who went for interviews and received job offers from every company with which they interviewed.
(b) Write an SQL query to list, for each degree program in which more than five students were offered jobs, the name of the degree and the average offered salary of students in this degree program.
Theory Explanation is given below. 
Question 120 
For relation R = (L, M, N , O, P), the following dependencies hold:
M → O NO → P P → L and L → MN
R is decomposed into R_{1} = (L, M, N , P) and R_{2} = (M, O).
 (a) Is the above decomposition a losslessjoin decomposition? Explain.
(b) Is the above decomposition dependencypreserving? If not, list all the dependencies that are not preserved.
(c) What is the highest normal form satisfied by the above decomposition?
Theory Explanation is given below. 
Question 121 
(a) The following table refers to search times for a key in Btrees and B^{+}trees.
A successful search means that the key exists in the database and unsuccessful means that it is not present in the database. Each of the entries X_{1}, X_{2}, X_{3} and X_{4} can have a value of either Constant or Variable. Constant means that the search time is the same, independent of the specific key value, where Variable means that it is dependent on the specific key value chosen for the search.
Give the correct values for the entries X_{1}, X_{2}, X_{3} and X_{4} (for example X_{1} = Constant, X_{2} = Constant, X_{3} = Constant, X_{4} = Constant).
(b) Relation R(A,B) has the following view defined on it:
CREATE VIEW V AS (SELECT R1.A, R2.B FROM R AS R1, R AS R2 WHERE R1.B = R2.A)
(i) The current contents of relation R are shown below. What are the contents of the view V?
(ii) The tuples (2,11) and (11,6) are now inserted into R. What are the additional tuples that are inserted in V?
Theory Explanation is given below. 
Question 122 
Consider a schema R(A,B,C,D) and functional dependencies A → B and C → D. Then the decomposition of R into R_{1}(AB) and R_{2}(CD) is
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 123 
Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length?
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 124 
Let r and s be two relations over the relation schemes R and S respectively, and let A be an attribute in R. Then the relational algebra expression σ_{A=a}(r⋈s) is always equal to
σ_{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 125 
R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?
A → B, B → CD  
A → B, B → C, C → D  
AB → C, C → AD  
A → BCD 
Question 126 
Which of the following relational calculus expressions is not safe?
{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 127 
Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.
create table geq ( Ib integer not null ub integer not null primary key 1b foreign key (ub) references geq on delete cascade )
Which of the following is possible if a tuple (x,y) is deleted?
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 128 
Consider the relation examinee (regno, name, score), where regno is the primary key to score is a real number.
(a) Write a relational algebra using (∏,σ,ρ,×) to find the list of names which appear more than once in examinee.
(b) Write an SQL query to list the regno of examinees who have a score greater than the average score.
(c) Suppose the relation appears (regno, centr_code) specifies the center where an examinee appears. Write an SQL query to list the centr_code having an examinee of score greater than 80.
Theory Explanation is given below. 
Question 129 
We wish to construct a B^{+} tree with fanout (the number of pointers per node) equal to 3 for the following set of key values:
80, 50, 10, 70, 30, 100, 90
Assume that the tree is initially empty and the values are added in the order given.
(a) Show the tree after insertion of 10, after insertion of 30, and after insertion
of 90. Intermediate trees need not be shown.
(b) The key values 30 and 10 are now deleted from the tree in that order. Show
the tree after each deletion.
Theory Explanation is given below. 
Question 130 
B^{+}trees are preferred to binary trees in databases because
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 131 
Given the relations
employee (name, salary, deptno) and department (deptno, deptname, address)
Which of the following queries cannot be expressed using the basic relational algebra operations (σ, π, ×, ⋈, ∪, ∩, )?
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 132 
Given the following relation instance.
x y z 1 4 2 1 5 3 1 6 3 3 2 2
Which of the following functional dependencies are satisfied by the instance?
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 133 
Given relations r(w, x) and s(y, z), the result of
select distinct w, x from r, s
is guaranteed to be same as r, provided
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 134 
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 135 
(a) Suppose you are given an empty B^{+}tree where each node (leaf and internal) can store up to 5 key values. Suppose values 1,2,….. 10 are inserted, in order, into the tree, Show the tree pictorially
(i) After 6 insertions, and
(ii) After all 10 insertions
Do NOT show intermediate stages.
(b) Suppose instead of splitting a node when it is full, we try to move a value to the left sibling. If there is no left sibling, or the left sibling is full, we split the node. Show the tree after values, 1, 2,….., 9 have been inserted. Assume, as in (a) that each node can hold up to 5 keys.
(c) In general, suppose a B^{+}tree node can hold a maximum of m keys, and you insert a long sequence of keys in increasing order. Then what approximately is the average number of keys in each leaf level node.
(i) In the normal case, and
(ii) With the insertion as in (b).
Theory Explanation is given below. 
(i)
(ii)
(b)
Question 136 
Consider a bank database with only one relation
transaction (transno, acctno, date, amount)
The amount attribute value is positive for deposits and negative for withdrawals.
(a) Define an SQL view TP containing the information.
(acctno, T1.date, T2.amount)
for every pair of transactions T1, T2 such that T1 and T2 are transaction on the same account and the date of T2 is ≤ the date of T1.
(b) Using only the above view TP, write a query to find for each account the minimum balance it ever reached (not including the 0 balance when the account is created). Assume there is at most one transaction per day on each account, and each account has had atleast one transaction since it was created. To simply your query, break it up into 2 steps by defining an intermediate view V.
Theory Explanation is given below. 
Question 137 
Consider the join of a relation R with a relation S. If R has m tuples and S has n tuples then the maximum and minimum sizes of the join respectively are
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 138 
The relational algebra expression equivalent to the following tuple calculus expression:
{t t ∈ r ∧(t[A] = 10 ∧ t[B] = 20)} isσ_{(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 139 
Let R = (A, B, C, D, E, F) be a relation scheme with the following dependencies: C→F, E→A, EC→D, A→B. Which of the following is a key of R?
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 140 
Which of the following is correct?
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 141 
1 Read A
2 Read B
3 Write A
4 Read A
5 Write A
6 Write B
7 Read B
8 Write B
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 142 
Consider the schema R = (S T U V) and the dependencies S → T, T → U, U → V and V → S. Let R = (R1 and R2) be a decomposition such that R1 ∩ R2 ≠ ∅ . The decomposition is
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 143 
Consider the circuit shown below. In a certain steady state, the line Y is at '1'. What are the possible values of A, B and C in this state?
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 144 
Which of the following sets of component(s) is/are sufficient to implement any arbitrary Boolean function?
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 145 
Which of the following is/are correct?
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 146 
Consider a Btree with degree m, that is, the number of children, c, of any internal node (except the root) is such that m ≤ c ≤ 2m1. Derive the maximum and minimum number of records in the leaf nodes for such a Btree with height h, h≥1. (Assume that the root of a tree is at height 0.)
Theory Explanation. 
Question 147 
Consider the set of relations
EMP(Employeeno, Deptno, Employeename, Salary) DEPT(Deptno, Deptname, Location)
Write an SQL query to:
(a) Find all employee names who work in departments located at "Calcutta" and whose salary is greater than Rs. 50,000.
(b) Calculate, for each department number, the number of employees with a salary greater than Rs. 100,000.
Theory Explanation. 
Question 148 
Given two union compatible relations R_{1}(A,B) and R_{2}(C,D), what is the result of the operation R_{1}A = CAB = DR_{2}?
R_{1} ∪ R_{2}  
R_{1} × R_{2}  
R_{1}  R_{2}  
R_{1} ∩ R_{2} 
Question 149 
Which normal form is considered adequate for normal relational database design?
2 NF  
5 NF  
4 NF  
3 NF 
Question 150 
There are 5 records in a database.
There is an index file associated with this and it contain the values 1, 3, 2, 5 and 4. Which one of the fields is the index built form?
Age  
Name  
Occupation  
Category 
Question 151 
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}  
π_{A1}(σ_{C1}(R_{1})) → σ_{C1}(σ_{A1}(R_{1})) 
Question 152 
(a) Suppose we have a database consisting of the following three relations.
FREQUENTS(student, parlor) giving the parlors each student visits.
SERVES(parlor, icecream) indicating what kind of icecreams each parlor serves.
LIKES(student, icecream) indicating what icecreams each parlor serves.
(Assuming that each student likes at least one icecream and frequents at least one parlor)
Express the following in SQL:
Print the students that frequent at least one parlor that serves some icecream that they like.
(b) In a computer system where the 'bestfit' algorithm is used for allocating 'jobs' to 'memory partitions', the following situation was encountered:
When will the 20K job complete? Note  This question was subjective type.
Theory Explanation. 
Question 153 
(a) Four jobs are waiting to be run. Their expected run times are 6, 3, 5 and x. In what order should they be run to minimize the average response time?
(b) Write a concurrent program using par begin  par end to represent the precedence graph shown below.
Theory Explanation. 
Question 154 
Consider the following database relations containing the attributes
Book_id Subject_Category_of_book Name_of_Author Nationality_of_Author with Book_id as the Primary Key.
(a) What is the highest normal form satisfied by this relation?
(b) Suppose the attributes Book_title and Author_address are added to the relation, and the primary key is changed to (Name_of_Author, Book_Title), what will be the highest normal form satisfied by the relation?
Theory Explanation. 
Question 155 
Consider the following relational database schemes:
COURSES(Cno, name) PREREQ(Cno, pre_Cno) COMPLETED(student_no, Cno)
COURSES give the number and the name of all the available courses.
PREREQ gives the information about which course are prerequisites for a given course.
COMPLETED indicates what courses have been completed by students.
Express the following using relational algebra:
List all the courses for which a student with student_no 2310 has completed all the prerequisites.
Theory Explanation. 
Question 156 
For a database relation R(a,b,c,d), where the domains a, b, c, d include only atomic values, only the following functional dependencies and those that can be inferred from them hold:
a → c b → d
This relation is
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 157 
Let R(a,b,c) and S(d,e,f) be two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations R and S
(a) Insert into R (b) Insert into S (c) Delete from R (d) Delete from S
Which of the following can cause violation of the referential integrity constraint above?
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 158 
A library relational database system uses the following schema
USERS (User#, UserName, HomeTown) BOOKS (Book#, BookTitle, AuthorName) ISSUED (Book#, User#, Date)
Explain in one English sentence, what each of the following relational algebra queries is designed to determine
(a) σ User #=6 (11 User #, Book Title ((USERS ISSUED) BOOKS)) (b) σ Author Name (BOOKS (σ Home Town) = Delhi (USERS ISSUED)))
Theory Explanation. 
Question 159 
(a) Consider the relation scheme R(A, B, C) with the following functional dependencies:
A, B → C, C → A
Show that the scheme R is the Third Normal Form (3NF) but not in BoyceCode Normal Form (BCNF).
(b) Determine the minimal keys of relation R.
Theory Explanation. 
Question 160 
Consider the relation scheme.
AUTHOR (ANAME, INSTITUTION, ACITY, AGE) PUBLISHER (PNAME, PCITY) BOOK (TITLE, ANAME, PNAME)
Express the following queries using (one or more of )SELECT, PROJECT, JOIN and DIVIDE operations.
(a) Get the names of all publishers.
(b) Get values of all attributes of all authors who have published a book for the
publisher with PNAME = ‘TECHNICAL PUBLISHERS’.
(c) Get the names of all authors who have published a book for any publisher located in Madras.
Theory Explanation. 
Question 161 
State True or False with reason
There is always a decomposition into BoyceCodd normal form (BCNF) that is
lossless and dependency preserving.
True  
False 
Question 162 
An instance of a relational scheme R(A, B, C) has distinct values for attribute A.
Can you conclude that A is a candidate key for R?
Yes  
No 
Question 163 
Give a relational algebra expression using only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.
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 164 
State True or False with reason
Logical data independence is easier to achieve than physical data independence
True  
False 
Question 165 
Consider the following relational schema:
COURSES (cno, cname) STUDENTS (rollno, sname, age, year) REGISTERED FOR (cno, rollno)
(a) Write a relational algebra query to
Print the roll number of students who have registered for cno 322.
(b) Write a SQL query to
Print the age and year of the youngest student in each year.
Theory Explanation. 
Question 166 
Consider B+ − tree of order d shown in figure? (A) B^{+} − tree of order d contains between d and 2d keys in each node. (a) Draw the resulting B^{+} − tree after inserted in the figure.
(b) For a B^{+} − tree of order d with n leaf nodes, the number of nodes accessed during a search is 0().
Theory Explanation. 
Question 167 
Consider a relational database containing the following schemas.
The primary key of each table is indicated by underlying the constituent fields.
SELECT s.sno, s.sname FROM Suppliers s, Catalogue c WHERE s.sno = c.sno AND Cost > (SELECT AVG (cost) FROM Catalogue WHERE pno = ‘P4’ GROUP BY pno);
The number of rows returned by the above SQL query is
0  
5  
4  
2 
AVG(COST)

225
The outer query “select s.sno, s.sname from suppliers s, catalogue c where s.sno=c.sno” returns:
SNO SNAME

S1 M/s Royal furniture
S1 M/s Royal furniture
S1 M/s Royal furniture
S2 M/s Balaji furniture
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
So, the final result of the query is:
SN SNAME

S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
Therefore, 4 rows will be returned by the query.
Question 168 
Which one of the following is used to represent the supporting manyone relationships of a weak entity set in an entityrelationship diagram?
Ovals that contain underlined identifiers
 
Rectangles with double/bold border  
Diamonds with double/bold border
 
Ovals with double/bold border

Question 169 
Consider a schedule of transactions T_{1} and T_{2}:
Here, RX stands for “Read(X)” and WX stands for “Write(X)”. Which one of the following schedules is conflict equivalent to the above schedule?
• First, let’s list the conflict operations of each of the schedule given in the options and compare with the conflict operations of schedule which is given in the question.
Given schedule:
Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R1(C) → W2(C)
R2(D) → W1(D)
Option(1):
Conflict operations:
R1(C) → W2(C)
W1(D) → R2(D)
W1(B) → R2(B)
W1(B) → W2(B)
Option(2):
Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R2(D) → W1(D)
R1(C) → W2(C)
Option(3):
Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R2(D) → W1(D)
W2(C) → R1(C)
Option(4):
Conflict operations:
R1(C) → W2(C)
W1(D) → R2(D)
R2(B) → W1(B)
W2(B) → W1(B)
The conflict operations in the option (2) and given schedule are appearing in the same sequence order, so option (2) is the answer.
Question 170 
Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?
A cell in R holds a set instead of an atomic value.  
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a nonprime attribute and X is not a proper subset of any key.
 
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a nonprime attribute and X is a proper subset of some key.  
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute. 
FDs:
AB → C
BC → A
(BD)^{+} = BD ✖
(ABD)^{+} = ABDC ✔
(CBD)^{+} = CBDA ✔
Candidate keys = {ABD, CBD}
• The relation R is in 3NF, as there are no transitive dependencies.
• The relation R is not in BCNF, because the left side of both the FD’s are not Super keys.
• In R, BC → A is a nontrivial FD and in which BC is not a Super key and A is a prime attribute.
Question 171 
Consider a database implemented using B+ tree for file indexing and installed on a disk drive with block size of 4 KB. The size of search key is 12 bytes and the size of tree/disk pointer is 8 bytes. Assume that the database has one million records. Also assume that no node of the B+ tree and no records are present initially in main memory. Consider that each record fits into one disk block. The minimum number of disk accesses required to retrieve any record in the database is ______.
4 
(1) Database BF = 1
No. of block = 10^{6} } ➝ 1 block access from database
(2) ⎡10^{6}/204⎤ = 491
(3) ⎡491/204⎤ = 3
(4) ⎡3/204⎤ = 1
So, 1+3 = 4 disk accesses are required to retrieve any record in the database.
Question 172 
The following relations are used to store data about students, courses, enrollment of students in courses and teachers of courses. Attributes for primary key in each relation are marked by ‘*’.
Students (rollno*, sname, saddr) courses (cno*, cname) enroll(rollno*, cno*,grade) teach(tno*,tname,cao*)
(cno is course number, cname is course name, tno is teacher number, tname is teacher name, sname is student name, etc.)
Write a SQL query for retrieving roll number and name of students who got A grade in at least one course taught by teacher named Ramesh for the above relational database.
Theory Explanation. 
Question 173 
The following relations are used to store data about students, courses, enrollment of students in courses and teachers of courses. Attributes for primary key in each relation are marked by ‘*’.
Students (rollno*, sname, saddr) courses (cno*, cname) enroll(rollno*, cno*,grade) teach(tno*,tname,cao*)
(cno is course number, cname is course name, tno is teacher number, tname is teacher name, sname is student name, etc.)
For the relational database given above, the following functional dependencies hold:
rollno → sname,sdaddr cno → cname tno → tname rollno, cno → grade
(a) Is the database in 3^{rd} normal form (3NF)?
(b) If yes, prove that it is in 3 NF. If not, normalize, the relations so that they
are in 3 NF (without proving).
Theory Explanation. 
Question 174 
(a) How is redundancy reduced in the following models?
(i) Hierarchical (ii) Network (iii) Relational
Write a one line answer in each case.
(b) Suppose we have a database consisting of the following three relations:
FREQUENTS (CUSTOMER, HOTEL) SERVES (HOTEL, SNACKS) LIKES (CUSTOMER, SNACKS)
The first indicates the hotels each customer visits, the second tells which snacks each hotel serves and the last indicates which snacks are liked by each customer. Express the following query in relational algebra: print the hotels that serve a snack that customer Rama likes.
Theory Explanation. 
Question 175 
Suppose, a database consists of the following relations:
SUPPLIER (SCODE, SNAME, CITY) PART (PCODE, PNAME, PDESC, CITY) PROJECTS (PRCODE, PRNAME, CITY) SPRR (SCODE, PCODE, PRCODE, QJY)
(a) Write SQL programs corresponding to the following queries:
(i) Print PCODE value for parts supplied to any project in DELHI by a supplier in DELHI.
(ii) Print all triples (CITY, PCODE, CITY), such that a supplier in the first city supplies the specified part to a project in the second city, but do not print triples in which the two CITY values are the same.
(b) Write algebraic solutions to the following:
(i) Get SCODE values for suppliers who supply to both projects PR1 and PR2.
(ii) Get PRCODE values for projects supplied by at least one supplier not in the same city.
Theory Explanation. 
Question 176 
Match the pairs in the following questions:
(a) Secondary index (p) Function dependency (b) Nonprocedural query language (q) Btree (c) Closure of a set of attributes (r) Domain calculus (d) Natural join (s) Relational algebraic operations </pre
(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 177 
Choose the correct alternatives (More than one may be correct).
Indicate which of the following statements are true: A relational database which is in 3NF may still have undesirable data redundancy because there may exist:
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 178 
For secondary key processing which of the following file organizations is preferred? Give a one line justification:
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.
Question 179 
Let R (A, B, C, D) be a relational schema with the following functional dependencies: A → B, B → C, C → D and D → B.
The decomposition of R into (A, B), (B, C), (B, D)
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 180 
Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional dependencies are known to hold:
AB → CD, DE → P, C → E, P → C and B → G.The relational schema R is
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 181 
Consider the following three schedules of transactions T1, T2 and T3. [Notation: In the following NYO represents the action Y (R for read, W for write) performed by transaction N on object O.]
(S1) 2RA 2WA 3RC 2WB 3WA 3WC 1RA 1RB 1WA 1WB (S2) 3RC 2RA 2WA 2WB 3WA 1RA 1RB 1WA 1WB 3WC (S3) 2RA 3RC 3WA 2WA 2WB 3WC 1RA 1RB 1WA 1WBWhich of the following statements is TRUE?
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 182 
Consider the following relational schema:
Student (schoolid, schrollno, sname, saddress) School (schoolid, schname, schaddress, schphone) Enrolment(schoolid, schrollno, erollno, examname) ExamResult(erollno, examname, marks)
What does the following SQL query output?
SELECT schname, COUNT (*) FROM School C, Enrolment E, ExamResult R WHERE E.schoolid = C.schoolid AND E.examname = R.examname AND E.erollno = R.erollno AND R.marks = 100 AND S.schoolid IN (SELECT schoolid FROM student GROUP BY schoolid HAVING COUNT (*) > 200) GROUP By schoolid
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 183 
Consider the following relational schema:
Student (schoolid, schrollno, sname, saddress) School (schoolid, schname, schaddress, schphone) Enrolment(schoolid, schrollno, erollno, examname) ExamResult(erollno, examname, marks)
Consider the following tuple relational calculus query:
If a student needs to score more than 35 marks to pass an exam, what does the query return?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 ∈ Enrollment ∧ x . schoolid = t }  * 100 > 35 }
This is school with enrollment % is 35 or above.
As we are actually taking percentage of
(Total count in which a student has passed from a particular school)/(Total exams taken by all student combined)
Eg: if A passed in 3 and B passed in 4 and they both took 55 exam each. Then it is (7/10)
Question 184 
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 type 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 185 
Consider the following two transactions: T_{1} and T_{2}.
T_{1}: read(A); read(B); if A=0 then B ← B+1; write(B); T_{2}: read(B); read(A); if B≠0 then A ← A1; write(A);Which of the following schemes, using shared and exclusive locks, satisfy the requirements for strict two phase locking for the above transactions?
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 186 
Consider the following implications relating to functional and multivalued dependencies given below, which may or may not be correct.
(i) If A ↠ B and A ↠ C then A → BC
(ii) If A → B and A → C then A ↠ BC
(iii) If A ↠ BC and A → B then A → C
(iv) If A → BC and A → B then A ↠ C
Exactly how many of the above implications are valid?
0  
1  
2  
3 
if A→B then A→→B holds but reverse is not true.
So,
(i) False.
(ii) True.
(iii) False.
(iv) True.
Question 187 
Consider the following relation schemas:
bSchema = (bname, bcity, assets)
aSchema = (anum, bname, bal)
dSchema = (cname, anumber)
Let branch, account and depositor be respectively instances of the above schemas. Assume that account and depositor relations are much bigger than the branch relation.
Consider the following query:
П_{cname} (σ_{bcity = "Agra" ⋀ bal < 0} (branch ⋈ (account ⋈ depositor)
Which one of the following queries is the most efficient version of the above query?
П_{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 188 
Consider the B^{+} tree in the adjoining figure, where each node has atmost two keys and three links.
Keys K15 and then K25 are inserted into this tree in that order. Exactly how many of the following nodes (disregarding the links) will be present in the tree after the two insertions?
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 189 
Consider the B^{+} tree in the adjoining figure, where each node has atmost two keys and three links.
Now the key K50 is deleted from the 8 tree resulting after the two insertions made earlier. Consider the following statements about the B tree resulting after this deletion.
(i) The height of the tree remains the same.
(ii) The node (disregarding the links) is present in the tree.
(iii) The root node remains unchanged (disregarding the links).
Which one of the following options is true?
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 190 
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 191 
Which of the following relational query languages have the same expressive power?
1. Relational algebra
2. Tuple relational calculus restricted to safe expressions
3. Domain relational calculus restricted to safe expressions
2 and 3 only  
1 and 2 only  
1 and 3 only  
1, 2 and 3 
Question 192 
Consider a relation R with five attributes V, W, X, Y, and Z. The following functional dependencies hold: VY → W, WX → Z, and ZY → V.
Which of the following is a candidate key for R?
VXZ  
VXY  
VWXY  
VWXYZ 
Candidate keys are
VXY, WXY, ZXY
Question 193 
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 194 
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available
What is the output of the following SQL query?
select D.dname from Drivers D where D.did in ( select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'red' intersect select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'green' )
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 195 
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available
Let n be the number of comparisons performed when the above SQL query is optimally executed. If linear search is used to locate a tuple in a relation using primary key, then n lies in the range
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 196 
Consider the entities 'hotel room', and 'person' with a many to many relationship 'lodging' as shown below:
If we wish to store information about the rent payment to be made by person (s) occupying different hotel rooms, then this information should appear as an attribute of
Person  
Hotel Room  
Lodging  
None of theseThe information should appear as an attribute of lodging, because this is the only common attribute that relating to the hotel room and person. The information should appear as an attribute of lodging, because this is the only common attribute that relating to the hotel room and person. 
Question 197 
A table has fields Fl, F2, F3, F4, F5 with the following functional dependencies
F1 → F3, F2→ F4, (F1.F2) → F5
In terms of Normalization, this table is in
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 198 
A BTree used as an index for a large database table has four levels including the root node. If a new key is inserted in this index, then the maximum number of nodes that could be newly created in the process are:
5  
4  
3  
2 
Here, the tree has 4 levels, then 4+1=5 nodes to be present in the newly created process.
Question 199 
Amongst the ACID properties of a transaction, the 'Durability' property requires that the changes made to the database by a successful transaction persist
Except in case of an Operating System crash  
Except in case of a Disk crash  
Except in case of a power failure  
Always, even if there is a failure of any kind 
Question 200 
A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema:
salesinfo = (salespersonid, totalsales, commission)
In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula:
If commission < = 50000, enhance it by 2% If 50000 < commission < = 100000, enhance it by 4% If commission > 100000, enhance it by 6%
The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows:
T1: Update salesinfo Set commission = commission * 1.02 Where commission < = 50000; T2: Update salesinfo Set commission = commission * 1.04 Where commission > 50000 and commission is < = 100000; T3: Update salesinfo Set commission = commission * 1.06 Where commission > 100000;Which of the following options of running these transactions will update the commission of all salespersons correctly
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 201 
A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below:
Table: Student ROLL NAME HOSTEL MARKS 1798 Manoj Rathod 7 95 2154 Soumic Banerjee 5 68 2369 Gumma Reddy 7 86 2581 Pradeep Pendse 6 92 2643 Suhas Kulkarni 5 78 2711 Nitin Kadam 8 72 2872 Kiran Vora 5 92 2926 Manoj Kunkalikar 5 94 2959 Hemant Karkhanis 7 88 3125 Rajesh Doshi 5 82 Table: hobby ROLL HOBBYNAME 1798 chess 1798 music 2154 music 2369 swimming 2581 cricket 2643 chess 2643 hockey 2711 volleyball 2872 football 2926 cricket 2959 photography 3125 music 3125 chess
The following SQL query is executed on the above tables:
select hostel from student natural join hobby where marks >= 75 and roll between 2000 and 3000;
Relations S and H with the same schema as those of these two tables respectively contain the same information as tuples. A new relation S’ is obtained by the following relational algebra operation:
S’ = ∏_{hostel} ((σ_{s.roll = H.roll}(σ_{marks > 75 and roll > 2000 and roll < 3000} (S)) X (H))
The difference between the number of rows output by the SQL statement and the number of tuples in S’ is
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 202 
In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items along with the stocklevel of these items.
Supply = (supplierid, itemcode)
Inventory = (itemcode, warehouse, stocklevel)
For a specific information required by the management, following SQL query has been written
Select distinct STMP.supplierid From Supply as STMP Where not unique (Select ITMP.supplierid From Inventory, Supply as ITMP Where STMP.supplierid = ITMP.supplierid And ITMP.itemcode = Inventory.itemcode And Inventory.warehouse = 'Nagpur');For the warehouse at Nagpur, this query will find all suppliers who
do not supply any item  
supply exactly one item  
supply one or more items  
supply two or more items 
Question 203 
In a schema with attributes A, B, C, D and E following set of functional dependencies are given
A → B A → C CD → E B → D E → AWhich of the following functional dependencies is NOT implied by the above set?
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 204 
A database table T1 has 2000 records and occupies 80 disk blocks. Another table T2 has 400 records and occupies 20 disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records from these two tables. The memory buffer space available can hold exactly one block of records for T1 and one block of records for T2 simultaneously at any point in time. No index is available on either table.
If Nestedloop join algorithm is employed to perform the join, with the most appropriate choice of table to be used in outer loop, the number of block accesses required for reading the data are
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 205 
A database table T1 has 2000 records and occupies 80 disk blocks. Another table T2 has 400 records and occupies 20 disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records from these two tables. The memory buffer space available can hold exactly one block of records for T1 and one block of records for T2 simultaneously at any point in time. No index is available on either table.
If, instead of Nestedloop join, Block nestedloop join is used, again with the most appropriate choice of table in the outer loop, the reduction in number of block accesses required for reading the data will be
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 206 
Which level of locking provides the highest degree of concurrency in a relational database?
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 207 
Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.
The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key attribute and A23 is a multivalued attribute. Relation R does not have any attribute. A relational database containing minimum number of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of tables in the database is
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 208 
A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:
Insert into department values (1, 'Mathematics') Insert into department values (2, 'Physics') Insert into student values (l, 'Navin', 1) Insert into student values (2, 'Mukesh', 2) Insert into student values (3, 'Gita', 1)How many rows and columns will be retrieved by the following SQL statement?
Select * from student, department
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 209 
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 210 
A table T1 in a relational database has the following rows and columns:
roll no. marks 1 10 2 20 3 30 4 NullThe following sequence of SQL statements was successfully executed on table T1.
Update T1 set marks = marks + 5 Select avg(marks) from T1What is the output of the select statement?
18.75  
20  
25  
NULL 
(15+25+35)/3 = 25
Question 211 
Consider two tables in a relational database with columns and rows as follows:
Table: Student ROLL_NO NAME DEPT_ID 1 ABC 1 2 DEF 1 3 GHI 2 4 JKL 3 Table: Department DEPT_ID DEPT_NAME 1 A 2 B 3 CRoll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id.
What will happen if we try to execute the following two SQL statements?
(i) update Student set Dept_id = Null where Roll_on = 1 (ii) update Department set Dept_id = Null where Dept_id = 1
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 212 
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 213 
What does the above expression generate?
Employee numbers of all employees whose age is not the maximum.  
Employee numbers of only those employees whose age is the maximum.  
Employee numbers of all employees whose age is not the minimum.  
Employee numbers of only those employees whose age is more than the age of exactly one other employee. 
Question 214 
S_{1}: r_{1}(x) r_{1}(y) r_{2}(x) r_{2}(y) w_{2}(y) w_{1}(x)
S_{2}: r_{1}(x) r_{2}(x) r_{2}(y) w_{2}(y) r_{1}(y) w_{1}(x)
Which one of the following options is correct?
S_{1}is not conflict serializable, and S_{2} is conflict serializable.  
Neither S_{1}nor S_{2}is conflict serializable.
 
Both S_{1}and S_{2}are conflict serializable.  
S_{1}is conflict serializable, and S_{2}is not conflict serializable. 
Question 215 
S1 is false and S2 is true.  
Both S1 and S2 are false.  
Both S1 and S2 are true.
 
S1 is true and S2 is false. 
 A database table may have more than one foreign key, and each foreign key can have a different parent table. Hence, the statement I is incorrect.
 A foreign key is a set of attributes in a table that refers to the primary key of another table or to the primary key of the same table (selfreferential table). Hence, the statement II is also incorrect.
Question 216 
Consider the decomposition of the relation R into the consistent relations according to the following two decomposition schemes.
D_{1}: R=[(P,Q,S,T); (P,T,X); (Q,Y); (Y,Z,W)]
D_{2}: R=[(P,Q,S);(T,X);(Q,Y);(Y,Z,W)]
Which one of the following options is correct?
D^{1}is a lossy decomposition, but D^{2}is a lossless decomposition.
 
Both D^{1}and D^{2}are lossy decompositions.  
Both D^{1}and D^{2}are lossless decompositions.  
D^{1}is a lossless decomposition, but D^{2}is a lossy decomposition. 
Given functional dependencies set:
PQ>X
P>YX
Q>Y
Y>ZW
 While merging the tables there should be some common attribute(s) and it should be a candidate key of one of the tables.
 R1 should be merged with R2 because PT is a key of R2.
 R3 should be merged with PQSTX because Q is a key of R3.
 R4 should be merged with PQSTXY because Y is a key of R4.
 R1 should be merged with R3 because Q is a key of R3.
 R4 should be merged with PQSY because Y is a key of R4.
 Now, there is no common attribute in between R2(TX) and PQSYZW.
 Hence, D2 is lossy decomposition.
Question 217 
The same undo and redo list will be used while recovering again.  
The database will become inconsistent.  
The system cannot recover any further.  
All the transactions that are already undone and redone will not be recovered again. 
Question 218 
820 
Explanation :
Probability of 1st condition being satisfied(say P(A)) = 10/15 = 2/3
Probability of 2nd condition being satisfied(say P(B)) = 1/20
Probability of both conditions being satisfied(say P(A intersection B)) = 2/3*1/20 = 1/30
Probability of any one condition being satisfied = P(A union B) = P(A)+P(B)P(A intersection B) = 2/3 + 1/20  1/30 = 41/60
therefore, expected number of tuples = (41/60)*1200 = 820
Question 219 
698 
Total no. of records = 150000
Block size = 4096 bytes
Key size = 12 bytes
Record pointer size = 7 bytes
Question 220 
R_{2}(Y), R_{1}(X), R_{3}(Z), R_{1}(Y), W_{1}(X), R_{2}(Z), W_{2}(Y), R_{3}(X), W_{3}(Z)
Consider the statements P and Q below:
P: S is conflictserializable.
Q: If T_{3}commits before T_{1}finishes, then S is recoverable.
Which one of the following choices is correct?
P is true and Q is false.  
Both P and Q are false.  
Both P and Q are true.  
P is false and Q is true. 
Question 221 
emp(empId, name, gender, salary, deptId)
Consider the following SQL query:
select deptId, count (*)
from emp
where gender = “female” and salary > (select avg(salary) from emp)
group by deptId;
The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of
employees in the department  
female employees in the company  
employees in the company  
female employees in the department 
The given SQL query is:
Select deptId, count(*)
from emp
where gender = “female” and
group by deptId
 The inner query will return the average salary of the emp table.
 Hence, the given query will return the deptId wise count of female employees whose salary is greater than the average salary of the emp table.
Question 222 
P⟶ QR
RS⟶ T
Which of the following functional dependencies can be inferred from the above functional dependencies?
PS ⟶ Q  
PS ⟶ T  
R ⟶ T  
P ⟶ R 
Question 223 
A relation with only two attributes is always in BCNF.  
If all attributes of a relation are prime attributes, then the relation is in BCNF.  
Every relation has at least one nonprime attribute.  
BCNF decompositions preserve functional dependencies. 
Example:
R(A, B).
Two functional dependencies possible for the relation: (1) A>B and (2) B>A
If there is no functional dependency, we can assume trivial functional dependencies like AB>A and AB>B.
In all cases, functional dependencies like A>B, A must be a key.
So they all will be in BCNF irrespective of the functional depencies set.
Question 224 
Employee ( eId , Name ), Brand ( bId , bName ), Own ( eId , bId )
Which of the following relational algebra expressions return the set of elds who own all the brands?
In relational algebra, divide (/) is not a basic operator and it can be derived from the basic operators. In option (B), the divide operator is derived using basic operators and which is equivalent to option (A)
Question 225 
AB → C; BC → D; C → E;
The number of superkeys in the relation R is _____________.
8 
AB → C
BC → D
C → E
¯ The attributes A, B are not there in the right hand side of any of the given FDs.
¯ So, AB can be a candidate key.
(AB)^+ = ABCDE
¯ (AB)^+ is deriving all the attributes of R Hence, AB is a candidate key.
¯ The number of super keys possible for R with “AB” candidate kay is:
2^5 – 2 = 2^3
= 8
Question 226 
Precedence Graph
T1 → T3 → T4 → T2
Question 227 
Student(sNo, sName, dNo) Dept(dNo, dName)
Course(cNo, cName, dNo)
Register(sNo, cNo)
The number of rows returned by the above SQL query is___________.
2 
+++
 sNo  sName  dNo 
++++
 S01  James  D01 
 S04  Jane  D01 
++++
Question 228 
5  
4  
3  
10 
→ If there are K searchkey values in the file, the path is no longer than ⌈log⌈n/2⌉(K)⌉.
→ A node is generally the same size as a disk block, typically 4 kilobytes, and n is typically around 100 (40 bytes per index entry).
→ With 1 million search key values and n = 100, at most log50(1000000)=4 nodes are accessed in a lookup.
Note: Contrast this with a balanced binary free with 1 million search key values around 20 nodes are accessed in a lookup. Above difference is significant since every node access may need a disk I/O, costing around 20 milliseconds!
Question 229 
Primary index  
Clusters index  
Secondary index  
secondary nonkey index 
→ In the sparse index, index records are not created for every search key. An index record here contains a search key and an actual pointer to the data on the disk. To search a record, we first proceed by index record and reach the actual location of the data. If the data we are looking for is not where we directly reach by following the index, then the system starts a sequential search until the desired data is found.
→ The secondary index usually dense.
→ In the dense index, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. Index records contain search key value and a pointer to the actual record on the disk.
Question 230 
If X is deleted, then Y is also deleted  
If Y is deleted, then X is also deleted  
If Y is deleted, then X is not deleted  
None of the above 
→ If we want to delete any entity in the dominant entity(Like primary key of a table) then subordinate entity(derived attribute) also deleted.
Note: If we want to delete any subordinate entity in entity then the dominant entity is not going to delete.
Question 231 
{Last Name}  
{Room}  
{Shift}  
{Room, Shift} 
→ In the above table, Room having duplicate values. So, we can’t say room is candidate key
→ The last name also having duplicates. So, we can’t say the last name is candidate key.
→ Shift also having duplicate keys, so we can’t say shift also a candidate key.
→ Combining Room and Shift we can say that candidate key.
Question 232 
CASCADE & MVD  
GRANT & REVOKE  
QUE & QUIST  
None of these 
Examples of DCL commands:
GRANTgives user’s access privileges to database.
REVOKEwithdraw user’s access privileges given by using the GRANT command.
Question 233 
Avg  
Select  
Ordered by  
distinct 
Syntax:
SELECT AVG(column_name) FROM table_name;
Question 234 
Schema  
Subschema  
Virtual table  
None of these 
Question 235 
Reflexivity  
Augmentation  
Transitivity  
Mutual dependency 
Axiom of Reflexivity:
This axiom states: if Y is a subset of X, then X determines Y
Axiom of Augmentation:
The axiom of augmentation, also known as a partial dependency, states if X determines Y, then XZ determines YZ, for any Z
Axiom of Transitivity:
The axiom of transitivity says if X determines Y, and Y determines Z, then X must also determine Z.
Question 236 
Project  
Join  
Extract  
Substitute 
Projection is used to project required column data from a relation. By Default projection removes duplicate data.
Example :
R(A B C)

1 2 4
2 2 3
3 2 3
4 3 4
π (BC)
B C

2 4
2 3
3 4
Question 237 
Two (or more) candidate keys  
Two candidate keys and composite  
The candidate key overlap  
Two mutually exclusive foreign keys 
Transformation into BoyceCodd normal form deals with the problem of overlapping keys.
Question 238 
Ensures serializability  
Prevents Deadlock  
Detects Deadlock  
Recover from Deadlock 
It ensures serializability but does not ensures freedom from deadlock.
Question 239 
First normal form but not in second normal form  
Second normal form but not in third normal form  
Third normal form  
None of the above 
→ So option A is right it is not in second normal form because its part of key(candidate key) determines nonkey.
→ The domains of a, b, c and d include only atomic values.
So, which satisfies the definition of first normal form.
Question 240 
Students : (Roll number, Name, Date of birth)
Courses: (Course number, Course name, instructor)
Grades: (Roll number, Course number, Grade)
SELECT DISTINCT Name
FROM Students, Courses, Grades
WHERE Students.Roll_number = Grades.Roll_number
AND Courses.Instructor =Sriram
AND Courses.Course_number = Grades.Course_number
AND Grades.Grade = A
Which of the following sets is computed by the above query?
Names of Students who have got an A grade in all courses taught by Sriram  
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 Sriram  
None of the above 
→ The above query they are using AND command, it means it satisfy all conditions.
Question 241 
SELECT DISTINCT w, x
FROM R, S
Is guaranteed to be the same as R, if
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 
→ S in nonempty if S is empty then R*S becomes empty.
Question 242 
Physical Data Independence  
Logical Data Independence  
Both (a) and (b)  
None of the above 
The ability to change the Conceptual (Logical) schema without changing the External schema (User View) is called logical data independence. For example, the addition or removal of new entities, attributes, or relationships to the conceptual schema or having to rewrite existing application programs.
→ Physical data independence:
The ability to change the physical schema without changing the logical schema is called physical data independence. For example, a change to the internal schema, such as using different file organization or storage structures, storage devices, or indexing strategy, should be possible without having to change the conceptual or external schemas.
Note: Immunity is when data at one layer is changed, it does not affect the data at another level.
Question 243 
X is functionally dependent on Y  
X is not functionally dependent on any subset of Y  
Both (a) and (b)  
None of these 
→ An attribute is fully functional dependent on another attribute, if it is Functionally Dependent on that attribute and not on any of its proper subset.
For example, an attribute X is fully functional dependent on another attribute Y, if it is Functionally Dependent on X and not on any of the proper subset of Y.
Must satisfied conditions:
i) X is functionally dependent on Y and
ii) X is not functionally dependent on any subset of Y.
Question 244 
S=r1(A); r2(B) ; w2(A); w1(B)
Which of the following is true?
Allowed under basic timestamp protocol.  
Not allowed under basic timestamp protocols because T1 is rolled back  
Not allowed under basic timestamp protocols because T2 is rolled back  
None of these 
→ There are 2 conflicting actions a and b is shown in above diagram.
→ In timestamp ordering protocol, conflicting actions in ascending order timestamps are allowed i.e 'a' is allowed but not 'b'.
→ So we need to roll back T1 after that only it will be allowed. Because of all conflicting actions in ascending order timestamps in below diagram.
Question 245 
Field names  
Field Formats  
Field Types  
All of these 
→ Oracle defines it as a collection of tables with metadata. The term can have one of several closely related meanings pertaining to databases and database management systems (DBMS)
1. A document describing a database or collection of databases
2. An integral component of a DBMS that is required to determine its structure
3. A piece of middleware that extends or supplants the native data dictionary of a DBMS
Question 246 
Timestamp ordering  
2 Phase Locking  
Both (a) and (b)  
None of the above 
→ Timestamp ordering protocol ensures conflict serializability and free from deadlock.
Question 247 
Atomicity, consistency, isolation, database  
Atomicity, consistency, isolation, durability  
Atomicity, consistency, integrity, durability  
Atomicity, consistency, integrity, database 
→ Responsible for Transaction Manager
Consistency: Database should be consistent before and after the execution of the transaction
→ Responsible for user/application manager
Isolation: Each transaction Tie must be executed without knowing what is happening with other transactions. Responsible for Concurrency control manager
Durability: All updates done by a transaction must become permanent.
→ Responsible for recovery manager
Question 248 
What is the output of the following SQL query?
select count(*) from ((select Employee, Department from Overtime_allowance) as S
natural join (select Department, OT_allowance from Overtime_allowance) as T);
16  
4  
8  
None of the above 
Common attributes in both the table column are the department. So, we apply natural join, it will give the output as common tuples in both the table S and R.
Question 249 
Double ellipse  
Dashed ellipse  
Squared ellipse  
An ellipse with attribute name underlined 
Example:
Question 250 
a cartesian product of two relations followed by a selection  
a cartesian product of two relations  
a union of two relations followed by cartesian product of the two relations  
a union of two relations 
→ The join operation can be defined as a cartesian product of two relations followed by a selection.
→ A SQL JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Different Types of SQL JOINs
1. INNER JOIN: Returns records that have matching values in both tables
2. LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
3. RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
4. FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
Question 251 
5  
4  
1  
2 
Here, the tree has 4 levels, then 4+1=5 nodes to be present in the newly created process.
Question 252 
Sailors(sid, sname, rating, age) with the following data
For the query
SELECT S.rating, AVG(S.age) AS average FROM Sailors S
Where S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT(*) FROM Sailors S2 where S.rating = S2.rating)
The number of rows returned is
6  
5  
4  
3 
Question 253 
Linear hashing  
Extendible hashing  
B+ Tree  
Bitmapped hashing 
→ Bitmap indexes have traditionally been considered to work well for lowcardinality columns, which have a modest number of distinct values, either absolutely, or relative to the number of records that contain the data.
→ The extreme case of low cardinality is Boolean data (e.g., does a resident in a city have internet access?), which has two values, True and False.
→ Bitmap indexes use bit arrays (commonly called bitmaps) and Solution queries by performing bitwise logical operations on these bitmaps. Bitmap indexes have a significant space and performance advantage over other structures for query of such data.
→ Their drawback is they are less efficient than the traditional Btree indexes for columns whose data is frequently updated: consequently, they are more often employed in readonly systems that are specialized for fast query  e.g., data warehouses, and generally unsuitable for online transaction processing applications.
Question 254 
m + n & 0  
mn & 0  
m + n &  m – n   
mn & m + n 
If there is common attribute in R and S, and every row of R match with every row of S then total no. of tuples will be mn.
For minimum:
If there is no common attribute between R and S or if there is common attribute but none of the row of R matches with rows of S then output tuples will be 0.
Question 255 
Both I and IV  
Both II and III  
All of these  
None of these 
Question 256 
select title
from book as B
where (select count(*)
from book as T
where T.price>B.price)<5
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 257 
avoiding data inconsistency  
being able to construct query easily  
being able to access data efficiently  
All of the above 
1. Avoiding data inconsistency
2. Able to construct query easily
3. Able to access data efficiently
Question 258 
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 259 
Statement that enables to start any DBMS  
Statement that is executed by the user when debugging an application program  
The condition that the system tests for the validity of the database user  
Statement that is executed automatically by the system as a side effect of a modification of the database 
→ The trigger is mostly used for maintaining the integrity of the information on the database.
→ For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.
→Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.
Question 260 
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 261 
nonkey and ordering  
nonkey and nonordering  
key and ordering  
key and nonordering 
1. Primary index(Sparse): Ordered with key field
2. Clustered index(Sparse): Ordered with non key field
3. Secondary index(dense): Ordered with either key or non key field.
Question 262 
Replace  
Join  
Change  
Update 
2. The UPDATE statement is used to modify the existing records in a table.
3.An SQL join clause  corresponding to a join operation in relational algebra  combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (selfjoin) or more tables by using values common to each. ANSIstandard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a selfjoin.
4.There is no change command in SQL
Question 263 
Accessed by only one user  
Modified by users with the correct password  
Used to hide sensitive information  
Updated by more than one user 
This allows only one user or process access to this file at any given time.
This is to prevent the problem of interceding updates on the same files.
Question 264 
Transaction log
 
Query language
 
Report writer
 
Data manipulation language 
Every database has a transaction log that is stored within the log file that is separate from the data file.
A transaction log basically records all database modifications. When a user issues an INSERT, for example, it is logged in the transaction log.
This enables the database to roll back or restore the transaction if a failure were to occur and prevents data corruption.
Question 265 
Null Integrity  
Referential Integrity  
Domain Integrity  
Null and Domain Integrity 
In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
For example, a table called Employees has a primary key called employee_id.
Another table called Employee Details has a foreign key which references employee_id in order to uniquely identify the relationship between the two tables.
Question 266 
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

An irrecoverable error is an error that remains an error, no matter how many times you try to perform the same action. In the integration space, that could mean trying to access a database table that does not exist, which would cause the JDBC driver to throw back a SQLException
Question 267 
Quick sort  
Insertion sort  
Selection sort  
Heap sort 
The algorithm finds the minimum value, swaps it with the value in the first position, and repeats these steps for the remainder of the list. It does no more than n swaps, and thus is useful where swapping is very expensive.
Question 268 
T1 − T2 − T3
 
T3 − T1 − T2  
T2 − T1 − T3  
T1 − T3 − T2 