(SEM V) THEORY EXAMINATION 2024-25 DATABASE MANAGEMENT SYSTEM

Subject Code: BCS501
Maximum Marks: 70
Time: 3 Hours
Paper ID: 310120

Question Paper Overview

SECTION A (2 × 7 = 14 Marks)

(Short conceptual and definition-based questions)

a. Define candidate key and super key with example.
b. Differentiate between TRUNCATE and DELETE command.
c. Define triggers and mention their types.
d. Analyze and find the Functional Dependencies (FDs) in the given relation:

e. List all prime and non-prime attributes in Relation R(A,B,C,D,E) with FD set F = {AB → C, B → E, C → D}.
f. Explain properties of a transaction.
g. Define multiple granularity in concurrency control.

SECTION B (Attempt any three × 7 = 21 Marks)

a. Explain the architecture of DBMS with a neat diagram, describing its major components and layers.
b. Explain joins. Discuss all types of joins (inner, outer, left, right, self) with examples.
c. Given relation R(P,Q,S,T,X,Y,Z,W) and FDs:

PQ → X                       P → YX

Q → Y                         Y → ZW

Check if the given decompositions are lossy or lossless:

D₁: R = {(P,Q,S,T), (P,T,X), (Q,Y), (Y,Z,W)}

D₂: R = {(P,Q,S), (T,X), (Q,Y), (Y,Z,W)}
d. Explain types of transaction failures and describe recovery techniques using log files with examples.
e. Discuss concurrency control, its need, and explain the timestamp-based ordering protocol.

SECTION C (Attempt one part from each question × 7 = 35 Marks)

Q3

(a) Illustrate the concept of data independence (logical and physical) with examples.
OR
(b) Reduce the given ER diagram (University example) into relational tables:

Entities: Student(StudentID, Name, Email), Course(CourseID, CourseName, Credits), Enrollment(EnrollmentID, DateEnrolled)

Relationship: Many-to-Many between Student and Course via Enrollment.
Identify primary and foreign keys for each relation.

Q4

(a) SQL queries for Employee Database:
Tables:                                                                     Employee(ename, street, city)

Worksfor(ename, company_name, salary)              Company(company_name, city)
Tasks:

Create the tables with data types.                          Find employees who live and work in the same city.

Find employees with salary > ₹50,000.                  Find employees not working in ‘TCS’.

Find employees whose second letter in name is ‘A’.         Find employee with second highest salary.

OR

(b) Relational Algebra (RA) queries using S(S#,SNAME,SCITY,TURNOVER), P(P#,WEIGHT,COLOR,COST,SELLING_PRICE), SP(S#,P#,QTY):

Get suppliers from Calcutta with TURNOVER = 80.             Get part numbers with weight between 25 and 55.

Get part numbers with cost > selling price.                          Get part numbers with color red or black.

Get supplier name where S# = 101.

Q5

(a) For Library Database:
Relations –                                                      Book(Title, Author, Catalog_no, Publisher, Year, Price)

                                                                       Collection(Title, Author, Catalog_no)
FDs –

Title, Author → Catalog_no

                                                                       Catalog_no → Title, Author, Publisher, Year

                                                                       Publisher, Title, Year → Price
➤ Find the highest normal form of both Book and Collection.

OR

(b) For relation R = {A,B,C,D,E,F,G,H,I,J} with FDs:

{A,B} → {C}             {A} → {D,E}              {B} → {F}            {F} → {G,H}            {D} → {I,J}
➤ Identify keys for R and define Multivalued Dependency (MVD) with example.

Q6

(a) Check if the given schedule is conflict serializable and view serializable:

S1: R1(X), R2(X), R2(Y), W2(Y), R1(Y), W1(X)

OR
(b) Define schedule and transaction. Explain recoverable, cascadeless, and strict schedules, comparing their recoverability.

Q7

(a) Explore:                                 Multiversion protocol

                                                  Validation-based protocol
OR
(b) Explain deadlock. Discuss its necessary conditions, and detection/recovery mechanisms.

Key Topics for Revision

1. DBMS Architecture

Three-level architecture:                            External (User View)

Conceptual (Logical Schema)                        Internal (Physical Storage)

Provides data independence and abstraction layers.

2. Keys in Relational Model

3. SQL Topics

DDL: CREATE, ALTER, DROP          DML: SELECT, INSERT, UPDATE, DELETE

TRUNCATE vs DELETE:

TRUNCATE: removes all rows, faster, non-rollback.  DELETE: removes selected rows, rollback possible.

4. Transaction Management

ACID Properties: Atomicity, Consistency, Isolation, Durability.

Concurrency Control: Locking, timestamp ordering, validation-based.

Recovery: Based on log files, checkpointing, and undo/redo methods.

5. Functional Dependencies & Normalization

FD: X → Y (Y depends on X).        Prime Attribute: Part of candidate key.

Normal Forms:

1NF: Atomic values.                                  2NF: No partial dependency.

3NF: No transitive dependency.               BCNF: Every determinant is a candidate key.

6. ER to Relational Mapping

Entities → Tables                                        Attributes → Columns

Relationships → Foreign Keys                    Many-to-many → Separate linking table.

7. Relational Algebra

Operations include:

Selection (σ)           Projection (π)

Join (⨝)                  Union (∪)

Difference (−)

8. Concurrency & Deadlock

Deadlock Conditions:

Mutual exclusion              Hold and wait

No preemption                Circular wait

Solutions: Prevention, detection, recovery (rollback).

9. Schedules

10. Protocols

Multiversion Protocol: Maintains multiple versions of data items.

Validation Protocol: Validation phase checks serializability before commit.