Skip to Content
PrepPath logoPrepPath

Answers

Comprehensive answers and worked solutions for Database Management System.

1. Transaction Management and ACID Properties

Answer 1.1: Fund Transfer System Crash

  • Violated Property: Atomicity (All-or-Nothing property).
  • Consistency Maintenance: A fund transfer consists of a debit operation followed by a credit operation. If a crash occurs before the credit completes, the debit must be undone. The database uses Write-Ahead Logging (WAL): log records (UNDO and REDO) are persisted to non-volatile disk before dirty buffers are written. During system reboot, the recovery manager performs an Undo pass to revert uncommitted debits, ensuring that either both accounts reflect the transfer or neither does.

Answer 1.2: Inventory Stock Update Failure

  • Compromised Property: Consistency (and Atomicity).
  • Reason: The business rule dictates that every order must correspond to a stock reduction. If stock decreases but the order is never inserted, the database enters an inconsistent state where inventory shows fewer items than physically sold, leading to phantom inventory shortages, incorrect replenishment triggers, and unbilled stock loss.

Answer 1.3: Concurrent Course Registration

  • Violated Property: Isolation.
  • Prevention: When two transactions execute concurrently without proper isolation, a race condition (lost update / dirty read) occurs where both read available_seats = 1 and decrement it to 0, resulting in over-allocation. The database prevents this by using isolation levels such as Serializable or pessimistic locking:
    SELECT available_seats FROM Courses WHERE course_id = 101 FOR UPDATE;
    The row-level exclusive lock forces the second transaction to wait until the first transaction commits or aborts.

Answer 1.4: Ride-Booking Confirmation and Server Crash

  • Violated Property: Durability.
  • Prevention: Once a transaction receives a successful commit confirmation, its changes must persist permanently, even if a power loss or crash occurs immediately afterward. The database prevents lost transactions by flushing commit log records to non-volatile disk storage before acknowledging the commit to the application client.

Answer 1.5: Loan Fee Deduction Failure

  • Violated Property: Atomicity.
  • Impact: The customer loses money without obtaining the requested loan record. This causes reconciliation discrepancies in bank financial audits and severely degrades customer trust and regulatory compliance.

Answer 1.6: Concurrent Railway Ticket Booking

  • Violated Property: Isolation.
  • Impact: Two passengers receive confirmation for the exact same physical berth, creating severe operational conflicts on the train. Proper concurrency control enforces strict two-phase locking (2PL) or atomic checks:
    UPDATE Seats SET is_reserved = 'Y' WHERE seat_no = 'B1-12' AND is_reserved = 'N';
    Only the transaction that updates 1 row proceeds; the second transaction updates 0 rows and is rejected.

Answer 1.7: Hospital Treatment Billing Failure

  • Violated Property: Durability (and Atomicity).
  • Impact: The hospital records patient treatment as completed, but accounts receivable reflects $0 for the visit, creating financial auditing holes and uncollected service revenues.

Answer 1.8: Ride-Sharing Driver Assignment Update

  • Violated Property: Atomicity.
  • Impact: The passenger's mobile interface shows the ride as accepted, but no driver has the trip in their queue. The passenger waits indefinitely while the driver receives no route details.

2. Entity-Relationship (E-R) Modeling and Relational Mapping

Answer 2.1: Gym Management System (Schema to ERD)

a. Primary and Foreign Keys

  • Member: Primary Key = MemberID.
  • Trainer: Primary Key = TrainerID.
  • Session: Primary Key = SessionID; Foreign Key = TrainerID REFERENCES Trainer(TrainerID).
  • Attendance: Composite Primary Key = (SessionID, MemberID); Foreign Keys = SessionID REFERENCES Session(SessionID) and MemberID REFERENCES Member(MemberID).

b. Cardinality Ratios

  • Trainer to Session: 1:N1:N (One trainer conducts many sessions; each session is conducted by one trainer).
  • Member to Session: M:NM:N (A member attends many sessions; a session has many attending members via Attendance).

c. ERD Structure (Mermaid)

Loading diagram...

Answer 2.2: Movie Theater Booking System

Loading diagram...
  • Relationships:
    • Customer to Booking: 1:N1:N (One customer can make multiple bookings; each booking belongs to one customer).
    • Movie to Booking: 1:N1:N (One movie can have multiple seat bookings; each booking is for one movie).

