Databases

In the context of schema normalization in relational
DBMS, consider a set F of functional dependencies. The
set of all functional dependencies implied by F is called
the closure of F. To compute the closure of F,
Armstrong's Axioms can be applied. Consider X, Y, and
Z as sets of attributes over a relational schema. The three
rules of Armstrong's axioms are described as follows.

Reflexivity: If Y ⊆ X, then X → Y
Augmentation: If X → Y, then XZ → YZ for any Z
Transitivity: If X → Y and Y → Z, then X → Z

The additional rule of Union is defined as follows.

Union: If X → Y and X → Z, then X → YZ

It can be proved that the additional rule of Union is also
implied by the three rules of Armstrong's Axioms.
Listed below are four combinations of these three rules.
Which one of these combinations is both necessary and
sufficient for the proof?

• (a)Reflexivity, Augmentation, and Transitivity
• (b)Reflexivity and Augmentation
• (c)Transitivity
• (d)Augmentation and Transitivity

Let P, Q, R and S be the attributes of a relation in a
relational schema. Let X → Y indicate functional
dependency in the context of a relational database, where
X, Y ⊆ {P, Q, R, S}.

Which of the following options is/are always true?

• (a)If {P, Q} → {R} and {P} → {R}, then {Q} → {R}
• (b)If {P, Q} → {R}, then ({P} → {R} or {Q} → {R})
• (c)If {P} → {R} and {Q} → {S}, then {P, Q} → {R, S}
• (d)If {P} → {R}, then {P, Q} → {R}

Consider a relational database schema with a relation R(A,
B, C, D). If {A, B} and {A, C} are the only two candidate
keys of the relation R, then the number of super keys of
relation R is ______ (answer in integer)

In the context of relational database normalization, which
of the following statements is/are true?

• (a)It is always possible to obtain a dependency- preserving 3NF decomposition of a relation
• (b)It is always possible to obtain a dependency- preserving 1NF decomposition of a relation
• (c)It is not always possible to obtain a dependency- preserving BCNF decomposition of a relation
• (d)It is not always possible to obtain a dependency- preserving 2NF decomposition of a relation

Consider the following relational schema along with all
the functional dependencies that hold on them.
R1(A,B,C,D,E):{D→E,EA→B,EB→C}
R2(A,B,C,D):{A→D,A→B,C→A}

• (a)R1 is in 3NF
• (b)R2 is in 3NF
• (c)R1 is NOT in 3NF
• (d)R2 is NOT in 3NF

Consider a relational schema team(name , city , owner)
with functional dependencies {name → city , name →
owner}
.
The relation team is decomposed into two relations,

DATABASE MANAGEMENT  SYSTEM
t1(name , city) and t2(name , owner). Which of the
following statement(s) is/are TRUE?

• (a)The relation team is NOT in BCNF.
• (b)The relations t1 and t2 are in BCNF.
• (c)The decomposition constitutes a lossless join.
• (d)The relation team is NOT in 3NF.

Which of the following statements about a relation R in
first normal form (1NF) is/are TRUE ?

• (a)R can have a multi-attribute key
• (b)R cannot have a foreign key
• (c)R cannot have a composite attribute
• (d)R cannot have more than one candidate key

The symbol → indicates functional dependency in the
context of a relational database. Which of the following
options is/are TRUE?

• (a)(X,Y)→(Z,W) implies X→(Z,W)
• (b)(X,Y)→(Z,W) implies (X,Y)→Z
• (c)((X,Y)→Z and W→Y) implies (X,W)→Z
• (d)(X→Y and Y→Z) implies X→Z

A functional dependency F: X→Y is termed as a useful
fimctional dependency if and only if it satisfies all the
following three conditions:
 X is not the empty set.
 Y is not the empty set.
 Intersection of X and Y is the empty set.

For a relation R with 4 attributes, the total number of
possible useful functional dependencies is ____

Which one of the options given below refers to the
degree (or arity) of a relation in relational database
systems?

• (a)Number of attributes of its relation schema.
• (b)Number of tuples stored in the relation.
• (c)Number of entries in the relation.
• (d)Number of distinct domains of its relation schema.

Consider a relation R(A,B,C,D,E) with the following three
functional dependencies.

AB→C; BC→D; C→E

The number of super keys in the relation R is...

In a relational data model, which one of the following
statements is TRUE?

• (a)A relation with only two attributes is always in BCNF.
• (b)If all attributes of a relation are prime attributes, then the relation is in BCNF.
• (c)Every relation has at least one non-prime attribute.
• (d)BCNF decompositions preserve functional dependencies.

Suppose the following functional dependencies hold on a
relation U with attributes P, Q R, S, and T:

P → QR

RS → T

Which of the following functional dependencies can be
inferred from the above functional dependencies?

• (a)PS → T
• (b)PS → Q
• (c)P → R
• (d)R → T

Consider the relation R(P, Q, S, T, X, Y, Z, W) with the
following functional dependencies.

PQ → X; P→YX ; Q→ Y; Y → ZW

Consider the decomposition of the relation R into the
constituent relations according to the following two
decomposition schemes.

DATABASE MANAGEMENT  SYSTEM

D1 : R=[(P, Q, S,T); (P,T, X); (Q,Y); (Y, Z,W)]

D2 : R= [(P, Q,S); (T, X); (Q,Y); (Y,Z,W)]

Which one of the following options is correct?

• (a)Both D1 and D2 are lossless decompositions.
• (b)Both D1 and D2 are lossy decompositions.
• (c)D1 is a lossless decomposition, but D2 is a lossy decomposition.
• (d)D1 is a lossy decomposition, but D2 is a lossless decomposition.

Consider a relational table R that is in 3NF, but not in
BCNF. Which one of the following statements is TRUE?

• (a)A cell in R holds a set instead of an atomic value.
• (b)R has a nontrivial functional dependency X → A, where x is not a super key and A is a non-prime attribute and X is not a proper subset of any key.
• (c)R has a nontrivial functional dependency X → A, where x is not a super key and A is a non-prime attribute and X is a proper subset of some key.
• (d)R has a nontrivial functional dependency X → A, where X is not a super key and A is a prime attribute.

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 statements is/are correct ?

• (a)I only
• (b)II only
• (c)Neither I nor II
• (d)Both I and II

Consider the following four relational schema. For each
schema, all non-trivial functional dependencies are
listed. The underlined attributes are the respective
primary keys.

Schema I: Registration (roll no, courses)

Field 'course' is a set-valued attribute containing the set of
courses a student has registered for.

Non-trivial functional dependency

roll no→ courses

Schema II: Registration(roll no, courseid, email)

Non-trivial functional dependencies:

roll no, course id→ email

Email →roll no

Schema III: Registration (roll no, course id, marks, grade)

Non-trivial functional dependencies:

roll no, course id→ marks ,grade

marks→ grade

Schema IV: Registration (roll no, course id, credit)

Non-trivial functional dependencies:

roll no, courseid→ credit

courseid→ credit

Which one of the relational schemas above is in 3NF but
not in BCNF?

• (a)Schema I
• (b)Schema II
• (c)Schema III
• (d)Schema IV

Consider the following tables T1 and T2.

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

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

V → W

VW → X

DATABASE MANAGEMENT  SYSTEM

Y → VX

Y → Z

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

• (a)V → W V → X Y → V Y → Z
• (b)V → W W → X Y → V Y → Z
• (c)V → W V → X Y → V Y → X Y → Z
• (d)V → W W → X Y → V Y → X Y → Z

A database of research articles in a journal uses the
following schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE,
TITLE, YEAR, PRICE)

The
primary
key
is
(VOLUME,
NUMBER,
STARTPAGE,
ENDPAGE)
and
the
following
functional dependencies exist in the schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) →
TITLE

(VOLUME, NUMBER) → YEAR

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) →
PRICE

The database is redesigned to use the following schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE,
TITLE, PRICE)

