Data base management system comprehensive module 2 ppt

Dbms comprehensive module 2

1. 1
Database Management Systems

2. QUE1
Which of the following is the property of transaction that
protects data from system failure?
a) Atomicity
b) Isolation
c) Durability
d) Consistency
2

3. QUE1
Which of the following is the property of transaction that
protects data from system failure?
a) Atomicity
b) Isolation
c) Durability
d) Consistency
3

4. QUE1
4

5. QUE2
Atomicity
Concurrency
Isolation
Durability
Which of the following is not a property of transactions?
a.
b.
c.
d.


5

6. QUE2
Atomicity &&
Concurrency&&
Isolation &
Durability
Which of the following is not a property of transactions?
a.
b.
c.
d.


6

7. QUE3
Which normalization form is based on the transitive
dependency?
a) 1NF
b) 2NF
c) 3NF
d) BCNF
7

8. QUE3
Which normalization form is based on the transitive
dependency?
a) 1NF
b) 2NF
c) 3NF
d) BCNF
8

9. QUE3
Normalization
•&Purpose: Remove points of inconsistencies and data
redundancies, which are cause by modification anomalies.
•&1NF, 2NF, 3NF & BCNF focus on eliminating data
redundancies based on undesirable functional
dependencies.
Higher Normal Forms deal with data redundancies
that occur for other reasons
1NF < 2NF < 3NF < BCNF
9

10. QUE3
it was defined to disallow multivalued attributes,
composite attributes, and their combinations.
only attribute values permitted by 1NF are single
atomic (or indivisible) values.
•&1NF - Legal atomic values only

10

11. QUE3
(A non-prime attribute is an attribute that does not occur
in any candidate key.)
• 2NF - if none of its non-prime attributes are functionally
dependent on a part (proper subset) of a candidate key.
11

12. QUE3
• 3NF - All non key attributes are dependent upon the
primary key.
12

13. QUE3
Third normal form (3NF) is based on the concept
of transitive dependency.
A functional dependency X → Y in a relation
schema R is a transitive dependency if there
exists a set of attributes Z in R that is neither a
candidate key nor a subset of any key of R, and
both X → Z and Z → Y hold.
• 3NF –



13

14. QUE3
• BCNF - If every determinant is a candidate key.
 Determinant: an attribute on which some other attribute
is fully functionally dependent
14

15. QUE3
•Fourth Normal Form(4NF)
• For a table to satisfy the Fourth Normal Form, it
should satisfy the following two conditions:
It should be in the Boyce-Codd Normal Form.
And, the table should not have any Multi-valued
Dependency.
15

16. Multivalued Dependency
For a dependency A → B, if for a single value of A, multiple value
of B exists, then the table may have multi-valued dependency.
Also, a table should have at-least 3 columns for it to have a multi-
valued dependency.
And, for a relation R(A,B,C), if there is a multi-valued dependency
between, A and B, then B and C should be independent of each
other.
A table is said to have multi-valued dependency, if the following
conditions are true,
1.
2.
3.
If all these conditions are true for any relation(table), it is said to
have multi-valued dependency.

17. QUE4
Which of the following SQL command is used for
removing (or deleting) a relation form the database?
a) Drop
b) Delete
c) Rollback
d) Remove
17

18. QUE4
Which of the following SQL command is used for
removing (or deleting) a relation form the database?
a) Drop
b) Delete
c) Rollback
d) Remove
18

19. QUE4
DELETE : - DML
It is used to delete one or more tuples of a table.
With the help of the “DELETE” command, we can
either delete all the rows in one go or can delete rows
one by one. It is comparatively slower than the
TRUNCATE command. The TRUNCATE command
does not remove the structure of the table.
If we want to delete the row of the table as per the
condition then we use the WHERE clause,
DELETE FROM table_name WHERE condition;
SYNTAX –
19

20. QUE4
DROP : - DDL
It is used to drop the whole table.
With the help of the “DROP” command we can drop
(delete) the whole structure in one go i.e. it removes the
named elements of the schema. By using this command
the existence of the whole table is finished or say lost.
If we want to drop the table:

SYNTAX –
DROP table <table_name>;
20

21. QUE4
TRUNCATE : - DDL
It is used to delete all the rows of a relation (table) in
one go.
With the help of the “TRUNCATE” command, we
can’t delete the single row as here WHERE clause is not
used.
By using this command the existence of all the rows of
the table is lost. It is comparatively faster than the delete
command as it deletes all the rows fastly.
If we want to use truncate :
SYNTAX –
TRUNCATE table <table_name>;
21

22. SQL statements

