Implement the tables from the COMPANY database schema shown in the pdf that acco
ID: 3683500 • Letter: I
Question
Implement the tables from the COMPANY database schema shown in the pdf
that accompanies this one in your DBMS, including data values. Then create the
following database elements:
1. A view that has the full supervisor name, department name, employee name
and employee salary in department number sequence
2. A trigger that generates a violation entry in a NEW TABLE (of your design)
when a new employee receives a lower salary than ALL other employees in
the department
2 Submission Instructions
You will be evaluated on:
1. The domain format and value constraints, along with the Foreign keys you
implement for the attributes in each table (10 points)
2. The View you build and execute meeting the above given requirements (20
points)
3. The Feasibility, Flexibility, and Appropriateness of the trigger you create
for the designated INSERT statement (20 points)
1
Export the CREATE SQL for the resulting tables, view, and trigger. Submit
the resulting file (concatenate all CREATE files if you export more than one, they
are text files, into one file for submission) using the following naming convention:
lastname:firstname:Exercise08:sql
Capture the data output from the VIEW and submit the resulting file using the
following naming convention:
lastname:firstname:Exercise08V iew:txt
Capture the data output in your NEW TABLE from the INSERT Statement for a
new Employee tuple which activates the TRIGGER and submit the resulting file
using the following naming convention:
lastname:firstname:Exercise08Trigger:txt
Explanation / Answer
Hi, your PDF isn't available. I have assumed certain table entries and have populated the value names on my own. It would be helpful if you could specify the location of the PDF. Thanks :)
Q1.
create view Q1_View( LastName, FirstName,SuperVisorName,empSal)
as select E.LastName, E.FirstName,S.FirstName, S.LastName, E.empSal
from EMPLOYEE E S, DEPARTMENT
where DNUMBER = E.DNO and E.SUPERSSN = S.SSN;
GROUPBY DNUMBER
Q2.
CREATE OR REPLACE TRIGGER check_Salary
BEFORE DELETE OR INSERT OR UPDATE ON employee
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal1 number;
BEGIN
FOR EACH ROW
WHEN (OLD.ID > 0)
DECLARE
sal2 number;
sal1 := :NEW.salary ;
sal2 := :OLD.salary ;
if(sal1<sal2)
dbms_output.put_line('Violation');
else
dbms_output.put_line('Good to go');
END;