Department of CSE(AI&ML)

Course Code: IO503PC Regulation R-22

Subject: Database Management Systems

Year & Sem III Year / I Sem

After Completion of this course, the student will be able to:

CO .No Course Outcomes

Gain knowledge of fundamentals of DBMS, database design and normal forms
CO1
Master the basics of SQL for retrieval and management of data.
CO2
Be acquainted with the basics of transaction processing and concurrency
CO3
control.
Familiarity with database storage structures and access techniques
CO4
Study distributed database systems and the challenges associated with data
CO5
Distribution and replication.

UNIT-I:
Database System Applications and Introduction to Database Design:

BTL:L1-Remember; L2-Understand; L3-Apply; L4-Analyze; L5-Evaluat; L6-

Create Course outcome 1: At the end of this course, students will be able to:

Gain knowledge of fundamentals of DBMS, database design and normal

forms

UNIT-I:
Database System Applications and Introduction to Database Design: Database System Applications: A
Historical Perspective, File Systems versus a DBMS, the Data Model, Levels of Abstraction in a DBMS, Data
Independence, Structure of a DBMS Introduction to Database Design: Database Design and ER Diagrams,
Entities, Attributes, and Entity Sets, Relationships and Relationship Sets, Additional Features of the ER
Model, Conceptual Design With the ER Model .

Unit I - Multiple choice Questions

Q.N Bloom's Course
Question PO,PSO
O Taxonom Outco
 y
me
Level
Which one of the following refers to the copies of the same data (or
information) occupying the memory space at multiple places.
a) Data Repository
[ PO1,
1 b) Data Inconsistency L1 CO1
] PSO2
c) Data Mining
d) Data Redundancy

Which one of the following refers to the "data about data"?

a) Directory
b) Sub Data [ PO1,PS
2 L1 CO1
c) Warehouse ] O1
d) Meta Data

Which of the following refers to the level of data abstraction that

describes exactly how the data actually stored? PO1,
a) Conceptual Level [ PO2
3 L2 CO1
b) Physical Level ] ,PS
c) File Level O1
d) Logical Level
To which of the following the term "DBA" referred?
a) Data Bank Administrator
[ PO1,PS
4 b) Database Administrator L1 CO1
] O2
c) Data Administrator
d) None of the above
The term "Data" refers to:
a) The electronic representation of the information
( or data)
[ PO1,PS
5 b) Basic information L1 CO1
] O1
c) Row Facts and figures
d) Both A and C

Which of the following refers to the number of tuples in a relation?

a) Entity
[ PO1,PS
6 b) Column L2 CO1
] O1
c) Cardinality
d) None of the above

Which one of the following is a type of Data Manipulation

Command?
a) Create
[ PO2,
7 b) Alter L3 CO1
] PO3,
c) Delete
d) All of the above. PSO
2
_________ command is a type of Data Definition language
command?
a) Create
[ PO1,
8 b) Update L2 CO1
] PO2,
c) Delete
d) Merge PSO
1
Which of the following is a top-down approach in which the entity's
[
9 higher level can be divided into two lower sub-entities? L3 CO1
]
a) Aggregation b) Generalization
 PO2,
c) Specialization d) All of the above PO3,
PSO
1

In which one of the following, the multiple lower entities are

grouped (or combined) together to form a single higher-level entity?
[
10 L3 CO1 PO2,
a) Specialization b) Generalization ]
c) Aggregation d) None of the above PO3,
PSO
1

Unit I - Fill in the blanks

Bloom's
Course
Q.N Taxonom
Question Outco PO,PSO
O y
me
Level
A Database Management System is a type of _________software.
1 L1 CO1 PO1,PS
O1
A huge collection of the information or data accumulated form several
2 L1 CO1 PO1,PS
different sources is known as ________
O2
In general, a file is basically a collection of all related______.
3 L1 CO1 PO1,PS
O1
Rows of a relation are known as the _______. PO1,PS
4 L1 CO1 O2,PS
O2
In a relation database, every tuples divided into the fields are known as PO
the______. 1,P
5 L2 CO1
SO
1
_____________is used in the application programs to request data from PO1,
6 the database management system? L2 CO1 PO2,PS
O1
In the relational table, which of the following can also be represented by PO1,
7 the term "attribute"____________? L2 CO1 PO2,PS
O1
__________ refers to the number of attributes in a relation.? PO1,
8 L2 CO1 PO2,PS
O2
___________ command is used to delete the existing row in a table? PO2,
9 L3 CO1 PO3,PS
O1
___________ commands is used to save any transaction permanently into PO2,
10 the database? L3 CO1 PO3,PS
O2