Answer 2.3: Car Rental and Subcontractor Maintenance System

a. ER Diagram

Loading diagram...

b. Relational Schema

-- 1. Subcontractors (Garages)
CREATE TABLE Subcontractor (
    garage_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    range_of_services VARCHAR(255),
    payment_terms VARCHAR(100)
);

-- 2. Cars (Serviced by Subcontractor)
CREATE TABLE Car (
    car_id INT PRIMARY KEY,
    garage_id INT NOT NULL,
    make VARCHAR(50),
    model VARCHAR(50),
    year INT,
    engine_size VARCHAR(20),
    fuel_type VARCHAR(20),
    passenger_no INT,
    registration_no VARCHAR(50) UNIQUE NOT NULL,
    purchase_date DATE,
    rent_price DECIMAL(10,2) NOT NULL,
    insurance_details VARCHAR(255),
    FOREIGN KEY (garage_id) REFERENCES Subcontractor(garage_id)
);

-- 3. Customers (Privileged / Casual modeled as attribute)
CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    phone_no VARCHAR(20) NOT NULL,
    driving_licence_no VARCHAR(50) UNIQUE NOT NULL,
    customer_type VARCHAR(20) CHECK (customer_type IN ('Privileged', 'Casual'))
);

-- 4. Reservations (Customer makes Reservation for Car)
CREATE TABLE Reservation (
    reservation_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    car_id INT NOT NULL,
    reservation_date DATE NOT NULL,
    duration INT NOT NULL,
    deposit_amount DECIMAL(10,2) DEFAULT 0.0,
    payment_mode VARCHAR(20) CHECK (payment_mode IN ('Credit Card', 'Cash')),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (car_id) REFERENCES Car(car_id)
);

-- 5. Company Expenditures (Related to Subcontractor)
CREATE TABLE Company_Expenditure (
    exp_id INT PRIMARY KEY,
    garage_id INT,
    exp_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    exp_type VARCHAR(30) CHECK (exp_type IN ('Purchase', 'Repair', 'Maintenance', 'Insurance')),
    FOREIGN KEY (garage_id) REFERENCES Subcontractor(garage_id)
);

-- 6. Company Revenue (Generated from Customer)
CREATE TABLE Company_Revenue (
    rev_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    rev_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    source VARCHAR(30) CHECK (source IN ('Car Hire', 'Car Sales', 'Insurance Claim')),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

c. Handling Customer Classification (Privileged vs. Casual)

  • Attribute Approach (Chosen): Add customer_type VARCHAR(20) CHECK (customer_type IN ('Privileged', 'Casual')) with a default of 'Casual'. Appropriate because both types share identical attributes (name, address, phone, license).
  • Specialization / Subtyping Alternative: If privileged customers have distinct attributes (e.g., discount_rate, credit_limit, loyalty_points), model a superclass Customer table with disjoint sub-tables Privileged_Customer(customer_id [PK, FK], discount_rate) and Casual_Customer(customer_id [PK, FK]).

Answer 2.4: Online Food Delivery System

a. ER Diagram

Loading diagram...

b. Relational Tables

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address VARCHAR(200)
);

CREATE TABLE Restaurant (
    restaurant_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    cuisine VARCHAR(50),
    address VARCHAR(200)
);

CREATE TABLE Rider (
    rider_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    vehicle_no VARCHAR(50) NOT NULL
);

CREATE TABLE Menu_Item (
    item_id INT PRIMARY KEY,
    restaurant_id INT NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    restaurant_id INT NOT NULL,
    rider_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id),
    FOREIGN KEY (rider_id) REFERENCES Rider(rider_id)
);

CREATE TABLE Order_Item (
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, item_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (item_id) REFERENCES Menu_Item(item_id)
);

Answer 2.5: Sports Tournament System

a. ER Diagram

Loading diagram...

b. Relational Tables

CREATE TABLE Team (
    team_id INT PRIMARY KEY,
    team_name VARCHAR(100) NOT NULL,
    coach VARCHAR(100)
);

