Question: Create a Entity Relationship Diagram from the following SQL Querry: - - Drop existing tables ( Oracle does not support IF EXISTS ) BEGIN EXECUTE

Create a Entity Relationship Diagram from the following SQL Querry:
-- Drop existing tables (Oracle does not support IF EXISTS) BEGIN EXECUTE IMMEDIATE 'DROP TABLE Inspection'; EXCEPTION WHEN OTHERS THEN NULL; -- Ignore errors if table does not exist END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE Employee'; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE Facility'; EXCEPTION WHEN OTHERS THEN NULL; END; /-- Create Facility table CREATE TABLE Facility ( facility_id INT PRIMARY KEY, name VARCHAR2(50) NOT NULL, address VARCHAR2(100) NOT NULL, risk_level VARCHAR2(20) NOT NULL ); -- Create Employee table CREATE TABLE Employee ( employee_id INT PRIMARY KEY, name VARCHAR2(50) NOT NULL, title VARCHAR2(50) NOT NULL, salary NUMBER(10,2) NOT NULL ); -- Create Inspection table CREATE TABLE Inspection ( inspection_id INT PRIMARY KEY, facility_id INT NOT NULL, employee_id INT NOT NULL, inspection_date DATE NOT NULL, results VARCHAR2(50) NOT NULL, FOREIGN KEY (facility_id) REFERENCES Facility(facility_id), FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)); -- Insert data into Facility table INSERT INTO Facility (facility_id, name, address, risk_level) VALUES (1, 'ABC Restaurant', '123 Main St', 'High'); INSERT INTO Facility (facility_id, name, address, risk_level) VALUES (2,'XYZ Cafe', '456 Elm St', 'Medium'); INSERT INTO Facility (facility_id, name, address, risk_level) VALUES (3,'LMN Bakery', '789 Oak St', 'Low'); INSERT INTO Facility (facility_id, name, address, risk_level) VALUES (4,'PQR Deli', '101 Pine St', 'High'); -- Insert data into Employee table INSERT INTO Employee (employee_id, name, title, salary) VALUES (1, 'John Doe', 'Inspector', 60000.00); INSERT INTO Employee (employee_id, name, title, salary) VALUES (2, 'Jane Smith', 'Senior Inspector', 75000.00); INSERT INTO Employee (employee_id, name, title, salary) VALUES (3, 'Alice Johnson', 'Inspector', 62000.00); INSERT INTO Employee (employee_id, name, title, salary) VALUES (4, 'Bob Brown', 'Inspector', 58000.00); -- Insert data into Inspection table INSERT INTO Inspection (inspection_id, facility_id, employee_id, inspection_date, results) VALUES (1,1,1, TO_DATE('2024-12-01','YYYY-MM-DD'), 'Pass'); INSERT INTO Inspection (inspection_id, facility_id, employee_id, inspection_date, results) VALUES (2,2,2, TO_DATE('2024-12-02','YYYY-MM-DD'), 'Fail'); INSERT INTO Inspection (inspection_id, facility_id, employee_id, inspection_date, results) VALUES (3,3,3, TO_DATE('2024-12-03','YYYY-MM-DD'), 'Pass'); INSERT INTO Inspection (inspection_id, facility_id, employee_id, inspection_date, results) VALUES (4,4,4, TO_DATE('2024-12-04','YYYY-MM-DD'), 'Pass'); -- Query all data from tables SELECT * FROM Facility; SELECT * FROM Employee; SELECT * FROM Inspection; -- Create a view to combine Inspection, Facility, and Employee data CREATE OR REPLACE VIEW InspectionView AS SELECT Inspection.inspection_id, Facility.name AS facility_name, Employee.name AS inspector_name, Inspection.inspection_date, Inspection.results FROM Inspection JOIN Facility ON Inspection.facility_id = Facility.facility_id JOIN Employee ON Inspection.employee_id = Employee.employee_id;

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!