Unit 1 - Short Questions- 1 Mark

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level
 1 What are the advantages of using a DBMS over file systems? L1 CO1 PO1,PSO1

PO1,
2 Explain different types of data models in DBMS. L2 CO1 PO2,PSO2

PO2,PSO1
3 Define data independence. Why is it important? L1 CO1
PO1,
4 Describe the levels of abstraction in a DBMS. L2 CO1 PO3,PSO2

5 What are entities, attributes, and entity sets? Provide examples. L1 CO1 PO2,
PO3,PSO2

6 Explain the significance of ER diagrams in database design. L2 CO1 PO3,PSO1

PO2,PSO1
7 What are relationships and relationship sets? L1 CO1

PO3,PSO2
8 Explain additional features of the ER model. L2 CO1

PO3,
9 Describe the steps in conceptual database design. L3 CO1 PO4,PS
O1
PO1,
10 How does the structure of a DBMS facilitate data management? L2 CO1 PO3,PS
O2

Unit 1 - Long Questions 5M or 10M

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level

1 L1 CO1 PO1,PSO1
What are the advantages of using a DBMS over file systems?
PO1,
2 L2 CO1 PO2,PSO1
Explain different types of data models in DBMS.
PO2
3 L1 CO1
Define data independence. Why is it important?
PO1,
4 L2 CO1 PO3,PSO1
Describe the levels of abstraction in a DBMS.

5 L1 CO1 PO2,
What are entities, attributes, and entity sets? Provide examples. PO3,PSO1

6 L2 CO1 PO3,PSO1
Explain the significance of ER diagrams in database design.
PO2,PSO1
7 L1 CO1
What are relationships and relationship sets?
PO3,PSO1
8 L2 CO1
Explain additional features of the ER model.
PO3,
9 L3 CO1 PO4,PS
Describe the steps in conceptual database design. O2
PO1,
10 L2 CO1
How does the structure of a DBMS facilitate data management? PO3,PS
 O2

UNIT-II:
Introduction to the Relational Model:
BTL:L1-Remember; L2-Understand; L3-Apply; L4-Analyze; L5-Evaluat; L6-
Create Course outcome 2: At the end of this course, students will be able to
Master the basics of SQL for retrieval and management of data

UNIT-II:
Introduction to the Relational Model: Introduction to the Relational Model: Integrity constraint over
relations, enforcing integrity constraints ,querying relational data, logical database design, introduction to
views, destroying/altering tables and views.
Relational Algebra, Tuple relational Calculus, Domain relational calculus.

Unit II - Multiple choice Questions

Bloom's
Course
Q.N Taxonom PO,PS
Question Outco
O y O
me
Level
Which of the following is not true about SQL statements?
a) A - SQL statements are not case sensitive.
b) SQL statements can be written on one or more lines. [
1 L1 CO2
c) Keywords cannot be split across lines. ]
PO1,P
d) Clauses must be written on separate lines.
SO1

Choose the correct statement regarding super keys? [

a) A super key is an attribute or a group of multiple attributes ]
that can uniquely identify a tuple
b) A super key is a tuple or a set of multiple tuples that can
uniquely identify an attribute
2 c) Every super key is a candidate key L2 CO2 PO
d) A super key is an attribute or a set of attributes that 1,
distinguish the relation from other relations. PO
2,P
SO
1
What is a foreign key.? [
PO
a) A foreign key is a primary key of a relation which is an ]
1,
attribute in another relation. PO
b) A foreign key is a super key of a relation which is an 2,P
attribute in more than one other relations. SO
3 L2 CO2
c) A foreign key is an attribute of a relation that is a primary 1
key of another relation.
d) A foreign key is the primary key of a relation that does not
occur anywhere else in the schema

4 What is an Instance of a Database.? [ L1 CO2 PO1,P

a) The logical design of the database system. ] O2,PS
b) The entire set of attributes of the Database put together in a O1
single relation.
c) The state of the database system at any given point of time.
 d) The initial values inserted into the Database immediately after