(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database
satisfies,
but
the
old
one
does
not?

• (a)1NF
• (b)2NF
• (c)3NF
• (d)BCNF

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

• (a)VXYZ
• (b)VWXZ
• (c)VWXY
• (d)VWXYZ

Consider the relation X(P, Q, R, S, T, U) with the
following set of functional dependencies
F = {{P, R} → {S, T}
{P, S, U} → {Q, R}}
Which of the following is the trivial functional
dependency in F+ is closure of F?

• (a){P, R} → {S, T}
• (b){P, R} → {R, T}
• (c){P, S} → {S}
• (d){P, S, U} → {Q}

Given the following two statements:

S1: Every table with two single-valued attributes is in 1NF,
2NF, 3NF and BCNF.

S2: AB → C, D → E, E → C is a minimal cover for the set
of functional dependencies AB → C, D → E, AB → E, E
→ C.

Which
one
of
the
following
is
CORRECT?

• (a)S1 is TRUE and S2 is FALSE.
• (b)Both S1 and S2 are TRUE.
• (c)S1 is FALSE and S2 is TRUE.
• (d)Both S1 and S2 are FALSE.

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

• (a)in all candidate keys of R,
• (b)in some candidate key of R
• (c)in a foreign key of R.
• (d)only in the primary key of R. DATABASE MANAGEMENT SYSTEM

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

• (a){E,F}
• (b){E,F,H}
• (c){E,F,H,K,L}
• (d){E}

Given an instance of the STUDENTS relation as shown
below.

For (StudentName, StudentAge) to be the key for

this instance, the value X should NOT be equal to______

Relation R has eight attributes ABCDEFGH. Fields of R
contain only atomic values.

F = {CH → G, A → BC, B → CFH, E → A, F → EG}
is a set of functional dependencies (FDs) so that F+ is
exactly the set of FDs that hold for R.

How many candidate keys does the relation R have?

• (a)3
• (b)4
• (c)5
• (d)6

Relation R has eight attributes ABCDEFGH. Fields of R
contain only atomic values.

F = {CH → G, A → BC, B → CFH, E → A, F → EG} is
a set of functional dependencies (FDs) so that F+ is
exactly the set of FDs that hold for R.

The relation R is ?

• (a)In 1NF, but not in 2NF.
• (b)In 2NF, but not in 3NF.
• (c)In 3NF, but not in BCNF.
• (d)In BCNF

Which of the following is TRUE?

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

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 of the following options is INCORRECT?

• (a)Bank Account _ Num is a candidate key.
• (b)Registration _ Num can be a primary key.
• (c)UID is a candidate key if all students are from the same country.
• (d)If S is a super key such that S∩UID is NULL then S ∪ UID is also a super key. Student ID Student Name Student Email Student Age CPI 2345 Shankar shankar@mat h X 9.4 1287 Swati swati@ee 19 9.5 7853 Shankar shankar@cse 19 9.4 9876 Swati swati@mech 18 9.3 8765 Ganesh ganesh@civil 19 8.7 DATABASE MANAGEMENT SYSTEM

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 unique name,
and (sname, city) forms a candidate key. No other
functional dependencies are implied other than those
implied by primary and candidate keys. Which one of
the following is TRUE about the above schema?

• (a)The schema is in BCNF
• (b)The schema is in 3NF but not in BCNF
• (c)The schema is in 2NF but not in 3NF
• (d)The schema is not in 2NF

Consider the following relational schemes for a library
database:

Book (Title, Author, Catalog_no, Publisher, Year, Price)
 Collection (Title, Author, Catalog_no)

with 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?

• (a)Both Book and Collection are in BCNF
• (b)Both Book and Collection are in 3NF only
• (c)Book is in 2NF and Collection in 3NF
• (d)Both Book and Collection are in 2NF only

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 , B → G

The relational schema R is

• (a)in BCNF
• (b)in 3NF, but not in BCNF
• (c)in 2NF, but not in 3NF
• (d)not in 2NF

Let R(A, B, C, D) be a relational schema with the
following functional dependencies:

F={A → B, B → C, C → D, D → B}

The decomposition of R into (A, B), (B, C), (B, D)

• (a)gives a lossless join, and is dependency preserving
• (b)gives a lossless join, but is not dependency preserving
• (c)does not give a lossless join, but is dependency preserving
• (d)does not give a lossless join and is not dependency preserving

Which one of the following statements is FALSE?

• (a)Any relation with two attributes is in BCNF
• (b)A relation in which every key has only one attribute is in 2NF
• (c)A prime attribute can be transitively dependent on a key in a 3NF relation
• (d)A prime attribute can be transitively dependent on a key in a BCNF relation

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?

• (a){CF}+ = {ACDEFG}
• (b){BG}+ = {ABCDG}
• (c){AF}+ = {ACDEFG}
• (d){AB}+ = {ABCDG} DATABASE MANAGEMENT SYSTEM

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?

• (a)VXZ
• (b)VXY
• (c)VWXY
• (d)VWXYZ

Consider a relation schema 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?

• (a)AE, BE
• (b)AE, BE, DE
• (c)AEH, BEH, BCH
• (d)AEH, BEH, DEH

Which one of the following statements about normal
forms is FALSE?

• (a)BCNF is stricter than 3NF
• (b)Lossless, dependency-preserving decomposition into 3NF is always possible
• (c)Lossless, dependency-preserving decomposition into BCNF is always possible
• (d)Any relation with two attributes is in BCNF

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

• (a)1 NF
• (b)2 NF
• (c)3 NF
• (d)none

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 → A

Which of the following functional dependencies is NOT
implied by the above set?

• (a)CD → AC
• (b)BD → CD
• (c)BC → CD
• (d)AC → BC

In the context of owner and weak entity sets in the ER
(Entity-Relationship) data model, which one of the
following statements is TRUE?

• (a)The weak entity set MUST have total participation in the identifying relationship
• (b)The owner entity set MUST have total participation in the identifying relationship
• (c)Both weak and owner entity sets MUST have total participation in the identifying relationship
• (d)Neither weak entity set nor owner entity set MUST have total participation in the identifying relationship

Let S be the specification: "Instructors teach courses.
Students register for courses. Courses are allocated
classrooms. Instructors guide students." Which one of
the following ER diagrams CORRECTLY represents S?

• (a)(i)
• (b)(ii)
• (c)(iii)
• (d)(iv)

Which one of the following is used to represent the
supporting many-one relationships of a weak entity set in
an entity-relationship diagram?

• (a)Diamonds with double/bold border
• (b)Rectangles with double/bold border
• (c)Ovals with double/bold border
• (d)Ovals that contain underlined identifiers

In an Entity-Relationship (ER) model, suppose R is a
many-to-one relationship from entity set E1 to entity set
E2. Assume that E1 and E2 participate totally in R and that
the cardinality of E1 is greater than the cardinality of E2.

Which one of the following is true about R?

• (a)Every entity in E1 is associated with exactly one entity in E2
• (b)Some entity in E1 is associated with more than one entity in E2
• (c)Every entity in E2 is associated with exactly one entity in E1
• (d)Every entity in E2 is associated with at most one entity in E1

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

• (a)Relationship R is one-to-many and the participation of A in R is total
• (b)Relationship R is one-to-many and the participation of A in R is partial
• (c)Relationship R is many-to-one and the participation of A in R is total
• (d)Relationship R is many-to-one and the participation of A in R is partial DATABASE MANAGEMENT SYSTEM

Consider an Entity-Relationship (ER) model in which
entity sets E1 and E2 are connected by an m:n
relationship R12. E1 and E3 are connected by a 1:n (1
on the side of E1 and n on the side of E3) relationship
R13.

E1 has two-singled attributes a11 and a12 of which a11
is the key attribute. E2 has two singled-valued attributes
a21 and a22 of which a21 is the key attribute. E3 has two
single-valued attributes a31 and a32 of which a31 is the
key attribute. The relationships do not have any
attributes.

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

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

• (a)An attribute of an entity can have more than one value
• (b)An attribute of an entity can be composite
• (c)In a row of a relational table, an attribute can have more than one value
• (d)In a row of a relational table, an attribute can have exactly one value or a NULL value

Consider the following ER diagram

Which of the following is a correct attribute set for one
of the tables for the minimum number of tables needed
to represent M, N, P, R1, R2 ?

• (a)M1, M2, M3, P1
• (b)M1, P1, N1, N2
• (c)M1, P1, N1
• (d)M1, P1

Consider the following ER diagram

The minimum number of tables needed to represent
M, N, P, R1, R2 is

• (a)2
• (b)3
• (c)4
• (d)5

Let E1 and E2 be two entities in an E/R diagram with
simple-valued attributes. R1 and R2 are two relationships
between E1 and E2, where R1 is one-to-many and R2 is
many-to-many. R1 and R2 do not have any attributes of
their own. What is the minimum number of tables
required to represent this situation in the relational model?

• (a)2
• (b)3
• (c)4
• (d)5

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 ?

• (a)Person
• (b)Hotel Room
• (c)Lodging
• (d)None of these DATABASE MANAGEMENT SYSTEM

The following table has two attributes A and C where A
is the primary key and C is the foreign key referencing
A with ON DELETE CASCADE.

The set of all tuples that must be additionally deleted to
preserve referential integrity when the tuple (2, 4) is
deleted is:

• (a)(3, 4) and (6, 4)
• (b)(5, 2) and (7, 2)
• (c)(5, 2), (7, 2) and (9, 5)
• (d)(3, 4), (4, 3) and (6, 4)

Consider a relational database schema with two
relations R(P, Q) and S(X, Y)
Let E= {⟨u⟩|∃v∃w⟨u, v⟩∈R∧⟨v, w⟩∈S} be a
tuple relational calculus expression.
Which one of the following relational algebraic
expressions is equivalent to E ?

• (a)ΠP(R⋈R.P=S.XS)
• (b)ΠP(S⋈S.X=R.QR)
• (c)ΠP(R⋈R.P=S.YS)
• (d)ΠP(S⋈S.Y=R.QR)

Consider two relations describing teams and players in
a sports league: teams (tid,tname): tid, tname are
team-id and team-name, respectively.players(pid,
pname,tid): pid, pname, and tid denote player-id,
playername and the team-id of the player, respectively.
Which ONE of the following tuple relational calculus
queries returns the name of the players who play for the
team having name as 'MI'?

• (a){ p. pname | p∈players∧∃t (t∈teams∧p.tid = t.tid∧t.tname = 'MI')
• (b){ p. pname | p∈teams∧∃t (t∈players∧p.tid = t.tid∧t.tname ='MI')}
• (c){ p. pname | p∈players∧∃t (t∈teams∧t.tname = 'MI')}
• (d){ p. pname | p∈teams∧∃t (t∈players∧t.tname = 'MI')}

Consider the following two relations, R(A,B) and
S(A,C):

R
A
B
10
20
20
30
30
40
30
50
50
95

S
A
C
10
90
30
45
40
80
The total number of tuples obtained by evaluating the
following expression
σB<C(R⋈R.A=S.A S) is ___________.

Consider the following three relations in a relational
database:
Employee(eId, eName)
 Brand(bId, bName)
 Own(eId, bId)
Which of the following relational algebra expressions
returns the set of eIds who own all the brands?

• (a)π eId ( (π eId,bld (Own)) / π bId (Brand) )
• (b)π eId (Own) - π eId ( (π eId (Own) × π bId (Brand)) - π eId,bld Own ))
• (c)π eId( π eId,bld (Own) / π bId (Own)
• (d)π eId ( (π eId (Own) × π bId (Own)) / π bId (Brand) ) DATABASE MANAGEMENT SYSTEM

The following relation records the age of 500 employees
of a company, where empNo (indicating the employee
number) is the key :

 empAge(empNo,age)

 Consider the following relational algebra expression
                   Πemp×No(empAge⋈(age>age1) ρempNo1,aye1(empAge))
 What does the above expression generate?

• (a)Employee numbers of all employees whose age is not the minimum.
• (b)Employee numbers of all employees whose age is the minimum.
• (c)Employee numbers of all employees whose age is more than the age of exactly one other employee.
• (d)Employee numbers of only those employees whose age is the maximum.

A relation r(A, B) in a relational database  has 1200
tuples. The attribute A has integer values ranging from 6
to 20, and the attribute B has integer values ranging from
1 to 20. Assume that the attribute A and B  are
independently distributed.  The estimated number of
tuples in the output of σ(A>10)v(B=18)(r) is ______.

Consider the following statements S1 and S2 about the
relational data model:

S1: A relation scheme can have at most one foreign key.

S2: A foreign key in a relation scheme R cannot be used
to refer to tuples of R.

Which one of the following choices is correct?

• (a)S1 is false and S2 is true.
• (b)S1 is true and S2 is false.
• (c)Both S1 and S2 are false.
• (d)Both S1 and S2 are true.

Consider the following relations P(X, Y, Z), Q(X, Y, T)
and R(Y, V).

Table : P

X
Y
Z
X1
Y1
Z1
X1
Y1
Z2
X2
Y2
Z2
X2
Y4
Z4

Table : Q

X
Y
T
X2
Y1
2
X1
Y2
5
X1
Y1
6
X3
Y3
1

Table : R
Y
V
Y1
V1
Y3
V2
Y2
V3
Y2
V2

How many tuples will be returned by the following
relational algebra query?
πx(σ(P.Y = R.Y ∧ R.V = V2(P X R))) - πx(σ(Q.Y = R.Y ∧ Q.T > 2(Q X
R)))

Consider the relations r(A, B) and s(B, C), where s,B is a
primary key and r.B is a foreign key referencing s.B.
Consider the query:

DATABASE MANAGEMENT  SYSTEM

Q: r ⟕ (σ B<5 (s))

Let LOJ denote the natural left outer-join operation.
Assume that r and s contain no null values.

Which one of the following queries is NOT equivalent
to Q?

• (a)σ B<5 (r ⋈ s)
• (b)σ B<5 (r LOJ s)
• (c)r LOJ (σ B<5 (s))
• (d)σ B<5 (r LOJ s)

Consider a database that has the relation schemas
EMP(Empld,
EmpName,
Deptld)
and
DEPT(DeptName, Deptld). Note that the Deptldcan  be
permitted to a NULL in the relation EMP. Consider the
following queries on the database expressed in tuple
relational calculus.

(I) {t|∃u ε EMP(t[EmpName) = u[EmpName] Λ ∀

         ∨ε DEPT(t[Deptld] ≠ V[Deptld]))}

(II) {t|∃u ε EMP(t[EmpName) = u[EmpName] Λ ∃∨ ε
DEPT(t[Deptld] ≠V[Deptld]))}

(III) {t|∃u ε EMP(t[EmpNmae) = u[EmpName] Λ ∃∨ ε
DEPT(t[Deptld] =V[Deptld]))}

Which of the above queries are safe?

• (a)I and II only
• (b)I and III only
• (c)II and III only
• (d)I, II and III.

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

Student Name
Course Name
SA
CA
SA
CB
SA
CC
SB
CB
SB
CC
SC
CA
SC
CB
SC
CC
SD
CA
SD
CB
SD
CC
SD
CD
SE
CD
SE
CA
SE
CB
SF
CA
SF
CB
SF
CC

The following query is run on the database:
T1 ← π CourseName (σ StudentName = 'SA' (CR))
T2 ← CR ÷ T1
The number of rows in T2 is ______.

Consider two relations R1(A, B) with the tuples (1, 5), (3,
7) and R2(A, C) = (1, 7), (4, 9).

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

• (a)R contains a,b,e,f,g but not c, d.
• (b)R contains all of a,b,c,d,e,f,g
• (c)R contains e,f,g but not a,b
• (d)R contains e but not f,g DATABASE MANAGEMENT SYSTEM

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

employee(empld,empName,empAge)

dependent(depld, eld,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 emplds of
employees whose age is greater than that of

• (a)Some dependent
• (b)All dependents.
• (c)Some of his/her dependents
• (d)All of his/her dependents

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 TABLES (

a INTEGER,

d integer,

e integer,

PRIMARY KEY (d),

FOREIGN key (a) references R)

Which one of the following statements is CORRECT?

• (a)S1 is TRUE and S2 is FALSE.
• (b)Both S1 and S2 are TRUE.
• (c)S1 is FALSE and S2 is TRUE
• (d)Both S1 and S2 are FALSE.

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

Assuming size(r(R)) < size(s(S)), the join will have
fewer number of disk block accesses if:

• (a)relation r(R) is in the outer loop.
• (b)relation s(S) is in the outer loop.
• (c)join selection factor between r(R) and s(S) is more than 0.5.
• (d)join selection factor between r(R) and s(S) is less than 0.5.

What is the optimized version of the relation algebra
expression  πA1(πA2(σF1(σF2(r)))), where A1, A2 are sets of
attributes in r with A1 ⊂ A2 and F1, F2 are Boolean
expressions based on the attributes in r?

• (a)πA1(σ(F1∧F2)(r))
• (b)πA1(σ(F1∨F2)(r))
• (c)πA2(σ(F1∧F2)(r))
• (d)πA2(σ(F1∨F2)(r))

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. rollno 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) {<SN>|∃SR∃RP(<SR,SN>∈Students∧<SR,107,RP>∈
Registration∧ RP>90)}

• (a)I, II, III and IV
• (b)I, II and III only
• (c)I, II and IV only
• (d)II, III and IV only

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

DATABASE MANAGEMENT  SYSTEM

• (a)ΠB(r1) – Πc(r2) = ∅
• (b)ΠC(r2) – ΠB(r1) = ∅
• (c)ΠB(r1) = Πc(r2)
• (d)ΠB(r1) – Πc(r2) ≠ ∅

Consider a relational table r with sufficient number of
records, having attributes A1, A2,..., An and let 1 <= p
<= n. Two queries Q1 and Q2 are given below.

Q1:
 π A1, A2, ..., Ap ( σ Ap = c ( r ) ) where c is a constant.

Q2:
 π A1, A2, ..., Ap ( σ c1 ≤ Ap ≤ c2 ( r ) ) where c1 and c2 are
constants.

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

• (a)Ordered indexing will always outperform hashing for both queries
• (b)Hashing will always outperform ordered indexing for both queries
• (c)Hashing will outperform ordered indexing on Q1, but not on Q2
• (d)Hashing will outperform ordered indexing on Q2, but not on Q1.

The following functional dependencies hold for
relations R(A, B, C) and S(B, D, E):

B -> A , A -> C

The relation R contains 200 tuples and the relation S
contains 100 tuples. What is the maximum number of
tuples possible in the natural join of R and S (R ⋈S) ?

• (a)100
• (b)200
• (c)300
• (d)2000

Let R and S be relational schemes such that R = {a, b,
c} and S = {c}. Now consider the following queries on
the database:
         1  πR-S(r) -πR-S(πR-S(r) × s-πR-S,S(r))

         2. {t|t∈πR-S(r) ∧∀u∈s(∃v∈r(u= v[S] ∧t=
v[R-S]))}

        3. {t|t∈πR-S(r) ∧∀v∈r(∃u∈s(u= v[S] ∧t=
v[R-S]))}

4. Select R.a, R.b

  From R, S
 Where R.c = S.c

Which of the above queries are equivalent?

• (a)1 and 2
• (b)1 and 3
• (c)2 and 4
• (d)3 and 4

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?

1. Π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)))

