THEORY EXAMINATION (SEM–IV) 2016-17 DATABASE MANAGEMENT SYSTEM

Subject: Database Management System (ECS402)

Exam Type: Theory
Semester: 4th
Session: 2016–17
Duration: 3 Hours
Maximum Marks: 100

Section A – Short Answer Questions (10 × 2 = 20 Marks)

This section focuses on short, conceptual questions to test fundamental understanding of DBMS principles.

Topics Covered:

Specialization: Concept of subclass/superclass in E-R modeling.

Advantages of Database Systems: Data integrity, reduced redundancy, easy data access.

DML (Data Manipulation Language): Definition and purpose (INSERT, DELETE, UPDATE, SELECT).

Logical Data Independence: Ability to change the logical schema without altering the external schema.

Entity Integrity Constraint: Ensuring that primary keys are unique and not null.

2NF (Second Normal Form): Definition and importance in normalization.

ACID Property – Isolation (I): Ensures transactions are executed independently.

Schedule: Definition and classification into serial and non-serial schedules.

Exclusive Lock: A lock mode used to prevent other transactions from reading or writing data.

Replication in Distributed Databases: Copying and maintaining database objects in multiple locations.

Section B – Descriptive / Analytical Questions (5 × 10 = 50 Marks)

This section consists of longer descriptive questions related to DB design, normalization, SQL, and concurrency control.

Key Questions:

Database Administrator (DBA): Discuss the role, functions, and responsibilities of a DBA.

Keys: Define and differentiate Primary Key, Super Key, Candidate Key, and Foreign Key with examples.

E-R Diagram (Hospital/Bank System):

Draw the ER diagram showing Specialization, Aggregation, and Generalization.

Convert it into relational schemas and write equivalent SQL DDL statements.

Join and Its Types: Discuss inner join, outer join, left and right joins with examples.

Triggers: Define triggers and explain types (BEFORE/AFTER, ROW/STATEMENT) with syntax and examples.

Short Notes:

MVD (Multivalued Dependency) and JD (Join Dependency)

Normalization and its advantages

BCNF vs 3NF – structure and dependency rules

Logs in DBMS: Explain what a log is, its importance in recovery, and features of Deferred Database Modification and Immediate Database Modification.

Two-Phase Locking (2PL): Explain the working of 2PL protocol with an example ensuring serializability

Section C – Advanced / Long Answer Questions (2 × 15 = 30 Marks)

These are comprehensive questions testing practical understanding of transaction management and SQL query formulation.

Questions:

Serializability:   Definition and importance.

Discuss conflict and view serializability with examples.

Explain how to test serializability using precedence graphs.

Concurrency Control:      

Explain multi-version concurrency control (MVCC) schemes with an example.

Discuss timestamp-based protocols and how they maintain consistency.

SQL and Relational Algebra Queries:
Based on relations:

Student(Roll_No, Name, Branch)

Book(ISBN, Title, Author, Publisher)

Issue(Roll_No, ISBN, Date_of_Issue)

List Roll Number and Name of all CSE branch students.

Find names of students who have issued books published by ‘BPB’.

List titles and authors of books issued by students whose names start with ‘A’.

List titles of all books issued on or before 20/09/2012.

List names of students who have issued books written by author ‘Sanjeev’

Major Topics Covered

DBMS architecture and data models                       ER modeling and relational schema design

Normalization (1NF to BCNF)                                  SQL commands and relational algebra

Transaction management and ACID properties      Concurrency control techniques (2PL, MVCC, Timestamp)

Database recovery and log management               Keys and constraints in relational databases

Purpose of the Paper

This exam evaluates the student’s ability to:

Design efficient databases using E-R modeling and normalization.

Write accurate SQL queries and relational algebra expressions.

Understand and apply transaction management and concurrency control.

Recognize the role of database security, recovery, and replication.

It bridges theoretical knowledge with practical database management skills, crucial for software development and system design.