Skip to Content
PrepPath logoPrepPath

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, and address.
  • A restaurant has restaurant_id (PK), name, cuisine, and address.
  • An order has order_id (PK), order_date, and total_amount.
  • A rider has rider_id (PK), name, and vehicle_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, and coach.
  • A player has player_id (PK), player_name, age, and role.
  • A match has match_id (PK), match_date, and venue.
  • 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).

  1. Find employees who are part of a project with a budget greater than 50,000 using IN.
  2. List all departments that have employees earning more than 55,000 using EXISTS.
  3. Find the names of employees who are not part of any project using NOT EXISTS.
  4. Display the department name of the employee with the highest salary using a scalar subquery.
  5. Find employees whose salary is greater than the average salary of all employees using > (SELECT AVG(salary)...).
  6. Find employees who are working in the same department as 'Bob' using a correlated subquery with IN.
  7. List all projects handled by employees from the 'HR' department using IN and a nested subquery.
  8. Retrieve details of employees who joined after the earliest joining date in the 'IT' department using > ALL.
  9. List all employees who are not working on projects that have a budget below 60,000 using NOT IN.
  10. Find the departments that have no projects assigned using NOT EXISTS.
  11. List the names of employees who are involved in all projects handled by the 'IT' department using ALL.
  12. Find employees who are working on some projects that employees from the 'Marketing' department are also working on using SOME.
  13. List all employees whose salary is greater than the salary of any employee in the 'HR' department using > ANY.
  14. Using a Common Table Expression (WITH), list the total budget of projects handled by each department.
  15. Find employees who are part of projects where the budget is either the highest or the lowest across all projects using IN with 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:

StudentIDStudentNameDeptCourseCodeCourseNameInstructorInstructorPhone
1001RafiCSECSE101DBMSDr. Hasan01711111111
1001RafiCSECSE202NetworkingDr. Amin01722222222
1002NusratEEEEEE101CircuitsDr. Karim01733333333
1003MahiCSECSE101DBMSDr. Hasan01711111111

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:

OrderIDCustomerNameCustomerPhoneProductNameQuantityPrice
101Rifat01744444444Pen105
101Rifat01744444444Notebook220
102Nila01755555555Pen55

a. Identify partial and transitive dependencies.
b. Produce the normalized 3NF schemas.

Question 4.3: Employee-Project-Location Worksheet

Normalize up to 3NF:

EmpIDEmpNameProjIDProjNameProjLocationDeptLocation
1RafiP1Web AppDhakaLevel 5
1RafiP2HR SystemDhakaLevel 5
2AshaP3Payroll AppChattogramLevel 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:

IssueIDStudentIDStudentNameBookIDBookTitleAuthorAuthorEmail
I001S01RanaB101DBMS ConceptsElmasrielmasri@email.com
I002S02SumiB102Computer NetworksTanenbaumtanenbaum@net.com
I003S01RanaB102Computer NetworksTanenbaumtanenbaum@net.com

Question 4.5: Customer-Order-Supplier Worksheet

Given:

OrderIDCustomerIDCustomerNameProductIDProductNameSupplierNameSupplierContact
O01C001RakibP01MouseLogitech0123456789
O01C001RakibP02KeyboardHP0171111111
O02C002NabilaP01MouseLogitech0123456789

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

  1. Retrieve all student names and their departments.
  2. Display students whose age is greater than 20.
  3. List students with marks between 60 and 80.
  4. Find students whose name starts with 'A'.
  5. Show students whose department is either 'CSE' or 'EEE' using IN.
  6. Show all students with null values in the marks column.
  7. Display students sorted by marks in descending order.
  8. Calculate the average marks of all students.
  9. Display department-wise average marks.
  10. Show the student_id and their total number of courses taken.
  11. List departments having more than 2 students (HAVING).
  12. Show students who are not enrolled in any course (LEFT JOIN).
  13. Find students who got grade 'A' in any course.
  14. Show courses with no student enrolled (RIGHT JOIN).
  15. Find students whose marks are above the overall average marks.
  16. Find the second-highest marks from STUDENTS using a subquery.
  17. Retrieve students whose marks are greater than all students in the EEE department (> ALL).

5.2 PL/SQL Anonymous Blocks

  1. Write a PL/SQL block to display the name and marks of the student with student_id = 5.
  2. Write a PL/SQL block to display all student names from STUDENTS using a loop.
  3. Write a PL/SQL block to calculate and display the average marks of all students.
  4. Write a PL/SQL block to check if a student's marks are greater than 80. If yes, display "Excellent", otherwise display "Keep Trying".
  5. Write a PL/SQL block to increase the marks of student_id = 3 by 10.
  6. Write a PL/SQL block to find and display the department of a student named 'Rahim'.

5.3 Cursors

  1. Write an explicit cursor to display all student names and their marks.
  2. Write an explicit cursor to display names of students who scored above 70.
  3. Write an explicit cursor to display student_id and total number of courses each student has taken.
  4. Write an explicit cursor to find students who have NULL marks.
  5. Write an explicit cursor to display all course names of student_id = 1.

5.4 Stored Functions and Procedures

  1. Write a function Get_Student_Marks(p_id IN NUMBER) RETURN NUMBER to return marks of a student.
  2. Write a function Calculate_Grade(p_marks IN NUMBER) RETURN VARCHAR2 based on marks (≥80→’A’\ge 80 \rightarrow \text{'A'}, 70–79→’B’70\text{--}79 \rightarrow \text{'B'}, 60–69→’C’60\text{--}69 \rightarrow \text{'C'}, else ’F’\text{'F'}).
  3. Write a function to count how many students are in a given department.
  4. Write a procedure Add_Five_Marks(p_id IN NUMBER) to update marks of a student by adding 5.
  5. Write a procedure to display all students in the 'CSE' department.
  6. Write a procedure to insert a new student into the STUDENTS table.
  7. Write a procedure to delete a student by student_id.

5.5 Database Triggers

  1. Create a trigger to prevent deletion of any student record from the STUDENTS table.
  2. Create a trigger to prevent insertion if marks > 100.
  3. Create a trigger to prevent insertion of a student with age < 18.
  4. Create a trigger to automatically set department = 'Unknown' if NULL is inserted for department.
  5. Create a trigger to log whenever a student's marks are updated into an audit table.

On this page