Skip to Content
PrepPath logoPrepPath

Practice Sheets

Solved practice worksheets and exercises for Database Management System.

1. Transaction Management and ACID Properties

Scenario 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.

  • Violated Property: Atomicity (All-or-Nothing execution).
  • Explanation: A logical fund transfer transaction consists of two operations: Debit(A) and Credit(B). Atomicity mandates that either all operations of the transaction are committed or none are reflected in the database. When the system crashes after Debit(A) but before Credit(B), the transaction is left partially executed. If the debit persists without the credit, ₹10,000 is lost from the system.
  • Consistency Enforcement:
    • Write-Ahead Logging (WAL): Undo and redo log records must be flushed to non-volatile storage before dirty buffers are written to disk.
    • Crash Recovery (Undo Phase): Upon reboot, the recovery manager scans active transactions. Because the transfer lacks a COMMIT log record, an UNDO pass reverses the debit on Account A (Balance(A) := Balance(A) + 10000), restoring total balance consistency.

Scenario 1.2: Inventory Stock Update Failure

In an online shopping app, a customer places an order for 3 items. The stock count should decrease accordingly. After the transaction, the database shows the order was created, but the stock count remains unchanged.

  • Compromised Property: Consistency (and Atomicity of the composite transaction).
  • Explanation: Consistency ensures that a transaction takes the database from one valid state to another, satisfying all declared schema constraints and business invariants: Stocknew=Stockold−QuantityOrdered\text{Stock}_{\text{new}} = \text{Stock}_{\text{old}} - \text{QuantityOrdered}
  • Impact: Recording the order without deducting stock leaves the database in an inconsistent state. The business faces overselling, phantom inventory discrepancies, and unfulfillable orders. Both updates must be bound within an atomic transaction:
    BEGIN TRANSACTION;
    UPDATE inventory SET stock = stock - 3 WHERE item_id = 101;
    INSERT INTO orders (order_id, customer_id, item_id, quantity) VALUES (501, 12, 101, 3);
    COMMIT;

Scenario 1.3: Concurrent Course Registration Race Condition

Two students simultaneously register for the last available seat in a university course. Both transactions read the seat as "available" before either commits. At the end, both registrations show as successful.

  • Violated Property: Isolation (Lost update anomaly / race condition).
  • Explanation: Concurrently executing transactions interleave operations:
    1. Transaction 1 reads available_seats = 1.
    2. Transaction 2 reads available_seats = 1.
    3. Transaction 1 updates available_seats = 0 and commits.
    4. Transaction 2 updates available_seats = 0 and commits. Both students receive confirmations for a single physical seat because neither transaction was isolated from the other's uncommitted state.
  • Resolution: Enforce Serializable isolation or pessimistic row-level locking:
    BEGIN TRANSACTION;
    SELECT available_seats FROM courses WHERE course_id = 'CSE301' FOR UPDATE;
    -- If available_seats > 0, proceed; otherwise rollback
    UPDATE courses SET available_seats = available_seats - 1 WHERE course_id = 'CSE301';
    INSERT INTO enrollments (student_id, course_id) VALUES (201, 'CSE301');
    COMMIT;

Scenario 1.4: Ride-Booking Confirmation and Immediate Crash

A ride-booking app 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.

  • Violated Property: Durability.
  • Explanation: Durability guarantees that once a transaction commits, its changes survive any subsequent crash or power outage. If the server acknowledged confirmation to the user while the log records were still buffered in volatile memory and not flushed to persistent disk, the transaction is lost on restart.
  • Resolution:
    • Force-Log-at-Commit: The database must write and flush log records to physical disk (fsync) before transmitting commit success to the client application.
    • Redo Logging: During crash recovery, the recovery manager re-executes all committed actions logged prior to the crash.

Scenario 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:

  • Violated Property: Atomicity.
  • Impact on Financial Records and Trust:
    • The customer's account balance decreases without their loan being generated, leading to customer disputes, audit discrepancies, and regulatory penalties.
    • The general ledger shows unallocated revenue with no offsetting loan liability.
  • Resolution: Group fee deduction and loan creation inside an atomic block. If the server crashes before commit, the recovery manager rolls back the fee deduction during restart.

Scenario 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:

  • Violated Property: Isolation.
  • Operational Conflicts:
    • Two passengers are issued valid tickets for the identical physical berth/seat, causing boarding conflicts and train departure delays.
  • Resolution: Use atomic conditional updates or exclusive lock acquisition:
    UPDATE seats
    SET is_reserved = 'Y'
    WHERE seat_no = 'B1-12' AND is_reserved = 'N';
    Only the transaction that modifies 1 row proceeds to insert into Bookings; the other transaction receives 0 affected rows and is aborted.

Scenario 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:

  • Violated Property: Durability (and Atomicity of the discharge workflow).
  • Discrepancies Caused:
    • Medical treatments, medications, and physician services are rendered without corresponding accounts receivable.
    • Hospital accounting suffers revenue leakage and audit discrepancies between medical logs and financial accounts.
  • Resolution: The billing insert must commit to durable disk storage before the patient's checkout state is confirmed.

Scenario 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:

  • Violated Property: Atomicity.
  • Resulting Confusion:
    • Passenger: Sees "Ride Accepted" but no assigned driver, vehicle number, or driver location.
    • Driver: Has no record of the ride in their active queue and is reassigned other trips.
    • System: The ride remains stranded in 'Accepted' status with no driver assigned to service it.
  • Resolution: Enclose both the status update and assignment insert inside an atomic database transaction. If the assignment insert fails, roll back the ride status back to 'Requested'.

Scenario 1.9: Bank Fund Transfer Partial Debit

A database records a fund transfer from Account A to Account B. The debit operation from Account A succeeds, but the credit operation in Account B fails due to a network timeout.

  • Violated Property: Atomicity.
  • Explanation: The transfer transaction is an all-or-nothing unit of work. Leaving Account A debited without crediting Account B violates atomicity and compromises ledger balance integrity. The database management system must execute an automatic rollback upon failure or during restart recovery.

2. Entity-Relationship (E-R) Modeling

Problem 2.1: Online Food Delivery Platform

An online food delivery company stores data about customers, restaurants, and orders. A customer has a customer ID, name, phone, and address. A restaurant has a restaurant ID, name, location, and cuisine type. A customer can place multiple orders. Each order has an order ID, date, and total amount. An order can contain multiple food items, and each food item has an item ID, name, and price. Restaurants prepare many food items.

  • Entities & Attributes:
    • Customer: customer_id (PK), name, phone, address
    • Restaurant: restaurant_id (PK), name, location, cuisine_type
    • Orders: order_id (PK), order_date, total_amount
    • FoodItem: item_id (PK), name, price
  • Relationships & Cardinalities:
    • Customer places Orders (1:N1 : N)
    • Restaurant receives Orders (1:N1 : N)
    • Restaurant prepares FoodItem (1:N1 : N)
    • Orders contains FoodItem (M:NM : N) with attribute quantity
  • Relational Tables:
    • Customer(customer_id [PK], name, phone, address)
    • Restaurant(restaurant_id [PK], name, location, cuisine_type)
    • FoodItem(item_id [PK], name, price, restaurant_id [FK])
    • Orders(order_id [PK], order_date, total_amount, customer_id [FK], restaurant_id [FK])
    • Order_Items(order_id [FK], item_id [FK], quantity, PRIMARY KEY (order_id, item_id))
Loading diagram...

Problem 2.2: Airline Management System

An airline company maintains information about flights, passengers, and bookings. Each passenger has a passenger ID, name, gender, passport number, and contact number. A flight has a flight number, source, destination, and departure time. A passenger can book multiple flights, and each booking has a booking ID, date, and seat number. Each flight is operated by a pilot, and each pilot has a pilot ID, name, and license number.

  • Entities & Attributes:
    • Passenger: passenger_id (PK), name, gender, passport_number, contact_number
    • Flight: flight_number (PK), source, destination, departure_time
    • Pilot: pilot_id (PK), name, license_number
    • Booking: booking_id (PK), booking_date, seat_number
  • Relationships & Cardinalities:
    • Passenger makes Booking (1:N1 : N)
    • Flight has Booking (1:N1 : N)
    • Pilot operates Flight (1:N1 : N)
  • Relational Tables:
    • Passenger(passenger_id [PK], name, gender, passport_number, contact_number)
    • Pilot(pilot_id [PK], name, license_number)
    • Flight(flight_number [PK], source, destination, departure_time, pilot_id [FK])
    • Booking(booking_id [PK], booking_date, seat_number, passenger_id [FK], flight_number [FK])