its creation.
What action does ⋈ operator perform in relational algebra.
a) Output specified attributes from all rows of the input relation
and remove duplicate tuples from the output.
b) Outputs pairs of rows from the two input relations that have
the same value on all attributes that have the same name.
c) Output all pairs of rows from the two input relations [
5 L3 CO2
(regardless of whether or not they have the same values on ]
common attributes) PO
d) Return rows of the input relation that satisfy the predicate. 2,
PO
3,P
SO
1
What is the method of specifying a primary key in a schema
description.?
a) By writing it in bold letters. [
6 L1 CO2 PO1,P
b) By underlining it using a dashed line. ]
SO1
c) By writing it in capital letters.
d) By underlining it using a bold line.
Statement 1: A tuple is a row in a relation.
Statement 2: Existence of multiple foreign keys in a same relation
is possible.
PO1,
a) Both the statements are true. [
7 L2 CO2 PO2,PS
b) Statement 1 is correct but Statement 2 is false. ]
O2
c) Statement 1 is false but Statement 2 is correct.
d) Both the statements are false.

A Relation is a.
a) Subset of a Cartesian product of a list of attributes.
PO
b) Subset of a Cartesian product of a list of domains. [ 1,
8 L2 CO2
c) Subset of a Cartesian product of a list of tuple. ] PO
d) Subset of a Cartesian product of a list of relations. 2,P
SO
2

Minimal Super keys are called.

a) Schema keys.
PO
b) Candidate keys. [ 1,
9 L2 CO2
c) Domain keys. ] PO
d) Attribute keys. 2,P
SO
1
Who proposed the relational model.?
a) Bill Gates.
b) E.F. Codd. [ PO1,PS
10 L1 CO2
c) Herman Hollerith. ] O2
d) Charles Babbage.

Unit II - Fill in the blanks

Q.N Bloom's Course
Question PO,PSO
O Taxonom Outco
 y
me
Level
For each attribute in the table, there is set of permitted values, called the PO1,PO
1 L1 CO2
________ of the attribute. 3,PSO1
An attribute is a __________ in a relation. PO1,PS
2 L1 CO2
O2
The mathematical terms relation and tuple are referred to PO1,PS
3 L1 CO2
as_______________? O2
________________describe logical design of database? PO1,
4 L2 CO2 PO2,PS
O2
________________ describe snapshot of the data in the database at a PO1,
5 instant of time? L2 CO2 PO2,PS
O1
________________ describes the relation name( table names), attributes PO1,PS
6 L1 CO2
and their names? O1
In relation model, a row of table is known to be______________? PO1,PS
7 L1 CO2
O1
The relational model is concerned with________________ PO
2,
PO
8 L2 CO2
3,P
SO
1
Network models are complicated by physical keys, but the Relation model PO
is __________? 2,
PO
9 L2 CO2
3,P
SO
1
Foreign key is the one in which the ______________of one relation is PO
referenced in another relation. 1,
PO
10 L2 CO2
2,P
SO
1

Unit II - Short Questions- 1 Mark

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level
PO1,PSO1
1 Define relational integrity constraints with examples. L1 CO2
PO2,
2 Explain relational algebra operations. L2 CO2 PO3,PSO1

Differentiate between tuple relational calculus and domain PO3,PSO1

3 L3 CO2
relational calculus.
PO1,
4 What is the purpose of views in relational databases? L2 CO2 PO2,PSO1
PO3,
5 Explain logical database design and its significance. L2 CO2 PO4,PSO1

How do you enforce integrity constraints in a relational PO2,

6 L3 CO2 PO3PS
database?
 O1
PO1,PSO1
7 Describe different types of relations in a database. L2 CO2

What are the steps involved in altering or destroying tables and PO3,
8 L3 CO2 PO5,PS
views? O1
PO2,
9 Compare relational algebra and relational calculus. L4 CO2 PO3,PS
O1
PO1,
10 Why are constraints important in relational database design? L2 CO2 PO3,PS
O1

Unit II - Long Questions 5M or 10M

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level
PO1,PSO2
1 Explain views in SQL language. L2 CO2
PO1,PO2,P
2 Explain integrity constraints over relations. L2 CO2 SO2

Write short notes on difference, union, rename and Cartesian PO1,PO2,P