CREATE TABLE Player (
    player_id INT PRIMARY KEY,
    team_id INT NOT NULL,
    player_name VARCHAR(100) NOT NULL,
    age INT CHECK (age > 0),
    role VARCHAR(50),
    FOREIGN KEY (team_id) REFERENCES Team(team_id)
);

CREATE TABLE Matches (
    match_id INT PRIMARY KEY,
    team1_id INT NOT NULL,
    team2_id INT NOT NULL,
    match_date DATE NOT NULL,
    venue VARCHAR(100) NOT NULL,
    FOREIGN KEY (team1_id) REFERENCES Team(team_id),
    FOREIGN KEY (team2_id) REFERENCES Team(team_id),
    CHECK (team1_id <> team2_id)
);

3. SQL Subqueries and Advanced Queries

Based on:

  • Employee(emp_id, emp_name, dept_id, salary, manager_id, join_date)
  • Department(dept_id, dept_name, location)
  • Project(project_id, project_name, budget)
  • Works_On(emp_id, project_id, hours)

1. Employees in projects with budget > 50,000 using IN

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
    )
);

2. Departments with employees earning > 55,000 using EXISTS

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
);

3. Employees not part of any project using NOT EXISTS

SELECT E.emp_id, E.emp_name
FROM Employee E
WHERE NOT EXISTS (
    SELECT 1
    FROM Works_On W
    WHERE W.emp_id = E.emp_id
);

4. Department name of employee with highest salary (Scalar Subquery)

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);

5. Salary > overall average salary

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

6. Employees in same department as 'Bob' using IN

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

7. Projects handled by employees from 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'
        )
    )
);

8. Employees joined after earliest IT joining date using > ALL

SELECT emp_id, emp_name, join_date
FROM Employee
WHERE join_date > ALL (
    SELECT MIN(join_date)
    FROM Employee E
    JOIN Department D ON E.dept_id = D.dept_id
    WHERE D.dept_name = 'IT'
);

9. Employees not working on projects with budget below 60,000 using NOT IN

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

10. Departments with no projects assigned using NOT EXISTS

SELECT D.dept_id, D.dept_name
FROM Department D
WHERE NOT EXISTS (
    SELECT 1
    FROM Employee E
    JOIN Works_On W ON E.emp_id = W.emp_id
    WHERE E.dept_id = D.dept_id
);

11. Employees involved in all projects handled by IT department using ALL / Division

SELECT E.emp_id, E.emp_name
FROM Employee E
WHERE NOT EXISTS (
    SELECT P.project_id
    FROM Project P
    WHERE P.project_id IN (
        SELECT W_IT.project_id
        FROM Works_On W_IT
        JOIN Employee E_IT ON W_IT.emp_id = E_IT.emp_id
        JOIN Department D_IT ON E_IT.dept_id = D_IT.dept_id
        WHERE D_IT.dept_name = 'IT'
    )
    AND NOT EXISTS (
        SELECT 1
        FROM Works_On W
        WHERE W.emp_id = E.emp_id
          AND W.project_id = P.project_id
    )
);

12. Employees working on projects that Marketing employees work on using SOME

SELECT DISTINCT emp_id, emp_name
FROM Employee
WHERE emp_id IN (
    SELECT emp_id
    FROM Works_On
    WHERE project_id = SOME (
        SELECT W.project_id
        FROM Works_On W
        JOIN Employee E ON W.emp_id = E.emp_id
        JOIN Department D ON E.dept_id = D.dept_id
        WHERE D.dept_name = 'Marketing'
    )
);

13. Salary greater than salary of any employee in HR using > ANY

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

14. Common Table Expression (WITH) for total project budget by department

WITH Dept_Project_Budget AS (
    SELECT
        E.dept_id,
        SUM(DISTINCT P.budget) AS total_project_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
    GROUP BY E.dept_id
)
SELECT
    D.dept_name,
    COALESCE(B.total_project_budget, 0) AS total_budget
FROM Department D
LEFT JOIN Dept_Project_Budget B ON D.dept_id = B.dept_id;

15. Employees on projects with highest or lowest budget using IN

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 IN (
    SELECT project_id
    FROM Project
    WHERE budget = (SELECT MAX(budget) FROM Project)
       OR budget = (SELECT MIN(budget) FROM Project)
);