Loading diagram...

Problem 2.3: Hospital Patient, Doctor, and Admission System

A hospital keeps records of patients, doctors, and treatments. A patient has a patient ID, name, age, address, and phone. A doctor has a doctor ID, name, specialization, and phone number. Patients can consult multiple doctors, and doctors can treat many patients via appointments with details including appointment ID, date, and time. Patients may also be admitted, recording admission ID, admission date, and ward number.

  • Relational Tables:
    • Patient(patient_id [PK], name, age, address, phone)
    • Doctor(doctor_id [PK], name, specialization, phone_number)
    • Appointment(appointment_id [PK], date, time, patient_id [FK], doctor_id [FK])
    • Admission(admission_id [PK], admission_date, ward_number, patient_id [FK])
Loading diagram...

Problem 2.4: E-Learning Platform

An e-learning platform stores data about students, courses, and instructors. A student has a student ID, name, email, and phone. An instructor has an instructor ID, name, qualification, and email. A course has a course ID, title, and credits. A student can enroll in many courses, and an instructor can teach multiple courses. Each enrollment has an enrollment date and status.

  • Relational Tables:
    • Student(student_id [PK], name, email, phone)
    • Instructor(instructor_id [PK], name, qualification, email)
    • Course(course_id [PK], title, credits, instructor_id [FK])
    • Enrollment(student_id [FK], course_id [FK], enrollment_date, status, PRIMARY KEY (student_id, course_id))

Problem 2.5: Library Borrowing Management

A library keeps records of books, members, and borrowings. A member has a member ID, name, address, and phone. Each book has a book ID, title, author, and publication year. A member can borrow multiple books, and each borrowing has a borrow date and return date.

  • Relational Tables:
    • Member(member_id [PK], name, address, phone)
    • Book(book_id [PK], title, author, publication_year)
    • Borrowing(borrow_id [PK], member_id [FK], book_id [FK], borrow_date, return_date)

Problem 2.6: Online Retail Store

An online store records customers, products, and orders. Each customer has an ID, name, email, and address. Each product has an ID, name, category, and price. A customer can place many orders, and each order includes order ID, date, and total cost. Orders can contain multiple products.

  • Relational Tables:
    • Customer(customer_id [PK], name, email, address)
    • Product(product_id [PK], name, category, price)
    • Orders(order_id [PK], customer_id [FK], order_date, total_cost)
    • Order_Items(order_id [FK], product_id [FK], quantity, PRIMARY KEY (order_id, product_id))

Problem 2.7: Bank Customers, Accounts, and Transactions

A bank stores information about customers, accounts, and transactions. A customer has an ID, name, address, and phone. An account has an account number, type, and balance. A customer can hold multiple accounts. Each transaction has a transaction ID, date, amount, and type (deposit/withdrawal).

  • Relational Tables:
    • Customer(customer_id [PK], name, address, phone)
    • Account(account_number [PK], account_type, balance, customer_id [FK])
    • Transaction(transaction_id [PK], account_number [FK], transaction_date, amount, type)

Problem 2.8: Hotel Reservation System

A hotel manages guests, rooms, and reservations. A guest has a guest ID, name, address, and phone. A room has a room number, type, and price. Guests can book many rooms, and each reservation has a reservation ID, check-in date, and check-out date.

  • Relational Tables:
    • Guest(guest_id [PK], name, address, phone)
    • Room(room_number [PK], room_type, price)
    • Reservation(reservation_id [PK], guest_id [FK], room_number [FK], check_in_date, check_out_date)

Problem 2.9: School Students, Teachers, and Classes

A school stores details about students, teachers, and classes. A student has an ID, name, age, and grade. A teacher has a teacher ID, name, and subject specialization. A class has a class ID, name, and schedule. Students enroll in many classes, and teachers can teach multiple classes.

  • Relational Tables:
    • Teacher(teacher_id [PK], name, specialization)
    • Class(class_id [PK], name, schedule, teacher_id [FK])
    • Student(student_id [PK], name, age, grade)
    • Student_Class(student_id [FK], class_id [FK], PRIMARY KEY (student_id, class_id))

Problem 2.10: Cinema Ticket Booking

A cinema records customers, movies, and bookings. A customer has an ID, name, email, and phone. A movie has a movie ID, title, genre, and duration. A booking has a booking ID, date, and seat number. A customer can book multiple movies, and each movie has many bookings.

  • Relational Tables:
    • Customer(customer_id [PK], name, email, phone)
    • Movie(movie_id [PK], title, genre, duration)
    • Booking(booking_id [PK], booking_date, seat_number, customer_id [FK], movie_id [FK])

Problem 2.11: Hospital Pharmacy Management

A hospital pharmacy maintains medicines, suppliers, and purchase orders. A medicine has a medicine ID, name, type, and price. A supplier has a supplier ID, name, and contact info. A purchase order has an order ID, date, and total amount. Medicines can be supplied by multiple suppliers.

  • Relational Tables:
    • Supplier(supplier_id [PK], name, contact_info)
    • Medicine(medicine_id [PK], name, type, price)
    • PurchaseOrder(order_id [PK], order_date, total_amount, supplier_id [FK])
    • PurchaseOrder_Details(order_id [FK], medicine_id [FK], quantity, unit_cost, PRIMARY KEY (order_id, medicine_id))

Problem 2.12: Company HR and Salary Records

A company stores information about employees, departments, and salaries. An employee has an employee ID, name, designation, and phone. A department has a department ID, name, and location. Each employee belongs to one department. Each salary record has a salary ID, date, and amount.

  • Relational Tables:
    • Department(department_id [PK], name, location)
    • Employee(employee_id [PK], name, designation, phone, department_id [FK])
    • Salary(salary_id [PK], payment_date, amount, employee_id [FK])

Problem 2.13: Hostel Room Allocation

A hostel keeps records of students, rooms, and allocations. A student has a student ID, name, and department. A room has a room number, type, and capacity. A student is allocated to one room, and each room may hold multiple students. Allocations have start date and end date.

  • Relational Tables:
    • Room(room_number [PK], type, capacity)
    • Student(student_id [PK], name, department)
    • Allocation(allocation_id [PK], student_id [FK, UNIQUE], room_number [FK], start_date, end_date)

Problem 2.14: Music Streaming Service

A streaming service stores users, playlists, and songs. A user has a user ID, name, and subscription type. A playlist has a playlist ID, name, and creation date. A song has a song ID, title, artist, and duration. Users can create multiple playlists, and playlists contain multiple songs.

  • Relational Tables:
    • User(user_id [PK], name, subscription_type)
    • Song(song_id [PK], title, artist, duration)
    • Playlist(playlist_id [PK], name, creation_date, user_id [FK])
    • Playlist_Song(playlist_id [FK], song_id [FK], track_position, PRIMARY KEY (playlist_id, song_id))

Problem 2.15: Tournament, Teams, and Matches

A tournament records teams, players, and matches. A team has a team ID, name, and coach. A player has a player ID, name, age, and role. A match has a match ID, date, and venue. Teams participate in matches, and players belong to teams.

  • Relational Tables:
    • Team(team_id [PK], name, coach)
    • Player(player_id [PK], name, age, role, team_id [FK])
    • Match(match_id [PK], match_date, venue, home_team_id [FK REFERENCES Team(team_id)], away_team_id [FK REFERENCES Team(team_id)], home_score, away_score)

Problem 2.16: Insurance Policies and Claims

An insurance company maintains information about policyholders, policies, and claims. A policyholder has a holder ID, name, address, and phone. A policy has a policy ID, type, and premium amount. A policyholder can have multiple policies. A claim has a claim ID, date, and claim amount.

  • Relational Tables:
    • Policyholder(holder_id [PK], name, address, phone)
    • Policy(policy_id [PK], policy_type, premium_amount, holder_id [FK])
    • Claim(claim_id [PK], claim_date, claim_amount, policy_id [FK])

Problem 2.17: Learning Portal Assignments and Submissions

A learning portal keeps records of students, instructors, and assignments. A student has an ID, name, and email. An instructor has an ID, name, and expertise. Each assignment has an assignment ID, title, and deadline. Students submit assignments to instructors.

  • Relational Tables:
    • Student(student_id [PK], name, email)
    • Instructor(instructor_id [PK], name, expertise)
    • Assignment(assignment_id [PK], title, deadline, instructor_id [FK])
    • Submission(submission_id [PK], student_id [FK], assignment_id [FK], submission_date, grade, UNIQUE (student_id, assignment_id))

