DBMS

Two Marks
01) What is a database?
• A structured collection of data that allows efficient storage, retrieval, and
management.
• Stores data in an organized manner.
• Ensures data integrity and consistency.
• Supports multi-user access and concurrency.

Example: A customer database storing names, emails, and purchase history.

02) State the primary purpose of a database system?

• To manage data efficiently while ensuring security, consistency, and ease of
access.
• Reduces redundancy and improves data organization.
• Supports multiple users simultaneously.
• Provides security through authentication and authorization.

Example: Banking systems manage customer accounts and transactions

securely.

03) What is a primary key in a relational database?

• A unique identifier for each record in a table, ensuring data integrity.

• Cannot contain NULL or duplicate values.
• Helps in indexing and fast data retrieval.
• Enforces entity integrity in relational databases.

Example: Student_ID in a Student table.

04) What is a foreign key in a relational database?

A column that establishes a relationship between two tables by referencing

the primary key of another table.

• Ensures referential integrity by preventing orphan records.

• Helps maintain consistency across related tables.
• Enables cascading actions like ON DELETE CASCADE.
• Example: Dept_ID in an Employee table referencing Department table.
05) What is the purpose of the INSERT INTO SQL statement?

• Used in SQL to add new records into a table.

• Can insert data into specific columns or all columns.
• Allows inserting multiple rows in a single query.
• Supports inserting values from another table using INSERT INTO
SELECT.

Example: INSERT INTO Employees (ID, Name, Salary) VALUES (1, 'John',
50000);

06) What is meant by the term "NULL" in SQL?

• Represents missing, unknown, or undefined data in a database.

• Different from zero or an empty string.
• Can be handled using functions like COALESCE or IFNULL.
• Requires special handling in conditions (IS NULL or IS NOT NULL).

Example: SELECT * FROM Customers WHERE Email IS NULL;

07) What is the difference between INNER JOIN and LEFT JOIN in SQL?

• SQL joins used to combine rows from two or more tables based on a related
column.
• INNER JOIN: Returns only matching records from both tables.
• LEFT JOIN: Returns all records from the left table and matching records from
the right table.
• Helps retrieve related data efficiently.

Example: SELECT Orders.OrderID, Customers.Name FROM Orders INNER

JOIN Customers ON Orders.Cust_ID = Customers.ID;

08) What is a trigger in a database, and why is it used?

• A stored procedure that automatically executes when a specified event

occurs.
• Used for logging, enforcing constraints, or automatic updates.
• Can be BEFORE, AFTER, or INSTEAD OF an event.
• Helps maintain consistency without manual intervention.
Example: CREATE TRIGGER update_salary BEFORE UPDATE ON Employees
FOR EACH ROW SET NEW.salary = NEW.salary * 1.1;

09) Define Functional Dependency.

• A relationship where one attribute uniquely determines another in a database

table.
• Expressed as A → B (if A changes, B must change).
• Used in normalization to eliminate redundancy.
• Helps maintain data consistency.

Example: Student_ID → Student_Name.

10) Define Normalization.

• The process of structuring a database to eliminate redundancy and

anomalies.
• Divides large tables into smaller ones and establishes relationships.
• Improves database efficiency and integrity.
• Follows normal forms (1NF, 2NF, 3NF, BCNF, etc.).

Example: Splitting Employee table into Employee and Department tables.

11) Define Entity – Relationship Model.

• A conceptual framework for designing databases using entities, attributes,

and relationships.
• Entities represent real-world objects (e.g., Student, Course).
• Attributes define entity properties (e.g., Name, Age).
• Relationships connect entities (e.g., "Student enrolls in Course").

Example: University ER model with Student, Faculty, and Course entities.

12) List the properties of decomposition.

• Characteristics of breaking a table into smaller tables while maintaining

integrity.
• Lossless Decomposition: Ensures data can be reconstructed without loss.
• Dependency Preservation: Retains all functional dependencies.
• Redundancy-Free: Eliminates duplicate data.

Example: Splitting an Employee table into Personal and Job Details tables.
13) List the anomalies of 1NF.

• Problems caused by unnormalized data structures.

