Set-3

1) ER Diagram for Online Auction System:

Entities: User, Item, Bid, Auction, Payment, Category
Relationships:
User places Bid (one-to-many)
Item is listed in Auction (many-to-one)
User makes Payment (one-to-one)
Item belongs to Category (many-to-many)

2) PL/SQL Program to Find Area of Circle:

CREATE OR REPLACE PROCEDURE calculate_circle_area(
    radius IN NUMBER,
    area OUT NUMBER
) AS
BEGIN
    area := 3.14 * radius * radius;
END;


3) SQL Queries:
a) UPDATE Physicians SET position = 'Senior Staff Internist' WHERE name = 'John Dorian';

b) CREATE VIEW NonInternPhysicians AS SELECT name, position FROM Physicians WHERE position <> 'Intern';

c) SELECT * FROM Physicians WHERE position = 'Attending Physician';

d) SELECT P.name AS physician_name, D.name AS department_name FROM Physicians P JOIN Departments D ON P.department_id = D.id;

e) SELECT name FROM Physicians WHERE position LIKE '%Physician%';

f) SELECT N.name FROM Nurses N JOIN Departments D ON N.department_id = D.id WHERE N.registered = 'Y' AND D.name = 'Surgery';

g) SELECT D.name AS department_name, COUNT(*) AS total_physicians FROM Physicians P JOIN Departments D ON P.department_id = D.id GROUP BY D.name;
                                           
h) DELETE FROM Nurses WHERE registered = 'N';