Problem 2.18: Real Estate Agency

A real estate company stores data about clients, properties, and agents. A client has a client ID, name, and contact info. A property has a property ID, location, type, and price. An agent has an agent ID, name, and license number. Clients can buy or rent properties through agents.

  • Relational Tables:
    • Agent(agent_id [PK], name, license_number)
    • Client(client_id [PK], name, contact_info)
    • Property(property_id [PK], location, type, price, agent_id [FK])
    • Contract(contract_id [PK], client_id [FK], property_id [FK], agent_id [FK], contract_date, agreed_price, contract_type)

Problem 2.19: Gym Members, Trainers, and Sessions

A gym records members, trainers, and workout sessions. A member has a member ID, name, and membership type. A trainer has a trainer ID, name, and specialization. A workout session has a session ID, date, and duration. Members can attend multiple sessions, and trainers can conduct multiple sessions.

  • Relational Tables:
    • Member(member_id [PK], name, membership_type)
    • Trainer(trainer_id [PK], name, specialization)
    • WorkoutSession(session_id [PK], session_date, duration, trainer_id [FK])
    • Attendance(session_id [FK], member_id [FK], PRIMARY KEY (session_id, member_id))

Problem 2.20: Reverse Engineering: Gym Schema to ERD

Given the relational schema:

  • Member(MemberID, Name, MembershipType)

  • Trainer(TrainerID, Name, Specialization)

  • Session(SessionID, Date, Duration, TrainerID)

  • Attendance(SessionID, MemberID)

  • Analysis:

    • TrainerID in Session is a foreign key referencing Trainer →\rightarrow Cardinality is 1:N1 : N (Trainer conducts Session).
    • Attendance is a bridge relation with composite primary key (SessionID, MemberID) →\rightarrow Cardinality is M:NM : N (Member attends Session).
Loading diagram...

Problem 2.21: Reverse Engineering: Cinema Schema to ERD

Given the relational schema:

  • Customer(CustomerID, Name, Email, Phone)

  • Movie(MovieID, Title, Genre, Duration)

  • Booking(BookingID, Date, SeatNo, CustomerID, MovieID)

  • Analysis:

    • Booking references Customer(CustomerID) and Movie(MovieID) as foreign keys.
    • A customer creates many bookings (1:N1 : N).
    • A movie is scheduled across many bookings (1:N1 : N).
Loading diagram...

Problem 2.22: Employee and Department Organization

Construct an ER diagram and relational schema for the following specifications:

  • EMPLOYEE: EMP_ID (PK), NAME, ADDRESS, BIRTHDATE

  • DEPARTMENT: DEPT_NO (PK), DEPT_NAME

  • WORKS_FOR: Many-to-One (M:1M : 1). Many employees work in one department; each employee works for only one department.

  • MANAGES: One-to-One (1:11 : 1). Each department is managed by one employee, and each manager manages only one department at a time.

  • Relational Tables:

    • DEPARTMENT(DEPT_NO [PK], DEPT_NAME, MANAGER_EMP_ID [FK REFERENCES EMPLOYEE(EMP_ID), UNIQUE])
    • EMPLOYEE(EMP_ID [PK], NAME, ADDRESS, BIRTHDATE, DEPT_NO [FK REFERENCES DEPARTMENT(DEPT_NO)])
Loading diagram...

Problem 2.23: Car Rental and Subcontractor Enterprise

A car hire company maintains:

  • 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 (Credit Card / Cash)

  • Relationships:

    • CAR – SERVICED_BY – SUBCONTRACTOR (M:1M : 1)
    • COMPANY_EXPENDITURE – RELATED_TO – SUBCONTRACTOR (M:1M : 1)
    • COMPANY_REVENUE – GENERATED_FROM – CUSTOMER (M:1M : 1)
    • CUSTOMER – MAKES – RESERVATION (1:N1 : N)
    • RESERVATION – FOR – CAR (M:1M : 1)
  • Relational Tables:

    • Subcontractor(Garage_ID [PK], Name, Address, Range_of_Services, Payment_Terms)
    • Car(Car_ID [PK], Make, Model, Year, Engine_Size, Fuel_Type, Passenger_No, Registration_No, Purchase_Date, Rent_Price, Insurance_Details, Garage_ID [FK])
    • Customer(Customer_ID [PK], Name, Address, Phone_No, Driving_Licence_No, Type, Credit_Limit)
    • Reservation(Reservation_ID [PK], Date, Duration, Deposit_Amount, Payment_Mode, Customer_ID [FK], Car_ID [FK])
    • Company_Expenditure(Exp_ID [PK], Date, Amount, Type, Garage_ID [FK], Car_ID [FK])
    • Company_Revenue(Rev_ID [PK], Date, Amount, Source, Customer_ID [FK], Reservation_ID [FK])

Problem 2.24: National Hockey League (NHL) Case Study

The NHL database maintains:

  • Teams have a name, city, coach, captain, and set of players.

  • Each player belongs to only one team and has a name, position, skill level, and injury records.

  • A team captain is also a player.

  • A game is played between two teams (host and guest) with a date and score.

  • Relational Tables:

    • Team(team_name [PK], city, coach, captain_id [FK])
    • Player(player_id [PK], name, position, skill_level, team_name [FK REFERENCES Team(team_name)])
    • Injury_Record(record_id [PK], player_id [FK REFERENCES Player(player_id)], injury_date, description)
    • Game(game_id [PK], game_date, host_team [FK REFERENCES Team(team_name)], guest_team [FK REFERENCES Team(team_name)], host_score, guest_score)

Problem 2.25: University Registrar System Case Study

A registrar maintains courses, course offerings, students, and instructors:

  • Relational Tables:
    • Course(course_number [PK], title, credits, syllabus)
    • Prerequisite(course_number [FK], prereq_course_number [FK], PRIMARY KEY (course_number, prereq_course_number))
    • Instructor(instructor_id [PK], name, department, title)
    • Course_Offering(course_number [FK], year, semester, section_number, timings, classroom, instructor_id [FK], PRIMARY KEY (course_number, year, semester, section_number))
    • Student(student_id [PK], name, program)
    • Enrollment(student_id [FK], course_number [FK], year, semester, section_number, grade, PRIMARY KEY (student_id, course_number, year, semester, section_number))

Problem 2.26: Car Insurance System Case Study

A car insurance company tracks customers who own one or more cars. Each car has associated with it zero or more recorded accidents.

  • Relational Tables:
    • Person(driver_id [PK], name, address)
    • Car(license [PK], year, model, driver_id [FK REFERENCES Person(driver_id)])
    • Accident(report_number [PK], accident_date, location)
    • Participated(driver_id [FK], license [FK], report_number [FK], damage_amount, PRIMARY KEY (driver_id, license, report_number))

Problem 2.27: Hospital and Doctor Test-Log Case Study

A hospital associates with each patient a log of various tests and examinations conducted by medical doctors.

  • Relational Tables:
    • Patient(SSN [PK], name, insurance)
    • Physician(physician_name [PK], specialization)
    • Test_Log(SSN [FK REFERENCES Patient(SSN)], test_name, test_date, test_time, PRIMARY KEY (SSN, test_name, test_date, test_time))
    • Doctor_Patient(physician_name [FK REFERENCES Physician(physician_name)], SSN [FK REFERENCES Patient(SSN)], PRIMARY KEY (physician_name, SSN))
    • Patient_History(SSN [FK REFERENCES Patient(SSN)], test_name, test_date, PRIMARY KEY (SSN, test_name, test_date))

Problem 2.28: Student Exam Marks (Ternary vs. Binary Modeling)

Record marks that students receive in different exams of different course offerings:

  • a. Ternary Relationship Model:
    • Connects Student, Exam, and Course_Offering through a single ternary relationship Takes_Exam:
    • Takes_Exam(student_id [FK], exam_id [FK], offering_id [FK], marks, PRIMARY KEY (student_id, exam_id, offering_id)).
  • b. Binary Relationship Alternative:
    • Model Exam as an entity set related to Course_Offering (N:1N : 1).
    • A student enrolls in Course_Offering via a binary relationship Enrollment.
    • Marks are recorded in Exam_Result(student_id [FK], exam_id [FK], marks, PRIMARY KEY (student_id, exam_id)), ensuring only a single binary relationship exists between student and course offering while supporting multiple exam evaluations.