3 L1 CO2
product operations in relational algebra. SO2
PO1,PSO1
4 Explain about outer join operation in relational algebra. L2 CO2

Explain about Specifying foreign key constraints in SQL with an PO1,PSO1

5 L2 CO2
example.
PO1,PO2,P
6 What is a key.? Explain types of keys. L2 CO2 SO1

PO1,PSO1
7 Explain about querying relation data. L2 CO2

What are integrity constraints ?Define the terms primary key

8 constraints and foreign key constraints. How are these expressed L1 CO2 PO1,PO2,P
in SQL? SO1
a) Discuss in detail about the properties of relation algebra.
9 b) How we can convert relationship sets with key constraints into L2 CO2 PO1,PO2,P
tables? Explain. SO2
Explain tuple relational calculus. PO1,PO2,PS
10 L2 CO2
O2

UNIT-III:
Sql Queries and Schema Refinement:

BTL:L1-Remember; L2-Understand; L3-Apply; L4-Analyze; L5-Evaluat; L6-

Create Course outcome 3: At the end of this course, students will be able to:

Be acquainted with the basics of transaction processing and concurrency

control.
 UNIT-III:
Sql Queries and Schema Refinement :SQL QUERIES,CONSTRAINTS,TRIGGERS:
Form of basic SQL query, UNION, INTERSECT, and
EXCEPT, Nested Queries, aggregation operators, NULL values, complex integrity constraints in SQL,
Triggers and active databases.
Schema Refinement: Problems caused by redundancy, decompositions, problems related to
decomposition,reasoningaboutfunctionaldependencies,First,Second,Thirdnormalforms, BCNF,
Lossless join decomposition, multivalued dependencies, Fourth normal form, Fifth normal form. .

Unit III- Multiple choice Questions

Bloom's
Course
Q.N Taxonom PO,PS
Question Outco
O y O
me
Level
Triggers are supported in
a) Delete. PO1,
[
1 b) Update. L2 CO3 PO2,PS
]
c) Views. O1
d) All of the mentioned.
What is the full form of SQL? [
a) Structured Query List. ]
PO1,P
b) Structured Query Language.
2 L1 CO3 O2,PS
c) Sample Query Language.
O1
d) None of these.

Which of the following is not a DDL command? [

a) TRUNCATE. ]
PO1,
b) ALTER.
3 L2 CO3 PO2,PS
c) CREATE.
O1
d) UPDATE.

Which statement is used to delete all rows in a table without having

the action logged?
a) DELETE. PO1,
[
4 b) REMOVE. L2 CO3 PO2,PS
]
c) DROP. O1
d) TRUNCATE.

SQL Views are also known as.

a) Simple tables.
PO1,
b) Virtual tables. [
5 L2 CO3 PO3,PS
c) Complex tables. ]
O1
d) Actual Tables.

Which data manipulation command is used to combines the records

PO1,
from one or more tables?
[ PO2,
6 a) SELECT. b) PROJECT. L3 CO3
] PO3,PS
c) JOIN. d) PRODUCT.
O1
7 Where sub queries cannot be used? [ L3 CO3 PO2,
a) Field names in the SELECT statement. ] PO3,PS
b) The WHERE clause only in the SELECT statement. O1
c) The WHERE clause in SELECT as well as all DML
statements.
 d) The FROM clause in the SELECT statement.

What is the function of the union operation?

a) It combines the results of any two different queries.
b) It combines the results of two different queries which
have the same set of attributes in the select clause. PO1,
[
8 c) It combines the results of two different queries which L2 CO3 PO2,PS
]
have the same condition in the where clause. O1
d) It gives the Cartesian product of the results of any 2
queries.

What is the function of the intersect operation?

a) It returns the intersection of the results of the results of
any two different queries.
b) It returns the intersection of the results of two different
PO1,
queries which have the same set of attributes in the select
[ PO2,
9 clause. L3 CO3
] PO3,PS
c) It returns the intersection of the results of two different
O1
queries which have the same condition in the where
clause.
d) None of the mentioned.

What is the function of the except operation?

a) It excludes all the results present in both the queries
b) It includes the results of the second query but excludes
the results of the first query PO2,
[
10 c) It includes the results of the first query but excludes the L3 CO3 PO3,PS
]
results of the second query O1
d) It includes all the results of both queries but removes
duplicates