• (a)Only I and II
• (b)Only I and III
• (c)Only I, II and III
• (d)Only I, III and IV

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))

• (a)I only
• (b)II only
• (c)III only
• (d)III and IV only

Consider the following relational schema:

• Student(school-id, sch-roll-no, sname, saddress)
 • School(school-id, sch-name, sch-address, sch-phone)
 • Enrolment(school-id, sch-roll-no, erollno, examname)
 • ExamResult(erollno, examname, marks)

Consider the following tuple relational calculus query.

DATABASE MANAGEMENT  SYSTEM

{ t | ∃E ∈ Enrolment ( t = E.school-id ∧
 |{ x | x ∈ Enrolment ∧x.school-id = t ∧
 (∃B ∈ExamResult ( B.erollno =
x.erollno∧B.examname = x.examname∧B.marks> 35 ))
}| ÷ |{ x | x ∈ Enrolment ∧x.school-id = t }| * 100 > 35
) }

If a student needs to score more than 35 marks to pass
an exam, what does the query return?

• (a)The empty set
• (b)schools with more than 35% of its students enrolled in some exam or the other
• (c)Schools with a pass percentage above 35% over all exams taken together
• (d)schools with a pass percentage above 35% over each exam

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) what 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?

πcourceId ((πstudId (σsex = 'female' ( studInfo )) ×
             πcourseId ( enroll )) - enroll )