23. DDL actually consists of the SQL commands that can be used
to define the database schema.
It simply deals with descriptions of the database schema and is
used to create and modify the structure of database objects in
the database.
Examples of DDL commands:
CREATE - is used to create the database or its objects (like
table, index, function, views, store procedure and triggers).
DROP - is used to delete objects from the database.
ALTER - is used to alter the structure of the database.
TRUNCATE - is used to remove all records from a table,
including all spaces allocated for the records are removed.
RENAME - is used to rename an object existing in the
database.
DDL(Data Definition Language)

24. Once the tables are created and the database is generated using
DDL commands, manipulation inside those tables and
databases is done using DML commands.
The advantage of using DML commands is, if in case any
wrong changes or values are made, they can be changed and
rolled back easily.
INSERT - used to insert data into a table.
UPDATE - is used to update any record of data in a table.
DELETE - used to delete data from a table.
SELECT - is used to select data from a table.
DML(Data Manipulation Language)

Examples of DML commands:

25. DCL is used to control user access in a database.
This command is related to the security issues.
Using DCL command, it allows or restricts the user from
accessing data in database schema.
DCL commands are as follows,
It is used to grant or revoke access permissions from any
database user.
DCL(Data Control Language)
1. GRANT
2. REVOKE

26. Transaction Control Language as the name suggests
manages the issues and matters related to the transactions
in any database.
They are used to rollback or commit the changes in the
database.
TCL(Transaction Control Language)

TCL commands are as follows:
1. COMMIT
2. SAVEPOINT
3. ROLLBACK
4. SET TRANSACTION

27. QUE4
Which of the following is known as minimal super key?
a) Primary key
b) Candidate key
c) Foreign key
d) None
27

28. QUE4
Which of the following is known as minimal super key?
a) Primary key
b) Candidate key
c) Foreign key
d) None
28

29. QUE4
Candidate Key
A candidate key is a minimal super key or a super key with no
redundant attribute.
It is called a minimal superkey because we select a candidate
key from a set of super key such that selected candidate key is
the minimum attribute required to uniquely identify the table. It
is selected from the set of the super key which means that all
candidate keys are super key. Candidate Keys are not allowed
to have NULL values.

29

30. QUE4
The primary key is the minimal set of attributes which uniquely
identifies any row of a table.
It is selected from a set of candidate keys.
Any candidate key can become a primary key .
It depends upon the requirements and is done by the Database
Administrator (DBA).
The primary key cannot have a NULL value.
It cannot have a duplicate value.
Primary Key
30

31. QUE4
All the candidate key which are not a primary key are called
an alternate key.
The foreign key of a table is the attribute which establishes the
relationship among tables.
The foreign key is the attribute which points to the primary
key of another table.
Alternate Key

Foreign Key
31

32. QUE5
32
XY -> Z and Z -> Y
YZ -> X and Y -> Z
YZ -> X and X -> Z
XZ -> Y and Y -> X
a.
b.
c.
d.

33. Definition of Functional Dependency( FD)
Constraint between two sets of attributes from the
database
This means that the values of the Y component of a
tuple in r depend on, or are determined by, the values of
the X component;

34. Functional Dependency( FD)
alternatively, the values of the X component of a tuple
uniquely (or functionally) determine the values of the Y
component.
We also say that there is a functional dependency from
X to Y, or that Y is functionally dependent on X.
The abbreviation for functional dependency is FD or
f.d.
The set of attributes X is called the left-hand side of the
FD, and Y is called the right-hand side.

36. Following do not hold because we already have violations
of them in the given extension:
A → B (tuples 1 and 2 violate this constraint);
B → A (tuples 2 and 3 violate this constraint);
D → C (tuples 3 and 4 violate it).

37. QUE5
37
XY -> Z and Z -> Y
YZ -> X and Y -> Z
YZ -> X and X -> Z
XZ -> Y and Y -> X
a.
b.
c.
d.

38. QUE5
38
the values of the X component of a tuple uniquely (or
functionally) determine the values of the Y component.
We also say that there is a functional dependency from
X to Y, or that Y is functionally dependent on X.

39. The cardinality ratio for a binary relationship
specifies the maximum number of
relationship instances to which an entity can
take part in it
It also specifies number of entities to which
other entity can be related by a relationship
Types
Cardinality Ratio
▫ One-to-one (1:1)
▫ One-to-many (1: N)
▫ Many-to-one (N: 1)
▫ Many-to-many (M: N)
88

40. When only a single instance of an entity is
associated with single instance of other entity
by a relationship
When every entity of one entity set is related to
maximum one entity of other entity set
One to One(1:1)
90

41. 9
1
Male
married
M1
M2
M3
M4
M5
M6
F1
F2
F3
F4
F5
F6
F7
1
1
Female

42. 42

43. When every entity of first entity set is related to
at most (max) n entities of other entity set then
it is one to many.
An entity in set A can be associated with any
number (zero or more) of entities in set B.
An entity in set B can be associated with at most
one entity in set A.
One to Many (1:M)
By this cardinality constraint,

93

44. One to Many (1:M)
93

