Set-10

1) ER Diagram for Online Medical Store:
Entities: User, Product, Order, Prescription, Pharmacy, Payment, Review
Relationships:

User places Order (one-to-many)
Order contains Product (many-to-many)
Prescription is related to User (many-to-one)
Product is sold by Pharmacy (many-to-one)
Payment is related to Order (one-to-one)
User writes Review for Product (one-to-many)

2) Trigger to Update Project Budget When Employee Salary is Increased:

CREATE OR REPLACE TRIGGER update_project_budget
AFTER UPDATE OF salary ON Employees
FOR EACH ROW
BEGIN
    UPDATE Projects
    SET budget = budget + (:new.salary - :old.salary)
    WHERE manager_id = :new.employee_id;
END;


3) SQL Queries:
A)
SELECT Department, MAX(Budget) AS Highest_Budget
FROM Projects
GROUP BY Department;
B)
SELECT SUM(Budget) AS Total_Budget FROM Projects;
C)
CREATE VIEW HighSalaryEmployees AS
SELECT * FROM Employees WHERE Salary > 80000;
D)
SELECT * FROM Employees WHERE Salary BETWEEN 60000 AND 80000;
E)
DELETE FROM Employees WHERE Salary < 60000;
F)
SELECT * FROM Projects WHERE Budget > (SELECT AVG(Budget) FROM Projects);
G)
SELECT * FROM Projects WHERE ProjectName LIKE 'Web%' ORDER BY Budget;
H)
SELECT Department, SUM(Budget) AS Total_Budget
FROM Projects
GROUP BY Department
ORDER BY Total_Budget;