BTECH II YEAR II SEMESTER (AR20)

Name of the Subject : DATABASE MANAGEMENT SYSTEM(20CS4002)

Name of the Subject Coordinator : [Link] Chakravarthy &[Link]
QUESTION BANK
(PREPARE QUESTION BANK TO COVER ALL THE TOPICS)
[Link] QUESTIONS Level Course MARKS
Outcome
UNIT 1
DETAILED SYLLABUS
An Overview of Database Management, Introduction to Database System, characteristics-Database vs.
File system; Database applications, Database Users, Advantages of Database systems, Three schema
Architecture for data independence; Database system environment, centralized and client server
architecture for the database.
Data Models: Brief introduction of different data models, The ER Model, ER diagrams-Entity sets,
attributes, relationship sets and constraints. The Relational Model, Integrity Constraints over relations-
key constraints-Foreign key constraints-General constraints. Mapping of ER model to Relational Model.

1 What are the advantages and disadvantages of DBMS? L1 CO1 4M

2 Describe in detail about client-server architectures. L3 CO1 4M
3 Explain briefly characteristics of Database vs Filesystem L2 CO1 4M
4 Write about the role of DBA. L1 CO1 4M
5 Differences between file system and Database management L3 CO1 4M
system.
6 Draw and explain the detailed system architecture of DBMS. L1 CO1 10M
7 Explain in detail about the three-tier schema architecture of DBMS L5 CO1 10M
and data independence.
8 Discuss in detail about the concepts of E-R model and draw an ER L4 CO1 10M
diagram for Banking Enterprise.
9 Explain about various constraints used in ER-model. L4 CO1 10M
10 Explain the terms primary key, foreign key, composite attribute, L5 CO1 10M
stored attribute, derived attribute and descriptive attribute with
examples.

UNIT 2
DETAILED SYLLABUS
Relational Algebra- Selection and Projection- Set Operation, Renaming – Joins- Division
SQL/PLSQLQueries: The Form of Basic SQL Query, Union, Intersect, and Except, Nested Queries,
Aggregate Operators, Null Values, Complex Integrity Constraints in SQL and
Introduction to pl/sql: functions, procedures, cursors and triggers

1 Explain Cartesian product with an example. L4 CO2 4M

2 What are the differences between inner join and outer join? L1 CO2 4M
3 What is DML? Explain DML operations with examples? L2 CO2 4M
 4 Explain about triggers in pl/sql with example. L2 CO2 4M
5 Write about nested queries with example. L3 CO2 4M
6 Discuss in detail the operators SELECTION, PROJECTION, L5 CO2 10M
UNION with suitable example?
7 Write DDL and DML statements to create the following tables. L5 CO2 10M
Student (rollno, name, dob, email, Did)
Department (Did, Dname, Dlocation)
Where, Rollno is the primary key of student and Did is the
foreign key. Did is the primary key of Department. Set all the
other attributes of both the tables to not null.
8 What are aggregate operators explain with example. L3 CO2 10M
9 Explain about functions, procedures and cursors of pl/sql with CO2 10M
examples.
10 Write about select clauses with examples in SQL. L1 CO2 10M

UNIT 3
DETAILED SYLLABUS
Schema Refinement (Normalization): Purpose of Normalization or schema refinement, concept of
functional dependency, normal forms based on functional dependency (1NF, 2NF and 3 NF), Boyce-
Codd normal form (BCNF), Lossless join and dependency preserving decomposition, Fourth normal
form(4NF).

1 Define Normalization? What are problems with redundant data L1 CO3 4M