Problem 2.29: Company Database with Projects, Departments, and Dependents

  • A company is organized into DEPARTMENTs. Each department has a unique name, number, a manager with start date, and several locations.

  • A department controls a number of PROJECTs, each with a unique name, number, and location.

  • EMPLOYEEs have a name, SSN, address, salary, sex, and birth date. Each employee is assigned to one department, may work on several projects with specified hours/week, and has an immediate supervisor.

  • DEPENDENTs are tracked for insurance: dependent name, birth date, relationship to employee.

  • Relational Tables:

    • Department(dnumber [PK], dname [UNIQUE], mgr_ssn [FK REFERENCES Employee(ssn)], mgr_start_date)
    • Dept_Locations(dnumber [FK], dlocation, PRIMARY KEY (dnumber, dlocation))
    • Project(pnumber [PK], pname [UNIQUE], plocation, dnumber [FK REFERENCES Department(dnumber)])
    • Employee(ssn [PK], fname, lname, address, salary, sex, bdate, dnumber [FK REFERENCES Department(dnumber)], super_ssn [FK REFERENCES Employee(ssn)])
    • Works_On(essn [FK REFERENCES Employee(ssn)], pno [FK REFERENCES Project(pnumber)], hours, PRIMARY KEY (essn, pno))
    • Dependent(essn [FK REFERENCES Employee(ssn)], dependent_name, sex, bdate, relationship, PRIMARY KEY (essn, dependent_name))

3. Relational Database Normalization

Problem 3.1: Student-Course-Instructor Worksheet

Normalize the following table up to 3NF:

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

1. Candidate Key and Functional Dependencies

  • Candidate Key: (StudentID, CourseCode)
  • Functional Dependencies (FDs):
    1. StudentID -> StudentName, Dept
    2. CourseCode -> CourseName, Instructor, InstructorPhone
    3. Instructor -> InstructorPhone

2. First Normal Form (1NF)

The table is in 1NF because all attribute values are atomic and a composite primary key (StudentID, CourseCode) is identified.

3. Second Normal Form (2NF) Decomposition

Eliminate partial dependencies on subsets of the composite candidate key:

  • StudentID -> StudentName, Dept (partial dependency on StudentID)
  • CourseCode -> CourseName, Instructor, InstructorPhone (partial dependency on CourseCode)

Decomposed tables:

  • Student(StudentID [PK], StudentName, Dept)
  • Course_Details(CourseCode [PK], CourseName, Instructor, InstructorPhone)
  • Enrollment(StudentID [FK], CourseCode [FK], PRIMARY KEY (StudentID, CourseCode))

4. Third Normal Form (3NF) Decomposition

Eliminate transitive dependencies in Course_Details where CourseCode -> Instructor and Instructor -> InstructorPhone:

  • Instructor(Instructor [PK], InstructorPhone)
  • Course(CourseCode [PK], CourseName, Instructor [FK REFERENCES Instructor(Instructor)])
  • Student(StudentID [PK], StudentName, Dept)
  • Enrollment(StudentID [FK REFERENCES Student(StudentID)], CourseCode [FK REFERENCES Course(CourseCode)], PRIMARY KEY (StudentID, CourseCode))

Problem 3.2: Sales Orders Worksheet

Normalize the following Sales table to 3NF:

OrderIDCustomerNameCustomerPhoneProductNameQuantityPrice
101Rifat01744444444Pen105
101Rifat01744444444Notebook220
102Nila01755555555Pen55

1. Candidate Key and Functional Dependencies

  • Candidate Key: (OrderID, ProductName)
  • Functional Dependencies:
    1. OrderID -> CustomerName, CustomerPhone
    2. CustomerName -> CustomerPhone
    3. ProductName -> Price
    4. (OrderID, ProductName) -> Quantity

2. Second Normal Form (2NF)

Eliminate partial dependencies on OrderID and ProductName:

  • Order_Customer(OrderID [PK], CustomerName, CustomerPhone)
  • Product(ProductName [PK], Price)
  • Order_Item(OrderID [FK], ProductName [FK], Quantity, PRIMARY KEY (OrderID, ProductName))

3. Third Normal Form (3NF)

Eliminate transitive dependency OrderID -> CustomerName -> CustomerPhone in Order_Customer:

  • Customer(CustomerName [PK], CustomerPhone)
  • Orders(OrderID [PK], CustomerName [FK REFERENCES Customer(CustomerName)])
  • Product(ProductName [PK], Price)
  • Order_Item(OrderID [FK REFERENCES Orders(OrderID)], ProductName [FK REFERENCES Product(ProductName)], Quantity, PRIMARY KEY (OrderID, ProductName))

Problem 3.3: Employee-Project-Location Worksheet

Normalize the following table up to 3NF:

EmpIDEmpNameProjIDProjNameProjLocationDeptLocation
1RafiP1Web AppDhakaLevel 5
1RafiP2HR SystemDhakaLevel 5
2AshaP3Payroll AppChattogramLevel 2

1. Candidate Key and Functional Dependencies

  • Candidate Key: (EmpID, ProjID)
  • Functional Dependencies:
    1. EmpID -> EmpName, DeptLocation
    2. ProjID -> ProjName, ProjLocation

2. 2NF & 3NF Decomposition

Both dependencies are partial dependencies on parts of the candidate key. Because no transitive dependencies exist between non-prime attributes, removing partial dependencies achieves both 2NF and 3NF:

  • Employee(EmpID [PK], EmpName, DeptLocation)
  • Project(ProjID [PK], ProjName, ProjLocation)
  • Emp_Project(EmpID [FK REFERENCES Employee(EmpID)], ProjID [FK REFERENCES Project(ProjID)], PRIMARY KEY (EmpID, ProjID))

Problem 3.4: Library Book Issue Worksheet

Normalize the table to 3NF:

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

1. Candidate Key and Functional Dependencies

  • Candidate Key: IssueID (Single attribute key)
  • Functional Dependencies:
    1. IssueID -> StudentID, BookID
    2. StudentID -> StudentName
    3. BookID -> BookTitle, Author
    4. Author -> AuthorEmail

2. 2NF Verification

Because the primary key IssueID is a single attribute (not composite), partial dependencies cannot exist. The table is inherently in 2NF.

3. 3NF Decomposition

Eliminate transitive dependencies:

  • Author(Author [PK], AuthorEmail)
  • Book(BookID [PK], BookTitle, Author [FK REFERENCES Author(Author)])
  • Student(StudentID [PK], StudentName)
  • Book_Issue(IssueID [PK], StudentID [FK REFERENCES Student(StudentID)], BookID [FK REFERENCES Book(BookID)])

Problem 3.5: Customer-Order-Supplier Worksheet

Convert the table to 1NF, 2NF, and 3NF:

OrderIDCustomerIDCustomerNameProductIDProductNameSupplierNameSupplierContact
O01C001RakibP01MouseLogitech0123456789
O01C001RakibP02KeyboardHP0171111111
O02C002NabilaP01MouseLogitech0123456789

1. First Normal Form (1NF)

Ensure atomic entries and establish primary key (OrderID, ProductID).

2. Second Normal Form (2NF)

Eliminate partial dependencies:

  • OrderID -> CustomerID, CustomerName
  • ProductID -> ProductName, SupplierName, SupplierContact

Decomposed tables:

  • Order_Cust(OrderID [PK], CustomerID, CustomerName)
  • Product_Supp(ProductID [PK], ProductName, SupplierName, SupplierContact)
  • Order_Product(OrderID [FK], ProductID [FK], PRIMARY KEY (OrderID, ProductID))

3. Third Normal Form (3NF)

Eliminate transitive dependencies CustomerID -> CustomerName and SupplierName -> SupplierContact:

  • Customer(CustomerID [PK], CustomerName)
  • Orders(OrderID [PK], CustomerID [FK REFERENCES Customer(CustomerID)])
  • Supplier(SupplierName [PK], SupplierContact)
  • Product(ProductID [PK], ProductName, SupplierName [FK REFERENCES Supplier(SupplierName)])
  • Order_Items(OrderID [FK REFERENCES Orders(OrderID)], ProductID [FK REFERENCES Product(ProductID)], PRIMARY KEY (OrderID, ProductID))

4. PL/SQL Programming: Blocks, Cursors, and Triggers

