Implement the tables from the COMPANY database schema shown in the pdf that acco
ID: 3683562 • 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
Source Link for PDF
www.comp.dit.ie/pobyrne/Database%20Applications/T01%20handoutDA%20Relational%20Algebra.pdf
Explanation / Answer
//create the audit table keeping the structure same as employee
create table employee_salary_audit as select * from
employee where 1=0;
//create view
create view VW_EmployeeDetails
as
select s.Fname||s.Lname as SupervisorName ,
e.Fname||e.Lname as employeeName,
d.dname as DepartMentName ,
e.salary
from
employee e
join employee s on (e.super_ssn = s.ssn)
join department d on (d.dnumber = e.dno);
order by d.dnumber;
//create trigger
CREATE TRIGGER lowSalViolationtrigger
AFTER INSERT ON employee
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN (newRow.salary < (select min(salary) from employee e where e.dno=newRow.dno)
BEGIN
INSERT INTO employee_salary_audit VALUES(:newRow.fname, :newRow.Minit
:newRow.Lname,:newRow.Ssn,:newRow.bdate,:newRow.Address,:newRow.sex,:newRow.salary,:newRow.super_ssn,:newRow.dno);
END lowSalViolationtrigger;