Module-5 (DBMS-BCS403): Question Bank

Qn-1: What is Concurrency Control in Database and Explain Two-Phase Locking

Protocol with diagram.
ANS: Concurrency Control is a technique used in DBMS to manage the simultaneous
execution of multiple transactions without causing inconsistency in the database.
When many users access and update the same data at the same time, concurrency
control ensures that the database remains accurate, consistent, and reliable.

Example
Consider two bank transactions occurring simultaneously:
T1: Withdraw ₹1000 from Account A.
T2: Deposit ₹500 into Account A.
If both transactions access the account balance at the same time without proper
control, the final balance may become incorrect.
Concurrency control ensures that the transactions execute in a controlled manner
and produce the correct result.

Techniques of Concurrency Control

 Lock-Based Protocols (Two-Phase Locking)
 Timestamp-Based Protocols
 Validation (Optimistic) Protocols
 Multiversion Concurrency Control (MVCC)

Lock-Based Protocols (Two-Phase Locking)

The Two-Phase Locking (2PL) Protocol is a concurrency control technique used
in DBMS to ensure serializability and maintain data consistency when multiple
transactions execute simultaneously. According to this protocol, a transaction must
follow two distinct phases while acquiring and releasing locks on data items.
Phases of Two-Phase Locking Protocol:

1. Growing Phase
 In this phase, a transaction can acquire new locks on data items.
 However, it cannot release any lock during this phase.
 The transaction keeps obtaining all the locks required for its execution.
2. Shrinking Phase
 In this phase, a transaction starts releasing locks.
 Once a lock is released, the transaction cannot acquire any new lock.
 The transaction continues releasing locks until it completes execution.

Working of 2PL Protocol:

 Before reading or writing a data item, a transaction must obtain the appropriate
lock.
 During the growing phase, locks are acquired.
 After the transaction releases its first lock, it enters the shrinking phase.
 No new locks can be obtained after entering the shrinking phase.
  This protocol ensures conflict-serializable schedules and prevents data
inconsistency.

Advantages:
 Ensures serializability of transactions.
 Maintains database consistency.
 Prevents problems such as lost updates and dirty reads.

Disadvantage:
 May lead to deadlocks, where two or more transactions wait indefinitely for
each other’s locks.

Qn-2: Discuss different types of locks in concurrency control.

ANS: In a DBMS, locks are used to control concurrent access to data items and maintain
database consistency. A lock prevents multiple transactions from accessing the same
data in a conflicting manner. Different types of locks are used depending on the
operation being performed on the data item.
1. Binary Lock

 A Binary Lock has only two states: Locked (1) and Unlocked (0).
 When a data item is locked by a transaction, no other transaction can access it.
 It is the simplest form of locking mechanism used in concurrency control.
2. Shared Lock (S-Lock)

 A Shared Lock is used when a transaction wants to read a data item.

 Multiple transactions can hold shared locks on the same data item simultaneously.
 Since transactions only read the data, they do not affect each other.
 Shared locks improve concurrency by allowing multiple read operations at the
same time.
3. Exclusive Lock (X-Lock)

 An Exclusive Lock is used when a transaction wants to modify (write) a data item.
 When a transaction holds an exclusive lock, no other transaction can read or write
that data item.
 This prevents data inconsistency during update operations.
 Exclusive locks provide complete control over the data item to the transaction
holding the lock.
Qn-3: Explain Timestamp Ordering Protocol.
ANS: The Timestamp Ordering Protocol (TOP) is a concurrency control technique used
in DBMS to ensure that transactions are executed in a serializable order based on their
timestamps. Each transaction is assigned a unique timestamp when it starts execution.
The protocol uses these timestamps to determine the order in which transactions are
allowed to read and write data items.

Working of Timestamp Ordering Protocol:

 When a transaction enters the system, it is assigned a timestamp (TS).
 The timestamp indicates the transaction's age. A smaller timestamp means an