Unit III - Fill in the blanks

Bloom's
Course
Q.N Taxonom
Question Outco PO,PSO
O y
me
Level
A __________ is a special kind of a store procedure that executes in
PO
response to certain action on the table like insertion, deletion or updation
1,
of data PO
1 L2 CO3
2,P
SO
1
Select * from student join takes using (ID); The above query is equivalent PO
to ____________? 1,
PO
2 L3 CO3
2,
PO3,
PSO1
3 The _________ construct returns true if a given tuple is present in the sub L2 CO3 PO
query. 1,
PO
2,P
 SO
1
Using the _________ clause retains only one copy of identical tuples. PO
1,
PO
4 L3 CO3
3,P
SO
1
The union operation is represented by__________ Symbol. PO1,PS
5 L1 CO3
O1
Third normal form is based on the concept of _________ PO
1,
PO
6 L2 CO3
2,P
SO
1
The intersection operator is used to get the ________ tuples. PO1,PO
7 L3 CO3 3,PSO1
The union operation automatically __________ unlike the select clause. PO
1,
PO
8 L2 CO3
2,P
SO
1
If we want to retain all duplicates, we must write ________ in place of PO
union. 1,
PO
2,
9 L3 CO3
PO
3,P
SO
1
_________ clause is an additional filter that is applied to the result. PO
1,
PO
10 L2 CO3
2,P
SO
1

Unit III - Short Questions- 1 Mark

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level
PO1,PSO1
1 What are the different types of SQL commands? L1 CO3

PO2,
2 Explain UNION, INTERSECT, and EXCEPT operators in SQL. L2 CO3
PO3,PSO1
PO3,
3 What are nested queries? Provide examples. L3 CO3 PO5,PSO1
PO1
4 Explain integrity constraints in SQL. L2 CO3
 PO2,
5 Define normalization and explain its types. L2 CO3 PO3,PSO1

PO3,
6 What problems arise due to redundancy in databases? L4 CO3 PO4,PS
O1
PO1,
7 Define functional dependencies and their role in normalization. L2 CO3 PO2,PS
O1
Explain the differences between BCNF and Third Normal Form PO3,
8 L3 CO3 PO4,PS
(3NF). O1
PO3,PSO1
9 What is a lossless join decomposition? L3 CO3

10 How do multivalued dependencies affect database design? L4 CO3 PO4,PSO1

Unit III - Long Questions 5M or 10M

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level
PO1,PSO1
1 Define Trigger and give an example. L1 CO3
PO1,PSO1
2 Explain about functional dependency? L2 CO3

Explain the following Operators in SQL with examples i) PO1,PSO1

3 L2 CO3
SOME ii) IN iii) EXCEPT iv) EXISTS v) UNION
What do you mean by schema refinement? Explain how it can be PO1,PO2,P
4 L2 CO3
accomplished? SO1
When is a decomposition said to be dependency preserving? PO1,PSO1
5 L2 CO3
Why this property Useful? Explain.

Explain 2NF, 3NF and BCNF Normal forms with example. What is
6 L2 CO3 PO1,PSO1
the difference between 3NF and BCNF ?

What is normalization? Explain 4NF and 5NF Normal forms with PO1,PO2,P
7 L2 CO3
example SO1
What is redundancy? Explain the problems caused by redundancy PO1,PO2,P
8 L2 CO3
with suitable example relation. SO1

9 What is trigger? Explain how to implement triggers in SQL? L1 CO3 PO1,PO2,P

SO1
What aggregate operators does SQL support? Explain with PO1,PO2,P
10 L2 CO3
examples. SO1

UNIT-IV:
Transaction Management:

BTL:L1-Remember; L2-Understand; L3-Apply; L4-Analyze; L5-Evaluat; L6-

Create Course outcome 4: At the end of this course, students will be able to:
 Familiarity with database storage structures and access techniques

UNIT-IV:
Transaction Management: Transaction Concept, Transaction State, Implementation of Atomicity and
Durability, Concurrent Executions, Serializability, Recoverability, Implementation of Isolation, Testing for
serializability, Lock Based Protocols, Time stamp Based Protocols, Validation-Based Protocols, Multiple
Granularity, Recovery and Atomicity, Log–Based Recovery, Recovery with Concurrent Transactions.