4. Relational Database Normalization

Answer 4.1: Student-Course-Instructor Worksheet

a. Functional Dependencies

  • StudentID →\rightarrow StudentName, Dept
  • CourseCode →\rightarrow CourseName, Instructor
  • Instructor →\rightarrow InstructorPhone
  • Primary Key of initial unnormalized table: (StudentID, CourseCode)

b. Step-by-Step Normalization

  1. 1NF: Table is already in 1NF as all values are atomic.
  2. 2NF (Eliminate Partial Dependencies):
    • Partial dependencies exist: StudentName and Dept depend only on StudentID. CourseName and Instructor depend only on CourseCode.
    • Decompose into:
      • Student(StudentID, StudentName, Dept) — PK: StudentID
      • Course(CourseCode, CourseName, Instructor, InstructorPhone) — PK: CourseCode
      • Student_Course(StudentID, CourseCode) — PK: (StudentID, CourseCode)
  3. 3NF (Eliminate Transitive Dependencies):
    • In Course, CourseCode →\rightarrow Instructor and Instructor →\rightarrow InstructorPhone. Transitive dependency on InstructorPhone.
    • Final 3NF Relations:
      • Student (StudentID [PK], StudentName, Dept)
      • Instructor_Details (Instructor [PK], InstructorPhone)
      • Course (CourseCode [PK], CourseName, Instructor [FK])
      • Enrollment (StudentID [FK], CourseCode [FK]) — PK: (StudentID, CourseCode)
Loading diagram...

Answer 4.2: Sales Orders Worksheet

Table: (OrderID, CustomerName, CustomerPhone, ProductName, Quantity, Price)
Primary Key: (OrderID, ProductName)

  • Dependencies:
    • OrderID →\rightarrow CustomerName, CustomerPhone (Partial dependency)
    • CustomerName →\rightarrow CustomerPhone (Transitive dependency)
    • ProductName →\rightarrow Price (Partial dependency)
    • (OrderID, ProductName) →\rightarrow Quantity (Full dependency)
  • 3NF Decomposition:
    1. Customer (CustomerName [PK], CustomerPhone)
    2. Orders (OrderID [PK], CustomerName [FK])
    3. Product (ProductName [PK], Price)
    4. Order_Item (OrderID [FK], ProductName [FK], Quantity) — PK: (OrderID, ProductName)
Loading diagram...

Answer 4.3: Employee-Project-Location Worksheet

Table: (EmpID, EmpName, ProjID, ProjName, ProjLocation, DeptLocation)
Primary Key: (EmpID, ProjID)

  • Dependencies:
    • EmpID →\rightarrow EmpName, DeptLocation (Partial dependency)
    • ProjID →\rightarrow ProjName, ProjLocation (Partial dependency)
    • (EmpID, ProjID) connects employee to projects.
  • 3NF Decomposition:
    1. Employee (EmpID [PK], EmpName, DeptLocation)
    2. Project (ProjID [PK], ProjName, ProjLocation)
    3. Emp_Project (EmpID [FK], ProjID [FK]) — PK: (EmpID, ProjID)
Loading diagram...

Answer 4.4: Library Book Issue Worksheet

Table: (IssueID, StudentID, StudentName, BookID, BookTitle, Author, AuthorEmail)
Candidate Key: IssueID

  • Dependencies:
    • IssueID →\rightarrow StudentID, BookID
    • StudentID →\rightarrow StudentName (Transitive)
    • BookID →\rightarrow BookTitle, Author (Transitive)
    • Author →\rightarrow AuthorEmail (Transitive)
  • 3NF Decomposition:
    1. Student (StudentID [PK], StudentName)
    2. Author_Info (Author [PK], AuthorEmail)
    3. Book (BookID [PK], BookTitle, Author [FK])
    4. Book_Issue (IssueID [PK], StudentID [FK], BookID [FK])
Loading diagram...

Answer 4.5: Customer-Order-Supplier Worksheet