older transaction, while a larger timestamp means a newer transaction.
 The protocol ensures that conflicting operations are executed according to the
timestamp order.
 If an operation violates the timestamp order, the transaction is rolled back and
restarted with a new timestamp.
Read Operation Rule
 Before a transaction T reads a data item Q, the system checks the Write
Timestamp (WTS) of Q.
 If TS(T) < WTS(Q), the read operation is rejected and the transaction is rolled
back.
 Otherwise, the read operation is allowed.
Write Operation Rule
 Before a transaction T writes a data item Q, the system checks the Read
Timestamp (RTS) and Write Timestamp (WTS) of Q.
 If TS(T) < RTS(Q) or TS(T) < WTS(Q), the write operation is rejected and the
transaction is rolled back.
 Otherwise, the write operation is performed successfully.
Advantages
 Ensures serializability of transactions.
  Prevents deadlocks because transactions do not wait for locks.
 Maintains database consistency.
Disadvantage
 Frequent rollbacks may occur when many transactions conflict with each other.

Qn-4: Explain Multi-version Concurrency Control Techniques.

ANS: Multi-version Concurrency Control (MVCC) is a concurrency control technique
in DBMS that maintains multiple versions of a data item instead of keeping only one
copy. When a transaction updates a data item, a new version of that item is created,
while the old version is retained. This allows multiple transactions to access the database
simultaneously without interfering with each other.

Working of MVCC
 In MVCC, every write operation creates a new version of the data item.
 Read operations can access an older version of the data item if required.
 Each version is associated with a timestamp indicating when it was created.
 A transaction reads the version that was valid at the time the transaction
started.
 This reduces conflicts between read and write operations.

Features of Multi-version Concurrency Control

1. Multiple Versions of Data
 The database maintains several versions of the same data item.
 Old versions are preserved until they are no longer needed.
2. Concurrent Read and Write Operations
 Readers can access old versions while writers create new versions.
 Read operations do not block write operations and vice versa.
3. Improved Concurrency
 Multiple transactions can execute simultaneously with fewer conflicts.
  Database throughput and performance are improved.
4. Consistent View of Data
 Each transaction sees a consistent snapshot of the database.
 This ensures correct and reliable query results.
Advantages
 Provides high concurrency.
 Eliminates read-write conflicts.
 Improves system performance.
 Allows transactions to access consistent data.
Disadvantage
 Requires additional storage space to maintain multiple versions of data items.

Qn-5: Explain Validation (Optimistic) Concurrency Control.

ANS: Validation (Optimistic) Concurrency Control (OCC) is a concurrency control
technique in which transactions execute without locking data items during their
execution. It assumes that conflicts between transactions are rare and checks for
conflicts only before a transaction commits. This method improves concurrency and
avoids deadlocks.
 Phases of Validation (Optimistic) Concurrency Control
1. Read Phase:
 The transaction reads data items from the database and performs all
required computations.
 Any updates are stored in a local workspace and are not immediately
written to the database.
2. Validation Phase:
 Before committing, the system checks whether the transaction violates
serializability.
 If no conflict is found with other concurrent transactions, validation
succeeds.
3. Write Phase :
 If validation is successful, all updates are written to the database.
 If validation fails, the transaction is rolled back and restarted.

Advantages
 No locking overhead during transaction execution.
 Deadlocks cannot occur because locks are not used.
 Suitable for environments where conflicts are infrequent.
 Provides higher concurrency and better resource utilization.
Disadvantages
 Transactions may need to be restarted if validation fails.
 Performance decreases when transaction conflicts are frequent.
 Not suitable for highly contended databases.

Qn-6: Explain Multiple Granularity Locking.

ANS: Multiple Granularity Locking (MGL) is a locking technique that allows
transactions to lock data items at different levels of granularity such as database, file,
page, or record level. Since the best granularity size depends on the type of transaction,
DBMS supports multiple levels of granularity to improve both concurrency and
performance.