• Insertion Anomaly: Cannot insert data without a primary key.
• Deletion Anomaly: Deleting one record removes useful data.
• Update Anomaly: Changing one record requires updating multiple rows.

Example: A table storing Employee and Department causes redundancy.

14) Define trivial dependency?

• A functional dependency where an attribute is dependent on itself or a subset

of the key.
• Always holds true and doesn’t help in normalization.
• Doesn't affect database structure.
• Common in unnormalized forms.

Example: Student_ID → Student_ID.

15) Discuss Lossless Decomposition?

• A method of breaking a table into smaller tables without losing data or

introducing inconsistencies.
• Ensures original data can be recovered by joining decomposed tables.
• Maintains data integrity and relationships.
• Must satisfy the Lossless Join Property.

Example: Splitting an Order table into Order and OrderDetails.

16) Define Join Dependency.

• A constraint stating that a table can be reconstructed by joining its

decomposed parts.
• Used in higher normal forms like 5NF.
• Ensures data integrity across multiple related tables.
• Helps eliminate redundancy in complex relationships.

Example: A table with multiple candidate keys requiring multiple joins to

reconstruct.
17) Define transaction.

• A sequence of operations performed as a single logical unit of work in a

database.
• Follows ACID properties (Atomicity, Consistency, Isolation, Durability).
• Ensures data integrity during concurrent operations.
• Can be committed or rolled back.

Example: A bank transfer between two accounts.

18) Give the reasons for allowing concurrency.

• Benefits of multiple users accessing the database simultaneously.

• Improves system performance and responsiveness.
• Maximizes resource utilization.
• Prevents long wait times for users.

Example: Multiple users accessing an online shopping database at once

19) Discuss the term aborted state.

• A transaction state where the operation fails and is rolled back to maintain
consistency.
• Prevents partial updates from being saved.
• Ensures database remains in a consistent state.
• Occurs due to errors, system failures, or deadlocks.

Example: A power failure during a money transfer causes rollback.

20) What are the different modes of lock?

• Locking mechanisms to control concurrent access to data.

• Shared Lock (S): Allows multiple reads but no writes.
• Exclusive Lock (X): Allows read and write but blocks other access.
• Intent Lock: Signals upcoming locks on a resource.

Example: LOCK TABLE Employees IN EXCLUSIVE MODE;

 16 Marks
01) Demonstrate in detail about types of DBMS Architecture.

A Database Management System (DBMS) is software that enables users to

define, create, manipulate, and manage databases efficiently. The architecture of
a DBMS defines how different components interact to store, retrieve, and process
data. It determines how users and applications communicate with the database,
affecting performance, scalability, and security.

DBMS architecture can be classified into three types based on the number of
tiers:

1. Single-Tier Architecture (1-Tier Architecture)

2. Two-Tier Architecture (Client-Server Architecture)

3. Three-Tier Architecture (Web-Based Architecture)

1. Single-Tier Architecture (1-Tier Architecture)

Description

• In this architecture, the database and the application reside on the same
system.

• The user interacts directly with the database without a network connection.
• Commonly used in personal database applications and development
environments.

Structure
• The DBMS, application, and user interface are integrated into a single layer.

• Example: Microsoft Access, SQLite

Advantages

• Fast performance since no network communication is required.

• Easy to develop and manage.

• Suitable for small-scale applications.

Disadvantages
• Not scalable for multiple users.

• Security risks as everything is on a single system.

• Limited data integrity and concurrency.

2. Two-Tier Architecture (Client-Server Architecture)

Description

• In this architecture, the system is divided into two layers:

1. Client Layer: Handles the user interface and application logic.

2. Database Server Layer: Stores, processes, and manages the
database.

• Clients send requests to the database server, which processes them and
returns results.

Structure
• The client and database communicate via SQL queries over a network.

• Example: MySQL with a desktop application

Advantages

• Better security and data integrity compared to single-tier architecture.

• Enables multiple users to access the database simultaneously.

• Reduces client-side workload by offloading query processing to the database

server.

Disadvantages

• Network latency can affect performance.

• Scalability is limited as the server handles all client requests.

• Increased complexity compared to single-tier architecture.

Use Cases

• Used in small to medium-sized applications where multiple users access a

