Set-1

SET-1

1.ER Diagram for Online Book Store:

	Entities: User, Book, Author, Publisher, Order, Payment, Review
	Relationships:
	User buys Book (one-to-many)
	Book is written by Author (many-to-many)
	Book is published by Publisher (many-to-one)
	Order contains Book (one-to-many)
	Payment is related to Order (one-to-one)
	User writes Review for Book (one-to-many)
2.PL/SQL Function to Calculate Factorial:


CREATE OR REPLACE FUNCTION factorial(n IN NUMBER) RETURN NUMBER IS
    result NUMBER := 1;
BEGIN
    FOR i IN 1..n LOOP
        result := result * i;
    END LOOP;
    RETURN result;
END;
3.SQL Queries:
A. SELECT * FROM Nurses WHERE registered = 'Y';

B. ALTER TABLE Nurses ADD phone VARCHAR2(20);

C. UPDATE Nurses SET registered = 'N' WHERE name = 'Laverne Roberts';

D. CREATE VIEW NursePositions AS SELECT name, position FROM Nurses;

E. SELECT name, position FROM Nurses UNION ALL SELECT name, position FROM Physicians;

F. SELECT D.name AS department_name, E.name AS department_head FROM Departments D JOIN Employees E ON D.head_id = E.id;

G. SELECT N.name AS nurse_name, D.name AS department_name FROM Nurses N JOIN Departments D ON N.department_id = D.id;

H. DELETE FROM Departments WHERE id = 2;