• (a)Courses in which all the female students are enrolled
• (b)Courses in which a proper subset of female students are enrolled.
• (c)Courses in which only male students are enrolled.
• (d)None of the above.

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.name | employee(e) ∧ }

(∀x) [ ¬employee(x) ∨x.supervisorName ≠ e.name
∨x.sex = "male" ] }

• (a)Names of employees with a male supervisor.
• (b)Names of employees with no immediate male subordinates.
• (c)Names of employees with no immediate female subordinates.
• (d)Names of employees with a female supervisor.

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?

• (a)50
• (b)100
• (c)150
• (d)200

Consider the following relation schemas :
 b -Schema = (b-name, b -city, assets)
 a-Schema = (a-num, b-name, bal)
 d-Schema = (c-name, a-number)
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:
Πc-name (σb-city = "Agra" ∧ bal < 0 (branch ⋈
(account ⋈ depositor)
Which one of the following queries is the most
efficient version of the above query?

• (a). Πc-name (σbal <0(σb-city = "Agra" branch ⋈ account ) ⋈ depositor )
• (b). Πc-name (σb-city = "Agra" branch ⋈(σbal<0 account ⋈ depositor))
• (c). Πc-name ((σb-city = "Agra" branch ⋈σb-city = "Agra" ∧ bal < 0 account ) ⋈ depositor )
• (d). Πc-name (σb-city = "Agra" branch ⋈(σb-city = "Agra" ∧ bal < 0 account ⋈ depositor )) DATABASE MANAGEMENT SYSTEM

Consider the relations r1(P, Q, R) and r2(R, S, T) with
primary keys P and R respectively. The relation r1
contains 2000 tuples and r2 contains 2500 tuples. The
maximum size of the join r1⋈ r2 is :

• (a)2000
• (b)2500
• (c)4500
• (d)5000

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

• (a)II and III only
• (b)I and II only
• (c)I and III only
• (d)I, II and III

Let r be a relation instance with schema R = (A, B, C,
D). We define
r1 = πA,B,C (r) and r2 = πA,D (r).
Let s = r1 ⋈ r2, where ⋈ denotes natural join. Given
that the decomposition of r into r1 and r2 is lossy,
which one of the following is TRUE?

• (a)s ⊂ r
• (b)r ∪ s = r
• (c)r ⊂ s
• (d)r ⋈ s = s

Consider the following relational schema:

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

Which of the following options is/are correct SQL
query/queries to retrieve the names of the students
enrolled in course number (i.e. courseno) 1470?

• (a)SELECT S.name FROM Students S WHERE EXISTS (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno)
• (b)SELECT S.name FROM Students S WHERE SIZEOF (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno) > 0
• (c)SELECT S.name FROM Students S WHERE 0 < (SELECT COUNT(*) FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno)
• (d)SELECT S.name FROM Students S NATURAL JOIN Enrolled E WHERE E.courseno = 1470

Consider the following database tables of a sports
league.
1.player (pid, pname, age)team(tid, tname, city, cid)
2. coach (cid, cname) members (pid,tid)

SELECT MIN (P.age)
FROM player P
WHERE P.pid IN (SELECT M.pid
FROM team T, coach C, members M
WHERE C.cname = 'Mark'
AND T.cid = C.cid
AND M.tid = T.tid);

The value returned by the given SQL query is _________
(Answer in integer)

The relation schema Person (pid, city) describes
the city of residence for every person uniquely
identified by pid. The following relational
algebra operators are available: selection,
projection, cross product, and rename.
 To find the list of cities where at least 3 persons
reside, using the above operators, the minimum
number of cross product operations that must be
used is:

• (a)1
• (b)2
• (c)3
• (d)4 DATABASE MANAGEMENT SYSTEM

Consider the following table named Student in a
relational database. The primary key of this table is
rollNum.

rollINum
name
gender
marks
1
Naman
M
62
2
Aliya
F
70
3
Aliya
F
80
4
James
M
82
5
Swati
F
65

The SQL query below is executed on this database.

SELECT *FROM Student
WHERE gender = 'F' AND  marks  > 65;
The number of rows returned by the query is
___________.

Consider the relational database with the following four
schemas and their respective
instances.

Student(sNo, sName, dNo)
 Dept(dNo, dName)
 Course(cNo, cName, dNo)
 Register(sNo, cNo)

Student

sNo
sName
dNo
S01
James
D01
S02
Rocky
D02
S03
Jackson
D02
S04
Jane
D01
S05
Milli
D02

Dept
dNo
dName
D01
CSE
D02
EEE

Course

cNo
cName
dNo
C11
DS
D01
C12
OS
D01
C21
DE
D02
C22
PT
D02
C23
CV
D03

Register

sNo
cNo
S01
C11
S01
C12
S02
C11
S03
C21
S03
C22
S03
C23
S04
C11
S04
C12
S05
C11
S05
C21

SELECT *

FROM Student AS S

WHERE NOT EXISTS (

    (SELECT cNo

     FROM Course

     WHERE dNo = 'D01')

    EXCEPT

    (SELECT cNo

     FROM Register

     WHERE sNo = S.sNo)

);

The number of rows returned by the above SQL query is
______.

DATABASE MANAGEMENT  SYSTEM

The relation scheme given below is used to store
information about the employees of a company, where
empId is the key and deptId indicates the department to
which the employee is assigned. Each employee is
assigned to exactly one department.

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:

• (a)employees in the company
• (b)female employees in the department.
• (c)employees in the department.
• (d)female employees in the company.

Consider a relational database containing the following
schemas.

Catalogue

Sno
Sname
location
S1
M/s Royal furniture
Delhi
S2
M/s Balaji furniture
Bangalore
S3
M/s Premium furniture
Chennai

pno
Sname
Location
P1
Table
Wood
P2
Chair
Wood
P3
Table
Steel
P4
Almirah
Steel
P5
Almirah
Wood

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
__________.

A relational database contains two tables Student and
Performance as shown below

Student

Roll_no. Student_name
1
Amit
2
Priya
3
Vinit
4
Rohan
5
Smita

Performance

Roll_no. Subject_Code Marks
1
A
86
1
B
95
1
C
90
2
A
89
2
C
92
3
C
80

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.
Sno
Pno
Parts
Suppliers

DATABASE MANAGEMENT  SYSTEM

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 _________?

Consider the following two tables and four queries in
SQL .

Book (isbn, bname), Stock (isbn, copies)

Query 1:      SELECT B.isbn, S.copies

FROM BOOK B INNER JOIN Stock S

ON B.isbn = S.isbn;

Query 2:      SELECT B.isbn, S.copies

FROM BOOK B LEFT OUTER JOIN Stock S

ON B.isbn=S.isbn;

Query 3: SELECT B.isbn, S.copies

FROM BOOk B RIGHT OUTER JOIN Stock S

ON B.isbn = S.isbn;

Query 4:   SELECT B.isbn, S.copies

FROM BOOk B FULL OUTER JOIN Stock S

ON B.isbn = S.isbn;

Which one of the queries above is certain to have an
output that is a superset of the outputs of the other three
queries?

• (a)Query 1
• (b)Query 2
• (c)Query 3
• (d)Query 4

Consider the following database table named

top_scorer:

player
country
goals
Klose
Germany
16
Ronaldo
Brazil
15
G Muller
Germany
14
Fontaine
France
13
Pele
Brazil
12
Klinsmann
Germany
11
Kocsis
Hungary
11
Batistuta
Argentina
10
Cubillas
Peru
10
Lato
Poland
10
Lineker
England
10
T Muller
Germany
10
Rahn
Germany
10

Consider the following SQL query:
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
______.

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.

SELECT AVG (EC.Num)

FROM EC

WHERE (DeptName, Num) IN

(SELECT DeptName, COUNT(EmpId) AS
EC(DeptName, Num)

FROM EMP

GROUP BY DeptName);
The output of executing the SQL query is ______?

EMP
Empld Emp Name
DeptName
1
XYA
AA
2
XYB
AA
3
XYC
AA
4
XYD
AA
5
XYE
AB
6
XYF
AB
7
XYG
AB
8
XYH
AC
9
XYI
AC
10
XYJ
AC
11
XYK
AD
12
XYL
AD
13
XYM
AE

DATABASE MANAGEMENT  SYSTEM

Consider the following database table named
water_schemes:

water-schemes
scheme_no district_name Capacity
1
1
2
3
1
2
1
Ajmer
Bikaner
Bikaner
Bikaner
Churu
Churu
Dungargarh
20
10
10
20
10
20
10
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;

Consider the following relation

Cinema (theater, address, capacity)

Which of the following options will be needed at the
end of the SQL query

SELECT P1. address

FROM CINEMA P1

such that it always finds the addresses of theaters with
maximum capacity?

• (a)WHERE P1.capacity>= All (select P2.capacity from Cinema P2)
• (b)WHERE P1.capacity>= Any (select P2.capacity from Cinema P2)
• (c)WHERE P1.capacity> All (select max(P2.capacity) from Cinema P2)
• (d)WHERE P1.capacity> Any (select max(P2.capacity) from Cinema P2)

SELECT operation in SQL is equivalent to

• (a)the selection operation in relational algebra
• (b)that operations in relations algebra, except that SELECT IN SQL retains duplicates
• (c)the projection operation in relational algebra
• (d)the projection operation in relational algebra except that SELECT in SQL retains duplicates

Consider the following relations:

            Student

Roll_No
Student_Name
1
Raj
2
Rohit
3
Raj

Performances

Roll_No Course Marks
1
Math
80
1
English 70
2
Math
75
3
English 80
2
Physics 65
3
Math
80

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 _______?

Consider the following relational schema:
employee(empid,empName.empDept)
customer(custid,custName,salesRepld,rating) salesRepId
is a foreign key referring to empId of theemployee
relation. Assume that each employee makes a sale to at
least one customer.

What does the following query return?

DATABASE MANAGEMENT  SYSTEM

SELECT empName

FROM employee E

WHERE NOT EXISTS (SELECT custld

 FROM customer C

WHERE C.salesRepId= E.empld

AND(C.rating<>'GOOD');

• (a)Names of all the employees with at least one of their customers having a 'GOOD' rating.
• (b)Names of all the employees with at most one of their customers having a 'GOOD' rating.
• (c)Names of all the employees with none of their customers having a 'GOOD' rating.
• (d)Names of all the employees with all their customers having a 'GOOD' rating.

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

select * from R

where a in (select S.a from S)

• (a)select R.* from R,S where R.a=S.a
• (b)select distinct R.* from R,S where R.a=S.a
• (c)select R.* from R,(select distinct a from S) as S1 where R.a = S1.a
• (d)select R * from R,S where R.a=S.a and is unique R

Given the following schema:

employees(emp-id, first-name, last-name, hire-date,
dept-id, salary)

departments(dept-id,dept-name,manager-id, location-
id)

You want to display the last names and hire dates of all
latest hires in their respective departments in the
location ID 1700. You issue the following query:

SQL> select last-name, hire-date

FROM employees

WHERE (dept-id, hire-date) IN ( SELECTdept-id,
MAX(hire-date)

FROM employees JOIN departments USING(dept-id)

WHERE location-id = 1700

GROUP BYdept-id);

What is the outcome?

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

Consider the above tables A, B and C. How many tuples
does the result of the  following SQL query contains?

Table A
Id
Name
Age
12
Arun
60
15
Shreya
24
99
Rohit
11
Table B
Id
Name
Age
15
Shreya
24
25
Hari
40
98
Rohit
20
98
Rohit
11

Table C
Id
Name
Age
10
2200
02
99
2100
01

SELECT A.id

FROM   A

WHERE  A.age> ALL (SELECT B.age
           FROM   B
          WHERE  B. name = "arun");

• (a)4
• (b)3
• (c)0
• (d)1 DATABASE MANAGEMENT SYSTEM

Consider the following relations A, B and C:

A
ID,
Name,
Age
12
Arun
60
15
Shreya
24
99
Rohit
11

B
ID,
Name,
Age
15
Shreya
24
25
Hari
40
98
Rohit
20
99
Rohit
11

C
ID,
Phone,
Area
10
2200
02
99
2100
01

How many tuples does the result of the following
relational algebra expression contain?
Assume that the schema of A ∪ B is the same as
that of A.
(A ∪ B) ⋈A.ID > 40 ∨ C.ID < 15 C

• (a)7
• (b)4
• (c)5
• (d)9

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

P: An SQL query can contain a HAVING clause even
if it does not have a GROUP BY clause.

Q: An SQL query can contain a HAVING clause only
if it has a GROUP BY clause.

R: All attributes used in the GROUP BY clause must
appear in the SELECT clause.

S: Not all attributes used in the GROUP BY clause
need to appear in the SELECT clause.

• (a)P and R
• (b)P and S
• (c)Q and R
• (d)Q and S

Consider a database table T containing two columns X
and Y each of type integer. After the creation of the table,
one record (X=1, Y=1) is inserted in the table. Let MX
and MY denote the respective maximum values of X and
Y among all records in the table at any point in time.
Using MX and MY, new records are inserted in the table
128 times with X and Y values being MX+1, 2*MY+1
respectively. It may be noted that each time after the
insertion, values of MX and MY change.

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

SELECT Y

FROM T

WHERE X=7;

• (a)127
• (b)255
• (c)129
• (d)257

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.00

What is the output of the following SQL query?

SELECT Count(*)

FROM ( ( SELECT Borrower, Bank_Manager

FROM Loan_Records) AS S

NATURAL JOIN ( SELECT
Bank_Manager,

Loan_Amount

FROM Loan_Records) AS T );

• (a)3
• (b)9
• (c)5
• (d)6 DATABASE MANAGEMENT SYSTEM

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 )

• (a)1,0
• (b)1,2
• (c)1,3
• (d)1,5

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?

• (a)Find the names of all suppliers who have supplied a non-blue part.
• (b)Find the names of all suppliers who have not supplied a non-blue part.
• (c)Find the names of all suppliers who have supplied only non-blue part.
• (d)Find the names of all suppliers who have not supplied only blue parts.

Consider the following relational schema:
Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-
phone)
Enrolment (school-id, sch-rollno, erollno,
examname)
ExamResult (erollno, examname, marks)
What does the following SQL query output?

SELECT sch-name, COUNT(*)
FROM School C, Enrolment E, ExamResult R
WHERE E.school-id = C.school-id
                 AND E.examname = R.examname
                  AND E.erollno = R.erollno
                  AND R.marks = 100
                   AND S.school-id IN
                   (SELECT school-id
                   FROM student
                   GROUP BY school-id
                    HAVING COUNT(*) > 200)
                    GROUP BY school-id;

DATABASE MANAGEMENT  SYSTEM

• (a)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.
• (b)for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students.
• (c)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.
• (d)nothing; the query has a syntax error.

Consider the table employee(empId, name, department,
salary) and the two queries Q1, Q2 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?

Q1:
 Select e.empId
 From employee e
 Where not exists
 (Select * From employee s Where s.department = "5"
and s.salary>= e.salary)

Q2:
 Select e.empId
 From employee e
 Where e.salary> Any
 (Select distinct salary From employee s Where
s.department = "5")

• (a)Q1 is the correct query
• (b)Q2 is the correct query
• (c)Both Q1 and Q2 produce the same answer
• (d)Neither Q1 nor Q2 is the correct query

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 micro-seconds. Which of the following
statements is correct?

• (a)Plan 1 and Plan 2 will not output identical row sets for all databases.
• (b)A course may be listed more than once in the output of Plan 1 for some databases
• (c)For x = 5000, Plan 1 executes faster than Plan 2 for all databases.
• (d)For x = 9000, Plan I executes slower than Plan 2 for all databases.

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?

• (a)All queries return identical row sets for any database
• (b)Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.
• (c)There exist databases for which Query3 returns strictly fewer rows than Query2
• (d)There exist databases for which Query4 will encounter an integrity violation at runtime. DATABASE MANAGEMENT SYSTEM

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?

• (a)2 and 5
• (b)1 and 3
• (c)1 and 4
• (d)3 and 5

Consider a database with three relation instances shown
below. The primary keys for the Drivers and Cars
relation are did and cidrespectively and the records are
stored in ascending order of these primary keys as
given in the tables. No indexing is available in the
database.

D: Drivers relation

did
dname
rating
age
22
Karthikeyan
7
25
20
Salman
1
33
31
Boris
8
55
32
Amoldt
8
25
58
Schumacher
10
35
64
Sachin
7
35
71
Senna
10
16
74
Sachin
9
35
83
Rahul
3
25
95
Ralph
3
53

R: Reserves relation

did
cid
day
22
101
10/10/06
22
102
10/10/06
22
103
08/10/06
22
104
07/10/06
31
102
10/11/06
31
103
06/11/06
31
104
12/11/06
64
101
05/09/06
64
102
08/09/06
74
103
08/09/06

C: cars celation

cid
cname
colour
101
Renault
blue
102
Renault
red
103
Ferrari
green
104
Jaguar
red

select D.dname

from Drivers D

where D.didin  (

DATABASE MANAGEMENT  SYSTEM

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'
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.

• (a)36-40
• (b)44-48
• (c)60-64
• (d)100-104

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 in the database.

D: Drivers relation

did
dname
rating
age
22
Karthikeyan
7
25
20
Salman
1
33
31
Boris
8
55
32
Amoldt
8
25
58
Schumacher
10
35
64
Sachin
7
35
71
Senna
10
16
74
Sachin
9
35
83
Rahul
3
25
95
Ralph
3
53

R: Reserves relation

did
cid
day
22
101
10/10/06
22
102
10/10/06
22
103
08/10/06
22
104
07/10/06
31
102
10/11/06
31
103
06/11/06
31
104
12/11/06
64
101
05/09/06
64
102
08/09/06
74
103
08/09/06

C: cars celation

cid
cname
colour
101
Renault
blue
102
Renault
red
103
Ferrari
green
104
Jaguar
red

What is the output of the following SQL query?

select D.dname

from Drivers D

where D.didin  (

select R.did

from Cars C, Reserves R

where R.cid = C.cid and C.colour = 'green'

• (a)Karthikeyan, Boris
• (b)Sachin, Salman
• (c)Karthikeyan, Boris, Sachin
• (d)Schumacher, Senna

The relation book(title, price) contains the titles and
prices of different books. Assuming that no two books
have the same price, what does the following SQL query
list?

DATABASE MANAGEMENT  SYSTEM

select title
from book as B
     where (select count(*)

             from book as T
                         where T.price>B.price) < 5

• (a)Titles of the four most expensive books
• (b)Title of the fifth most inexpensive book
• (c)Title of the fifth most expensive book
• (d)Titles of the five most expensive books

A table 'student' with schema (roll, name, hostel,
marks), and another table 'hobby' with schema (roll,
hobbyname) contains records as shown below:

The following SQL query is executed on the above
tables:

select hostel

from student natural join hobbywhere 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 .

• (a)6
• (b)4
• (c)2
• (d)0

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 stock-level 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

• (a)do not supply any item
• (b)supply exactly one item
• (c)supply one or more items
• (d)supply two or more items

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:

DATABASE MANAGEMENT  SYSTEM
  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

• (a)Execute T1 followed by T2 followed by T3
• (b)Execute T2, followed by T3; T1 running concurrently throughout
• (c)Execute T3 followed by T2; T1 running concurrently throughout
• (d)Execute T3 followed by T2 followed by T1

An index in a DBMS is said to be dense if an index entry
appears for every search-key value in the indexed file.
Otherwise it is called a sparse index. Consider the
following two statements:

S1: A hash index must be a dense index
S2: A B+ tree index can be a sparse index

Which one of the following options is correct?

• (a)Both S1 and S2 are true
• (b)Both S1 and S2 are false
• (c)S1 is true and S2 is false
• (d)S1 is false and S2 is true

In the context of DBMS, consider the two sets T and S
given below.

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

Which one of the following is the correct match from T
to S?

• (a)I – L, II – M, III – N
• (b)I – M, II – L, III – N
• (c)I – N, II – M, III – L
• (d)I – N, II – L, III – M

In a B+ tree where each node can hold at most four
key values, a root to leaf path consists of the
following nodes:
A = (49, 77, 83, ), B = (7, 19, 33, 44), C = (20*,
22*, 25*, 26*)
The *-marked keys signify that these are data
entries in a leaf.
Assume that a pointer between keys k1 and k2 points
to a subtree containing keys in [k1, k2), and that when
a leaf is created, the smallest key in it is copied up
into its parent.
A record with key value 23 is inserted into the B+
tree.
The smallest key value in the parent of the leaf that
contains 25* is ______
(Answer in integer)

Consider the following B + tree with 5 nodes, in which a
node can store at most 3 key values. The value 23 is now
inserted in the B + tree. Which of the following options(s)
is/are CORRECT?

• (a)None of the nodes will split.
• (b)At least one node will split and redistribute
• (c)The total number of nodes will remain same
• (d)The height of the tree will increase.

Which of the following file organizations is/are I/O
efficient for the scan operation in DBMS?

• (a)Sorted
• (b)Heap
• (c)Unclustered tree index
• (d)Unclustered hash index DATABASE MANAGEMENT SYSTEM

In a B+Tree , the requirement of atleast half-full(50%)
node occupancy is relaxed for which one of the
following cases?

• (a)Only the root node
• (b)All leaf nodes
• (c)All internal nodes
• (d)Only the leftmost leaf node

Consider a database of fixed-length records, stored as an
ordered file. The database has 25,000 records, with each
record being 100 bytes, of which the primary key
occupies 15 bytes. The data file is block-aligned in that
each data record is fully contained within a block. The
database is indexed by a primary index file, which is also
stored as a block-aligned ordered file. The figure below
depicts this indexing scheme.

Suppose the block size of the file system is 1024 bytes,
and a pointer to a block occupies 5 bytes. The system
uses binary search on the index file to search for a
record with a given key. You may assume that a binary
search on an index file of b blocks takes ⌈log2b⌉ block
accesses in the worst case.
Given a key, the number of block accesses required to
identify the block in the data file that may contain a
record with the key, in the worst case, is _____

A data file consisting of 1,50,000 student-records is stored
on a hard disk with block size of 4096 bytes. The data file
is sorted on the primary key RollNo. The size of a record
pointer for this disk is 7 bytes. Each student-record has a
candidate key attribute called ANum of size 12 bytes.
Suppose an index file with records consisting of two fields,
ANum value and the record pointer to the corresponding
student record, is built and stored on the same disk. Assume
that the records of data file and index file are not split
across disk blocks. The number of blocks in the index file
is____.?

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 ______?

Which one of the following statements is NOT
correct  about the B+ tree data structure used for creating
an index of a relational database table?

• (a)Each leaf node has a pointer to the next leaf node
• (b)Non-leaf nodes have pointers to data records
• (c)B+ tree is a height-balanced tree
• (d)Key values in each node are kept in sorted order

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

DATABASE MANAGEMENT  SYSTEM

B+ Trees are considered BALANCED because ...

• (a)The lengths of the paths from the root to all leaf nodes are all equal.
• (b)The lengths of the paths from the root to all leaf nodes differ from each other by at most 1.
• (c)The number of children of any two non-leaf sibling nodes differ by at most 1.
• (d)The number of records in any two leaf nodes differ by at most 1.

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

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
greater than or equal to 7 and less than 15" is
___________

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

• (a)Dense
• (b)Sparse
• (c)Clustered
• (d)Unclustered

An index is clustered, if

• (a)It is on a set of fields that form a candidate key.
• (b)It is on a set of fields that include the primary key.
• (c)The data records of the file are organized in the same order as the data entries of the index.
• (d)The data records of the file are organized not in the same order as the data entries of the index.

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

• (a)1
• (b)2
• (c)3
• (d)4

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

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

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

• (a)2
• (b)3
• (c)4
• (d)5

A clustering index is defined on the fields which are of
type

• (a)non-key and ordering
• (b)non-key and non-ordering
• (c)key and ordering
• (d)key and non-ordering DATABASE MANAGEMENT SYSTEM

A B-tree of order 4 is built from scratch by 10 successive
insertions. What is the maximum number of node
splitting operations that may take place?

• (a)3
• (b)4
• (c)5
• (d)6

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 non-key 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, then the number of first-level and
second-level blocks in the multilevel index are
respectively:

• (a)8 and 0
• (b)128 and 6
• (c)256 and 4
• (d)512 and 5

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?

• (a)63
• (b)64
• (c)67
• (d)68

Consider the B+tree in the adjoining figure, where each
node has at most two keys and three links.

Keys K'15 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?

• (a)1
• (b)2
• (c)3
• (d)4

Consider the B+tree in the adjoining figure, where each
node has at most two keys and three links.

Now the key K50 is deleted from the B+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.

DATABASE MANAGEMENT  SYSTEM
iii. The root node remains unchanged (disregarding the
links).

Which one of the following options is true?

• (a)Statements (i) and (ii) are true
• (b)Statements (ii) and (iii) are true
• (c)Statements (iii) and (i) are true
• (d)All the statements are false

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 non-leaf node in a B+ tree
implementing this file structure is.

• (a)23
• (b)24
• (c)34
• (d)44

Which of the following is a key factor for preferring B+-
trees to binary search trees for indexing database
relations?

• (a)Database relations have a large number of records
• (b)Database relations are sorted on the primary key
• (c)B+-trees require less memory than binary search trees
• (d)Data transfer from disks is in blocks

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 Nested-loop 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  .

• (a)800000
• (b)40080
• (c)32020
• (d)100

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 Nested-loop join, Block nested-loop 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

• (a)0
• (b)30400
• (c)38400
• (d)798400

A B-Tree 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:

• (a)5
• (b)4
• (c)3
• (d)2 DATABASE MANAGEMENT SYSTEM

Consider concurrent execution of two transactions T1
and T2 in a DBMS, both of which access a data object
A. For these two transactions to not conflict on A, which
one of the following statements must be true?

• (a)Both T1 and T2 only read A
• (b)T1 reads A and T2 writes A
• (c)T1 writes A and T2 reads A
• (d)Both T1 and T2 write A

Consider the database transactions T1and T2, and data
items X and Y. Which of the schedule(s) is/are conflict
serializable?

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

• (a)R1(X),W2(X),W1(Y),W2(Y),R1(X), W1(X),COM MIT(T2),COMMIT(T1)
• (b)W2(X),R1(X),W2(Y),W1(Y),R1(X),COMMIT (T2),W1(X),COMMIT(T1)
• (c)R1(X),W1(Y),W2(X),W2(Y),R1(X),W1(X), COMMIT(T1),COMMIT(T2)
• (d)W2(X),R1(X),W1(Y),W2(Y),R1(X), COMMIT(T2),W1(X),COMMIT(T1)

An audit of a banking transactions system has found that
on an earlier occasion, two joint holders of account A
attempted simultaneous transfers of Rs. 10000 each from
account A to account B. Both transactions read the same
value, Rs. 11000, as the initial balance in A and were
allowed to go through. B was credited Rs. 10000 twice.
A was debited only once and ended up with a balance of
Rs. 1000.

Which of the following properties is/are certain to have
been violated by the system?

• (a)Atomicity
• (b)Consistency
• (c)Isolation
• (d)Durability

A schedule of three database transactions T1,T2, andT3 is
shown. Ri(A) and Wi (A) denote read and write of data
item A by transaction Ti, i=1,2,3 The transaction T1 aborts
at the end. Which other transaction(s) will be required to
be rolled back?
R1(X); W1(Y); R2(X); R2(Y); R3(Y) ;ABORT(T1);

• (a)Only T2
• (b)Only T3
• (c)Both T2 and T3
• (d)Neither T2 nor T3

Which of the following statements about the Two Phase
Locking (2PL) protocol is/are TRUE?

• (a)2PL permits only serializable schedules
• (b)With 2PL, a transaction always locks the data item being read or written just before every operation and always releases the lock just after the operation
• (c)With 2PL, once a lock is released on any data item inside a transaction, no more locks on any data item can be obtained inside that transaction
• (d)A deadlock is possible with 2PL

Once the DBMS informs the user that a transaction has
been successfully completed, its effect should persist even
if the system crashes before all its changes are reflected
on disk. This property is called

• (a)Durability
• (b)Atomicity
• (c)Consistency
• (d)Isolation DATABASE MANAGEMENT SYSTEM

Consider the following read-write schedule S over three
transactions T1,T2 and T3, where the subscripts in the
schedule indicate transaction IDs:

S: r1(z); w1(z);r 2(x); r3(y); w3(y); r2(y); w2(x);
w2(y);

Which of the following transaction schedules is/are
conflict equivalent to S?

• (a)T1T2T3
• (b)T1T3T2
• (c)T3T2T1
• (d)T3T1T2

Let Ri(z) and Wi(z) denote read and write operations on
a data element z by a transaction Ti, respectively.
Consider the schedule S with four transactions.

S: R4(x) R2(x) R3(x) R1(y) W1(y) W2(x) W3(y) R4(y)

Which one of the following serial schedules is conflict
equivalent to S ?

• (a)T1→T3→T4→T2
• (b)T1→T4→T3→T2
• (c)T4→T1→T3→T2
• (d)T3→T1→T4→T2

Suppose a database system crashes again while
recovering
from
a
previous
crash.
Assume
checkpointing is not done by the database either during
the transactions or during recovery. Which of the
following statements is/are correct?

• (a)The system cannot recover any further.
• (b)The same undo and redo list will be used while recovering again.
• (c)The database will become inconsistent.
• (d)All the transactions that are already undone and redone will not be recovered again.

Let S be the following schedule of operations of three
transactions T1, T2 and T3 in a relational database system:

R2(Y), R1(X), R3(Z), R1(Y), W1(X), R2(Z), W2(Y),
R3(X), W3(Z)

Consider the statements P and Q below:

P: S is conflict-serializable.

Q: If T3 commits before T1 finishes, then S is recoverable.

Which one of the following choices is correct?

• (a)Both P and Q are true.
• (b)P is false and Q is true.
• (c)Both P and Q are false.
• (d)P is true and Q is false.

Let ri(z) and wi(z) denote read and write operations
respectively on a data item z by a transaction Ti. Consider
the following two schedules.

S1 : r1(x) r1(y) r2(x) r2(y) w2(y) w1(x)

S2 : r1(x) r2(x) r2(y) w2(y) r1(y) w1(x)

Which one of the following options is correct?

• (a)S1 is not conflict serializable, and S2 is conflict serializable.
• (b)Both S1 and S2 are conflict serializable.
• (c)Neither S1 nor S2 is conflict serializable.
• (d)S1 is conflict serializable, and S2 is not conflict serializable.

Consider the following two statements about database
transaction schedules:

I. Strict two-phase locking protocol generates conflict
serializable schedules that are also recoverable.

II. Timestamp-ordering concurrency control protocol with
Thomas write rule can generate view serializable schedules
that are not conflict serializable.

Which of the above statements is/are TRUE?

• (a)Both I and II
• (b)Neither I nor II
• (c)II only
• (d)I only DATABASE MANAGEMENT SYSTEM

Consider a schedule of transactions T1 and T2 :

Here, RX stands for "Read(X)" and WX stands for
"Write(X)". Which one of the following schedules is
conflit equivalent to the above schedule?

• (a)
• (b)
• (c)
• (d)

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

if TS(T2) < TS(T1) then

T1 is killed

else T2 waits.

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

• (a)The database system is both deadlock-free and starvation-free.
• (b)The database system is deadlock-free, but not starvation-free.
• (c)The database system is starvation-free, but not deadlock-free.
• (d)The database system is neither deadlock-free nor starvation-free.

Two transactions T1 and T2 are given as:

T1 : r1(X)w1(X)r1(Y)w1(Y)

T2 : r2 (Y)w2(Y)r2(Z)w2(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 T1 and T2 is
_____.

Suppose a database schedule S involves transactions
T1,...,Tn. Construct the precedence graph of S  with
vertices representing the transactions and edges
representing the conflicts. If S is serializable, which one
of the following orderings of the vertices of the
precedence graph is guaranteed to yield a serial schedule?

• (a)Topological order
• (b)Depth-first order
• (c)Breadth-first order
• (d)Ascending order of the transaction indices.

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

• (a)Atomicity
• (b)Consistency
• (c)Isolation
• (d)Deadlock-freedom DATABASE MANAGEMENT SYSTEM

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

Step 1. T acquires exclusive locks to O1,....Ok in
increasing order of their addresses.

Step 2. The required operations are performed.

Step 3. All locks are released.

This protocol will ?

• (a)guarantee serializability and deadlock-freedom
• (b)guarantee neither serializability nor deadlock- freedom
• (c)guarantee serializability but not deadlock- freedom
• (d)guarantee deadlock-freedom but not serializability

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

S – r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2

Where ri(Z) denotes a read operation by transaction Ti
on a variable Z, wi(Z) denotes a write operation by Ti on
a variable Z and ai denotes an abort by transaction Ti.

Which one of the following statements about the above
schedule is TRUE?

• (a)S is non-recoverable
• (b)S is recoverable, but has a cascading abort
• (c)S does not have a cascading abort
• (d)S is strict

Consider the following directed graph:

The number of different topological ordering of the
vertices of the graph is ____ .

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

Schedule S

Time Instance
Transaction ID

T1
T2
1
read(A)

2
write (A)

3

read(C)
4

write (C)
5

read(B)
6

write (B)
7

read(A)
8

commit
9
read(B)

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

• (a)T2 must be aborted and then both T1 and T2 must be re-started to ensure transaction atomicity
• (b)Schedule S is non-recoverable and cannot ensure transaction atomicity
• (c)Only T2 must be aborted and then re- started to ensure transaction atomicity
• (d)Schedule S is recoverable and can ensure atomicity and nothing else needs to be done.

Consider the following transaction involving two bank
accounts x and y.

read (x) ;

x : =x - 50;

write (x) ;

read (y);

y : = y + 50;

write (y)

The constraint that the sum of the accounts x and y should
remain constant is that of

DATABASE MANAGEMENT  SYSTEM

• (a)Atomicity
• (b)Consistency
• (c)Isolation
• (d)Durability

Consider a simple checkpointing protocol and the
following set of operations in the log.

(Start, T4);

(write, T4, y, 2, 3);

(Start, T1);

(commit, T4);

(write, T1, z, 5, 7);

(check point);

(Start, T2);

(write, T2, x, 1, 9);

(commit, T2);

(start, T3);

(write, T3, z, 7, 2);

If a crash happens now and the system tries to recover
using both undo and redo operations, what are the
contents of the undo list and the redo list?

• (a)Undo T3, T1; Redo T2
• (b)Undo T3, T1; Redo T2, T4
• (c)Undo: none; redo: T2, T4, T3, T1
• (d)Undo T3, T1; T4; Redo: T2

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

T1:r1(X);r1(Z);w1(X);w1(Z)

T2:r2(Y);r2(Z);w2(Z)

T3:r3(Y);r3(X);w3(Y)

S1:r1(X);r3(Y);r3(X);r2(Y);r2(Z);w3(Y);w2(Z);r1(Z);
w1(X);w1(Z)

S2:r1(X);r3(Y);r2(Y);r3(X);r1(Z);r2(Z);w3(Y);w1(X);
w2(Z);w1(Z)

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

• (a)Only S1 is conflict-serializable.
• (b)Only S2 is conflict-serializable.
• (c)Both S1 and S2 are conflict-serializable
• (d)Neither S1 nor S2 is conflict-serializable.

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

• (a)r1 (x); r2 (x);w1 (x); r3 (x); w2 (x)
• (b)r2 (x); r1 (x);w2 (x); r3 (x); w1 (x)
• (c)r3 (x); r2 (x);r1 (x); w2 (x); w1 (x)
• (d)r2 (x); w2 (x);r3 (x); r1 (x); w1 (x)

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

T1
T3
T3
T.
Writes
(X)
Commit

Writes
(Y)
Reads (Z)
Commit

Reads
(X)
Reads
(Y)
Commit

Which one of the following statements is CORRECT?

• (a)S is conflict-serializable but not recoverable
• (b)S is not conflict-serializable but is recoverable
• (c)S is both conflict-serializable and recoverable
• (d)S is neither conflict-serializable nor is it recoverable

Consider 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 Non-serial interleaving of T1 and T2 for concurrent
execution leads to
Read
(X)
Writes (X)
Commit

DATABASE MANAGEMENT  SYSTEM

• (a)a serializable schedule
• (b)a schedule that is not conflict serializable
• (c)a conflict serializable schedule
• (d)a schedule for which a precedence graph cannot be drawn

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

I. 2-phase locking

II. Time-stamp ordering

• (a)I only
• (b)II only
• (c)Both I and II
• (d)Neither I nor II

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

Which one of the schedules below is the correct
serialization of the above?
T1
T2
T3
Read(X)

Read (Y)

Read (Y)

Write (Y)

Write (X)

Write (X)

Read (X)

Write (X)

• (a)T1 → T3 → T2
• (b)T2 → T1 → T3
• (c)T2 → T3 → T1
• (d)T3 → T1 → T2

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]
 S4 : R2[x] R2[y] R1[x] W1[x] W1[y] W2[y]

Which of the above schedules are conflict-serializable?

• (a)S1 and S2
• (b)S2 and S3
• (c)S3 only
• (d)S4 only

Consider the following three schedules of transactions T1,
T2 and T3.
(Notations: 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 1WB

Which of the following statements is TRUE?

• (a)S1, S2 and S3 are all conflict equivalent to each other
• (b)No two of S1, S2 and S3 are conflict equivalent to each other
• (c)S2 is conflict equivalent to S3, but not to S1
• (d)S1 is conflict equivalent to S2, but not to S3

Consider
the
following
schedules
involving
two
transactions. Which one of the following statements is
TRUE?

S1 : r1(X); r1(Y); r2(X); r2(Y); w2(Y); w1(X)

S2 : r1(X); r2(X); r2(Y); w2(Y); r1(Y); w1(X)

• (a)Both S1 and S2 are conflict serializable.
• (b)S1 is conflict serializable and S2 is not conflict serializable.
• (c)S1 is not conflict serializable and S2 is conflict serializable.
• (d)Both S1 and S2 are not conflict serializable. DATABASE MANAGEMENT SYSTEM

Consider the following two transactions: T1 and T2.

T1:
read(A);

read(B);

if A = 0 then B ← B + 1;
 write(B);
T2:
 read(B);
 read(A);
 if B ≠ 0 then A ← A -
1;
 write(A);

Which of the following schedules, using shared and
exclusive locks, satisfy the requirements for strict two-
phase
locking
(Strict
2PL)
for
the
above
transactions?

• (a)S1 : lock S(A); read (A); lock S(B); read(B); ifA = 0 then B ←B + 1; write(B); commit; unlock(A); unlock (B); S2 : lock S(B); read
• (b); lock S(A); read(A); ifB≠0 then A ←A -1; write(A); commit; unlock(B); unlock (A);
• (b)S1: lock X(A); read(A); lock X(B); read(B); if A = 0 then B ← B + 1; write(B); unlock(A); commit; unlock(B); S2: lock X(B); read(B); lock X(A); read(A); if B ≠ 0 then A ← A - 1; write(A); unlock(A); commit; unlock(A);
• (c)S1: lock S(A); read(A); lock X(B); read(B); if A = 0 then B ← B + 1; write(B); unlock(A); commit; unlock (B); S2: lock S(B); read(B); lock X(A); read(A); if B ≠ 0 then A ← A - 1; write(A); unlock(A); commit; unlock (A);
• (d)S1: lock S(A); read(A); lock X(B); read(B); if A = 0 then B ← B + 1; write(B); unlock(A); unlock(B); commit; S2: lock S(B); read(B); lock X(A); read(A); if B ≠ 0 then A ← A - 1; write(A); unlock(A); unlock(B); commit;

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?

• (a)We must redo log record 6 to set B to 10500
• (b)We must undo log record 6 to set B to 10000 and then redo log records 2 and 3.
• (c)We need not redo log records 2 and 3 because transaction T1 has committed.
• (d)We can apply redo and undo operations in arbitrary order because they are idempotent DATABASE MANAGEMENT SYSTEM

Amongst the ACID properties of a transaction, the
'Durability' property requires that the changes made to
the database by a successful transaction persist

• (a)Except in case of an Operating System crash
• (b)Except in case of a Disk crash
• (c)Except in case of a power failure
• (d)Always, even if there is a failure of any kind of failure