shared database.
• Example: Banking systems, library management systems.

3. Three-Tier Architecture (Web-Based Architecture)

Description

• Introduces an additional middle tier between the client and the database
server to handle business logic.

• Consists of three layers:

 1. Presentation Layer (Client Layer): User interface (web browser,
mobile app, etc.).

2. Application Layer (Business Logic): Processes requests, applies

logic, and interacts with the database.

3. Database Layer: Stores and retrieves data.

• Used in web-based applications with a large number of users.

Structure

• The client sends a request to the application server, which processes it and
communicates with the database.

• Example: Web applications using PHP, Java, or .NET with MySQL or

PostgreSQL.

Advantages

• Highly scalable – can handle a large number of users.

• Improved security – the client does not directly access the database.
• Better performance – load balancing distributes requests efficiently.

• Flexible maintenance – changes in one layer do not affect others.

Disadvantages

• Complex architecture requiring careful design.

• Higher costs due to additional hardware and software requirements.

• Increased latency compared to two-tier architecture.

Use Cases
• Enterprise-level applications such as e-commerce platforms, CRM
systems, and social media sites.

02) Examine the various types of keys—primary keys, foreign keys,

candidate keys, and composite keys—and their roles in maintaining data
integrity and enabling efficient querying. Discuss how keys relate to the
normalization process and contribute to reducing redundancy and
ensuring referential integrity.

1. Primary Key

• A column (or a set of columns) that uniquely identifies each record in a table.
• Ensures that no duplicate or NULL values exist.
• Helps in efficient searching, indexing, and enforcing entity integrity.

• Example: Student_ID in a Students table.

2. Foreign Key

• A column that establishes a relationship between two tables by referencing

the primary key of another table.

• Maintains referential integrity by ensuring that referenced records exist.

• Prevents deletion of records that are referenced in another table.

• Example: Department_ID in an Employee table referencing the
Departments table.
3. Candidate Key

• A column or set of columns that can uniquely identify each record in a table.

• One of the candidate keys is chosen as the primary key, while others remain
as alternate keys.

• Example: In a User table, both Email and Phone_Number can uniquely

identify a user, making them candidate keys.

4. Composite Key

• A key made up of two or more columns that together uniquely identify a

record.

• Used when a single column is not sufficient to ensure uniqueness.

• Example: (Order_ID, Product_ID) in an OrderDetails table, where a

combination of these two identifies a record uniquely.

5. Super Key

• A set of one or more columns that can uniquely identify a record in a table.

• A super key may contain extra attributes that are not necessary for
uniqueness.

• Example: (Student_ID, Email, Phone_Number) in a Students table

(Student_ID alone is enough to be a primary key).

6. Alternate Key

• A candidate key that is not selected as the primary key.

• Acts as an additional way to uniquely identify a record.

• Example: If Email and Phone_Number are candidate keys, and Email is

chosen as the primary key, then Phone_Number is an alternate key.
Role of Keys in Normalization

1. Eliminating Redundancy

• Keys help organize data properly by ensuring that records are unique and
efficiently linked.

• This reduces data duplication, improving storage efficiency.

2. Ensuring Referential Integrity

• Foreign keys enforce integrity by preventing orphan records.

• This ensures that every record maintains valid relationships with others.

3. Enhancing Query Efficiency

• Indexing on primary keys speeds up data retrieval.

• Well-defined keys improve the performance of JOIN operations between

tables.

Keys and Normalization Process

1st Normal Form (1NF)

• Uses primary keys to uniquely identify each record.

• Ensures atomicity by removing duplicate columns.

2nd Normal Form (2NF)

• Eliminates partial dependencies by ensuring all attributes depend on the

entire primary key (important for composite keys).

3rd Normal Form (3NF)

• Removes transitive dependencies by using foreign keys to reference other

tables.

Boyce-Codd Normal Form (BCNF)

• Ensures that every determinant (attribute determining another) is a candidate

key.

Conclusion

Keys are essential for maintaining data integrity, reducing redundancy,

