Questions
Practice questions and exam suggestions for Database Management System.
1. Transaction Management and ACID Properties
The following scenarios are taken directly from the ACID transaction practice problem sets.
Question 1.1: Fund Transfer System Crash
A bank's database records a fund transfer of ₹10,000 from Account A to Account B. The debit operation from Account A succeeds, but due to an unexpected system crash, the credit operation in Account B fails.
- Which ACID property is violated in this case?
- What should the database ensure to maintain consistency?
Question 1.2: Inventory Stock Update Failure
In an online shopping system, when a customer places an order, the system decreases the stock count from the Inventory table and then inserts the order details into the Orders table. If the inventory update succeeds but the order insertion fails:
- Which ACID property is violated?
- Explain how this failure leads to inconsistencies in stock management.
Question 1.3: Concurrent Course Registration
Two students simultaneously register for the last available seat in a university course. Both transactions read the seat as "available" before either transaction commits. At the end, both registrations show as confirmed and successful.
- Which ACID property is violated?
- How does transaction isolation prevent this over-allocation?
Question 1.4: Ride-Booking Confirmation and Server Crash
A ride-booking application confirms a cab booking to a customer. Right after confirmation, the server crashes, and when the system restarts, the booking record is missing from the database.
- Which ACID property is violated?
- What recovery mechanism should the database implement to prevent lost data upon restart?
Question 1.5: Loan Fee Deduction Failure
In a banking system approving a loan, the database first deducts the processing fee from the customer's account and then records the loan details in the Loans table. If the system crashes after deducting the fee but before inserting loan details:
- Which ACID property is violated?
- How does this affect financial records and customer trust?
Question 1.6: Concurrent Railway Ticket Booking
In a railway reservation system, when booking a ticket, the database first marks a seat as reserved in the Seats table and then records passenger details in the Bookings table. If Passenger A and Passenger B attempt to book the same last seat simultaneously, both see it as available, and the system confirms both:
- Which ACID property is violated?
- Explain how this creates operational conflicts and how concurrency control resolves it.
Question 1.7: Hospital Treatment Billing Failure
In a hospital billing system, once a patient's treatment is completed, the system calculates the bill amount and stores it in the Billing table. If the calculation is completed in memory but the bill record is never saved to disk due to a system crash:
- Which ACID property is violated?
- Explain how this failure causes discrepancies in hospital accounting records.
Question 1.8: Ride-Sharing Driver Assignment Update
In a ride-sharing application, once a driver accepts a ride, the system updates the ride status in the Rides table and saves driver assignment details in the Assignments table. If the ride status is updated to "Accepted" but the driver assignment insert fails:
- Which ACID property is violated?
- Explain the resulting operational confusion for passengers and drivers.
2. Entity-Relationship (E-R) Modeling and Relational Mapping
Problems selected directly from the E-R practice worksheets and case studies.
Question 2.1: Gym Management System (Schema to ERD)
Given the following relational schema:
Member(MemberID, Name, MembershipType)Trainer(TrainerID, Name, Specialization)Session(SessionID, Date, Duration, TrainerID)Attendance(SessionID, MemberID)
a. Identify all primary keys and foreign key relationships.
b. Determine the cardinality ratios between Member, Session, and Trainer.
c. Draw the corresponding Entity-Relationship Diagram (ERD).
Question 2.2: Movie Theater Booking System
Given the schema:
Customer(CustomerID, Name, Email, Phone)Movie(MovieID, Title, Genre, Duration)Booking(BookingID, Date, SeatNo, CustomerID, MovieID)
a. State the entity sets, attributes, and relationships.
b. Draw the complete ERD showing primary keys and cardinality ratios.
Question 2.3: Car Rental and Subcontractor Maintenance System
A car rental enterprise manages vehicles, customers, subcontractors, expenditures, and revenue:
- Entities:
CAR:Car_ID(PK),Make,Model,Year,Engine_Size,Fuel_Type,Passenger_No,Registration_No,Purchase_Date,Rent_Price,Insurance_Details.SUBCONTRACTOR(GARAGE):Garage_ID(PK),Name,Address,Range_of_Services,Payment_Terms.COMPANY_EXPENDITURE:Exp_ID(PK),Date,Amount,Type(Purchase, Repair, Maintenance, Insurance).COMPANY_REVENUE:Rev_ID(PK),Date,Amount,Source(Car Hire, Car Sales, Insurance Claim).CUSTOMER:Customer_ID(PK),Name,Address,Phone_No,Driving_Licence_No,Type(Privileged / Casual).RESERVATION:Reservation_ID(PK),Date,Duration,Deposit_Amount,Payment_Mode.
- Relationships:
CAR–SERVICED_BY–SUBCONTRACTOR(Many-to-One)COMPANY_EXPENDITURE–RELATED_TO–SUBCONTRACTOR(Many-to-One)COMPANY_REVENUE–GENERATED_FROM–CUSTOMER(Many-to-One)CUSTOMER–MAKES–RESERVATION(One-to-Many)RESERVATION–FOR–CAR(Many-to-One)
a. Specify the relational schemas resulting from converting this conceptual design to tables, marking PKs and FKs.
b. How should customer classification (Privileged vs. Casual) be handled in the schema?
Question 2.4: Online Food Delivery System
An online food delivery company stores data about customers, restaurants, menu items, orders, and delivery riders:
- A customer has
customer_id(PK),name,phone, andaddress. - A restaurant has
restaurant_id(PK),name,cuisine, andaddress. - An order has
order_id(PK),order_date, andtotal_amount. - A rider has
rider_id(PK),name, andvehicle_no.
Design the ER diagram and write the converted relational tables with all foreign keys.
Question 2.5: Sports Tournament System
A tournament records teams, players, and matches:
- A team has
team_id(PK),team_name, andcoach. - A player has
player_id(PK),player_name,age, androle. - A match has
match_id(PK),match_date, andvenue. - Teams participate in matches, and players belong to teams.
Draw the ER diagram and convert it into relational tables.
3. SQL Subqueries and Advanced Queries
The following queries are taken directly from the Subqueries practice worksheet based on corporate database schema Employee(emp_id, emp_name, dept_id, salary, manager_id, join_date), Department(dept_id, dept_name, location), Project(project_id, project_name, budget), and Works_On(emp_id, project_id, hours).
- Find employees who are part of a project with a budget greater than 50,000 using
IN. - List all departments that have employees earning more than 55,000 using
EXISTS. - Find the names of employees who are not part of any project using
NOT EXISTS. - Display the department name of the employee with the highest salary using a scalar subquery.
- Find employees whose salary is greater than the average salary of all employees using
> (SELECT AVG(salary)...). - Find employees who are working in the same department as 'Bob' using a correlated subquery with
IN. - List all projects handled by employees from the 'HR' department using
INand a nested subquery. - Retrieve details of employees who joined after the earliest joining date in the 'IT' department using
> ALL. - List all employees who are not working on projects that have a budget below 60,000 using
NOT IN. - Find the departments that have no projects assigned using
NOT EXISTS. - List the names of employees who are involved in all projects handled by the 'IT' department using
ALL. - Find employees who are working on some projects that employees from the 'Marketing' department are also working on using
SOME. - List all employees whose salary is greater than the salary of any employee in the 'HR' department using
> ANY. - Using a Common Table Expression (
WITH), list the total budget of projects handled by each department. - Find employees who are part of projects where the budget is either the highest or the lowest across all projects using
INwith multiple subqueries.
4. Relational Database Normalization
Problems taken directly from the Normalization practice worksheet.
Question 4.1: Student-Course-Instructor Worksheet
Normalize the following relation up to 3NF:
| StudentID | StudentName | Dept | CourseCode | CourseName | Instructor | InstructorPhone |
|---|---|---|---|---|---|---|
| 1001 | Rafi | CSE | CSE101 | DBMS | Dr. Hasan | 01711111111 |
| 1001 | Rafi | CSE | CSE202 | Networking | Dr. Amin | 01722222222 |
| 1002 | Nusrat | EEE | EEE101 | Circuits | Dr. Karim | 01733333333 |
| 1003 | Mahi | CSE | CSE101 | DBMS | Dr. Hasan | 01711111111 |
a. State the functional dependencies (FDs) present in the table.
b. Show the 1NF, 2NF, and 3NF decompositions with primary and foreign keys.
Question 4.2: Sales Orders Worksheet
Normalize the following Sales table to 3NF:
| OrderID | CustomerName | CustomerPhone | ProductName | Quantity | Price |
|---|---|---|---|---|---|
| 101 | Rifat | 01744444444 | Pen | 10 | 5 |
| 101 | Rifat | 01744444444 | Notebook | 2 | 20 |
| 102 | Nila | 01755555555 | Pen | 5 | 5 |
a. Identify partial and transitive dependencies.
b. Produce the normalized 3NF schemas.
Question 4.3: Employee-Project-Location Worksheet
Normalize up to 3NF:
| EmpID | EmpName | ProjID | ProjName | ProjLocation | DeptLocation |
|---|---|---|---|---|---|
| 1 | Rafi | P1 | Web App | Dhaka | Level 5 |
| 1 | Rafi | P2 | HR System | Dhaka | Level 5 |
| 2 | Asha | P3 | Payroll App | Chattogram | Level 2 |
a. Determine candidate key(s).
b. Show step-by-step decomposition to 2NF and 3NF.
Question 4.4: Library Book Issue Worksheet
Normalize the table to 3NF:
| IssueID | StudentID | StudentName | BookID | BookTitle | Author | AuthorEmail |
|---|---|---|---|---|---|---|
| I001 | S01 | Rana | B101 | DBMS Concepts | Elmasri | elmasri@email.com |
| I002 | S02 | Sumi | B102 | Computer Networks | Tanenbaum | tanenbaum@net.com |
| I003 | S01 | Rana | B102 | Computer Networks | Tanenbaum | tanenbaum@net.com |
Question 4.5: Customer-Order-Supplier Worksheet
Given:
| OrderID | CustomerID | CustomerName | ProductID | ProductName | SupplierName | SupplierContact |
|---|---|---|---|---|---|---|
| O01 | C001 | Rakib | P01 | Mouse | Logitech | 0123456789 |
| O01 | C001 | Rakib | P02 | Keyboard | HP | 0171111111 |
| O02 | C002 | Nabila | P01 | Mouse | Logitech | 0123456789 |
a. Identify repeating groups and convert to 1NF.
b. Eliminate partial dependencies for 2NF.
c. Eliminate transitive dependencies for 3NF.
5. SQL & PL/SQL on Students and Courses
Based strictly on the data tables and questions from the PL/SQL practice question set:
Database Tables & Records
-- STUDENTS Table
-- (student_id, name, department, age, marks)
(1, 'Rahim', 'CSE', 21, 85)
(2, 'Karim', 'EEE', 22, 75)
(3, 'Anika', 'CSE', 19, 65)
(4, 'Rafiq', 'BBA', 23, 55)
(5, 'Arman', 'CSE', 20, 95)
(6, 'Nadia', 'EEE', 18, 60)
(7, 'Sajid', 'CSE', 22, 70)
(8, 'Salman', 'LAW', 24, 50)
(9, 'Farhan', 'EEE', 20, 80)
(10, 'Mim', 'BBA', 19, NULL)
-- COURSES Table
-- (course_id, course_name, student_id, grade)
(101, 'Database', 1, 'A')
(102, 'Networking', 1, 'B')
(103, 'Database', 2, 'B')
(104, 'Operating Systems', 3, 'C')
(105, 'Networking', 4, 'A')
(106, 'Database', 5, 'A')
(107, 'AI', 5, 'A')
(108, 'Machine Learning', 6, 'B')
(109, 'Cybersecurity', 7, 'C')
(110, 'Database', 9, 'B')5.1 SQL Queries
- Retrieve all student names and their departments.
- Display students whose age is greater than 20.
- List students with marks between 60 and 80.
- Find students whose name starts with 'A'.
- Show students whose department is either 'CSE' or 'EEE' using
IN. - Show all students with null values in the marks column.
- Display students sorted by marks in descending order.
- Calculate the average marks of all students.
- Display department-wise average marks.
- Show the
student_idand their total number of courses taken. - List departments having more than 2 students (
HAVING). - Show students who are not enrolled in any course (
LEFT JOIN). - Find students who got grade 'A' in any course.
- Show courses with no student enrolled (
RIGHT JOIN). - Find students whose marks are above the overall average marks.
- Find the second-highest marks from
STUDENTSusing a subquery. - Retrieve students whose marks are greater than all students in the EEE department (
> ALL).
5.2 PL/SQL Anonymous Blocks
- Write a PL/SQL block to display the name and marks of the student with
student_id = 5. - Write a PL/SQL block to display all student names from
STUDENTSusing a loop. - Write a PL/SQL block to calculate and display the average marks of all students.
- Write a PL/SQL block to check if a student's marks are greater than 80. If yes, display "Excellent", otherwise display "Keep Trying".
- Write a PL/SQL block to increase the marks of
student_id = 3by 10. - Write a PL/SQL block to find and display the department of a student named 'Rahim'.
5.3 Cursors
- Write an explicit cursor to display all student names and their marks.
- Write an explicit cursor to display names of students who scored above 70.
- Write an explicit cursor to display
student_idand total number of courses each student has taken. - Write an explicit cursor to find students who have NULL marks.
- Write an explicit cursor to display all course names of
student_id = 1.
5.4 Stored Functions and Procedures
- Write a function
Get_Student_Marks(p_id IN NUMBER) RETURN NUMBERto return marks of a student. - Write a function
Calculate_Grade(p_marks IN NUMBER) RETURN VARCHAR2based on marks (, , , else ). - Write a function to count how many students are in a given department.
- Write a procedure
Add_Five_Marks(p_id IN NUMBER)to update marks of a student by adding 5. - Write a procedure to display all students in the 'CSE' department.
- Write a procedure to insert a new student into the
STUDENTStable. - Write a procedure to delete a student by
student_id.
5.5 Database Triggers
- Create a trigger to prevent deletion of any student record from the
STUDENTStable. - Create a trigger to prevent insertion if
marks > 100. - Create a trigger to prevent insertion of a student with
age < 18. - Create a trigger to automatically set
department = 'Unknown'ifNULLis inserted for department. - Create a trigger to log whenever a student's marks are updated into an audit table.