2 What is multi value dependency? Define 4NF. L3 CO3 4M
3 Briefly describe BCNF. L2 CO3 4M
4 What is lossless join decomposition? Explain the same with an L1 CO3 4M
example.
5 Why normalization is required in DBMS? Justify L1 CO3 4M
6 Why normalization is needed in Database design? Explain 1nf, L5 CO3 10M
2nf, 3nf with examples.
7 Explain decomposition? What are the advantages of L2 CO3 10M
decomposition? Discuss the problems faced in decomposition.
8 Given Relation, R=(A,B,C,D,E,F,G) and Functional L4 CO3 10M
Dependencies F={ {A,B}→{C}, { A,C}→{B}, {A,D}→{E},
{B}→{D}, { B,C}→{A}, {E}→{F}}. Check whether the
following decomposition of R into R1=(A,B,C), R2=(A,C,D,E)
and R3=(A,D,F) is satisfying the lossless Decomposition
property
9 What is a closure of functional dependency? Write and explain L3 CO3 10M
Armstrong rules of functional dependency?
10 Find the highest normal form of the given functional L4 CO3 10M
dependencies of relation R(A,B,C,D,E)
FD:{A→BCDE
B→ACE
D→E}
 UNIT 4
DETAILED SYLLABUS
Transaction managemet and concurrency control: Transaction, properties of transactions,
Concurrency control for lost updates, uncommitted data, inconsistent retrievals and the Scheduler.
Concurrency control with locking methods: lock types, two phase locking for ensuring serializability,
deadlocks, Concurrency control with time stamp ordering,Wait/Die and Wound/Wait Schemes, Database
Recovery management : Transaction recovery.

1 What is Transaction? Explain its properties. L1 CO4 4M

2 Explain state transition diagram of transaction processing L2 CO4 4M
system.
3 Explain 2 phase locking protocol with example. L5 CO4 4M
4 Write about shared and exclusive locking in transaction L4 CO4 4M
processing system.
5 What is time stamp ordering? Explain how it is used for L3 CO4 4M
concurrency control?
6 Discuss various anomalies caused due to interleaved execution L4 CO4 10M
with examples.
7 Discuss about conflict Serializability with an example. L5 CO4 10M
8 What is a deadlock? Explain wait/die and wound/wait schemes L2 CO4 10M
9 How to perform rollback, commit, check pointing operations on L1 CO4 10M
transactions? Explain.
10 Explain transaction recovery techniques L3 CO4 10M

UNIT 5
DETAILED SYLLABUS
Overview of Storages and Indexing, Data on External Storage- File Organization and Indexing –
Clustered Indexing – Primary and Secondary Indexes, Index Data Structures, Hash-Based Indexing –
Tree-Based Indexing, Comparison of File Organization
1 Explain about hash based indexing with an example. L2 CO5 4M
2 What is primary indexing and secondary indexing? Give L3 CO5 4M
examples of each.
3 Write short notes on clustered indexing technique. L3 CO5 4M
4 Explain secondary index with an example. L4 CO5 4M
5 What are the advantages of B+ tree over B tree? Justify. L1 CO5 4M
6 Explain in detail about file organisation in DBMS. L1 CO5 10M
7 When does a collision occur in hashing? Illustrate various L5 CO5 10M
collision resolution techniques.
8 Show insertion and deletion operations in B tree. L5 CO5 10M
5,8,4,20,11,50,9,2,17,34,21,39,40
9 When does a collision occur in hashing? Illustrate various L4 CO5 10M
collision resolution techniques.
10 What is an index? Explain its role in improving database access L1 CO5 10M
L1: Remembering
L2: Understanding
L3: Applying
L4: Analyzing
L5: Evaluating
Course Outcomes:
At the end of the Course, Student will be able to:
CO-1 Describe the use of DBMS and the data models for efficient database design for
real-world applications.
CO-2 Create, maintain and manipulate a relational database using SQL.
CO-3 Apply normal forms for an efficient relational schema design.
CO-4 Understand the issues and techniques in transaction management and concurrency
control.
CO-5 Understand efficient indexing mechanisms for efficient data storage and retrieval.

Text Books:
1 Fundamentals of Database Systems, 6/e RamezElmasri, Shamkant B.
Navathe, PEA,2010
2 Introduction to Database Systems, CJ Date, Pearson
3 Data base Management Systems, Raghurama Krishnan, Johannes
Gehrke, TATA McGraw Hill 3rd Edition

4 Database Systems - The Complete Book, H G Molina, J D Ullman, J

Widom Pearson
5

Reference Books:
1 Database Systems design, Implementation, and Management, Peter Rob
& Carlos Coronel 7th Edition.
2 Database System Concepts.5/e,Silberschatz, Korth,
TMH, 2006
3
4
5