enforcing relationships, and optimizing query performance in a database.
Properly defining keys improves efficiency, security, and scalability of the
system.
03) Evaluate the basic structure of SQL queries and the effectiveness of
SQL in various types of database operations. Discuss the key
components of SQL queries, including the SELECT, FROM, WHERE,
GROUP BY, HAVING, and ORDER BY clauses.
1. Introduction to SQL

SQL (Structured Query Language) is used to interact with relational databases by

performing operations like data retrieval, insertion, update, and deletion.
A basic SQL query follows this structure:

SELECT column_names

FROM table_name

WHERE condition

GROUP BY column_names

HAVING condition

ORDER BY column_name ASC/DESC;

2. Key Components of SQL Queries

1. SELECT Clause

• Extracts specific columns from a table.

• Can retrieve all columns using SELECT *.

• Example:

SELECT name, age FROM Students;

2. FROM Clause

• Specifies the table from which data is fetched.

• Example:

SELECT * FROM Employees;

3. WHERE Clause

• Filters records based on a condition.

• Example:
SELECT * FROM Orders WHERE Status = 'Pending';
4. GROUP BY Clause
• Groups rows based on one or more columns.

• Used with aggregate functions like COUNT(), SUM(), etc.

• Example:

SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

5. HAVING Clause

• Filters grouped results, unlike WHERE, which filters individual rows.

• Example:

SELECT Department, COUNT(*) FROM Employees

GROUP BY Department

HAVING COUNT(*) > 10;

6. ORDER BY Clause

• Sorts the result set in ascending (ASC) or descending (DESC) order.

• Example:

SELECT name, salary FROM Employees ORDER BY salary DESC;

3. Effectiveness of SQL in Database Operations

1. Data Retrieval (Read Operations)

• Fast and optimized queries using indexing and structured search.

• Supports complex filtering and sorting.

2. Data Manipulation (Write Operations)
• INSERT, UPDATE, and DELETE allow seamless modifications.

• Supports bulk operations, making it efficient for large datasets.

3. Data Aggregation & Reporting

• GROUP BY and HAVING enable effective data summarization.

• Useful for business intelligence and analytics.

4. Data Integrity & Security

• Constraints (like PRIMARY KEY, FOREIGN KEY) ensure data accuracy.

• User privileges control access to sensitive data.

Conclusion
SQL provides a powerful, flexible, and efficient way to manage relational
databases. Its structured approach ensures data integrity, optimized retrieval,
and scalability across different applications.

04) Evaluate the use of SQL aggregate functions (COUNT, SUM, AVG, MAX,
MIN) in analyzing data from a relational database. Create a set of queries
using these aggregate functions to solve a practical problem in a real-
world scenario. What is the impact of using aggregate functions in large
datasets and handling NULL values

1. Introduction to Aggregate Functions

SQL aggregate functions perform calculations on multiple rows and return a

single summary value. They are commonly used for data analysis and
reporting.

Common Aggregate Functions

• COUNT(): Returns the number of rows.

• SUM(): Computes the total sum of a numeric column.

• AVG(): Finds the average value.

• MAX(): Returns the highest value.

• MIN(): Returns the lowest value.

2. Practical Example – Sales Analysis

Scenario: A retail store wants to analyze sales performance.

Total Number of Orders (COUNT)

SELECT COUNT(*) AS Total_Orders FROM Orders;

• Helps measure sales volume.

Total Revenue (SUM)

SELECT SUM(Amount) AS Total_Revenue FROM Orders WHERE Status =

'Completed';

• Determines overall earnings from completed orders.

Average Order Value (AVG)

SELECT AVG(Amount) AS Avg_Order_Value FROM Orders WHERE Status =

'Completed';
• Provides insights into customer spending patterns.

Highest and Lowest Sales (MAX, MIN)

SELECT MAX(Amount) AS Max_Sale, MIN(Amount) AS Min_Sale FROM Orders;

• Identifies the highest and lowest transactions.

Sales Per Region (GROUP BY with SUM)

SELECT Region, SUM(Amount) AS Total_Sales FROM Orders GROUP BY

Region;
• Compares sales across different locations.

3. Impact of Aggregate Functions on Large Datasets

1. Performance Considerations

• Aggregations on large datasets can be slow, especially without indexing.

• Indexes and partitioning optimize queries.

2. Handling NULL Values