Unit IV - Multiple choice Questions

Bloom
's
Q.N Course PO,PS
Question Taxon
O Outcome O
omy
Level

The method in which records are physically stored in a specified

order according to a key field in each record is. PO
[
1 a) hash. b) direct. L2 C04 1,
] PO
c) sequential. d) all of the above.
2,P
SO
1
Transaction processing is associated with everything below except. [
]
a) producing detail, summary, or exception reports.
b) recording a business activity. PO
2 c) confirming an action or triggering a response. L2 C04 1,
d) maintaining data. PO
3,P
SO
1
What does Rollback do? [
PO
a) Undoes the transactions before commit. ]
1,
b) Clears all transactions. PO
c) Redoes the transactions before commit. 2,
3 L3 C04
d) No action. PO
3,P
SO
1
A _________ consists of a sequence of query and/or update
statements.
[
4 a) Transaction. b) Commit. L1 C04
] PO1,
c) Rollback. d) Flashback.
PSO
1
Consider the following action.
Transaction…..
Commit;
Rollback;
What does Rollback do? [ PO
5 L3 C04
a) Undoes the transactions before commit. ] 1,
b) Clears all transactions. PO
c) Redoes the transactions before commit. 2,P
d) No action. SO
1
 Which of the following is not a state in Transaction
a) Active. PO
[ 1,
6 b) Terminated. L2 C04
] PO
c) Nullified.
2,P
d) Commit.
SO
1
The situation where the lock waits only for a specified amount of
time for another lock to be released is
a) Lock timeout.
PO
b) Wait-wound. [
7 L2 C04 1,
c) Timeout. ] PO
d) Wait. 3,P
SO
1

In a granularity hierarchy the highest level represents the.

a) Entire database. PO
b) Area. [ 1,
8 L2 C04
c) File. ] PO
d) Record. 2,P
SO
1

If a node is locked in an intention mode, explicit locking is done at a

lower level of the tree. This is called. PO
a) Intention lock modes. 1,
[ PO
9 b) Explicit lock. L3 C04
] 2,
c) Exclusive lock.
PO
d) Implicit lock
3,P
SO
1
A condition that occurs when two transactions wait for each other to
unlock data is known as a(n)
a) Shared Lock. PO1,
[
10 b) Exclusive Lock. L3 C04 PO3,PS
]
c) Binary Lock. O1
d) Dead lock.

Unit IV - Fill in the blanks

Bloom's
Course
Q.N Taxonom
Question Outco PO,PSO
O y
me
Level
In a database the file is contained in __________ PO1,PS
1 L1 CO4
O1
If a node is locked in __________ explicit locking is being done at a lower
PO
level of the tree, but with only shared-mode locks.
1,
2 L2 CO4 PO
2,P
SO
1
 If a node is locked in ____________ then explicit locking is being done at a
PO
lower level, with exclusive-mode or shared-mode locks.
1,
3 L2 CO4 PO
2,P
SO
1
____________ denotes the largest timestamp of any transaction that PO1,
4 executed write(Q) successfully. L3 CO4 PO3,PS
O1
The _____________ ensures that any conflicting read and write operations PO1,
5 are executed in timestamp order. L3 CO4 PO3,PS
O1
The __________ requires that each transaction Ti executes in two or three
PO
different phases in its lifetime, depending on whether it is a read-only or
1,
an update transaction. PO
6 L3 CO4
3,P
SO
1
This validation scheme is called the __________ scheme since transactions
PO
execute optimistically, assuming they will be able to finish execution and
1,
validate at the end. PO
7 L3 CO4
3,P
SO
1
A lock that prevents the use of any tables in the database from one
PO
transaction while another transaction is being processed is called
1,
______________ PO
8 L2 CO4
2,P
SO
1
The fields which are used to retrieve the related records from other files PO
are called_________ 1,
PO
9 L2 CO4
2,P
SO
1
The _________ lock allows concurrent transactions to access the same
PO
row as long as they require the use of different fields within that row.
1,
10 L3 CO4 PO
3,P
SO
1

Unit IV - Short Questions- 1 Mark

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level
 PO1,PSO1
1 What are ACID properties in DBMS transactions? L1 CO4
PO2,PSO1
2 Explain serializability and its types. L2 CO4