45. One to Many (1:M)
93
One student can enroll in any number (zero or more) of
courses.
One course can be enrolled by at most one student.
Here,

46. 94
Department
has
d1
d2
d3
d4
d5
e1
e2
e3
e4
e5
e6
e7
1
M
Employees

47. 96
Manager
manage
s
m1
m2
m3
m4
p1
p2
p3
p4
p5
p6
p7
1
M
Projects

48. When many entities of first entity set is related
to 1 entity of other entity set then it is many to
one.
An entity in set A can be associated with at
most one entity in set B.
An entity in set B can be associated with any
number (zero or more) of entities in set A.
Many to One (M:1)
By this cardinality constraint,

97

49. Many to One (M:1)
97

50. Many to One (M:1)
97
One student can enroll in at most one course.
One course can be enrolled by any number (zero or more) of
students.
Here,

51. 98
Employee
Works
for
Department
d1
d2
d3
d4
e1
e2
e3
e4
e5
e6
e7
M 1

52. When many occurrences of one entity is
related to many occurrences of another entity.
An entity in set A can be associated with any
number (zero or more) of entities in set B.
An entity in set B can be associated with any
number (zero or more) of entities in set A.
Many to Many(M:N)
By this cardinality constraint,

100

53. Many to Many(M:N)
100

54. Many to Many(M:N)
100
One student can enroll in any number (zero or more) of courses.
One course can be enrolled by any number (zero or more) of students.
Here,
 

55. 55

56. 10
1
Teache
r
has
s1
s2
s3
s4
s5
s6
t1
t2
t3
M
N
Students

57. QUE6
An entity in A is associated with at most one entity in B.
An entity in B, however, can be associated with any
number (zero or more) of entities in A.
a) One-to-many b) One-to-one
c) Many-to-many d) Many-to-one
57

58. QUE6
An entity in A is associated with at most one entity in B.
An entity in B, however, can be associated with any
number (zero or more) of entities in A.
a) One-to-many b) One-to-one
c) Many-to-many d) Many-to-one
58

59. QUE7
59
One-to-many
One-to-one
Many-to-many
Many-to-one
An entity in A is associated with at most one entity in B,
and an entity in B is associated with at most one entity in
A. This is called as
a.
b.
c.
d.

60. QUE7
60
One-to-many
One-to-one
Many-to-many
Many-to-one
An entity in A is associated with at most one entity in B,
and an entity in B is associated with at most one entity in
A. This is called as
a.
b.
c.
d.

61. QUE8
Which commands are used to control access over objects
in relational database?
a) CASCADE & MVD
b) GRANT & REVOKE
c) QUE & QUIST
d) None of these
61

62. QUE8
Which commands are used to control access over objects
in relational database?
a) CASCADE & MVD
b) GRANT & REVOKE
c) QUE & QUIST
d) None of these
62

63. QUE9
(i) only
(i) and (ii) only
(i), (ii) and (iv) only
All the four are true
A relation schema R is in BCNF if whenever a nontrivial
functional dependency X → A holds in R, then which all
statements are true?:
(i) X can be a superkey of R
(ii) X can be a primary key of R
(iii) X can be secondary of R
(iv) X can be any candidate key of R
a.
b.
c.
d.
63

64. QUE9
(i) only
(i) and (ii) only
(i), (ii) and (iv) only
All the four are true
A relation schema R is in BCNF if whenever a nontrivial
functional dependency X → A holds in R, then which all
statements are true?:
(i) X can be a superkey of R
(ii) X can be a primary key of R
(iii) X can be secondary of R
(iv) X can be any candidate key of R
a.
b.
c.
d.
64

65. QUE10
Every relation in 3NF is also in BCNF &
A relation R is in 3NF if every non-prime 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 &
Which of the following is TRUE? &
a.
b.
c.
d.

65

66. QUE10
Every relation in 3NF is also in BCNF
A relation R is in 3NF if every non-prime 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
Which of the following is TRUE?
a.
b.
c.
d.

66

67. QUE11
UV
UW
UX
UY
Identify the minimal key for relational scheme R(U, V, W,
X, Y, Z) with functional dependencies
F = {U → V, V → W, W → X, VX → Z}
a.
b.
c.
d.
67

68. QUE11
UV
UW
UX
UY
Identify the minimal key for relational scheme R(U, V, W,
X, Y, Z) with functional dependencies
F = {U → V, V → W, W → X, VX → Z}
a.
b.
c.
d.
68

69. QUE12
M:1 relationship
M:N relationship
1:1 relationship
option (B) or(C)
It is given that: “Every student need to register one course
and each course registered by many students”, what is the
cardinality of the relation say “Register” from the
“Student”
entity to the “Course” entity in the ER diagram to
implement the given requirement.
a.
b.
c.
d.
69