• COUNT(*) counts all rows, including NULLs.

• SUM(), AVG(), MAX(), and MIN() ignore NULL values.

• Use COALESCE(column, 0) to replace NULLs if necessary.

SELECT SUM(COALESCE(Amount, 0)) FROM Orders;

4. Conclusion

SQL aggregate functions simplify data analysis, enabling businesses to make

data-driven decisions. However, efficient indexing and NULL handling are crucial
for optimal performance in large datasets.

05) Illustrate with an example what is meant by partial functional

dependency and describe how this type of dependency relates to 2NF.

1. Understanding Functional Dependency

A functional dependency means that a column's value is determined by another

column.

• Example: If Student_ID → Student_Name, then Student_Name is functionally

dependent on Student_ID.
2. What is Partial Functional Dependency?
A partial functional dependency occurs when an attribute depends on only a
part of a composite primary key, rather than the entire key.

3. How Partial Dependency Relates to 2NF?

A table is in 1st Normal Form (1NF) if it has:
✔ Unique rows
✔ Atomic (indivisible) values

To move to 2nd Normal Form (2NF):

Remove partial dependencies
Ensure all non-key attributes depend on the entire primary key

4. Converting to 2NF (Eliminating Partial Dependency)

To achieve 2NF, split the table into three tables:

Now:
Every attribute fully depends on the entire primary key.
No partial dependencies remain.
The database is now in 2NF, reducing redundancy and improving data
integrity.

5. Why is 2NF Important?

• Reduces Data Redundancy: No duplicate storage of Student_Name or
Course_Name.

• Improves Data Integrity: Updating a student’s name in the Student table

ensures consistency.

• Efficient Queries: Faster searches and updates.

Conclusion

Partial functional dependency violates 2NF by causing redundant data storage.

By breaking the table into separate related tables, we eliminate redundancy,
ensuring better performance and data consistency.

06) (i) Develop an E-R diagram for a car-insurance company whose

customers ownone or more cars each. Each car has associated with it
zero to any number of recorded accidents. State any assumptions you
make. (6) (ii) A university registrar’s office maintains data about
following entities: 1)Courses, including number, title, credits, syllabus,
and prerequisites; 2) Course offerings, including course number, year,
semester, section number,instructor, timings and classroom; 3)
Students, including student-id, name, and program; and Instructors,
including identification number, name, department, and title. Further, the
enrollment of students in courses and grades awarded to students in
each course they are enrolled for must be appropriately modeled.
Construct an E-R diagram for the registrar’s office. Document all
assumptions that you make about the mapping constraints.
(i) E-R Diagram for a Car-Insurance Company

Entities & Relationships:

1. Customer (Customer_ID, Name, Address, Phone)

o A customer can own one or more cars.

2. Car (Car_ID, Model, Year, Registration_Number)

o A car is owned by one customer.

3. Accident (Accident_ID, Date, Location, Description)

o A car can be involved in zero or more accidents.

4. Relationship Mapping:

o Customer (1) → (Owns) → (M) Car

o Car (1) → (Has) → (M) Accident

Assumptions:
• Each car is owned by a single customer.

• A car may or may not have accident records.

(ii) E-R Diagram for a University Registrar’s Office

Entities & Attributes:

1. Course (Course_ID, Title, Credits, Syllabus, Prerequisites)

2. Course_Offering (Offering_ID, Course_ID, Year, Semester, Section,

Instructor_ID, Timing, Classroom)

3. Student (Student_ID, Name, Program)

4. Instructor (Instructor_ID, Name, Department, Title)

5. Enrollment (Student_ID, Offering_ID, Grade)

Relationship Mapping:

• Course (1) → (Has) → (M) Course_Offerings

• Course_Offering (M) → (Taught by) → (1) Instructor

• Student (M) → (Enrolled in) → (M) Course_Offering

• Course (1) → (Prerequisite) → (M) Course

Assumptions:
• A course can have multiple offerings in different semesters.

• A student can enroll in multiple courses per semester.

• Each course offering is assigned to one instructor.

• Grades are assigned per student per course offering.

 07) Determine about 3NF and BCNF with relevant table structure.

Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF) with Examples

1. Third Normal Form (3NF)