Granularity Hierarchy
The database is organized in a hierarchical structure. A lock can be requested at
any level of the hierarchy.
Intention Locks
To make Multiple Granularity Locking practical, special locks called Intention
Locks are used. These locks indicate the type of lock that a transaction intends to acquire
on lower-level nodes.
Types of Intention Locks
1. IS (Intention Shared) Lock
o Indicates that shared locks will be requested on descendant nodes.
2. IX (Intention Exclusive) Lock
o Indicates that exclusive locks will be requested on descendant nodes.
3. SIX (Shared Intention Exclusive) Lock
o Indicates that the current node is locked in shared mode and exclusive
locks may be requested on some descendant nodes.

Rules of Multiple Granularity Locking

1. The root node of the hierarchy must be locked first.
2. A node can be locked in S or IS mode only if its parent is locked in IS or IX
mode.
3. A node can be locked in X, IX, or SIX mode only if its parent is locked in IX or
SIX mode.
 4. A transaction can lock a node only if it has not unlocked any node earlier,
following the Two-Phase Locking (2PL) protocol.
5. A node can be unlocked only when none of its children are locked by the same
transaction.
Advantages
 Supports both fine-grained and coarse-grained locking.
 Improves concurrency among transactions.
 Reduces locking overhead for large transactions.
 Suitable for a mix of short transactions accessing a few records and long
transactions accessing entire files.

Qn-7: What is NoSQL? Explain its characteristics.

ANS: NoSQL (Not Only SQL) is a class of database systems designed to handle large
volumes of structured, semi-structured, and unstructured data. NoSQL databases are
developed to support distributed computing, high scalability, high availability, and
flexible data models. Unlike traditional relational databases, NoSQL systems do not
require a fixed schema and are suitable for Big Data applications.

Characteristics of NoSQL Databases

1. Flexible Data Model
 NoSQL databases support structured, semi-structured, and unstructured data.
 They do not require a predefined schema, making data storage more flexible.
 New attributes can be added without modifying the entire database structure.
2. High Scalability
 NoSQL systems are designed for horizontal scaling.
 Additional servers (nodes) can be added easily to handle increasing data and user
requests.
 This makes NoSQL suitable for large-scale web applications.
3. Distributed Architecture
 Data is distributed across multiple servers or nodes.
 The workload is shared among nodes, improving performance and reliability.
 Even if one node fails, the system can continue functioning.
4. High Availability
 NoSQL databases use replication techniques to maintain multiple copies of data.
 Data remains accessible even when some servers fail.
 This ensures continuous service to users.
5. Support for Big Data
 NoSQL databases can efficiently store and process very large datasets.
 They are widely used in social media, cloud computing, e-commerce, and real-
time analytics applications.
6. Efficient Data Access
 NoSQL systems provide fast data retrieval using techniques such as hashing and
range partitioning.
 This enables quick access to millions of records with minimal delay.
7. Replication Support
 Data is copied across multiple nodes using master-slave or master-master
replication models.
 Replication improves fault tolerance and availability.
8. Sharding (Horizontal Partitioning)
 Large datasets are divided into smaller partitions called shards.
 Each shard is stored on a different node.
 Sharding improves load balancing and system performance.

Types of NoSQL Databases

 The major categories of NoSQL databases are:
1. Document-based databases
2. Key-value stores
3. Column-based databases
4. Graph databases
Advantages of NoSQL
 Flexible schema design.
 High scalability and availability.
 Supports distributed environments.
 Efficient handling of Big Data.
 Faster data access and processing.

Qn-8: Explain CAP Theorem.

ANS: The CAP Theorem is an important concept in distributed database systems.
It states that a distributed system with replicated data cannot guarantee all three
properties simultaneously:
1. Consistency (C)
2. Availability (A)
3. Partition Tolerance (P)
A distributed system can guarantee at most two out of these three properties at the
same time.