70. QUE12
M:1 relationship
M:N relationship
1:1 relationship
option (B) or(C)
It is given that: “Every student need to register one course
and each course registered by many students”, what is the
cardinality of the relation say “Register” from the
“Student”
entity to the “Course” entity in the ER diagram to
implement the given requirement.
a.
b.
c.
d.
70

71. QUE13
A → BC
AC → B
AB → C
BC → A
A relation R(ABC) is having the tuples(1,2,1),(1,2,2),
(1,3,1) and (2,3,2). Which of the following functional
dependencies holds well?
a.
b.
c.
d.
71

72. QUE13
A → BC
AC → B
AB → C
BC → A
A relation R(ABC) is having the tuples(1,2,1),(1,2,2),
(1,3,1) and (2,3,2). Which of the following functional
dependencies holds well?
a.
b.
c.
d.
72

73. QUE14
BCNF
3 NF
2 NF
1 NF
Consider a relation R with attributes A, B, C, D and E and
functional dependencies A→BC, BC→E, E→DA. What is
the highest normal form that the relation satisfies?
a.
b.
c.
d.

73

74. QUE14
BCNF
3 NF
2 NF
1 NF
Consider a relation R with attributes A, B, C, D and E and
functional dependencies A→BC, BC→E, E→DA. What is
the highest normal form that the relation satisfies?
a.
b.
c.
d.

74

75. QUE15
T1→T2→T3
T2->T1->T3
T3→T1→T2
Not conflict serializable
For the given schedule S, find out the conflict equivalent
schedule.
S : r1(x); r2(Z) ; r3(X); r1(Z); r2(Y); r3(Y);W1(X); W2(Z);
W3(Y); W2(Y)
a.
b.
c.
d.
75

76. Two operations in a schedule are said to
conflict if they satisfy all three of the
following conditions:
they belong to different transactions;
they access the same item X; and
at least one of the operations is a
write_item(X).

1.
2.
3.

76

77. QUE15
T1→T2→T3
T2->T1->T3
T3→T1→T2
Not conflict serializable
For the given schedule S, find out the conflict equivalent
schedule.
S : r1(x); r2(Z) ; r3(X); r1(Z); r2(Y); r3(Y);W1(X); W2(Z);
W3(Y); W2(Y)
a.
b.
c.
d.
77

78. QUE16
top-down
bottom up
Both (A) and (B)
none of these
Specialization is __________ process.
a.
b.
c.
d.
78

79. QUE16
top-down
bottom up
Both (A) and (B)
none of these
Specialization is __________ process.
a.
b.
c.
d.
79

80. QUE16
Specialization and generalization are fundamental concepts in
database modeling that are useful for establishing superclass-
subclass relationships.
Specialization is a top-down approach in which a higher-level
entity is divided into multiple specialized lower-level entities.
In addition to sharing the attributes of the higher-level entity,
these lower-level entities have specific attributes of their own.
Specialization is usually used to find subsets of an entity that
has a few different or additional attributes.
Specialization


80

81. QUE16
81

82. QUE16
82
Generalization - Generalization is a bottom-up approach in
which multiple lower-level entities are combined to form a
single higher-level entity. Generalization is usually used to
find common attributes among entities to form a
generalized entity. It can also be thought of as the opposite
of specialization.

83. QUE16
83

84. QUE17
D1+D2+ … +Dn
D1×D2× … ×Dn
D1∪D2∪ … ∪Dn
D1–D2– … –Dn
If D1, D2, .., Dn are domains in a relational model, then
the relation is a table, which is a subset of
a.
b.
c.
d.

84

85. QUE17
D1+D2+ … +Dn
D1×D2× … ×Dn
D1∪D2∪ … ∪Dn
D1–D2– … –Dn
If D1, D2, .., Dn are domains in a relational model, then
the relation is a table, which is a subset of
a.
b.
c.
d.

85

86. QUE18
Serializable schedule
Recoverable schedule.
Deadlock free schedule.
Cascadeless schedule.
Precedence graphs help to find a
a.
b.
c.
d.
86

87. QUE18
Serializable schedule
Recoverable schedule.
Deadlock free schedule.
Cascadeless schedule.
Precedence graphs help to find a
a.
b.
c.
d.
87

88. 88

89. If a schedule should be recoverable, then
there shouldn’t be any dirty reads [reading
of uncommitted data].
If a dirty read is there, then we need to
check for commit operations.
Recoverable schedule:
// If no dirty read then schedule is
recoverable.


89

90. Recoverable schedule: Example1
Schedule S1 is recoverable
S1: R1(x), W1(x), R2(x), R1(y), R2(y), W2(x),
W1(y), C1, C2;

91. Recoverable schedule: Example2
Given Schedule is recoverable

T2 performs a dirty read
operation.
The commit operation of
T2 is delayed till T1
commits or roll backs.
T1 commits later.
T2 is now allowed to
commit.
In case, T1 would have
failed, T2 has a chance to
recover by rolling back.
Here,

