(SEM V) THEORY EXAMINATION 2022-23 DATABASE MANAGEMENT SYSTEM

Course: B.Tech (Semester V)                                                               Subject Code: KCS-501

Subject Name: Database Management System                                Time: 3 Hours

Total Marks: 100

Note: Attempt all sections. If any data is missing, assume suitably. 

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

Answer all in brief:

List any four disadvantages of the file system approach over the database approach.

Differentiate between physical and logical data independence.

Difference between DROP and DELETE commands.

Explain different Integrity Constraints.

List all prime and non-prime attributes for relation R(A,B,C,D,E) with F = {AB→C, B→E, C→D}.

Explain Multivalued Dependency (MVD) with an example.

Discuss Consistency and Isolation properties of a transaction.

Draw a state diagram showing typical states of a transaction during execution.

Differentiate between Conservative 2PL and Strict 2PL.

Describe how View Serializability relates to Conflict Serializability. 

Section B – Descriptive Questions (10 × 3 = 30 Marks)

Attempt any three:

Design an E-R diagram for a sports league database, map it to a relational model.

Explain Joins and types (Inner, Outer, Left, Right, Full) with examples.

For relation R{A, B, C, D, E, F} with FDs:

Find a minimum cover for this FD set.

Define a schedule and explain recoverable, cascadeless, and strict schedules.

Discuss Immediate Update Recovery Technique for both single-user and multiuser environments. 

Section C – Long Answer Questions (10 × 5 = 50 Marks)

Q3

(a) Describe the Three-Schema Architecture and explain the need for mappings between schema levels.
or
(b) Explain different types of Data Models in DBMS.

Q4

(a) For schema:

Student (RollNo, Name, Father_Name, Branch) Book (ISBN, Title, Author, Publisher) Issue (RollNo, ISBN, Date_of_Issue)

Write SQL and relational algebra queries for:

(i) Students of branch ‘CSE’                                            (ii) Student who issued a book by publisher ‘ABC’

(iii) Titles of books issued to student ‘RAM’                  (iv) Books issued on or before Dec 1, 2020

(v) Books published by ‘ABC’
or
(b) Explain types of Triggers in SQL/PL-SQL.

Q5

(a) Given schema R(V,W,X,Y,Z) and FDs {Z→V, W→Y, XY→Z, V→WX},
check if decompositions:

(i) R1=(V,W,X), R2=(V,Y,Z)                                              (ii) R1=(V,W,X), R2=(X,Y,Z)
are lossless join decompositions.
or
(b) For relation R={A,B,C,D,E,F,G,H,I,J} with

F = {AB→C, A→DE, B→F, F→GH, D→IJ} 

Find keys for R and decompose into 2NF and 3NF.

Q6

(a) Determine whether each schedule (S1, S2, S3) is strict, cascadeless, recoverable, or non-recoverable.
or
(b) For transactions T1, T2, T3 with given schedules S1, S2, check if schedules are serializable and find equivalent serial schedules.

Q7

(a) Discuss Timestamp Ordering Protocol for concurrency control and differentiate strict vs basic timestamp ordering.
or
(b) Explain Optimistic Concurrency Control (OCC) techniques, why they are also called validation/certification techniques, and describe the phases of OCC. 

AB → C, C → A, BC → D, ACD → B, BE → C, EC → FA, CF → BD, D → E