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.
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.
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.
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.
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.
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.
Impact: The hospital records patient treatment as completed, but accounts receivable reflects $0 for the visit, creating financial auditing holes and uncollected service revenues.
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.
-- 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));
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]).
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));
SELECT emp_id, emp_nameFROM EmployeeWHERE emp_id IN ( SELECT emp_id FROM Works_On WHERE project_id IN ( SELECT project_id FROM Project WHERE budget > 50000 ));
SELECT ( SELECT D.dept_name FROM Department D WHERE D.dept_id = E.dept_id) AS department_nameFROM Employee EWHERE E.salary = (SELECT MAX(salary) FROM Employee);
SELECT project_id, project_nameFROM ProjectWHERE 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' ) ));
SELECT emp_id, emp_name, join_dateFROM EmployeeWHERE 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');
SELECT emp_id, emp_nameFROM EmployeeWHERE 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);
SELECT D.dept_id, D.dept_nameFROM Department DWHERE 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);
SELECT E.emp_id, E.emp_nameFROM Employee EWHERE 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 ));
SELECT DISTINCT emp_id, emp_nameFROM EmployeeWHERE 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' ));
SELECT emp_id, emp_name, salaryFROM EmployeeWHERE salary > ANY ( SELECT salary FROM Employee E JOIN Department D ON E.dept_id = D.dept_id WHERE D.dept_name = 'HR');
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_budgetFROM Department DLEFT JOIN Dept_Project_Budget B ON D.dept_id = B.dept_id;
SELECT DISTINCT E.emp_id, E.emp_nameFROM Employee EJOIN Works_On W ON E.emp_id = W.emp_idWHERE W.project_id IN ( SELECT project_id FROM Project WHERE budget = (SELECT MAX(budget) FROM Project) OR budget = (SELECT MIN(budget) FROM Project));
-- 1. All student names and departmentsSELECT name, department FROM Students;-- 2. Names of students older than 20SELECT name FROM Students WHERE age > 20;-- 3. Students with marks between 60 and 80SELECT 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 INSELECT name, department FROM Students WHERE department IN ('CSE', 'EEE');-- 6. Students with NULL marksSELECT student_id, name FROM Students WHERE marks IS NULL;-- 7. Students sorted by marks descendingSELECT 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 marksSELECT department, ROUND(AVG(marks), 2) AS dept_avgFROM StudentsWHERE marks IS NOT NULLGROUP BY department;-- 10. Student ID and total courses takenSELECT student_id, COUNT(course_id) AS total_coursesFROM CoursesGROUP BY student_id;-- 11. Departments having more than 2 studentsSELECT department, COUNT(*) AS student_countFROM StudentsGROUP BY departmentHAVING COUNT(*) > 2;-- 12. Students not enrolled in any course (LEFT JOIN)SELECT S.student_id, S.nameFROM Students SLEFT JOIN Courses C ON S.student_id = C.student_idWHERE C.course_id IS NULL;-- 13. Students who got grade 'A' in any courseSELECT DISTINCT S.nameFROM Students SJOIN Courses C ON S.student_id = C.student_idWHERE C.grade = 'A';-- 14. Courses with no student enrolled (RIGHT JOIN)SELECT C.course_id, C.course_nameFROM Students SRIGHT JOIN Courses C ON S.student_id = C.student_idWHERE S.student_id IS NULL;-- 15. Students whose marks are above the averageSELECT name, marksFROM StudentsWHERE marks > (SELECT AVG(marks) FROM Students);-- 16. Second-highest marks from STUDENTSSELECT MAX(marks) AS second_highestFROM StudentsWHERE marks < (SELECT MAX(marks) FROM Students);-- 17. Marks greater than all students in EEE departmentSELECT name, marksFROM StudentsWHERE marks > ALL ( SELECT marks FROM Students WHERE department = 'EEE' AND marks IS NOT NULL);
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;/
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;/
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;/
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;/
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;/
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;/
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;/
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;/
CREATE OR REPLACE FUNCTION Get_Student_Marks (p_id IN NUMBER)RETURN NUMBERAS 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;/
CREATE OR REPLACE FUNCTION Calculate_Grade (p_marks IN NUMBER)RETURN VARCHAR2ASBEGIN 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;/
CREATE OR REPLACE PROCEDURE Show_CSE_StudentsASBEGIN 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;/
CREATE OR REPLACE TRIGGER Trg_Prevent_Student_DeleteBEFORE DELETE ON StudentsFOR EACH ROWBEGIN RAISE_APPLICATION_ERROR(-20005, 'Deletion of student records is strictly prohibited.');END;/
CREATE OR REPLACE TRIGGER Trg_Check_Max_MarksBEFORE INSERT OR UPDATE OF marks ON StudentsFOR EACH ROWBEGIN IF :NEW.marks > 100 THEN RAISE_APPLICATION_ERROR(-20006, 'Marks cannot exceed 100.'); END IF;END;/
CREATE OR REPLACE TRIGGER Trg_Check_Student_AgeBEFORE INSERT ON StudentsFOR EACH ROWBEGIN IF :NEW.age < 18 THEN RAISE_APPLICATION_ERROR(-20007, 'Student must be at least 18 years old.'); END IF;END;/
CREATE OR REPLACE TRIGGER Trg_Default_DepartmentBEFORE INSERT ON StudentsFOR EACH ROWBEGIN IF :NEW.department IS NULL THEN :NEW.department := 'Unknown'; END IF;END;/