92. Recoverable schedule: Example3
Schedule S2 is recoverable

93. All conflict serializable schedules are
recoverable.
All recoverable schedules may or may not
be conflict serializable.
Notes :


93

94.  If in a schedule, A transaction performs a
dirty read operation from an uncommitted
transaction
And commits before the transaction from
which it has read the value
then such a schedule is known as
an Irrecoverable Schedule.
Irrecoverable Schedules(non-recoverable
Schedules)

94

95. non-recoverable Schedule - example
T2 performs a dirty
read operation.
T2 commits before
T1.
T1 fails later and roll
backs.
The value that T2
read now stands to
be incorrect.
T2 can not recover
since it has already
committed.

96. If in a schedule, failure of one transaction
causes several other dependent transactions
to rollback or abort, then such a schedule is
called as a Cascading Schedule or Cascading
Rollback or Cascading Abort.
It simply leads to the wastage of CPU time.
cascading rollback (or cascading abort) :

97. cascading rollback - Example

98. A schedule is said to be cascadeless, or to
avoid cascading rollback, if every transaction
in the schedule reads only items that were
written by committed transactions.
In this case, all items read will not be
discarded because the transactions that wrote
them have committed, so no cascading
rollback will occur.
Cascadeless schedule:

99. In other words,
Cascadeless schedule allows only committed
read operations.
Therefore, it avoids cascading roll back and
thus saves CPU time.
Cascadeless schedule:
If in a schedule, a transaction is not allowed to
read a data item until the last transaction that has
written it is committed or aborted, then such a
schedule is called as a Cascadeless Schedule.

100. Cascadeless schedule:

101.  Cascadeless schedule allows only
committed read operations.
However, it allows uncommitted write
operations.
Cascadeless schedule: NOTE-

102. Strict Schedules:
transactions can neither read nor write an
item X until the last transaction that wrote
X has committed (or aborted).
Strict schedule allows only committed
read and write operations.
Clearly, strict schedule implements more
restrictions than cascadeless schedule.

102

103. Strict Schedules:

104. Remember-
Strict schedules are more strict than cascadeless schedules.
All strict schedules are cascadeless schedules.
All cascadeless schedules are not strict schedules.

105. The cascadeless schedules will be a subset of
the recoverable schedules, and
the strict schedules will be a subset of the
cascadeless schedules.
Thus, all strict schedules are cascadeless,
and all cascadeless schedules are
recoverable.
we can divide the schedules into two disjoint
subsets:
recoverable and nonrecoverable.

106. Characterizing Schedules Based on Serializability
Now we characterize the types of schedules that are
always considered to be correct when concurrent
transactions are executing. Such schedules are known
as serializable schedules.

107. Characterizing Schedules Based on
Serializability
Serial schedule:
Schedules A and B in Figures 20.5(a) and (b) are called
serial

108. Serial schedule:
Schedules A and B in Figures 20.5(a) and (b) are called
serial because the operations of each transaction are
executed consecutively, without any interleaved operations
from the other transaction.
In a serial schedule, entire transactions are performed in
serial order:
T1 and then T2 in Figure 20.5(a), and
T2 and then T1 in Figure 20.5(b).
Characterizing Schedules Based on Serializability

109. In Serial schedules,
All the transactions execute serially one after the
other.
When one transaction executes, no other
transaction is allowed to execute.
Consistent
Recoverable
Cascadeless
Strict
Characteristics- Serial schedules are
always-

Characterizing Schedules Based on Serializability

110. Serial schedule - Example1

Characterizing Schedules Based on Serializability

111. problem with serial schedules :
they limit concurrency by prohibiting interleaving of
operations.
if a transaction waits for an I/O operation to complete, we
cannot switch the CPU processor to another transaction, thus
wasting valuable CPU processing time.
if some transaction T is quite long, the other transactions must
wait for T to complete all its operations before starting.
serial schedules are considered unacceptable in practice.


Characterizing Schedules Based on Serializability

112. In Nonserial schedules:
Multiple transactions execute concurrently.
Operations of all the transactions are inter
leaved or mixed with each other.
Characteristics- Non-serial schedules
are NOT always-
Consistent
Recoverable
Cascadeless
Strict



Characterizing Schedules Based on Serializability

113. Nonserial schedule:
Characterizing Schedules Based on Serializability

114. Nonserial schedule:
Characterizing Schedules Based on Serializability

115. Serializability in DBMS-
Some non-serial schedules may lead to
inconsistency of the database.
Serializability is a concept that helps to identify
which non-serial schedules are correct and will
maintain the consistency of the database.

Characterizing Schedules Based on Serializability

