The Relational Model Employee ID, FName, LName, Salary, jobTitle, officeNum, emp
ID: 3798545 • Letter: T
Question
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)Explanation / Answer
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;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.