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 facilityid INT PRIMARY KEY, name VARCHAR NOT NULL, address VARCHAR NOT NULL, risklevel VARCHAR NOT NULL ; Create Employee table CREATE TABLE Employee employeeid INT PRIMARY KEY, name VARCHAR NOT NULL, title VARCHAR NOT NULL, salary NUMBER NOT NULL ; Create Inspection table CREATE TABLE Inspection inspectionid INT PRIMARY KEY, facilityid INT NOT NULL, employeeid INT NOT NULL, inspectiondate DATE NOT NULL, results VARCHAR NOT NULL, FOREIGN KEY facilityid REFERENCES Facilityfacilityid FOREIGN KEY employeeid REFERENCES Employeeemployeeid; Insert data into Facility table INSERT INTO Facility facilityid name, address, risklevel VALUES 'ABC Restaurant', Main St 'High'; INSERT INTO Facility facilityid name, address, risklevel VALUES XYZ Cafe', Elm St 'Medium'; INSERT INTO Facility facilityid name, address, risklevel VALUES LMN Bakery', Oak St 'Low'; INSERT INTO Facility facilityid name, address, risklevel VALUES PQR Deli', Pine St 'High'; Insert data into Employee table INSERT INTO Employee employeeid name, title, salary VALUES 'John Doe', 'Inspector', ; INSERT INTO Employee employeeid name, title, salary VALUES 'Jane Smith', 'Senior Inspector', ; INSERT INTO Employee employeeid name, title, salary VALUES 'Alice Johnson', 'Inspector', ; INSERT INTO Employee employeeid name, title, salary VALUES 'Bob Brown', 'Inspector', ; Insert data into Inspection table INSERT INTO Inspection inspectionid facilityid employeeid inspectiondate, results VALUES TODATEYYYYMMDD 'Pass'; INSERT INTO Inspection inspectionid facilityid employeeid inspectiondate, results VALUES TODATEYYYYMMDD 'Fail'; INSERT INTO Inspection inspectionid facilityid employeeid inspectiondate, results VALUES TODATEYYYYMMDD 'Pass'; INSERT INTO Inspection inspectionid facilityid employeeid inspectiondate, results VALUES TODATEYYYYMMDD '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.inspectionid Facility.name AS facilityname, Employee.name AS inspectorname, Inspection.inspectiondate, Inspection.results FROM Inspection JOIN Facility ON Inspection.facilityid Facility.facilityid JOIN Employee ON Inspection.employeeid Employee.employeeid;
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