Database Tables and Seed Data

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    department VARCHAR2(20),
    age NUMBER,
    marks NUMBER
);

CREATE TABLE courses (
    course_id NUMBER PRIMARY KEY,
    course_name VARCHAR2(50),
    student_id NUMBER REFERENCES students(student_id),
    grade VARCHAR2(5)
);

INSERT INTO students VALUES (1, 'Rahim', 'CSE', 21, 85);
INSERT INTO students VALUES (2, 'Karim', 'EEE', 22, 75);
INSERT INTO students VALUES (3, 'Anika', 'CSE', 19, 65);
INSERT INTO students VALUES (4, 'Rafiq', 'BBA', 23, 55);
INSERT INTO students VALUES (5, 'Arman', 'CSE', 20, 95);
INSERT INTO students VALUES (6, 'Nadia', 'EEE', 18, 60);
INSERT INTO students VALUES (7, 'Sajid', 'CSE', 22, 70);
INSERT INTO students VALUES (8, 'Salman', 'LAW', 24, 50);
INSERT INTO students VALUES (9, 'Farhan', 'EEE', 20, 80);
INSERT INTO students VALUES (10, 'Mim', 'BBA', 19, NULL);

INSERT INTO courses VALUES (101, 'Database', 1, 'A');
INSERT INTO courses VALUES (102, 'Networking', 1, 'B');
INSERT INTO courses VALUES (103, 'Database', 2, 'B');
INSERT INTO courses VALUES (104, 'Operating Systems', 3, 'C');
INSERT INTO courses VALUES (105, 'Networking', 4, 'A');
INSERT INTO courses VALUES (106, 'Database', 5, 'A');
INSERT INTO courses VALUES (107, 'AI', 5, 'A');
INSERT INTO courses VALUES (108, 'Machine Learning', 6, 'B');
INSERT INTO courses VALUES (109, 'Cybersecurity', 7, 'C');
INSERT INTO courses VALUES (110, 'Database', 9, 'B');
COMMIT;

4.1 SQL Practice Queries

-- 1. Retrieve all student names and their departments
SELECT name, department FROM students;

-- 2. Display the names of students whose age is greater than 20
SELECT name FROM students WHERE age > 20;

-- 3. List students with marks between 60 and 80
SELECT * FROM students WHERE marks BETWEEN 60 AND 80;

-- 4. Find students whose name starts with 'A'
SELECT * FROM students WHERE name LIKE 'A%';

-- 5. Show students whose department is either 'CSE' or 'EEE' using IN
SELECT * FROM students WHERE department IN ('CSE', 'EEE');

-- 6. Display students not belonging to the 'CSE' department
SELECT * FROM students WHERE department <> 'CSE';

-- 7. Retrieve the list of distinct departments from the STUDENTS table
SELECT DISTINCT department FROM students;

-- 8. Show all students with null values in the marks column
SELECT * FROM students WHERE marks IS NULL;

-- 9. Display students sorted by marks in descending order
SELECT * FROM students ORDER BY marks DESC;

-- 10. Retrieve the top 3 students with the highest marks
SELECT * FROM (
    SELECT * FROM students WHERE marks IS NOT NULL ORDER BY marks DESC
) WHERE ROWNUM <= 3;

-- 11. Find the total number of students
SELECT COUNT(*) AS total_students FROM students;

-- 12. Calculate the average marks of all students
SELECT AVG(marks) AS average_marks FROM students;

-- 13. Find the maximum marks in the STUDENTS table
SELECT MAX(marks) AS max_marks FROM students;

-- 14. Display the minimum age among all students
SELECT MIN(age) AS min_age FROM students;

-- 15. Show the total marks obtained by students in the CSE department
SELECT SUM(marks) AS total_cse_marks FROM students WHERE department = 'CSE';

-- 16. Count how many students belong to the EEE department
SELECT COUNT(*) AS total_eee_students FROM students WHERE department = 'EEE';

-- 17. Display the department-wise average marks
SELECT department, AVG(marks) AS avg_marks FROM students GROUP BY department;

-- 18. Find the number of distinct departments
SELECT COUNT(DISTINCT department) AS distinct_dept_count FROM students;

-- 19. Show the student_id and their total number of courses taken
SELECT student_id, COUNT(*) AS total_courses FROM courses GROUP BY student_id;

-- 20. List departments having more than 2 students
SELECT department, COUNT(*) AS student_count FROM students GROUP BY department HAVING COUNT(*) > 2;

-- 21. Display student names along with the courses they are enrolled in
SELECT s.name, c.course_name FROM students s JOIN courses c ON s.student_id = c.student_id;

-- 22. Show students who are not enrolled in any course (LEFT JOIN)
SELECT s.student_id, s.name FROM students s LEFT JOIN courses c ON s.student_id = c.student_id WHERE c.course_id IS NULL;

-- 23. List all courses and the students who take them (INNER JOIN)
SELECT c.course_name, s.name FROM courses c INNER JOIN students s ON c.student_id = s.student_id;

-- 24. Display the names of students and their grades in each course
SELECT s.name, c.course_name, c.grade FROM students s JOIN courses c ON s.student_id = c.student_id;

-- 25. Retrieve student names and course names using JOIN ON
SELECT s.name AS student_name, c.course_name FROM students s JOIN courses c ON s.student_id = c.student_id;

-- 26. Find students who got grade 'A' in any course
SELECT DISTINCT s.student_id, s.name FROM students s JOIN courses c ON s.student_id = c.student_id WHERE c.grade = 'A';

-- 27. Show courses with no student enrolled (RIGHT JOIN)
SELECT c.course_id, c.course_name FROM students s RIGHT JOIN courses c ON s.student_id = c.student_id WHERE s.student_id IS NULL;

-- 28. List all students along with course names (FULL OUTER JOIN)
SELECT s.name, c.course_name FROM students s FULL OUTER JOIN courses c ON s.student_id = c.student_id;

-- 29. Display student names who are enrolled in more than one course
SELECT s.name FROM students s JOIN courses c ON s.student_id = c.student_id GROUP BY s.student_id, s.name HAVING COUNT(c.course_id) > 1;

-- 30. Retrieve student and course information where course_name is 'Database'
SELECT s.student_id, s.name, s.department, c.course_id, c.grade FROM students s JOIN courses c ON s.student_id = c.student_id WHERE c.course_name = 'Database';

-- 31. Find students whose marks are above the average marks
SELECT * FROM students WHERE marks > (SELECT AVG(marks) FROM students);

-- 32. Display students who scored the highest marks
SELECT * FROM students WHERE marks = (SELECT MAX(marks) FROM students);

-- 33. Show the names of students who are enrolled in a course named 'Networking'
SELECT name FROM students WHERE student_id IN (SELECT student_id FROM courses WHERE course_name = 'Networking');

-- 34. Retrieve the department of the student who scored the lowest marks
SELECT department FROM students WHERE marks = (SELECT MIN(marks) FROM students);

-- 35. Find students older than the student named 'Rahim'
SELECT * FROM students WHERE age > (SELECT age FROM students WHERE name = 'Rahim');

-- 36. List students who have the same marks as student ID = 5
SELECT * FROM students WHERE marks = (SELECT marks FROM students WHERE student_id = 5) AND student_id <> 5;

-- 37. Show the student(s) who enrolled in the maximum number of courses
SELECT s.student_id, s.name, COUNT(*) AS course_count FROM students s JOIN courses c ON s.student_id = c.student_id GROUP BY s.student_id, s.name HAVING COUNT(*) = (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM courses GROUP BY student_id));

-- 38. Find the second-highest marks from STUDENTS using a subquery
SELECT MAX(marks) AS second_highest_marks FROM students WHERE marks < (SELECT MAX(marks) FROM students);

-- 39. List the names of students who are not enrolled in any course (subquery)
SELECT name FROM students WHERE student_id NOT IN (SELECT student_id FROM courses WHERE student_id IS NOT NULL);

-- 40. Retrieve students whose marks are greater than all students in the EEE department
SELECT * FROM students WHERE marks > ALL (SELECT marks FROM students WHERE department = 'EEE' AND marks IS NOT NULL);

-- 41. Retrieve students whose names end with the letter 'n'
SELECT * FROM students WHERE name LIKE '%n';

-- 42. Show students whose name contains the substring 'ar'
SELECT * FROM students WHERE name LIKE '%ar%' OR name LIKE '%Ar%';

-- 43. Find students aged between 18 and 22 using BETWEEN
SELECT * FROM students WHERE age BETWEEN 18 AND 22;