Definition:
A table is in 3NF if:

1. It is in 2NF (i.e., no partial dependencies).

2. It has no transitive dependencies, meaning non-key attributes should

depend only on the primary key and not on another non-key attribute.

Issues:

• Dept_Name and HOD depend on Dept_ID, not on Student_ID, causing

transitive dependency.

3NF Solution:

Break into two tables:

2. Boyce-Codd Normal Form (BCNF)

Definition:

A table is in BCNF if:

1. It is in 3NF.

2. Every determinant is a candidate key (i.e., no non-trivial functional

dependencies exist where a non-key attribute determines another attribute).
Example:

Issues:

• Instructor → Room (An instructor is always assigned to the same room).

• Course_ID alone is not a unique key since multiple instructors may teach the
same course.
 • The composite key (Course_ID, Instructor) is the primary key, but Instructor →
Room violates BCNF.

BCNF Solution:

Break into two tables:

Summary:
• 3NF removes transitive dependencies to maintain data integrity.

• BCNF further eliminates anomalies by ensuring all determinants are

candidate keys.
08) Explain Functional dependency and trivial functional dependency with
examples.
1. Functional Dependency (FD)

A functional dependency exists in a relation when one attribute (or a set of

attributes) uniquely determines another attribute. It helps in maintaining data
consistency and normalization in databases.

Notation:
If X → Y, then for each unique value of X, there is a unique value of Y.

• X is called the determinant.

• Y is the dependent attribute.

Example:
Types of Functional Dependencies:

1. Fully Functional Dependency: If an attribute is fully dependent on a

candidate key and not on part of it.

o Example: Student_ID → Name (Each Student_ID uniquely determines

Name).

2. Partial Functional Dependency: If an attribute is dependent on only part of

a composite key.

o Example: In {Student_ID, Course_ID} → Marks, if Marks depends only

on Course_ID, then it's a partial dependency.

3. Transitive Functional Dependency: If X → Y and Y → Z, then X → Z is a

transitive dependency.

o Example: Student_ID → Dept_ID, Dept_ID → HOD, so Student_ID →

HOD.

2. Trivial Functional Dependency

A trivial functional dependency occurs when the dependent attribute is already a

subset of the determinant. It does not provide new information about the
relation.

Notation:

If X → Y and Y is a subset of X, then it is trivial.

Examples:
 1. {Student_ID, Name} → Student_ID (Trivial because Student_ID is already in
the determinant).

2. {Course_ID, Instructor} → Course_ID (Trivial because Course_ID is part of

the left-hand side).

Non-Trivial Functional Dependency Example:

• Student_ID → Name (Non-trivial because Name is not a subset of

Student_ID).

• Course_ID → Instructor (Non-trivial because Instructor is independent of

Course_ID).

Importance of Functional Dependencies in Database Normalization

Functional dependencies help in:

• Eliminating redundancy (avoiding duplicate data).

• Ensuring data consistency (avoiding anomalies).

• Improving query performance (by structuring tables properly).

09) Illustrate in detail about the following

(i)Non loss decomposition.
(ii)Lossy decomposition.
i)Non-Loss Decomposition (Lossless Decomposition)

Definition:

A non-loss (lossless) decomposition ensures that when a relation (table) is

split into two or more tables, it can be perfectly reconstructed by joining them
back using natural join or equijoin. No information is lost in the process.
Mathematical Condition for Lossless Decomposition:

A decomposition of relation R into R1 and R2 is lossless if:

R1 ∩ R2 → R1 or R1 ∩ R2 → R2

This means the intersection of R1 and R2 must be a super key in at least one of the
decomposed tables.

Example of Non-Loss Decomposition:

Verifying Losslessness:

• The common attribute between the two tables is Dept_ID, which is a key in
the Department table.

• We can perform a natural join on Dept_ID to restore the original relation.

• Hence, the decomposition is lossless because no data is lost.
Advantages of Non-Loss Decomposition:

✔ Preserves all data without redundancy.

✔ Avoids anomalies (insertion, deletion, update).
✔ Ensures database integrity.

ii) Lossy Decomposition

Definition:
A lossy decomposition means that when a relation is decomposed into multiple
tables, some data is lost and cannot be reconstructed fully using joins.