Table: (OrderID, CustomerID, CustomerName, ProductID, ProductName, SupplierName, SupplierContact)
Primary Key: (OrderID, ProductID)

  • Dependencies:
    • OrderID →\rightarrow CustomerID (Partial)
    • CustomerID →\rightarrow CustomerName (Transitive)
    • ProductID →\rightarrow ProductName, SupplierName (Partial)
    • SupplierName →\rightarrow SupplierContact (Transitive)
  • 3NF Decomposition:
    1. Customer (CustomerID [PK], CustomerName)
    2. Orders (OrderID [PK], CustomerID [FK])
    3. Supplier (SupplierName [PK], SupplierContact)
    4. Product (ProductID [PK], ProductName, SupplierName [FK])
    5. Order_Line (OrderID [FK], ProductID [FK]) — PK: (OrderID, ProductID)
Loading diagram...

5. SQL & PL/SQL on Students and Courses

5.1 SQL Queries & Expected Results

-- 1. All student names and departments
SELECT name, department FROM Students;

-- 2. Names of students older than 20
SELECT name FROM Students WHERE age > 20;

-- 3. Students with marks between 60 and 80
SELECT name, marks FROM Students WHERE marks BETWEEN 60 AND 80;

-- 4. Students whose name starts with 'A'
SELECT name FROM Students WHERE name LIKE 'A%';

-- 5. Students in 'CSE' or 'EEE' using IN
SELECT name, department FROM Students WHERE department IN ('CSE', 'EEE');

-- 6. Students with NULL marks
SELECT student_id, name FROM Students WHERE marks IS NULL;

-- 7. Students sorted by marks descending
SELECT name, marks FROM Students ORDER BY marks DESC NULLS LAST;

-- 8. Average marks of all students (ignoring NULL)
SELECT ROUND(AVG(marks), 2) AS avg_marks FROM Students;

-- 9. Department-wise average marks
SELECT department, ROUND(AVG(marks), 2) AS dept_avg
FROM Students
WHERE marks IS NOT NULL
GROUP BY department;

-- 10. Student ID and total courses taken
SELECT student_id, COUNT(course_id) AS total_courses
FROM Courses
GROUP BY student_id;

-- 11. Departments having more than 2 students
SELECT department, COUNT(*) AS student_count
FROM Students
GROUP BY department
HAVING COUNT(*) > 2;

-- 12. Students 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;

-- 13. Students who got grade 'A' in any course
SELECT DISTINCT S.name
FROM Students S
JOIN Courses C ON S.student_id = C.student_id
WHERE C.grade = 'A';

-- 14. 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;

-- 15. Students whose marks are above the average
SELECT name, marks
FROM Students
WHERE marks > (SELECT AVG(marks) FROM Students);

-- 16. Second-highest marks from STUDENTS
SELECT MAX(marks) AS second_highest
FROM Students
WHERE marks < (SELECT MAX(marks) FROM Students);

-- 17. Marks greater than all students in EEE department
SELECT name, marks
FROM Students
WHERE marks > ALL (
    SELECT marks
    FROM Students
    WHERE department = 'EEE' AND marks IS NOT NULL
);

5.2 PL/SQL Anonymous Blocks

Block 1: Display student name and marks for student_id = 5

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;
/

Block 2: Display all student names using a loop

DECLARE
    CURSOR c_students IS SELECT name FROM Students;
BEGIN
    FOR r_stu IN c_students LOOP
        DBMS_OUTPUT.PUT_LINE('Student Name: ' || r_stu.name);
    END LOOP;
END;
/

Block 3: Calculate and display average marks

DECLARE
    v_avg NUMBER(5, 2);
BEGIN
    SELECT AVG(marks) INTO v_avg FROM Students;
    DBMS_OUTPUT.PUT_LINE('Average Marks: ' || v_avg);
END;
/