-- 44. Display students whose marks are not between 50 and 70
SELECT * FROM students WHERE marks NOT BETWEEN 50 AND 70;

-- 45. Retrieve students whose names are exactly 5 characters long
SELECT * FROM students WHERE name LIKE '_____';

-- 46. List students who belong to departments starting with 'C'
SELECT * FROM students WHERE department LIKE 'C%';

-- 47. Show students whose marks are greater than 70 OR age less than 20
SELECT * FROM students WHERE marks > 70 OR age < 20;

-- 48. Display students whose department is not null
SELECT * FROM students WHERE department IS NOT NULL;

-- 49. Find the students with marks in the set {55, 65, 75}
SELECT * FROM students WHERE marks IN (55, 65, 75);

-- 50. Retrieve student names and marks ordered first by department, then by marks descending
SELECT name, department, marks FROM students ORDER BY department ASC, marks DESC;

4.2 Anonymous Blocks

-- 1. Display name and marks of student with student_id = 5
SET SERVEROUTPUT ON;
DECLARE
    v_name  students.name%TYPE;
    v_marks students.marks%TYPE;
BEGIN
    SELECT name, marks INTO v_name, v_marks
    FROM students
    WHERE student_id = 5;
    DBMS_OUTPUT.PUT_LINE('Student: ' || v_name || ' | Marks: ' || v_marks);
END;
/

-- 2. Display all student names from STUDENTS table using a loop
DECLARE
    CURSOR c_students IS SELECT name FROM students;
    v_name students.name%TYPE;
BEGIN
    OPEN c_students;
    LOOP
        FETCH c_students INTO v_name;
        EXIT WHEN c_students%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Student Name: ' || v_name);
    END LOOP;
    CLOSE c_students;
END;
/

-- 3. Calculate and display the average marks of all students
DECLARE
    v_avg NUMBER(5,2);
BEGIN
    SELECT AVG(marks) INTO v_avg FROM students;
    DBMS_OUTPUT.PUT_LINE('Overall Average Marks: ' || v_avg);
END;
/

-- 4. Check if student's marks > 80: display "Excellent", otherwise "Keep Trying"
DECLARE
    v_marks students.marks%TYPE;
BEGIN
    SELECT marks INTO v_marks FROM students WHERE student_id = 1;
    IF v_marks > 80 THEN
        DBMS_OUTPUT.PUT_LINE('Excellent');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Keep Trying');
    END IF;
END;
/

-- 5. Increase the marks of student_id = 3 by 10
BEGIN
    UPDATE students
    SET marks = marks + 10
    WHERE student_id = 3;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Marks updated for student 3.');
END;
/

-- 6. Find and display the department of a student named 'Rahim'
DECLARE
    v_dept students.department%TYPE;
BEGIN
    SELECT department INTO v_dept FROM students WHERE name = 'Rahim';
    DBMS_OUTPUT.PUT_LINE('Department of Rahim: ' || v_dept);
END;
/

4.3 Explicit Cursors

-- 7. Cursor to display all student names and their marks
DECLARE
    CURSOR cur_all IS SELECT name, marks FROM students;
BEGIN
    FOR r IN cur_all LOOP
        DBMS_OUTPUT.PUT_LINE('Name: ' || r.name || ' | Marks: ' || NVL(TO_CHAR(r.marks), 'N/A'));
    END LOOP;
END;
/

-- 8. Cursor to display names of students who scored above 70
DECLARE
    CURSOR cur_above70 IS SELECT name, marks FROM students WHERE marks > 70;
BEGIN
    FOR rec IN cur_above70 LOOP
        DBMS_OUTPUT.PUT_LINE(rec.name || ' scored ' || rec.marks);
    END LOOP;
END;
/

-- 9. Cursor to display student_id and total number of courses taken
DECLARE
    CURSOR cur_course_count IS
        SELECT student_id, COUNT(*) AS total
        FROM courses
        GROUP BY student_id;
BEGIN
    FOR rec IN cur_course_count LOOP
        DBMS_OUTPUT.PUT_LINE('StudentID: ' || rec.student_id || ' | Courses: ' || rec.total);
    END LOOP;
END;
/

-- 10. Cursor to display all course names of student_id = 1
DECLARE
    CURSOR cur_s1_courses IS
        SELECT course_name FROM courses WHERE student_id = 1;
BEGIN
    FOR rec IN cur_s1_courses LOOP
        DBMS_OUTPUT.PUT_LINE('Course: ' || rec.course_name);
    END LOOP;
END;
/

-- 11. Cursor to find students who have NULL marks
DECLARE
    CURSOR cur_null_marks IS
        SELECT student_id, name FROM students WHERE marks IS NULL;
BEGIN
    FOR rec IN cur_null_marks LOOP
        DBMS_OUTPUT.PUT_LINE('Student with NULL marks: ' || rec.name || ' (ID: ' || rec.student_id || ')');
    END LOOP;
END;
/

-- 12. Cursor to display students and their grades from COURSES table
DECLARE
    CURSOR cur_grades IS
        SELECT s.name, c.course_name, c.grade
        FROM students s
        JOIN courses c ON s.student_id = c.student_id;
BEGIN
    FOR r IN cur_grades LOOP
        DBMS_OUTPUT.PUT_LINE(r.name || ' | ' || r.course_name || ' | Grade: ' || r.grade);
    END LOOP;
END;
/

4.4 Stored Functions

-- 13. Function to return the marks of a student given student_id
CREATE OR REPLACE FUNCTION get_student_marks(p_id IN NUMBER)
RETURN NUMBER IS
    v_marks NUMBER;
BEGIN
    SELECT marks INTO v_marks FROM students WHERE student_id = p_id;
    RETURN v_marks;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
/

-- 14. Function to return the department of a given student name
CREATE OR REPLACE FUNCTION get_student_dept(p_name IN VARCHAR2)
RETURN VARCHAR2 IS
    v_dept VARCHAR2(20);
BEGIN
    SELECT department INTO v_dept FROM students WHERE name = p_name;
    RETURN v_dept;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN 'Not Found';
END;
/

-- 15. Function to calculate grade based on marks (>=80 A, 70-79 B, 60-69 C, else F)
CREATE OR REPLACE FUNCTION calculate_grade(p_marks IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
    IF p_marks IS NULL THEN RETURN 'N/A';
    ELSIF p_marks >= 80 THEN RETURN 'A';
    ELSIF p_marks >= 70 THEN RETURN 'B';
    ELSIF p_marks >= 60 THEN RETURN 'C';
    ELSE RETURN 'F';
    END IF;
END;
/

-- 16. Function to count how many students are in a given department
CREATE OR REPLACE FUNCTION count_dept_students(p_dept IN VARCHAR2)
RETURN NUMBER IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM students WHERE department = p_dept;
    RETURN v_count;
END;
/

-- 17. Function to calculate average marks of CSE department students
CREATE OR REPLACE FUNCTION get_cse_avg_marks
RETURN NUMBER IS
    v_avg NUMBER;
BEGIN
    SELECT AVG(marks) INTO v_avg FROM students WHERE department = 'CSE';
    RETURN ROUND(v_avg, 2);
END;
/

-- 18. Function to return number of courses taken by given student_id
CREATE OR REPLACE FUNCTION get_course_count(p_id IN NUMBER)
RETURN NUMBER IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM courses WHERE student_id = p_id;
    RETURN v_count;
END;
/

4.5 Stored Procedures

-- 19. Procedure to display student details (name, department, marks) for given student_id
CREATE OR REPLACE PROCEDURE display_student_details(p_id IN NUMBER) IS
    v_name students.name%TYPE;
    v_dept students.department%TYPE;
    v_marks students.marks%TYPE;
BEGIN
    SELECT name, department, marks INTO v_name, v_dept, v_marks
    FROM students WHERE student_id = p_id;
    DBMS_OUTPUT.PUT_LINE('ID: ' || p_id || ' | Name: ' || v_name || ' | Dept: ' || v_dept || ' | Marks: ' || v_marks);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student ID ' || p_id || ' not found.');
END;
/

-- 20. Procedure to update marks of a student by adding 5
CREATE OR REPLACE PROCEDURE add_five_marks(p_id IN NUMBER) IS
BEGIN
    UPDATE students SET marks = marks + 5 WHERE student_id = p_id;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Added 5 marks to Student ID: ' || p_id);
END;
/

-- 21. Procedure to display all students in CSE department
CREATE OR REPLACE PROCEDURE display_cse_students IS
BEGIN
    FOR r IN (SELECT student_id, name, marks FROM students WHERE department = 'CSE') LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || r.student_id || ' | ' || r.name || ' | Marks: ' || r.marks);
    END LOOP;