Example of Lossy Decomposition:

Why is this Lossy?

• The common attribute Dept_ID is completely missing in Employee_Part1.

• When we try to join the two tables, we cannot uniquely associate an

Emp_ID with a Dept_ID.
 • The original information about which employee belongs to which
department is lost.

Disadvantages of Lossy Decomposition:

Leads to data loss and inconsistency.

Cannot fully reconstruct the original table.
Causes incorrect query results.

10) i) Describe the ACID Properties of a transaction.

ii) What benefit does rigorous two-phase locking provide? Show how
does it compare with other forms of two-phase locking?

(i) ACID Properties of a Transaction

A transaction in a database is a sequence of operations performed as a

single unit. To ensure reliability and consistency in a database system,
transactions must satisfy the ACID properties:
1. Atomicity (All or Nothing)
• A transaction must be completely executed or completely rolled back in
case of failure.
• If any part of the transaction fails, all changes made so far are undone.
• Example: If a bank transfer transaction debits ₹500 from one account but fails
to credit it to another, the debit should be reversed to maintain consistency.
2. Consistency
• Ensures that a transaction preserves database integrity by maintaining all
constraints and rules.
• The database must move from one valid state to another.
 • Example: If a transaction updates an account balance, the total sum of all
balances must remain unchanged to maintain consistency.
3. Isolation
• Transactions should execute independently, without interfering with each
other.
• Intermediate states of one transaction should not be visible to another until
committed.
• Example: If two users transfer money simultaneously, one transaction should
not see the partial effects of another.
4. Durability
• Once a transaction is committed, its changes must persist even if the
system crashes.
• The database system uses logs and backups to ensure durability.
• Example: After confirming a successful money transfer, the bank ensures that
the update is not lost even if the system fails.

(ii) Rigorous Two-Phase Locking (2PL) and Its Benefits

What is Two-Phase Locking (2PL)?

• 2PL ensures serializability (transactions execute in a strict order).
• A transaction goes through two phases:
1. Growing Phase: It acquires locks but cannot release any.
2. Shrinking Phase: It releases locks but cannot acquire new ones.
What is Rigorous Two-Phase Locking?
• In rigorous 2PL, a transaction holds all locks until it is completely
committed or aborted.
• Unlike standard 2PL, no locks are released during execution, ensuring
stronger isolation.
Benefits of Rigorous 2PL
✔ Prevents cascading rollbacks by ensuring a transaction is not affected by
others before committing.
✔ Provides strict serializability, making the schedule safe and predictable.
✔ Stronger isolation compared to other 2PL forms, as transactions do not
access uncommitted data.
Trade-off of Rigorous 2PL

• Stronger isolation improves consistency but reduces concurrency since

transactions hold locks longer.
• Suitable for high-security applications like banking but may slow down
performance in highly concurrent systems.

11) (i)What is concurrency control? Illustrate the two-phase locking

protocol with an example.
(ii)What is conflict serializability and view serializability? Illustrate with
an example.

(i) Concurrency Control and Two-Phase Locking (2PL)

Concurrency Control

Concurrency control ensures that multiple transactions can execute simultaneously

without leading to inconsistencies in the database. It prevents data corruption,
dirty reads, and deadlocks by managing the execution order of transactions.

Two-Phase Locking (2PL) Protocol

2PL ensures serializability by dividing a transaction’s execution into two phases:

1. Growing Phase

o The transaction acquires locks on required data but cannot release

any.

2. Shrinking Phase

o Once a lock is released, the transaction cannot acquire new locks.

Example of 2PL:
Consider two transactions, T1 and T2, trying to access the same data:

• T1: Reads and writes X.

• T2: Reads and writes X.

Without 2PL, T2 might read X while T1 is modifying it, causing inconsistencies. With
2PL:

1. T1 acquires a lock on X → Reads X → Modifies X.

2. T1 releases the lock → Only now can T2 acquire the lock on X.

This ensures that T2 sees only the final committed value of X, avoiding dirty
reads.
(ii) Conflict Serializability and View Serializability

1. Conflict Serializability

A schedule is conflict serializable if it can be transformed into a serial schedule