PO3,PSO1
3 Describe different types of concurrency control techniques. L3 CO4
PO3,
4 Explain lock-based protocols in transaction management. L3 CO4 PO4,PSO1
PO4,PSO1
5 What is log-based recovery in DBMS? L4 CO4

PO2
,
6 Explain the significance of transaction states. L2 CO4 PO3
,PS
O1
PO3,
7 Describe timestamp-based protocols in concurrency control. L3 CO4 PO4,PS
O1
PO4,
8 How do validation-based protocols work? L4 CO4 PO6,PS
O1
PO3,
9 Explain recoverability in database transactions. L3 CO4 PO4,PS
O1
PO4,
10 How does multiple granularity improve database locking? L4 CO4 PO5,PS
O1

Unit IV - Long Questions 5M or 10M

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level

1 Explain the ACID Properties of transaction with examples . L2 CO4 PO1,PSO1

PO1,PO2,P
2 What is a deadlock? Explain with an example.? L2 CO4 SO1

PO1,PO2,P
3 What is recoverability. CO4 SO1
PO1,PSO1
4 Explain about Validation -Based Protocol L2 CO4

Explain ensuring atomicity and durability properties for a PO1,PSO1

5 L2 CO4
transaction by DBMS

6 Design the compatibility matrix for multiple granularity schemes. L6 CO4 PO1,PO3,P
SO1

7 Explain about Strict Two-Phase Locking (Strict 2PL) in detail. L2 CO4 PO1,PSO1

a).Explain the Time Stamp - Based Concurrency Control protocol .

How is it used to ensure serializability ? PO1,PSO1
8 L2 CO4
b) Explain the Check point log based recovery scheme for
recovering the data base
 a. Explain multiple granularity of locking protocol with example.
9 L2 CO4 PO1,PO2,P
b) What is serializability? Explain in detail .
SO1
a) What is transaction? Explain the properties of transaction.
10 L2 CO4 PO1,PO2,P
b)Give an overview of validation based protocol.
SO1

UNIT-V:

File Organization System:

BTL:L1-Remember; L2-Understand; L3-Apply; L4-Analyze; L5-Evaluat; L6-

Create Course outcome 5: At the end of this course, students will be able to

Study distributed database systems and the challenges associated with data

distribution and replication.

UNIT-V:
File Organization System: Data on External Storage, File Organization and Indexing, Cluster Indexes,
Primary and Secondary Indexes, Index data Structures, Hash Based Indexing, Tree based Indexing,
Comparison of File Organizations, Indexes- Intuitions for tree Indexes, Indexed Sequential Access Methods
(ISAM), B+ Trees: A Dynamic Index Structure.

Unit V - Multiple choice Questions

Bloom's
Course
Q.N Taxonom PO,PS
Question Outco
O y O
me
Level
The method of access which uses key transformation is known as.
a) direct.
b) hash. [
1 L1 CO5
c) random. ] PO1,P
d) sequential. SO1

The file organization that provides very fast access to any arbitrary [
record of a file is ]
a) Ordered file
PO
b) Unordered file
2 L2 CO5 1,
c) Hashed file
PO
d) B-tree 2,P
SO
1
The 2 basic types of record access methods are: [
a) sequential and random ]
b) sequential and indexing PO
3 c) direct and immediate L2 CO5 1,
d) none PO
2,P
SO
1
 What is the best definition of a collision in a hash table?
a) Two entries are identical except for their keys.
b) Two entries with different data have the exact same key.
PO
c) Two entries with different keys have the same exact hash [ 1,
4 L3 CO5
value. ] PO
d) Two entries with the exact same key have different hash 3,P
values. SO
1

Which of the following scenarios leads to linear running time for a

random search hit in a linear-probing hash table?
a) All keys hash to same index. PO
[
5 b) All keys hash to different indices. L3 CO5 1,
]
c) All keys hash to an even-numbered index. PO
d) All keys hash to different even-numbered indices. 3,P
SO
1
key value pairs are usually used in.
a) Hash Tables
b) Heaps
[ PO1,
6 c) Both hash and heap L1 CO5
] PSO
d) None.
1

The method of file organization in which data records in a file are

arranged in a specified order according to key field is known as the.

a ) Sequential access method

