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

SQL> create table Room(Num varchar2(10),Occupied varchar2(10));

Table created.

SQL> desc Room

Name Null? Type

----------------------------------------- -------- ----------------------------

NUM VARCHAR2(10)

OCCUPIED VARCHAR2(10)

SQL> create table RoomService(roomNum varchar2(15),service varchar2(20));

Table created.

SQL> desc RoomService

Name Null? Type

----------------------------------------- -------- ----------------------------

ROOMNUM VARCHAR2(15)

SERVICE VARCHAR2(20)

SQL> create table Admission(Num varchar2(10),AsdmissionDate date,LeaveDate date,TotalPayment real,InsurancePayment real,Patient_SSN varchar2(20),FutureVisit varchar2(15));

Table created.

SQL> desc Admission

Name Null? Type

----------------------------------------- -------- ----------------------------

NUM VARCHAR2(10)

ADMISSIONDATE DATE

LEAVEDATE DATE

TOTALPAYMENT FLOAT(63)

INSURANCEPAYMENT FLOAT(63)

PATIENT_SSN VARCHAR2(20)

FUTUREVISIT VARCHAR2(15)

SQL> insert into RoomService values(\'101\',\'ICU\');

1 row created.

SQL> insert into RoomService values(\'101\',\'Metaranty\');

1 row created.

SQL> insert into RoomService values(\'101\',\'Emergency\');

1 row created.

SQL> select * from RoomService;

ROOMNUM SERVICE

--------------- --------------------

101 ICU

101 Metaranty

101 Emergency

Trigger creation and Execution:

1) SQL> create or replace trigger tr_service

2 before insert or update or delete on RoomService

3 for each row

4 when (new.roomnum>0)

5 declare

6 cn number(3);

7 begin

8 select count(service) into cn from RoomService;

9 if cn=3 then

10 raise_application_error(-20015,\'You can not offer > 3 for room\');

11 end if;

12 end;

13 /

Trigger created.

SQL> insert into RoomService values(\'101\',\'Critical\');

insert into RoomService values(\'101\',\'Critical\')

*

ERROR at line 1:

ORA-20015: You can not offer > 3 for room

ORA-06512: at \"CSEB.TR_SERVICE\", line 6

ORA-04088: error during execution of trigger \'CSEB.TR_SERVICE\'

2)SQL> create or replace trigger Insurance

2 after insert or update or delete of TotalPayment on Admission

3 for each row

4 declare

5 Ins real;

6 T real;

7 begin

8 select TotalPayment into T from Admission;

9 Ins := (70*T)/100;

10 update Admission set InsurancePayment = Ins;

11 dbms_output.put_line(\'Insurance Payment:\'||Ins);

12 end;

13 /

Trigger created.

SQL> insert into Admission values(\'&Num\',\'&AdmissionDate\',\'&LeaveDate\',&TotalPayment,&InsurancePayment,\'&Patient_SSN\',\'&Futurevisit\');

Enter value for num: 234

Enter value for admissiondate: 08jan2017

Enter value for leavedate: 27feb2017

Enter value for totalpayment: 28000

Enter value for insurancepayment: 0

Enter value for patient_ssn: 5676867987080

Enter value for futurevisit: allowed

old 1: insert into Admission values(\'&Num\',\'&AdmissionDate\',\'&LeaveDate\',&TotalPayment,&InsurancePayment,\'&Patient_SSN\',\'&Futurevisit\')

new 1: insert into Admission values(\'234\',\'08jan2017\',\'27feb2017\',28000,0,\'5676867987080\',\'allowed\')

1 row created.

SQL> select * from Admission;

NUM ADMISSION LEAVEDATE TOTALPAYMENT INSURANCEPAYMENT

---------- --------- --------- ------------ ----------------

PATIENT_SSN FUTUREVISIT

-------------------- ---------------

234 08-JAN-17 27-FEB-17 28000 19600

5676867987080 allowed