by swapping non-conflicting operations.

Example:
Transactions T1 and T2:

T1: Read(A) → Write(A)

T2: Read(A) → Write(A)

This is not conflict serializable because both T1 and T2 modify A without

ordering. However:

T1: Read(A) → Write(A) → Commit

T2: Read(A) → Write(A) → Commit

Now, T1 completes before T2 starts, making it serializable.

2. View Serializability

A schedule is view serializable if it produces the same final output as a serial

execution, even if conflicting operations exist.

Example:

• If T1 writes A, and T2 reads A, the final values in both cases must be the
same as a serial schedule.

• Unlike conflict serializability, view serializability allows schedules that cannot

be transformed via swaps but still preserve the correct final result.

Key Difference:

• Conflict serializability is stricter and ensures a direct swap to serial

execution.
• View serializability allows more schedules as long as the final outcome
remains correct.

12) Write a short note on:

i) Transaction concept.

(ii) Deadlock.
(i) Transaction Concept

A transaction is a sequence of database operations performed as a single

logical unit. Transactions ensure data consistency and integrity even in cases
of system failures or concurrent executions.

Properties of Transactions (ACID Properties)

1. Atomicity – The transaction is all or nothing; either all operations execute

successfully, or none do.

2. Consistency – The database moves from one valid state to another without
violating constraints.

3. Isolation – Transactions execute independently without interfering with each

other.

4. Durability – Once a transaction commits, changes remain even if the system

crashes.
Example:

A bank transfer from Account A to Account B involves:

1. Withdraw ₹5000 from A

2. Deposit ₹5000 to B

3. Commit the transaction

If a failure occurs after step (1), the system rolls back to avoid inconsistencies.

(ii) Deadlock in DBMS

A deadlock occurs when two or more transactions wait indefinitely for

resources locked by each other, preventing further execution.

Example of Deadlock:
• T1 locks data item X and requests Y (held by T2).

• T2 locks data item Y and requests X (held by T1).

• Neither transaction can proceed → Deadlock occurs.

Deadlock Detection and Resolution

1. Wait-for Graph – Detects cycles in transaction wait dependencies.

2. Timeouts – If a transaction waits too long, it is aborted.
Deadlock Prevention Techniques
1. Wait-Die Protocol –

o Older transactions wait for younger ones.

o Younger transactions die (abort and restart) if waiting for older ones.

2. Wound-Wait Protocol –
o Older transactions force younger ones to abort and retry.

o Avoids starvation of older transactions.

13) (i)What is concurrency control? How is it implemented in DBMS?

(ii)Generalize with a suitable example.

(i) Concurrency Control in DBMS

Concurrency control ensures that multiple transactions can execute

simultaneously without leading to inconsistencies, data loss, or corruption. It
maintains data integrity and isolation in multi-user database environments.
Methods of Concurrency Control:

1. Lock-Based Protocols

o Shared Lock (S): Allows multiple transactions to read a data item.

o Exclusive Lock (X): Only one transaction can read and write a data
item.

2. Timestamp-Based Protocols

o Assigns a unique timestamp to each transaction and ensures older

transactions execute first.

3. Optimistic Concurrency Control

o Transactions execute without locks but validate changes before

committing.

4. Multi-Version Concurrency Control (MVCC)

o Maintains multiple versions of a data item to allow simultaneous

reads and writes without conflicts.

(ii) Example of Concurrency Control

Scenario: Bank Transactions

Two transactions (T1 and T2) attempt to withdraw ₹5000 from the same
account (A) at the same time.
1. T1 checks balance: ₹10,000
2. T2 checks balance: ₹10,000

3. T1 deducts ₹5000 → Updates balance to ₹5000

4. T2 deducts ₹5000 → Updates balance to ₹5000 (Incorrect final balance!)

This leads to an inconsistent state (balance should be ₹5000, but remains

₹5000 after two withdrawals).

Solution: Implementing Concurrency Control

Using lock-based concurrency control:

• T1 locks Account A → Reads, deducts ₹5000, updates, and commits.

• T2 waits until T1 releases the lock, then proceeds safely.

This ensures the correct final balance of ₹5000 and prevents data
inconsistency.