END;
/

-- 22. Procedure to insert a new student into STUDENTS table
CREATE OR REPLACE PROCEDURE insert_student(
    p_id IN NUMBER,
    p_name IN VARCHAR2,
    p_dept IN VARCHAR2,
    p_age IN NUMBER,
    p_marks IN NUMBER
) IS
BEGIN
    INSERT INTO students (student_id, name, department, age, marks)
    VALUES (p_id, p_name, p_dept, p_age, p_marks);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Inserted student: ' || p_name);
END;
/

-- 23. Procedure to delete student by student_id
CREATE OR REPLACE PROCEDURE delete_student(p_id IN NUMBER) IS
BEGIN
    DELETE FROM courses WHERE student_id = p_id;
    DELETE FROM students WHERE student_id = p_id;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Deleted student ID: ' || p_id);
END;
/

-- 24. Procedure to display all course names for given student_id
CREATE OR REPLACE PROCEDURE display_student_courses(p_id IN NUMBER) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Courses for Student ' || p_id || ':');
    FOR r IN (SELECT course_name, grade FROM courses WHERE student_id = p_id) LOOP
        DBMS_OUTPUT.PUT_LINE('- ' || r.course_name || ' (Grade: ' || r.grade || ')');
    END LOOP;
END;
/

4.6 Database Triggers

-- 25. Trigger to prevent deletion of any student record
CREATE OR REPLACE TRIGGER trg_prevent_student_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Deletion of student records is prohibited.');
END;
/

-- 26. Trigger to prevent insertion if marks > 100
CREATE OR REPLACE TRIGGER trg_check_marks_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    IF :NEW.marks > 100 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Marks cannot exceed 100.');
    END IF;
END;
/

-- 27. Trigger to log whenever a student's marks are updated
CREATE TABLE student_marks_audit (
    audit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id NUMBER,
    old_marks NUMBER,
    new_marks NUMBER,
    changed_by VARCHAR2(50),
    changed_at DATE
);

CREATE OR REPLACE TRIGGER trg_log_marks_update
AFTER UPDATE OF marks ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_marks_audit (student_id, old_marks, new_marks, changed_by, changed_at)
    VALUES (:OLD.student_id, :OLD.marks, :NEW.marks, USER, SYSDATE);
END;
/

-- 28. Trigger to automatically set department = 'Unknown' if NULL is inserted
CREATE OR REPLACE TRIGGER trg_default_dept
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    IF :NEW.department IS NULL THEN
        :NEW.department := 'Unknown';
    END IF;
END;
/

-- 29. Trigger to prevent insertion of student with age < 18
CREATE OR REPLACE TRIGGER trg_prevent_underage
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    IF :NEW.age < 18 THEN
        RAISE_APPLICATION_ERROR(-20003, 'Student age must be at least 18.');
    END IF;
END;
/

-- 30. Trigger to print a message when a new course is inserted
CREATE OR REPLACE TRIGGER trg_notify_new_course
AFTER INSERT ON courses
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('New course assigned: ' || :NEW.course_name || ' to Student ID: ' || :NEW.student_id);
END;
/

4.7 Cursor Attributes and Row Operations

-- Implicit Cursor Attributes Demo (%FOUND, %ROWCOUNT)
BEGIN
    UPDATE students SET marks = marks + 2 WHERE department = 'EEE';
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated successfully.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No records updated.');
    END IF;
END;
/

-- Explicit Cursor Fetching Top 3 Records
DECLARE
    CURSOR c_top3 IS
        SELECT name, marks FROM students WHERE marks IS NOT NULL ORDER BY marks DESC;
    v_name  students.name%TYPE;
    v_marks students.marks%TYPE;
BEGIN
    OPEN c_top3;
    LOOP
        FETCH c_top3 INTO v_name, v_marks;
        EXIT WHEN c_top3%NOTFOUND OR c_top3%ROWCOUNT > 3;
        DBMS_OUTPUT.PUT_LINE(v_name || ' - Marks: ' || v_marks);
    END LOOP;
    CLOSE c_top3;
END;
/

4.8 Data Integrity and Audit Triggers

-- Validation Trigger: Prevent negative salary update
CREATE OR REPLACE TRIGGER trg_emp_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');
    END IF;
END;
/

-- Audit Trigger: Record deleted employees
CREATE TABLE emp_audit (
    emp_id NUMBER,
    deleted_on DATE
);

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO emp_audit (emp_id, deleted_on)
    VALUES (:OLD.employee_id, SYSDATE);
END;
/

-- Trigger to prevent inserting employee with salary < 1000
CREATE OR REPLACE TRIGGER prevent_low_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 1000 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Salary must be at least 1000.');
    END IF;
END;
/

-- Statement-Level Trigger: Display message after employee insertion
CREATE OR REPLACE TRIGGER show_insert_message
AFTER INSERT ON employees
BEGIN
    DBMS_OUTPUT.PUT_LINE('A new employee has been added to the database.');
END;
/

-- Trigger to auto-increase salary by 10% on update
CREATE OR REPLACE TRIGGER auto_increase_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    :NEW.salary := :NEW.salary * 1.10;
END;
/

-- INSTEAD OF Trigger on Complex View
CREATE OR REPLACE TRIGGER emp_dept_insert
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (emp_id, name, dept_id)
    VALUES (:NEW.emp_id, :NEW.name, :NEW.dept_id);
END;
/

5. Advanced SQL and Subqueries

Schema Definitions

Employee (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    dept_id NUMBER REFERENCES Department(dept_id),
    salary NUMBER(10,2),
    manager_id NUMBER,
    join_date DATE
);

Department (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50),
    location VARCHAR2(50)
);

Project (
    project_id NUMBER PRIMARY KEY,
    project_name VARCHAR2(50),
    budget NUMBER(12,2),
    dept_id NUMBER REFERENCES Department(dept_id)
);

Works_On (
    emp_id NUMBER REFERENCES Employee(emp_id),
    project_id NUMBER REFERENCES Project(project_id),
    hours NUMBER(4,1),
    PRIMARY KEY (emp_id, project_id)
);

Query 5.1: Project Budget Greater Than 50,000 (IN)

Find employees who are part of a project with a budget greater than 50,000.

SELECT emp_id, emp_name
FROM Employee
WHERE emp_id IN (
    SELECT emp_id
    FROM Works_On
    WHERE project_id IN (
        SELECT project_id
        FROM Project
        WHERE budget > 50000
    )
);

Query 5.2: Departments with High-Earning Employees (EXISTS)

List all departments that have employees earning more than 55,000.

SELECT d.dept_id, d.dept_name
FROM Department d
WHERE EXISTS (
    SELECT 1
    FROM Employee e
    WHERE e.dept_id = d.dept_id
      AND e.salary > 55000
);

Query 5.3: Employees Without Assigned Projects (NOT EXISTS)

Find the names of employees who are not part of any project.

SELECT e.emp_name
FROM Employee e
WHERE NOT EXISTS (
    SELECT 1
    FROM Works_On w
    WHERE w.emp_id = e.emp_id
);

Query 5.4: Department of Highest-Paid Employee (Scalar Subquery)

Display the department name of the employee with the highest salary.

SELECT (
    SELECT d.dept_name
    FROM Department d
    WHERE d.dept_id = e.dept_id
) AS department_name
FROM Employee e
WHERE e.salary = (
    SELECT MAX(salary)
    FROM Employee
);

Query 5.5: Salaries Above Global Average (> AVG)

Find employees whose salary is greater than the average salary of all employees.

SELECT emp_id, emp_name, salary
FROM Employee
WHERE salary > (
    SELECT AVG(salary)
    FROM Employee
);

Query 5.6: Employees Sharing Department with Bob (Correlated IN)

Find employees who are working in the same department as Bob.

SELECT e.emp_id, e.emp_name, e.dept_id
FROM Employee e
WHERE e.dept_id IN (
    SELECT b.dept_id
    FROM Employee b
    WHERE b.emp_name = 'Bob'
)
AND e.emp_name <> 'Bob';

Query 5.7: Projects Handled by HR Employees (Nested IN)

List all projects handled by employees from the HR department.