Three Properties of CAP Theorem

1. Consistency (C)
 All nodes contain the same and most recent copy of data.
 Whenever data is updated, every user sees the latest value.
 No two copies of the same data should have different values.
2. Availability (A)
 Every read or write request receives a response.
 The system remains available even when some nodes fail.
 Users can continue accessing data without interruption.
3. Partition Tolerance (P)
 The system continues to operate even if communication between nodes is lost
due to network failure.
 Different partitions of the distributed system can still function independently.
Simple Diagram of CAP Theorem

Example
Consider an online shopping application with data stored on two servers:
Server A ─────X───── Server B
(Network Failure)
Suppose a customer updates the product quantity on Server A, but due to a
network failure the update cannot reach Server B.
Now the system has two choices:

Case 1: Choose Consistency + Partition Tolerance (CP)

 Server B refuses user requests until it receives the latest update.
 All users see correct and consistent data.
  Availability is sacrificed.

Case 2: Choose Availability + Partition Tolerance (AP)

 Both servers continue serving users.
 Users can access the system even during network failure.
 Some users may see old data.
 Consistency is sacrificed.
Thus, it is impossible to provide Consistency, Availability, and Partition
Tolerance together during a network partition.
Importance of CAP Theorem
 Helps in designing distributed databases.
 Explains trade-offs among consistency, availability, and partition tolerance.
 Widely used in NoSQL database systems.
 Many NoSQL systems prefer Availability and Partition Tolerance (AP) and
use eventual consistency.

Qn-9: Explain Document-Based NoSQL Systems and MongoDB CRUD operations.

ANS: Document-Based NoSQL systems store data in the form of documents rather than
rows and columns. Documents are usually represented using formats such as JSON
(JavaScript Object Notation). Each document contains data and its description together,
making the database flexible and easy to modify. Documents can be accessed using a
document ID and can also be retrieved efficiently through indexes. MongoDB is one of
the most popular document-based NoSQL databases.

Characteristics of Document-Based NoSQL Systems

1. Data is stored as documents instead of tables and rows.
2. Documents are generally represented using JSON format.
3. Flexible schema allows different documents to have different fields.
4. Suitable for storing semi-structured and unstructured data.
 5. Supports horizontal scaling and distributed storage.
6. Provides high performance and easy data retrieval through indexes.

Example of a MongoDB Document

Consider a student document stored in MongoDB:
{ "_id": 101,
"Name": "Ravi",
"Branch": "CSE",
"Semester": 5,
"CGPA": 8.5
}
In MongoDB, a collection contains multiple documents similar to a table
containing rows in a relational database.

MongoDB CRUD Operations

CRUD stands for Create, Read, Update, and Delete operations performed on
documents in MongoDB.
1. Create Operation (Insert)
The Create operation is used to insert a new document into a collection.
Syntax:
[Link](
{
"_id":101,
"Name":"Ravi",
"Branch":"CSE",
"Semester":5
}
)
  A new document is added to the student collection.
 MongoDB automatically stores it in JSON-like format.

2. Read Operation (Find)

The Read operation retrieves documents from a collection.
Syntax: [Link]()
Specific Search:
[Link]({"Name":"Ravi"})
 Displays all documents or selected documents based on a condition.
 Used for querying data from MongoDB.
3. Update Operation
The Update operation modifies existing documents.
Syntax:
[Link](
{"Name":"Ravi"},
{$set:{"CGPA":9.0}}
)
 Updates the CGPA field of the student Ravi.
 Existing document is modified without creating a new document.

4. Delete Operation
The Delete operation removes documents from a collection.
Syntax:
[Link](
{"Name":"Ravi"}
)
 Deletes the document matching the specified condition.
 Removes unwanted data from the collection.
 Advantages of MongoDB
1. Flexible schema design.
2. Stores data in JSON-like documents.
3. Easy scalability and replication.
4. High performance for large datasets.
5. Suitable for Big Data and web applications.