116. Serializable schedule:
A schedule S of n transactions is serializable if it
is equivalent to some serial schedule of the same
n transactions.
If a given non-serial schedule of ‘n’ transactions
is equivalent to some serial schedule of ‘n’
transactions, then it is called as a serializable
schedule.

OR

Characterizing Schedules Based on Serializability

117. Serializable schedule:
Serializable schedules behave exactly same as
serial schedules.
Thus, serializable schedules are always-
Consistent
Recoverable
Casacadeless
Strict

Characterizing Schedules Based on Serializability

118. Serial Schedules Vs Serializable Schedules-

119. two disjoint groups of the nonserial schedules
those that are equivalent to one (or more) of the serial
schedules and hence are serializable
those that are not equivalent to any serial schedule and
hence are not serializable.
1.
2.
Characterizing Schedules Based on Serializability

120. Saying that a nonserial schedule S is
serializable is equivalent to saying that it is
correct, because it is equivalent to a serial
schedule, which is considered correct.
Characterizing Schedules Based on Serializability

121. Characterizing Schedules Based on Serializability
Serializability is mainly of two types-
Conflict Serializability
View Serializability
1.
2.

122. Result equivalent:
Two schedules are called result equivalent if
they produce the same final state of the
database.
Conflict equivalent:
Two schedules are said to be conflict equivalent
if the order of any two conflicting operations is
the same in both schedules.

Characterizing Schedules Based on Serializability

123. Conflict equivalent: Two schedules are said to be conflict
equivalent if the order of any two conflicting operations is
the same in both schedules.
Characterizing Schedules Based on Serializability

125. Conflict serializable:
A schedule S is said to be conflict serializable if
it is conflict equivalent to some serial schedule
S’.
We can reorder the nonconflicting operations in
S until we form the equivalent serial schedule
S’.
Characterizing Schedules Based on Serializability

126. Testing for Conflict Serializability of a
Schedule
Construct a precedence graph (or serialization
graph), which is a directed graph G = (N, E)
that consists of a set of nodes N = {T₁, T₂, ...,
Tn } and a set of directed edges E = {e₁,e₂, ...,
em }.
There is one node in the graph for each
transaction Ti in the schedule.
Each edge ei in the graph is of the form
(Tj→Tk ), 1 ≤ j ≤ n, 1 ≤ k  

127. Testing for Conflict Serializability of a
Schedule

128. Checking Whether a Schedule is Conflict
Serializable Or Not-
 

129. Conflict serializable schedules are always
recoverable.
129

131. QUE18
Consider the following schedules involving two transactions.
Which one of the
following statements is TRUE?
S₁ : r₁(X); r₁(Y); r₂(X); r₂(Y); w₂(Y); w₁(X)
S₂ : r₁(X); r₂(X); r₂(Y); w₂(Y); r₁(Y); w₁(X)
(A) Both S₁ and S₂ are conflict serializable.
(B) S₁ is conflict serializable and S₂ is not conflict serializable.
(C) S₁ is not conflict serializable and S₂ is conflict serializable.
(D) Both S₁ and S₂ are not conflict serializable
131

132. QUE18
Consider the following schedules involving two transactions.
Which one of the
following statements is TRUE?
S₁ : r₁(X); r₁(Y); r₂(X); r₂(Y); w₂(Y); w₁(X)
S₂ : r₁(X); r₂(X); r₂(Y); w₂(Y); r₁(Y); w₁(X)
(A) Both S₁ and S₂ are conflict serializable.
(B) S₁ is conflict serializable and S₂ is not conflict serializable.
(C) S₁ is not conflict serializable and S₂ is conflict serializable.
(D) Both S₁ and S₂ are not conflict serializable
132

133. QUE18
Conflict serializable:
A schedule S is said to be conflict serializable if it
is conflict equivalent to some serial schedule S’.
We can reorder the nonconflicting operations in S
until we form the equivalent serial schedule S’.
133

134. QUE19
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}+ = {ABCDFG}
134

135. QUE19
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}+ = {ABCDFG}
135

136. QUE20
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?
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
136

137. QUE20
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?
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
137

138. QUE20
QueryX : select title from book as B where QueryY
Assuming that no two books have the same price

QueryY : (select count(*) from book as T where T.price>B.price) < 5
Let there be 7 tuples
title-------------price------------QueryY --------------------------QueryX
a----------------10--------------6-----------------------------------not selected
b----------------20--------------5-----------------------------------not selected
c----------------30--------------4-----------------------------------selected
d----------------40--------------3----------------------------------- selected
e----------------50--------------2----------------------------------- selected
f----------------60--------------1-----------------------------------selected
g----------------70--------------0-----------------------------------selected
138

139. QUE21
Consider the following SQL query
select distinct al, a2,........., an
from r1, r2,........, rm
where P
For an arbitrary predicate P, this query is equivalent to
which of the following relational algebra expressions ?
139

140. QUE21
140