SELECT project_id, project_name
FROM Project
WHERE project_id IN (
    SELECT project_id
    FROM Works_On
    WHERE emp_id IN (
        SELECT emp_id
        FROM Employee
        WHERE dept_id IN (
            SELECT dept_id
            FROM Department
            WHERE dept_name = 'HR'
        )
    )
);

Query 5.8: Join Dates After Earliest in IT (> ALL)

Retrieve details of employees who have joined after the earliest joining date in the IT department.

SELECT *
FROM Employee
WHERE join_date > ALL (
    SELECT MIN(e.join_date)
    FROM Employee e
    JOIN Department d ON e.dept_id = d.dept_id
    WHERE d.dept_name = 'IT'
);

Query 5.9: Employees Excluded from Low-Budget Projects (NOT IN)

List all employees who are not working on projects that have a budget below 60,000.

SELECT emp_id, emp_name
FROM Employee
WHERE emp_id NOT IN (
    SELECT w.emp_id
    FROM Works_On w
    JOIN Project p ON w.project_id = p.project_id
    WHERE p.budget < 60000
);

Query 5.10: Departments with No Projects (NOT EXISTS)

Find the departments that have no projects assigned.

SELECT d.dept_id, d.dept_name
FROM Department d
WHERE NOT EXISTS (
    SELECT 1
    FROM Project p
    WHERE p.dept_id = d.dept_id
);

Query 5.11: Employees Involved in All IT Projects (Relational Division)

List the names of employees who are involved in all projects handled by the IT department.

SELECT e.emp_name
FROM Employee e
WHERE NOT EXISTS (
    SELECT p.project_id
    FROM Project p
    JOIN Department d ON p.dept_id = d.dept_id
    WHERE d.dept_name = 'IT'
    MINUS
    SELECT w.project_id
    FROM Works_On w
    WHERE w.emp_id = e.emp_id
);

Query 5.12: Projects Overlapping with Marketing Department (SOME)

Find employees who are working on some projects that employees from the Marketing department are also working on.

SELECT DISTINCT e.emp_id, e.emp_name
FROM Employee e
JOIN Works_On w ON e.emp_id = w.emp_id
WHERE w.project_id = SOME (
    SELECT w2.project_id
    FROM Works_On w2
    JOIN Employee e2 ON w2.emp_id = e2.emp_id
    JOIN Department d ON e2.dept_id = d.dept_id
    WHERE d.dept_name = 'Marketing'
)
AND e.dept_id <> (
    SELECT dept_id
    FROM Department
    WHERE dept_name = 'Marketing'
);

Query 5.13: Salaries Exceeding Any HR Employee (> ANY)

List all employees whose salary is greater than the salary of any employee in the HR department.

SELECT emp_id, emp_name, salary
FROM Employee
WHERE salary > ANY (
    SELECT e.salary
    FROM Employee e
    JOIN Department d ON e.dept_id = d.dept_id
    WHERE d.dept_name = 'HR'
);

Query 5.14: Department Project Budgets (Common Table Expression)

Using a Common Table Expression (WITH), list the total budget of projects handled by each department.

WITH DeptProjectBudget AS (
    SELECT dept_id, SUM(budget) AS total_project_budget
    FROM Project
    GROUP BY dept_id
)
SELECT d.dept_id, d.dept_name, NVL(dpb.total_project_budget, 0) AS total_budget
FROM Department d
LEFT JOIN DeptProjectBudget dpb ON d.dept_id = dpb.dept_id;

Query 5.15: Projects at Budget Extremes (Multi-Subquery IN)

Find employees who are part of projects where the budget is either the highest or the lowest across all projects.

SELECT DISTINCT e.emp_id, e.emp_name, p.project_name, p.budget
FROM Employee e
JOIN Works_On w ON e.emp_id = w.emp_id
JOIN Project p ON w.project_id = p.project_id
WHERE p.budget IN (
    (SELECT MAX(budget) FROM Project),
    (SELECT MIN(budget) FROM Project)
);

On this page

1. Transaction Management and ACID PropertiesScenario 1.1: Fund Transfer System CrashScenario 1.2: Inventory Stock Update FailureScenario 1.3: Concurrent Course Registration Race ConditionScenario 1.4: Ride-Booking Confirmation and Immediate CrashScenario 1.5: Loan Fee Deduction FailureScenario 1.6: Concurrent Railway Ticket BookingScenario 1.7: Hospital Treatment Billing FailureScenario 1.8: Ride-Sharing Driver Assignment UpdateScenario 1.9: Bank Fund Transfer Partial Debit2. Entity-Relationship (E-R) ModelingProblem 2.1: Online Food Delivery PlatformProblem 2.2: Airline Management SystemProblem 2.3: Hospital Patient, Doctor, and Admission SystemProblem 2.4: E-Learning PlatformProblem 2.5: Library Borrowing ManagementProblem 2.6: Online Retail StoreProblem 2.7: Bank Customers, Accounts, and TransactionsProblem 2.8: Hotel Reservation SystemProblem 2.9: School Students, Teachers, and ClassesProblem 2.10: Cinema Ticket BookingProblem 2.11: Hospital Pharmacy ManagementProblem 2.12: Company HR and Salary RecordsProblem 2.13: Hostel Room AllocationProblem 2.14: Music Streaming ServiceProblem 2.15: Tournament, Teams, and MatchesProblem 2.16: Insurance Policies and ClaimsProblem 2.17: Learning Portal Assignments and SubmissionsProblem 2.18: Real Estate AgencyProblem 2.19: Gym Members, Trainers, and SessionsProblem 2.20: Reverse Engineering: Gym Schema to ERDProblem 2.21: Reverse Engineering: Cinema Schema to ERDProblem 2.22: Employee and Department OrganizationProblem 2.23: Car Rental and Subcontractor EnterpriseProblem 2.24: National Hockey League (NHL) Case StudyProblem 2.25: University Registrar System Case StudyProblem 2.26: Car Insurance System Case StudyProblem 2.27: Hospital and Doctor Test-Log Case StudyProblem 2.28: Student Exam Marks (Ternary vs. Binary Modeling)Problem 2.29: Company Database with Projects, Departments, and Dependents3. Relational Database NormalizationProblem 3.1: Student-Course-Instructor Worksheet1. Candidate Key and Functional Dependencies2. First Normal Form (1NF)3. Second Normal Form (2NF) Decomposition4. Third Normal Form (3NF) DecompositionProblem 3.2: Sales Orders Worksheet1. Candidate Key and Functional Dependencies2. Second Normal Form (2NF)3. Third Normal Form (3NF)Problem 3.3: Employee-Project-Location Worksheet1. Candidate Key and Functional Dependencies2. 2NF & 3NF DecompositionProblem 3.4: Library Book Issue Worksheet1. Candidate Key and Functional Dependencies2. 2NF Verification3. 3NF DecompositionProblem 3.5: Customer-Order-Supplier Worksheet1. First Normal Form (1NF)2. Second Normal Form (2NF)3. Third Normal Form (3NF)4. PL/SQL Programming: Blocks, Cursors, and TriggersDatabase Tables and Seed Data4.1 SQL Practice Queries4.2 Anonymous Blocks4.3 Explicit Cursors4.4 Stored Functions4.5 Stored Procedures4.6 Database Triggers4.7 Cursor Attributes and Row Operations4.8 Data Integrity and Audit Triggers5. Advanced SQL and SubqueriesSchema DefinitionsQuery 5.1: Project Budget Greater Than 50,000 (IN)Query 5.2: Departments with High-Earning Employees (EXISTS)Query 5.3: Employees Without Assigned Projects (NOT EXISTS)Query 5.4: Department of Highest-Paid Employee (Scalar Subquery)Query 5.5: Salaries Above Global Average (> AVG)Query 5.6: Employees Sharing Department with Bob (Correlated IN)Query 5.7: Projects Handled by HR Employees (Nested IN)Query 5.8: Join Dates After Earliest in IT (> ALL)Query 5.9: Employees Excluded from Low-Budget Projects (NOT IN)Query 5.10: Departments with No Projects (NOT EXISTS)Query 5.11: Employees Involved in All IT Projects (Relational Division)Query 5.12: Projects Overlapping with Marketing Department (SOME)Query 5.13: Salaries Exceeding Any HR Employee (> ANY)Query 5.14: Department Project Budgets (Common Table Expression)Query 5.15: Projects at Budget Extremes (Multi-Subquery IN)