THEORY EXAMINATION (SEM–VI) 2016-17 ADVANCE DBMS

ADVANCE DBMS (NCS069)

Section-Wise Solved Answers

SECTION A – Short Answer Questions

(10 × 2 = 20 marks)

(a) Transaction transparency

Transaction transparency ensures that transactions execute correctly even in the presence of failures, concurrency, or distribution. Users are unaware of internal transaction management details like recovery and concurrency control.

(b) Concurrency control

Concurrency control is the mechanism used to manage simultaneous execution of transactions in a database while maintaining consistency and isolation.

(c) System log

A system log is a file that records all changes made to the database by transactions. It is used for recovery in case of system failure.

(d) Orphan and inconsistent message

Orphan message: A message whose parent transaction has been aborted.

Inconsistent message: A message that violates database consistency due to improper execution order.

(e) Checkpoint

A checkpoint is a recovery technique where the DBMS saves the current state of the database to disk, reducing recovery time after failure.

(f) Semi joins and bloom joins

Semi join: Reduces data transfer by sending only required tuples.

Bloom join: Uses a bloom filter to reduce data transmission in distributed joins.

(g) Data replication

Data replication is the process of storing copies of data at multiple sites to improve availability, fault tolerance, and performance.

(h) Serializable schedules

A schedule is serializable if its outcome is equivalent to some serial execution of transactions, ensuring database consistency.

(i) Thomas write rule

Thomas write rule allows ignoring obsolete write operations based on timestamps, reducing unnecessary rollbacks in timestamp-based protocols.

(j) Active databases

Active databases automatically respond to events using rules of the form Event-Condition-Action (ECA).

SECTION B – Long Answer Questions

(Attempt any FIVE – 10 × 5 = 50 marks)

(a) MOSS concurrency protocol and architecture

MOSS (Multiversion Optimistic Serialization with Snapshot isolation) is a concurrency control protocol that allows transactions to read consistent snapshots without blocking.

Architecture:                                         Multiple versions of data

Snapshot isolation                                  Validation phase before commit

Advantages:                                           High concurrency

Reduced locking                                      Better performance for read-heavy workloads

(b) Why row-level locking is preferred over table-level locking

Row-level locking locks only the required rows, allowing other transactions to access remaining rows. This improves concurrency and reduces waiting time compared to table-level locking, which blocks the entire table.

(c) Deferred vs Immediate modification recovery schemes

Deferred modification:                          Updates applied after commit

Simple recovery                                        No undo required

Immediate modification:                       Updates applied before commit

Faster response                                         Requires undo and redo

Deferred modification is easier to implement, while immediate modification has higher overhead.

(d) Database replication techniques and eager replication parameters

Replication techniques include:                Master-slave

Multi-master                                             Snapshot replication

Eager replication parameters:               Consistency

Atomicity                                                  Synchronization

Communication cost

(e) Deadlock and handling in distributed DBMS

Deadlock occurs when transactions wait indefinitely for resources held by each other.

Handling methods:                                Deadlock prevention

Deadlock detection using wait-for graph

Deadlock resolution by aborting transactions

(f) Fragmentation in distributed databases

Fragmentation divides database into smaller pieces for distribution.

Types:

Horizontal fragmentation                     Vertical fragmentation

Hybrid fragmentation                           Purpose is improved performance, locality of access, and scalability.

(g) Lock scheduler and lock table

A lock scheduler manages lock requests.

Lock process:                                      Transaction requests lock

Scheduler checks lock table                 Grant or block request

Lock table contains:                          Data item

Lock mode                                           Transaction ID

Lock status

(h) Distributed transaction management

In distributed DBMS, transactions span multiple sites.

Differences from standalone DB:

Communication delays                        Partial failures

Coordination issues

Two-Phase Commit (2PC) ensures atomicity across sites.

SECTION C – Very Long Answer Questions

(Attempt any TWO – 15 × 2 = 30 marks)

Q3. Multiple Granularity Locking

Multiple granularity allows locking at different levels such as:

Database                                             Table

Page                                                     Row

Example:
A transaction locks a table in intention-shared mode and rows in shared mode.

Significance:                                       Reduces overhead

Improves concurrency                          Flexible locking mechanism

Q4. Concurrency control protocols

Lock-based protocol

Uses locks (shared/exclusive) to control access. Ensures serializability but may cause deadlocks.

Timestamp-based protocol

Orders transactions based on timestamps. Prevents deadlocks but may cause rollbacks.

Q5. RDBMS vs OODBMS

Object vs Entity:
Objects encapsulate data and behavior, while entities represent real-world objects without behavior.