141. QUE21 – ANS A
141

142. QUE21 – ANS A
142

143. QUE22
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?
143

144. QUE22
Which of the following sets is computed by the above
query?
(A) Names of students who have got an A grade in all
courses taught by Korth
(B) Names of students who have got an A grade in all
courses
(C) Names of students who have got an A grade in at least
one of the courses taught by Korth
(D) None of the above
144

145. QUE22
Which of the following sets is computed by the above
query?
(A) Names of students who have got an A grade in all
courses taught by Korth
(B) Names of students who have got an A grade in all
courses
(C) Names of students who have got an A grade in at least
one of the courses taught by Korth
(D) None of the above
145

146. QUE23
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
99 Rohit 11
146
2100 01
Table C
Id Phone Area
-----------------
10 2200 02
1.





Consider the above tables A, B and C.

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

147. QUE23
4
3
0
1
SELECT A.id
FROM A
WHERE A.age > ALL (SELECT B.age
FROM B
WHERE B. name = "arun“)
a.
b.
c.
d.
147

148. QUE23
4
3
0
1
The meaning of “ALL” is the A.Age should be greater than all the
values returned by the subquery. There is no entry with name
“arun” in table B. So the subquery will return NULL. If a subquery
returns NULL, then the condition becomes true for all rows of A.
So all rows of table A are selected.
a.
b.
c.
d.

148

149. QUE24
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?
149

150. QUE24
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
150

151. QUE24
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
151

152. QUE24
Following will be contents of temporary table S
Borrower Bank_Manager
--------------------------
Ramesh Sunderajan
Suresh Ramgopal
Mahesh Sunderajan
Following will be contents of temporary table T
Bank_Manager Loan_Amount
---------------------------
Sunderajan 10000.00
Ramgopal 5000.00
Sunderajan 7000.00
Following will be the result of natural join of above two tables. The key thing
to note is that the natural join happens on column name with same name
which is BankManager in the above example. “Sunderajan” appears two
152

153. QUE24
Following will be contents of temporary table S
Borrower Bank_Manager
--------------------------
Ramesh Sunderajan
Suresh Ramgopal
Mahesh Sunderajan
Following will be contents of temporary table T
Bank_Manager Loan_Amount
---------------------------
Sunderajan 10000.00
Ramgopal 5000.00
Sunderajan 7000.00
153

154. QUE24
Following will be the result of natural join of above two tables. The key thing
to note is that the natural join happens on column name with same name which
is Bank_Manager in the above example. “Sunderajan” appears two times in
Bank_Manager column, so there will be four entries with Bank_Manager as
“Sunderajan”.
Borrower Bank_Manager Loan_Amount
------------------------------------
Ramesh Sunderajan 10000.00
Ramesh Sunderajan 7000.00
Suresh Ramgopal 5000.00
Mahesh Sunderajan 10000.00
Mahesh Sunderajan 7000.00
154

155. QUE25
Number of attributes of its relation schema.
Number of tuples stored in the relation.
Number of entries in the relation.
Number of distinct domains of its relation schema.
Which one of the options given below refers to the degree
(or arity) of a relation in relational database systems?
a.
b.
c.
d.

155

156. QUE25
Number of attributes of its relation schema.
Number of tuples stored in the relation.
Number of entries in the relation.
Number of distinct domains of its relation schema.
Which one of the options given below refers to the degree
(or arity) of a relation in relational database systems?
a.
b.
c.
d.

156

157. QUE25
The degree of a relation (table) is the number of attributes
(columns) in the given table. It is also called Arity.
An attribute is a property or feature of an entity. An
entity can have an unlimited number of attributes. One
of the attributes is considered to be the primary key.
Attributes in an Entity-Relationship model are
represented by an ellipse shape.


157

158. QUE26
The clause in SQL that specifies that the query result
should be sorted in ascending or descending order based on
the values of one or more columns is
(A) View
(B) Order by
(C) Group by
(D) Having
158

159. QUE26
The clause in SQL that specifies that the query result
should be sorted in ascending or descending order based on
the values of one or more columns is
(A) View
(B) Order by
(C) Group by
(D) Having
159

160. QUE27
Consider the relations r₁(P, Q, R) and r₂(R, S, T) with
primary keys P and R respectively. The relation r₁ contains
2000 tuples and r₂ contains 2500 tuples. The maximum size
of the join r₁⋈ r₂ is :
(A) 2000
(B) 2500
(C) 4500
(D) 5000
160

161. QUE27
Consider the relations r₁(P, Q, R) and r₂(R, S, T) with
primary keys P and R respectively. The relation r₁ contains
2000 tuples and r₂ contains 2500 tuples. The maximum size
of the join r₁⋈ r₂ is :
(A) 2000
(B) 2500
(C) 4500
(D) 5000
161

162. QUE27
r₁⋈ r₂  is a join operation done on the common attribute
R. Further R is the primary key of R2
When we take a , the value of common attribute( R2 in
this case) should match.The value of  R in r2 is matched
with corresponding R in r1 . So it will have 2000 tuples.
So correct option is (A).
(A) 2000


162

163. QUE28
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: R₄(x)R₂(x)R₃(x)R₁(y)W₁(y)W₂(x)W₃(y)R₄(y) 
Which one of the following serial schedules is
conflict equivalent to S?
163

164. QUE28
164

165. QUE28 – ANS - 1
165

166. QUE28
The precedence graph of the given schedule will be
166

167. QUE29
Select operation in SQL is equivalent to
(A) the selection operation in relational algebra
(B) the selection operation in relational algebra, except that
select in SQL retains duplicates
(C) the projection operation in relational algebra
(D) the projection operation in relational algebra, except
that select in SQL retains duplicates
167

168. QUE29
Select operation in SQL is equivalent to
(A) the selection operation in relational algebra
(B) the selection operation in relational algebra, except that
select in SQL retains duplicates
(C) the projection operation in relational algebra
(D) the projection operation in relational algebra, except
that select in SQL retains duplicates
168

169. QUE29
Select operation is equivalent to the projection
operation in relational algebra, except that
select in SQL retains duplicates and on the
contrary projection removes the duplicates.
169

170. QUE30
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?
(a) AE, BE
(b) AE, BE, DE
(c) AEH, BEH, BCH
(d) AEH, BEH, DEH
 
170

171. QUE30
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?
(a) AE, BE
(b) AE, BE, DE
(c) AEH, BEH, BCH
(d) AEH, BEH, DEH
 
171

172. QUE31
Schema definition
Schema modification
Granting of authorization for data access
All of the above
Typically, a database administrator (DBA) is responsible
for:
a.
b.
c.
d.
172

173. QUE31
Schema definition
Schema modification
Granting of authorization for data access
All of the above
Typically, a database administrator (DBA) is responsible
for:
a.
b.
c.
d.

https://www.includehelp.com/dbms/database-
administrators-in-dbms.aspx
173

174. QUE32
SELECT rollNo FROM student where name = ‘_p’;
SELECT rollNo FROM student where name LIKE ‘_p’;
SELECT rollNo FROM student where name LIKE
‘_p%’;
SELECT rollNo FROM student where name IN ‘_p%’;
Which of the following queries will retrieve students whose
name has ‘p’ as the second letter ?
a.
b.
c.
d.
174

175. QUE32
SELECT rollNo FROM student where name = ‘_p’;
SELECT rollNo FROM student where name LIKE ‘_p’;
SELECT rollNo FROM student where name LIKE
‘_p%’;
SELECT rollNo FROM student where name IN ‘_p%’;
Which of the following queries will retrieve students whose
name has ‘p’ as the second letter ?
a.
b.
c.
d.
175

176. QUE33
R is in 1NF, not in 2NF
R is in 2NF, not in 3NF
R is in 3NF, not in BCNF
R is in BCNF
Consider a relation R(A, B, C, D, E) and a set of all FDs
that hold on R as given below:
{A → BC, CD → E, B → D, E → A}
Choose the correct option
a.
b.
c.
d.
176

177. QUE33
R is in 1NF, not in 2NF
R is in 2NF, not in 3NF
R is in 3NF, not in BCNF
R is in BCNF
Consider a relation R(A, B, C, D, E) and a set of all FDs
that hold on R as given below:
{A → BC, CD → E, B → D, E → A}
Choose the correct option
a.
b.
c.
d.
177

178. QUE34
only F covers G
only G covers F
F and G are equivalent
None of the above
Consider the following two sets of functional dependencies:
F = {A → C, AC → D, E → AD, E → H}
G = {A → CD, E → AH}
Choose the correct option
a.
b.
c.
d.
178

179. QUE34
only F covers G
only G covers F
F and G are equivalent
None of the above
Consider the following two sets of functional dependencies:
F = {A → C, AC → D, E → AD, E → H}
G = {A → CD, E → AH}
Choose the correct option
a.
b.
c.
d.
179

180. QUE35
R1(X); W1(X);
W1(X); R2(X);
W1(X); W2(X);
R1(X); W2(X);
Consider the following schedule S.
S: R1(X); W1(X); R2(X); W2(X); R1(Y); R2(Y);
Which of the following is a non-conflicting pair of
operations in the schedule S?
a.
b.
c.
d.
180

181. QUE35
R1(X); W1(X);
W1(X); R2(X);
W1(X); W2(X);
R1(X); W2(X);
Consider the following schedule S.
S: R1(X); W1(X); R2(X); W2(X); R1(Y); R2(Y);
Which of the following is a non-conflicting pair of
operations in the schedule S?
a.
b.
c.
d.
181

182. 182
Thank You…