Block 4: Check if marks > 80 ("Excellent" vs "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;
/

Block 5: Increase 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;
/

Block 6: Display department of student 'Rahim'

DECLARE
    v_dept Students.department%TYPE;
BEGIN
    SELECT department INTO v_dept
    FROM Students
    WHERE name = 'Rahim';

    DBMS_OUTPUT.PUT_LINE('Rahim belongs to department: ' || v_dept);
END;
/

5.3 Explicit Cursors

Cursor 1: All student names and marks

DECLARE
    CURSOR c_stu IS SELECT name, marks FROM Students;
    v_name  Students.name%TYPE;
    v_marks Students.marks%TYPE;
BEGIN
    OPEN c_stu;
    LOOP
        FETCH c_stu INTO v_name, v_marks;
        EXIT WHEN c_stu%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Marks: ' || NVL(TO_CHAR(v_marks), 'N/A'));
    END LOOP;
    CLOSE c_stu;
END;
/

Cursor 2: Students with marks > 70

DECLARE
    CURSOR c_high IS
        SELECT name, marks FROM Students WHERE marks > 70;
BEGIN
    FOR r IN c_high LOOP
        DBMS_OUTPUT.PUT_LINE('High Achiever: ' || r.name || ' (' || r.marks || ')');
    END LOOP;
END;
/

Cursor 3: Student ID and total courses taken

DECLARE
    CURSOR c_course_count IS
        SELECT student_id, COUNT(*) AS course_cnt
        FROM Courses
        GROUP BY student_id;
BEGIN
    FOR r IN c_course_count LOOP
        DBMS_OUTPUT.PUT_LINE('Student ID: ' || r.student_id || ', Courses: ' || r.course_cnt);
    END LOOP;
END;
/

Cursor 4: Students with NULL marks

DECLARE
    CURSOR c_null_marks IS
        SELECT student_id, name FROM Students WHERE marks IS NULL;
BEGIN
    FOR r IN c_null_marks LOOP
        DBMS_OUTPUT.PUT_LINE('Needs Marks Entry - ID: ' || r.student_id || ', Name: ' || r.name);
    END LOOP;
END;
/

Cursor 5: Course names of student_id = 1

DECLARE
    CURSOR c_stu1_courses IS
        SELECT course_name, grade FROM Courses WHERE student_id = 1;
BEGIN
    FOR r IN c_stu1_courses LOOP
        DBMS_OUTPUT.PUT_LINE('Course: ' || r.course_name || ' (Grade: ' || r.grade || ')');
    END LOOP;
END;
/

5.4 Stored Functions and Procedures

Function 1: Return marks by student_id

CREATE OR REPLACE FUNCTION Get_Student_Marks (p_id IN NUMBER)
RETURN NUMBER
AS
    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;
/

Function 2: Calculate letter grade based on marks

CREATE OR REPLACE FUNCTION Calculate_Grade (p_marks IN NUMBER)
RETURN VARCHAR2
AS
BEGIN
    IF p_marks IS NULL THEN
        RETURN 'Incomplete';
    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;
/

Procedure 1: Add 5 marks to a student

CREATE OR REPLACE PROCEDURE Add_Five_Marks (p_id IN NUMBER)
AS
BEGIN
    UPDATE Students
    SET marks = marks + 5
    WHERE student_id = p_id;
    COMMIT;
END;
/

Procedure 2: Display CSE department students

CREATE OR REPLACE PROCEDURE Show_CSE_Students
AS
BEGIN
    FOR r IN (SELECT student_id, name, marks FROM Students WHERE department = 'CSE') LOOP
        DBMS_OUTPUT.PUT_LINE(r.student_id || ': ' || r.name || ' - ' || r.marks);
    END LOOP;
END;
/

5.5 Database Triggers

Trigger 1: Prevent deletion of any student

CREATE OR REPLACE TRIGGER Trg_Prevent_Student_Delete
BEFORE DELETE ON Students
FOR EACH ROW
BEGIN
    RAISE_APPLICATION_ERROR(-20005, 'Deletion of student records is strictly prohibited.');
END;
/

Trigger 2: Prevent insertion if marks > 100

CREATE OR REPLACE TRIGGER Trg_Check_Max_Marks
BEFORE INSERT OR UPDATE OF marks ON Students
FOR EACH ROW
BEGIN
    IF :NEW.marks > 100 THEN
        RAISE_APPLICATION_ERROR(-20006, 'Marks cannot exceed 100.');
    END IF;
END;
/

Trigger 3: Prevent insertion if age < 18

CREATE OR REPLACE TRIGGER Trg_Check_Student_Age
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
    IF :NEW.age < 18 THEN
        RAISE_APPLICATION_ERROR(-20007, 'Student must be at least 18 years old.');
    END IF;
END;
/

Trigger 4: Default department to 'Unknown' if NULL

CREATE OR REPLACE TRIGGER Trg_Default_Department
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
    IF :NEW.department IS NULL THEN
        :NEW.department := 'Unknown';
    END IF;
END;
/

Trigger 5: Log student mark updates to audit table

-- Audit table
CREATE TABLE Student_Marks_Audit (
    audit_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  NUMBER,
    old_marks   NUMBER,
    new_marks   NUMBER,
    modified_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    modified_by VARCHAR2(50) DEFAULT USER
);

-- Trigger
CREATE OR REPLACE TRIGGER Trg_Audit_Marks_Update
AFTER UPDATE OF marks ON Students
FOR EACH ROW
BEGIN
    INSERT INTO Student_Marks_Audit (student_id, old_marks, new_marks)
    VALUES (:OLD.student_id, :OLD.marks, :NEW.marks);
END;
/

On this page

1. Transaction Management and ACID PropertiesAnswer 1.1: Fund Transfer System CrashAnswer 1.2: Inventory Stock Update FailureAnswer 1.3: Concurrent Course RegistrationAnswer 1.4: Ride-Booking Confirmation and Server CrashAnswer 1.5: Loan Fee Deduction FailureAnswer 1.6: Concurrent Railway Ticket BookingAnswer 1.7: Hospital Treatment Billing FailureAnswer 1.8: Ride-Sharing Driver Assignment Update2. Entity-Relationship (E-R) Modeling and Relational MappingAnswer 2.1: Gym Management System (Schema to ERD)a. Primary and Foreign Keysb. Cardinality Ratiosc. ERD Structure (Mermaid)Answer 2.2: Movie Theater Booking SystemAnswer 2.3: Car Rental and Subcontractor Maintenance Systema. ER Diagramb. Relational Schemac. Handling Customer Classification (Privileged vs. Casual)Answer 2.4: Online Food Delivery Systema. ER Diagramb. Relational TablesAnswer 2.5: Sports Tournament Systema. ER Diagramb. Relational Tables3. SQL Subqueries and Advanced Queries1. Employees in projects with budget > 50,000 using IN2. Departments with employees earning > 55,000 using EXISTS3. Employees not part of any project using NOT EXISTS4. Department name of employee with highest salary (Scalar Subquery)5. Salary > overall average salary6. Employees in same department as 'Bob' using IN7. Projects handled by employees from HR department8. Employees joined after earliest IT joining date using > ALL9. Employees not working on projects with budget below 60,000 using NOT IN10. Departments with no projects assigned using NOT EXISTS11. Employees involved in all projects handled by IT department using ALL / Division12. Employees working on projects that Marketing employees work on using SOME13. Salary greater than salary of any employee in HR using > ANY14. Common Table Expression (WITH) for total project budget by department15. Employees on projects with highest or lowest budget using IN4. Relational Database NormalizationAnswer 4.1: Student-Course-Instructor Worksheeta. Functional Dependenciesb. Step-by-Step NormalizationAnswer 4.2: Sales Orders WorksheetAnswer 4.3: Employee-Project-Location WorksheetAnswer 4.4: Library Book Issue WorksheetAnswer 4.5: Customer-Order-Supplier Worksheet5. SQL & PL/SQL on Students and Courses5.1 SQL Queries & Expected Results5.2 PL/SQL Anonymous BlocksBlock 1: Display student name and marks for student_id = 5Block 2: Display all student names using a loopBlock 3: Calculate and display average marksBlock 4: Check if marks > 80 ("Excellent" vs "Keep Trying")Block 5: Increase marks of student_id = 3 by 10Block 6: Display department of student 'Rahim'5.3 Explicit CursorsCursor 1: All student names and marksCursor 2: Students with marks > 70Cursor 3: Student ID and total courses takenCursor 4: Students with NULL marksCursor 5: Course names of student_id = 15.4 Stored Functions and ProceduresFunction 1: Return marks by student_idFunction 2: Calculate letter grade based on marksProcedure 1: Add 5 marks to a studentProcedure 2: Display CSE department students5.5 Database TriggersTrigger 1: Prevent deletion of any studentTrigger 2: Prevent insertion if marks > 100Trigger 3: Prevent insertion if age < 18Trigger 4: Default department to 'Unknown' if NULLTrigger 5: Log student mark updates to audit table