DBMS (Semester – IV)
Practice Questions
Module 3
SQL Based Questions
1. A supermarket management system maintains the following tables:
Customer(Customer_ID, Name, City)
Product(Product_ID, Product_Name, Category, Price)
Purchase(Purchase_ID, Customer_ID, Product_ID, Quantity)
Write suitable SQL queries for the following:
1. Display all records from Product table.
2. Retrieve names of customers from Thane city.
3. Display total quantity purchased for each product.
4. Retrieve names of customers who purchased products belonging to Grocery
category.
5. Display Product_Name and Price of products having price greater than a specified
value.
2. An online examination system stores the following tables:
Student(Student_ID, Name, Department)
Exam(Exam_ID, Subject, Total_Marks)
Result(Student_ID, Exam_ID, Marks)
Construct SQL queries to:
1. Display names of students belonging to AI department.
2. Retrieve student names along with marks obtained in exams.
3. Find average marks scored in each exam.
4. Display names of students who scored more than a given threshold.
5. Retrieve details of exams attempted by students.
3. A hotel booking system maintains the following relations:
Guest(Guest_ID, Name, City)
Room(Room_ID, Room_Type, Charges)
Booking(Booking_ID, Guest_ID, Room_ID, Days)
Apply SQL queries to determine:
1. Names of guests staying in Deluxe rooms.
2. Total booking charges for each guest.
3. Details of rooms having charges between specified values.
4. Guest names who booked rooms for more than 3 days.
5. Room types booked by guests from Mumbai.
Relational Algebra Based Questions
4. A sports academy maintains the following relations:
Player(Player_ID, Name, Sport)
Coach(Coach_ID, Name, Experience)
Training(Player_ID, Coach_ID, Duration)
Formulate relational algebra expressions for:
1. Retrieve all records from Player relation.
2. Display names of players belonging to Cricket sport.
3. Retrieve Player_ID and Duration from Training relation.
4. Display names of coaches having experience greater than a specified value.
5. Retrieve players receiving training sessions longer than a given duration.
5. An online shopping platform maintains:
Seller(Seller_ID, Name, City)
Product(Product_ID, Product_Name, Category)
Orders(Order_ID, Seller_ID, Product_ID, Quantity)
Formulate relational algebra expressions to:
1. Retrieve products belonging to Electronics category.
2. Display names of sellers from Pune.
3. Retrieve Product_ID and Quantity from Orders relation.
4. Display sellers who sold products with quantity greater than a given value.
5. Retrieve names of products ordered by customers.
Set Operations / Relational Operations
6. A university maintains separate databases for students participating in indoor and
outdoor sports activities.
Analyze the role of set operations and illustrate the following with suitable examples:
1. Union
2. Intersection
3. Difference
7. A banking database processes customer information using different relational operations
during query execution.
Analyze the role of the following operations with suitable examples:
1. Selection
2. Projection
3. Join
8. A music streaming application stores listener and playlist data in multiple relations.
Explain the importance of joins and nested queries in SQL with suitable examples for each.
Module 4
Transaction Concepts & ACID Properties
1. An online railway reservation system processes ticket booking, cancellation, and payment
transactions simultaneously.
Illustrate ACID properties and demonstrate how they help in maintaining consistency and
reliability of the database system.
2. A digital wallet application transfers money between users while handling thousands of
concurrent transactions.
Explain different transaction states with suitable examples:
1. Active
2. Partially Committed
3. Failed
4. Aborted
5. Committed
Serializability & Concurrency Control
3. A library database system executes the following transaction schedule on data items A
and B:
Read₁(A) → Write₁(A) → Read₂(A) → Write₂(A) → Read₂(B) → Write₂(B) → Read₁(B) →
Write₁(B)
Apply conflict serializability concepts to:
1. Draw the precedence graph
2. Determine whether the schedule is conflict serializable
3. Find the equivalent serial schedule if it exists
4. A stock market database processes concurrent updates on share prices from multiple
traders.
Analyze the importance of concurrency control and explain the Two-Phase Locking (2PL)
protocol with suitable example.
5. A warehouse management system allows multiple branches to update inventory details
simultaneously.
Explain timestamp-based concurrency control protocol and illustrate how timestamps are
used to resolve conflicts between transactions.
Deadlock Handling
6. A cloud computing platform allocates shared resources to multiple transactions running
simultaneously. Some transactions keep waiting indefinitely for resources held by others.
Analyze the situation and explain:
1. Necessary conditions for deadlock
2. Deadlock prevention techniques
3. Deadlock detection and recovery methods
7. A banking server executes multiple fund transfer operations concurrently. Due to
improper locking, transactions become blocked while waiting for resources.
Illustrate deadlock handling methods with suitable examples and explain how system
performance is affected.
Recovery System
8. A database system records the following transaction log:
<T1, start>
<T1, A, 1000, 1500>
<T2, start>
<T2, B, 2000, 2500>
<T1, commit>
<T3, start>
<T3, C, 3000, 3500>
A system crash occurs immediately after the last entry.
Apply log-based recovery techniques to determine:
1. Transactions to be redone
2. Transactions to be undone
3. Final values of A, B, and C after recovery
9. An insurance management system maintains transaction logs during policy updates and
claim processing.
Explain the role of log-based recovery in maintaining database consistency after system
failures. Illustrate immediate update and deferred update techniques with suitable
examples.