b) Queuing method [ PO
7 L2 CO5 1,
c) Predetermined method ]
PO
d) Direct access method. 2,P
SO
1
The hash functions that does not maintain order of values of hash
field are called
a) order preserving PO
[ 1,
8 b) pointer preserving L2 CO5
] PO
c) Block preserving
d) Record preserving. 2,P
SO
1

The kind of hashing technique in which a directory of having

addresses 2d is maintained is called.
a) dynamic hashing PO
[
9 b) extendible hashing L3 CO5 1,
]
c) Non- extendible hashing PO
d) Static hashing. 3,P
SO
1
The hashing process if used for disk files is classified as
a) preserving hashing PO
b) external hashing [ 1,
10 L3 CO5 PO
c) internal hashing ]
3,P
d) reversal hashing.
SO
1
 Unit V - Fill in the blanks
Bloom's
Course
Q.N Taxonom
Question Outco PO,PSO
O y
me
Level
A file is organized so that the ordering of data records is the same as or
PO
close to the ordering of data entries in some index. Then that index is
1,
called____________ PO
1 L2 CO5
2,P
SO
2
The physical location of a record determined by a formula that transforms
PO
a file key into a record location is_____________
1,
2 L3 CO5 PO
3,P
SO
1
In __________ allocation method for disk block allocation in a file system,
PO
insertion and deletion of blocks in a file is easy.
1,
3 L2 CO5 PO
2,P
SO
2
The level of aggregation of information required for operational control
4 L1 CO5 PO1,PS
is___________
O1
The directory can be viewed as ________ that translates filenames into PO
their directory entries. 1,
PO
5 L2 CO5
2,P
SO
1
In a Hierarchical database, a hashing function is used to locate the PO
___________ 1,
PO
6 L3 CO5
3,P
SO
1
Non-leaf nodes are also called as __________ PO1,
7 L2 CO5 PO2,
PSO1
Insertion of a large number of entries at a time into an index is referred to PO
as _________ of the index. 1,
PO
8 L3 CO5
3,P
SO
1
__________ are the indices that store the values of some attributes along PO
with the pointers to the record. 1,
PO
9 L2 CO5
2,P
SO
1
10 Dynamic hashing is also called as _________ L3 CO5 PO
 1,
PO
3,P
SO
1

Unit V - Short Questions- 1 Mark

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level

1 What is indexing in databases? Explain different types of indexes. L1 CO5 PO1,PSO2

PO1,
2 Explain file organization techniques in DBMS. L2 CO5 PO2,PSO2

PO2,
3 Differentiate between primary and secondary indexes. L3 CO5 PO3,PSO2
PO3,
4 Explain the concept of B+ trees and their advantages. L3 CO5 PO4,PSO1
PO3,PSO1
5 What is hash-based indexing? How does it work? L3 CO5

PO2,
6 What are clustered and non-clustered indexes? L2 CO5 PO3,PS
O2

7 How does ISAM (Indexed Sequential Access Method) work? L3 CO5 PO3,PSO1

PO4,PSO1
8 Compare tree-based indexing and hash-based indexing. L4 CO5

PO4,
9 What are the advantages of B-trees over B+ trees? L4 CO5 PO5,PS
O2

10 How do index structures improve database performance? L4 CO5 PO5,PSO1

Unit V - Long Questions 5M or 10M

Bloom's
Q.N Course
Question Taxonomy PO,PSO
O Outcome
Level
Explain the Insertion and deletion Operations in B+ trees with PO1,PSO2
1 L2 CO5
example.
PO1,PSO2
2 Explain Deletion and insertion operations in ISAM with example . L2 CO5

PO1,PO2,P
3 What is an index? Give an example. L2 CO5 SO2

4 How the concurrency control is done in B+ trees? Explain. L2 CO5 PO1,PSO1

 PO1,PSO2
5 Explain about clustered index organization. L2 CO5

State and explain various file organization methods. Give suitable PO1,PO2,P
6 L2 CO5
examples to each them. SO2

7 What are indexed data structures? Explain any one of them. L2 CO5 PO1,PSO1

PO1,PSO1
8 Explain indexes and performance tuning. L2 CO5

PO1,PSO1
9 Explain about Hash indexing method(ISAM) in detail. L2 CO5

10 Explain about B+ trees in detail. L2 CO5 PO1,PSO1