📊 Database Management System

MAKAUT B.Tech (PCC-CS601) 2023–24 – Detailed Solutions

✅ Group-A (1 Mark) | Group-B (5 Marks) | Group-C (15 Marks) | Exam-ready answers
📌 Group-A (Very Short Answer – 1 Mark Each)
(i) Rectangle in ER diagram represents

Answer: Entity Set

(ii) Which is not a fundamental operation in relational algebra?

Answer: Natural Join

(iii) Top-to-bottom relationship among items is

Answer: Hierarchical Schema

(iv) Which is not an example of DBMS?

Answer: Google

(v) SQL Injection is a

Answer: Code Penetration Technique

(vi) What is Granularity?

Answer: The size of a data item (unit of locking).

(vii) Subset of SQL commands used to manipulate data

Answer: Data Manipulation Language (DML)

(viii) A data dictionary does not provide information about

Answer: How data is used

(ix) Weak entity should be associated with

Answer: Strong Entity Set

(x) Full form of TCL

Answer: Transaction Control Language

(xi) SQL Injection attack can

Answer: All of the above

(xii) Integrity constraint in the given statement

Answer: Referential Integrity Constraint

✍️ Group-B (Short Answer – 5 Marks)
1. Discuss ACID Properties of Transaction

Atomicity: all-or-nothing; Consistency: valid state; Isolation: concurrent txns don't interfere; Durability: committed changes persist. Example: Bank transfer: debit & credit both occur or none.

2. Distinguish Between Locking and Timestamp Protocols
Locking ProtocolTimestamp Protocol
Uses locks, may cause deadlockUses timestamps, deadlock free
Transactions waitTransactions abort/restart
Example: 2PLExample: Timestamp Ordering
3. What is Heuristic Based Optimization?

Rule-based optimization: early selection/projection, replace Cartesian product with join, reduce intermediate results → faster execution, lower memory.

4. What is Data Abstraction? Discuss DBA Role

Abstraction levels: Physical, Logical, View. DBA: user management, security, backup/recovery, performance tuning, schema management, authorization.

5. Discuss Different Anomalies with Examples

Insertion anomaly: can't insert without related data. Deletion anomaly: removing one deletes useful info. Update anomaly: inconsistency due to duplication.

📘 Group-C (Long Answer – 15 Marks)
7(a) ER Diagram Problem – Supplier–Item System (M:N relationship)

Entities: Supplier (Sup_ID PK, Name, Address), Item (Item_Type PK, Description). Relationship Supplies with attributes: Quantity, Price. Cardinality: M:N.

Supplier (Sup_ID, Name, Address) ---- Supplies (Qty, Price) ---- Item (Item_Type, Description)
7(b) B-Tree Construction (Order 3) on keys: 10,20,30,12,15,35,18,38,8,50

Final B-Tree: Root [20]; left child [10,15]; right child [35]; leaves: [8],[12],[18],[30],[38,50].

8(a) Explain ACID Properties (with diagram/example)

Atomicity, Consistency, Isolation, Durability. Example: money transfer ensures both debit/credit or none; after commit, data persists.

8(b) Wait-Die and Wound-Wait Protocols

Wait-Die: older waits, younger dies. Wound-Wait: older wounds (aborts) younger. Both prevent deadlock in concurrency control.

8(c) B+ Tree with Example

Data only in leaves, internal nodes contain search keys, leaves linked. Example: root [20|40] → leaves [10],[30],[50]. Advantages: fast search, efficient range queries.

9(a) Why Query Optimization is Needed? Heuristic Optimization

Reduces time, disk access, memory. Steps: parsing → translation → optimization → plan generation → execution. Heuristic uses rules (e.g., early selection).

9(b) Granularity of Locking

Levels: Database, Table, Page, Record, Field. Fine granularity → better concurrency, coarser → less overhead.

9(c) Deadlock Avoidance in Concurrency

Methods: Wait-Die, Wound-Wait, Timeout, Resource Ordering. Prevents circular wait, improves concurrency.

10. Short Notes: 5NF, View, Trigger, Recovery, Concurrency Problems, 2PL, Cursor

5NF: eliminates join dependency. View: virtual table. Trigger: automatic procedure on event. Recovery: restores via logs/checkpoints. Problems: lost update, dirty read, unrepeatable read, phantom. 2PL: growing + shrinking phases → serializability. Cursor: row-by-row processing.

11(a) Two-Phase Locking Solution (A=5000, B=3000)

T1: Lock-X(A), Read(A), A=A-500, Write(A), Lock-X(B), Read(B), B=B+500, Write(B), Unlock(A), Unlock(B), Commit. T2: Lock-S(A), Read(A), Lock-S(B), Read(B), Display, Unlock(A), Unlock(B). Follows 2PL (all locks before release).

11(b) DAC and MAC
DAC (Discretionary)MAC (Mandatory)
Owner controlled, GRANT/REVOKESystem controlled by security levels
Flexible, less secureStrict, more secure (military)

📖 Exam-focused Summary

✔ Complete coverage of MAKAUT DBMS (PCC-CS601) 2023–24 – all groups.
✔ Includes ER diagram, relational algebra, hierarchical schema, SQL injection, granularity, DML, ACID, locking vs timestamp, heuristics, data abstraction, anomalies, B-Tree/B+Tree, Wait-Die/Wound-Wait, query optimization, 2PL, DAC/MAC, and short notes.
✔ Answers structured for 1, 5, and 15-mark questions with clear headings & exam-style presentation.