Qn-10: Explain Key-Value Stores with examples.

ANS: Key-Value Stores are one of the simplest types of NoSQL databases. In this
model, data is stored as a collection of (Key, Value) pairs, where each key is unique and
is used to access its corresponding value quickly. The value may be a simple data item, a
record, an object, a document, or a more complex data structure.

Working of Key-Value Stores

 Each data item is associated with a unique key.
 The key acts as an identifier for retrieving data.
 When a user provides a key, the system returns the corresponding value.
 This model provides very fast data access and retrieval.
Example
KEY VALUE
--------------------------------
101 Ravi, CSE, 5th Sem
102 Priya, AI&DS, 5th Sem
103 Kiran, ECE, 5th Sem
If the key 102 is searched, the database returns:
Key = 102
Value = Priya, AI&DS, 5th Sem

Characteristics of Key-Value Stores

1. Simple Data Model
 Data is stored as key-value pairs.
 Easy to understand and implement.
2. Fast Data Access
 Data is retrieved directly using the key.
 Provides very high read and write performance.
3. Scalability
 Supports horizontal scaling by adding more servers.
 Suitable for large-scale distributed applications.
4. High Availability
 Data can be replicated across multiple nodes.
 Ensures continuous access even if some nodes fail.
5. Suitable for Big Data Applications
 Can handle millions of records efficiently.
 Widely used in cloud computing and web applications.

Examples of Key-Value Stores

Some popular Key-Value NoSQL databases are:
1. Redis
 Stores data mainly in main memory for very high performance.
 Supports replication and high availability.
2. Amazon DynamoDB
 Distributed key-value database service.
 Provides scalability and fault tolerance.
 3. Riak
 Distributed key-value store designed for high availability.
4. Apache Cassandra
 Supports features of both key-value and column-based databases.

Advantages
 Simple and efficient data model.
 Very fast data retrieval.
 Easy scalability.
 High availability and fault tolerance.
 Suitable for distributed systems.

Qn-11: Explain Column-Oriented NoSQL Databases.

ANS: Column-Oriented NoSQL Databases (also called Wide Column Databases) are a
category of NoSQL databases in which data is stored and organized by columns rather
than by rows. These databases are designed to handle very large amounts of data and
provide efficient storage and retrieval in distributed environments. Examples include
Google BigTable, Apache HBase, and Apache Cassandra.

Concept of Column-Oriented Databases

 In traditional relational databases, data is stored row by row.
 In column-oriented databases, related columns are grouped into column
families and stored together.
 Each column family is maintained separately, improving storage efficiency and
query performance.
Example
Row-Oriented Storage
ID Name Branch CGPA
--------------------------------
1 Ravi CSE 8.5
2 Priya AI&DS 9.0
3 Kiran ECE 8.2
Column-Oriented Storage
ID Column Name Column Branch Column
1 Ravi CSE
2 Priya AI&DS
3 Kiran ECE
In column-oriented storage, values of the same column are stored together,
making retrieval of a particular column faster.

Characteristics of Column-Oriented NoSQL Databases

1. Column Family Storage
 Data is organized into column families.
 Each column family is stored separately.
 Improves storage and retrieval efficiency.
2. Distributed Architecture
 Data is distributed across multiple servers.
 Supports large-scale storage and processing.
3. High Scalability
 Additional nodes can be added easily.
 Suitable for Big Data applications.
4. Supports Versioning
 Multiple versions of data items can be stored.
 Each version is associated with a timestamp.
 5. High Performance
 Efficient for analytical queries involving large datasets.
 Faster retrieval of selected columns compared to row-oriented databases.

HBase Data Model

According to the reference material, HBase organizes data using:
 Namespaces
 Tables
 Column Families
 Column Qualifiers
 Columns
 Rows
 Data Cells
