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

Subject: Database Management System (CS402)

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 contains direct conceptual questions covering core DBMS concepts

Topics included:

Specialization: Concept of sub-entities in E-R models.

Advantages of Database: Data sharing, integrity, and security.

DML (Data Manipulation Language): Definition and use in SQL.

Logical Data Independence: Separation of logical and physical schema.

Entity Integrity Constraints: Rules ensuring unique and non-null primary keys.

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

ACID Property (I - Isolation): Ensuring independent transaction execution.

Schedule: Definition and types (serial, non-serial).

Exclusive Lock: Concept in concurrency control.

Replication: Definition and role in distributed databases.

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

These questions focus on database design, SQL, concurrency, and normalization concepts

Key questions include:

Database Administrator (DBA): Role, responsibilities, and functions.

Explanation and examples of Primary Key, Super Key, Candidate Key, and Foreign Key.

E-R Diagram: Hospital or Bank database showing specialization, aggregation, and generalization; convert to relational schemas and SQL DDL statements.

Join Operations: Explanation of different joins (inner, outer, left, right) with examples.

Triggers: Definition, types (before/after, row/statement), and syntax examples.

Short Notes:

MVD (Multi-Valued Dependency) and JD (Join Dependency)

Normalization and its advantages

Comparison between BCNF and 3NF

Log Management: Explanation of deferred and immediate database modification techniques.

Two-Phase Locking (2PL): Process and example for maintaining serializability.

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

These are advanced questions on concurrency, transaction management, and query writing

Questions include:

Serializability:               Conflict and view serializability with examples.

                                       Serializability testing methods.

Concurrency Control:

                                                   Multiversion schemes explained with examples.

                                                   Description of timestamp-based protocols for concurrency control.

Relational Algebra & SQL Queries:

                                                                   Based on the following schema:

Student (Roll No, Name, Branch)               Book (ISBN, Title, Author, Publisher)

Issue (Roll No, ISBN, Date_of_Issue)          List Roll No and Name of all CSE students.

Find students who issued books published by ‘BPB’.

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

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

Find names of students who issued books written by author ‘Sanjeev’.

Major Topics Covered

Database design (E-R models, keys, constraints)          Normalization (1NF–BCNF)

SQL and relational algebra                                           Transactions and concurrency control

Locking, timestamping, and logging                            Triggers and distributed databases

Purpose of the Paper

This exam tests both theoretical understanding and practical SQL application of database concepts. It evaluates skills in designing normalized databases, writing SQL queries, and managing concurrency and transactions in multi-user environments.