Set-9

1) ER Diagram for Railway Reservation System:
Entities: Train, Passenger, Ticket, Route, Station, Reservation, Payment
Relationships:

Passenger books Ticket (one-to-many)
Ticket is for Train (many-to-one)
Ticket is for Passenger (many-to-one)
Train travels on Route (many-to-many)
Route includes Station (one-to-many)
Reservation is related to Ticket (one-to-one)
Payment is related to Ticket (one-to-one)

2) PL/SQL Program to Calculate Sum of Digits of any Number:

CREATE OR REPLACE PROCEDURE calculate_digit_sum(
    num IN NUMBER,
    sum OUT NUMBER
) AS
    total_sum NUMBER := 0;
BEGIN
    WHILE num != 0 LOOP
        total_sum := total_sum + MOD(num, 10);
        num := num / 10;
    END LOOP;
    sum := total_sum;
END;


3) SQL Queries:
A
SELECT * FROM Employees WHERE Department = 'IT';
B)
UPDATE Employees SET Salary = NEW_SALARY WHERE EmployeeID = 3;
C)
SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
D)
SELECT * FROM Employees WHERE Department = 'IT' AND Salary > 70000;
E)
SELECT SUM(Budget) AS Total_Budget FROM Projects WHERE Department = 'Marketing';
F)
SELECT * FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees);
G)
SELECT E.EmployeeID, E.EmployeeName, P.ProjectName
FROM Employees E
JOIN Project_Assignment PA ON E.EmployeeID = PA.EmployeeID
JOIN Projects P ON PA.ProjectID = P.ProjectID;
H)
SELECT Department, COUNT(EmployeeID) AS Total_Employees
FROM Employees
GROUP BY Department;