A column is identified by a combination of Column Family : Column
Qualifier. Data values are stored as strings and multiple versions of data can be
maintained using timestamps.
Examples of Column-Oriented NoSQL Databases
1. Google BigTable
 Distributed storage system used in many Google applications.
2. Apache HBase
 Open-source column-oriented database built on Hadoop Distributed File
System (HDFS).
3. Apache Cassandra
 Widely used distributed NoSQL database supporting column-family
storage.
Advantages
 Efficient storage of large datasets.
 Faster access to specific columns.
 Supports distributed processing.
  Highly scalable and fault tolerant.
 Suitable for cloud and Big Data applications.

Qn-12: Explain Graph Databases and Neo4j.

ANS: Graph Databases are a type of NoSQL database designed to store and manage
highly connected data. They represent data using Nodes (Entities), Relationships
(Connections), and Properties (Attributes). Graph databases are useful when
relationships between data items are as important as the data itself.

Graph Database Model

A graph database consists of:
1. Nodes
 Nodes represent entities or objects in the system.
 Examples: Person, Student, Course, Product, City.
2. Relationships
 Relationships connect nodes and represent interactions between them.
 Examples: FRIENDS_WITH, STUDIES_IN, PURCHASED, LIVES_IN.
3. Properties
 Properties store additional information about nodes and relationships.
 Example: Name, Age, Course Name, Product Price.

Structure of a Graph Database

Characteristics of Graph Databases
1. Relationship-Oriented
 Relationships are stored directly with the data.
 Traversing connected data is very fast.
2. Flexible Schema
 New nodes and relationships can be added without changing the entire database
structure.
3. High Performance
 Efficient for queries involving complex relationships and network structures.
4. Easy Data Visualization
 Data is naturally represented as graphs, making it easier to understand
connections.
5. Suitable for Connected Data
 Widely used in social networks, recommendation systems, fraud detection, and
knowledge graphs.

Neo4j
Neo4j is one of the most popular open-source Graph Database Management
Systems. It stores data as nodes, relationships, and properties rather than tables and
rows. Neo4j is optimized for handling highly connected data efficiently.
Features of Neo4j
 Uses a native graph storage model.
  Supports fast graph traversal.
 Provides ACID transaction support.
 Uses Cypher Query Language for querying graph data.
 Scales well for relationship-intensive applications.

Example in Neo4j
(Node) Student: Ravi
│
│ ENROLLED_IN
▼
(Node) Course: DBMS
Here:
 Student and Course are nodes.
 ENROLLED_IN is a relationship.
 Additional properties such as Student ID, Name, and Course Code can be stored
with the nodes.

Applications of Graph Databases

 Social networking sites.
 Recommendation systems.
 Fraud detection systems.
 Network and route analysis.
 Knowledge management systems.

Advantages
 Efficient handling of interconnected data.
 Fast relationship-based queries. , Flexible and scalable structure.
 Easy representation of real-world networks.
 Better performance for graph traversal operations.
Qn-13: Compare SQL and NoSQL Databases.
ANS: Comparison Between SQL and NoSQL Databases
Feature SQL Databases NoSQL Databases

Data is stored in tables

Data is stored as key-value pairs,
Data Model consisting of rows and
documents, columns, or graphs.
columns.
Schema Fixed and predefined schema. Flexible and dynamic schema.
Generally supports vertical
Supports horizontal scaling by
Scalability scaling by increasing hardware
adding more servers.
resources.
Suitable for structured, semi-
Data Structure Suitable for structured data.
structured, and unstructured data.
Relationships are handled
Supports complex relationships
Relationships differently depending on the
using joins.
NoSQL model.
Good for complex transactions High performance for large-scale
Performance
and queries. distributed applications.
Often follows BASE principles and
Consistency Follows ACID properties.
distributed architecture.
MySQL, Oracle, SQL Server, MongoDB, Cassandra, Redis,
Examples
PostgreSQL. HBase, Neo4j.

-------------------------------------------------------END-----------------------------------------------------