The Relational Model Employee ID FName LName Salary jobTitle

The Relational Model Employee ID, FName, LName, Salary, jobTitle, officeNum, empRank, supervisorID) That is one design, other designs for the ISA relationship are possible... Equipment Type (ID, Desc, model, instructions) Equipment (Serialft, TypeID, PurchaseYear, Lastlnspection, roomNum) Room (Num, occupied flag) RoomService (roomNum service Room Access (roomNum EmplD) Patient (SSN, FirstName, LastName, Address, TelNum) Doctor (LD, gender, specialty, LastName, FirstName) Admission (Num, AdmissionDate, LeaveDate, TotalPayment, InsurancePayment, Patient SSN, FutureVisit) Examine (Doctor ID, AdmissionNum, comment) Stayln(AdmissionNum, RoomNum, startDate, endDate)

Solution

1)

Create or replace trigger reg_div_check

Before Insert

on table Employee

for each row

Declare

v_empid number;

v_sid number;

v_eid number;

v_spid number;

emp_exception EXCEPTION;

begin

v_empid:= :new.ID;

select superVisorId into v_sid from employee where ID=v_empid;

if ISNULL(v_sid) then

raise emp_exception;

end if;

SELECT e1.ID, e2.ID into v_eid,v_spid

FROM Employee e1,Employee e2

where e1.supervisorID = e2.ID

AND e1.empRank=1 AND e2.empRank=0;

if ISNULL(v_eid) OR ISNULL(v_spid) then

raise emp_exception;

Exception

when emp_exception then

End;

2)

Create or replace trigger reg_div_check

Before Insert

on table Employee

for each row

Declare

v_empid number;

v_sid number;

v_eid number;

v_spid number;

emp_exception EXCEPTION;

begin

v_empid:= :new.ID;

select superVisorId into v_sid from employee where ID=v_empid;

if ISNULL(v_sid) then

raise emp_exception;

end if;

SELECT e1.ID, e2.ID into v_eid,v_spid

FROM Employee e1,Employee e2

where e1.supervisorID = e2.ID

AND e1.empRank=2 AND e2.empRank=1;

if ISNULL(v_eid) OR ISNULL(v_spid) then

raise emp_exception;

Exception

when emp_exception then

End;

3)

Create or replace trigger icu_admit After insert on Admission for each row

declare

v_ser varchar2(10);

v_ad date;

v_future date;

v_ssn number;

begin

select rs.service,a.admissiondate,a.futureVisitDate,patient_ssn into v_ser,v_ad,v_future,v_snn from RoomService rs,Admission a

where a.Num=rs.roomNum And rs.service=\'ICU\';

select add_months(v_future,3) into v_future from dual;

update Admission set futureVisitDate=v_future where admissiondate=v_ad AND patient_ssn=v_ssn;

Exception

when others then

End;