ICT285: Database Implementation for Felicity Stillwell Hospital

This document presents a comprehensive database implementation project for the Felicity Stillwell Hospital, addressing the requirements outlined in the ICT285 assignment. The project begins with a revised Entity Relationship Diagram (ERD) that reflects changes made to the original design, specifically removing stock and supplier details. A detailed data dictionary is provided, defining the attributes and data types for each table, including Ward, Staff, Patient, and Appointment tables, among others. The implementation section includes the SQL code for creating these tables, along with constraints such as primary and foreign keys. The assignment further demonstrates the use of SQL queries to insert data into the tables, including examples for staff, patients, and roster information. Several views are created to retrieve specific data, such as the count of staff per ward and patient details based on appointment and ward criteria. Finally, the project includes a discussion of further constraints, such as ensuring bed availability before allocating a patient to a ward and restricting staff record updates to Personnel Officers, along with corresponding trigger implementations. A bibliography of relevant database texts is also included.

Running head: DATABASE CONCEPTS
DATABASE CONCEPTS
Name of the Student
Name of the University
Author Note

1
DATABASE CONCEPTS
Table of Contents
Entity Relationship Diagram...........................................................................................................2
Changes in ERD..............................................................................................................................3
Data Dictionary................................................................................................................................3
Implementation..............................................................................................................................10
Task................................................................................................................................................18
Queries – 5A..............................................................................................................................18
Queries – 5B..............................................................................................................................19
Queries – 5C..............................................................................................................................19
View – A....................................................................................................................................20
View – B....................................................................................................................................21
View – C....................................................................................................................................21
View – D....................................................................................................................................21
Further Constraints........................................................................................................................22
Bibliography..................................................................................................................................24

2
DATABASE CONCEPTS
Entity Relationship Diagram

3
DATABASE CONCEPTS
Figure 1: Revised Entity Relationship diagram
Source: created by author
Changes in ERD
The changes in ERD has been made by removing Stock and other supplier details which
is not required at the moment. This ERD is capable of explaining patient appointment, ward
allocation, staff roster and staff allocation to any ward in the hospital. It store patient details.
Local doctors details and next of the kin details of the patient. The staff can be nurse, charge
nurse staff nurse, medical director, personnel officer and specialist staff. Charge nurse can only
make roster and allocate nurses to the staff. Only a consultant staff can consult patient for
appointment. Appointment is either outpatient or inpatient type.
Data Dictionary
Ward table:
Column name Data type Nullable Key
Ward_number number NO PRIMARY KEY
Ward_name varchar2 (50) YES
Location varchar2(50) YES
Total_beds number YES
Tele_ext number YES
Staff table:
Column name Data type Nullable Key

4
DATABASE CONCEPTS
Staff_number varchar2 (20) NO PRIMARY KEY
First_name varchar2 (50)
Lasst_name varchar2 (50)
street varchar2 (50)
suburb varchar2 (50)
state varchar2 (20)
Post_code number
T_number number
Dob date
Gender varchar2 (5)
Taxfile_number number
Position_held varchar2(20)
Current_salary varchar2(20)
Salary_scale varchar2(3)
QUALIFICATION TABLE:
Column_name Data_type Nullable Key
Staff_number varchar2(20) NO PRIMARY KEY,
FOREIGN KEY
Qualification_date date

5
DATABASE CONCEPTS
Qualification_type varchar2(50)
Institution_name varchar2(50)
EXPERIENCES Table:
Column_name Data_type Nullable Key
Satff_number varchar2(20) NO PRIMARY KEY,
FOREIGN KEY
position varchar2(20)
Organization varchar2(20)
Start_date date
Finish_date date
PATIENT table:
Column_name Data_type Nullable Key
Patient_number varchar2(10 byte) NO PRIMARY KEY
First_name varchar2(50)
Last_name varchar2(50)
T_number number
Dob date
Gender varchar2(1)
Marital Status varchar2(10 )

6
DATABASE CONCEPTS
Date registered date
Provider_Number number Not null FOREIGN KEY
INPATIENT table:
Column_name Data_type Nullable Key
Appointment_number VARCHAR2(20
BYTE)
Not null PRIMARY KEY,
FOREIGN KEY
Placed_in_waiting_list DATE
Placed_in_ward DATE
Stay_duration number
Expected_leave date
Actual_leave date
Bed date
NEXT OF KIN TABLE:
Column_name Data_type Nullable Key
Patient_number varchar2(20 byte) NO PRIMARY KEY,
FOREIGN KEY
First_name varchar2(30 byte)
Last_name varchar2(30 byte)
Relation_with_patient varchar2(20 byte)
Street varchar2(50 byte)

7
DATABASE CONCEPTS
Subrub varchar2(20 byte)
State varchar2(20 byte)
Post_code number
T_number number
LOCAL DOCTOR DETAILS TABLE:
Column_name Data_type Nullable Key
Providerno varchar2(20 byte) NO PRIMARY KEY
Fullname varchar2(60 byte)
Street varchar2(20 byte)
Suburb varchar2(20 byte)
State varchar2(20 byte)
Post_code number
T_number number
CONTRACT Table
Column name Data type Nullable Key
Staff_number varchar2 (20) NO PRIMARY KEY
FOREIGN KEY
Work_hr_per_week numer YES
Mode_of_payment varchar2(30) YES

8
DATABASE CONCEPTS
CHARGE NURSE Table
Column name Data type Nullable Key
Staff_number varchar2 (20) NO PRIMARY KEY
FOREIGN KEY
PERSONNEL OFFICER Table
Column name Data type Nullable Key
Staff_number varchar2 (20) NO PRIMARY KEY
FOREIGN KEY
MEDICAL DIRECTOR Table
Column name Data type Nullable Key
Staff_number varchar2 (20) NO PRIMARY KEY
FOREIGN KEY
SPECIALIST STAFF Table
Column name Data type Nullable Key
Staff_number varchar2 (20) NO PRIMARY KEY
FOREIGN KEY
Speciality varchar2 (100)

9
DATABASE CONCEPTS
OUTPATIENT TABLE
Column name Data type Nullable Key
Appointment_number varchar2(20) Not null PRIMARY KEY,
FOREIGN KEY
ROSTER Table
Column name Data type Nullable Key
Roster_Number varchar2(20) Not null PRIMARY KEY
Roster_Date number
Nurse_number varchar2(20) FOREIGN KEY
Ward_Number number FOREIGN KEY
STAFFROSTER Table
Column name Data type Nullable Key
Staff_Number varchar2(20) Not null PRIMARY KEY
FOREIGN KEY
Roster_Number varchar2(20) PRIMARY KEY
FOREIGN KEY
Is_Present varchar2(20)
BED Table

10
DATABASE CONCEPTS
Column name Data type Nullable Key
Bed_Number number Not null PRIMARY KEY
Ward_Number number FOREIGN KEY
Is_Occupied varchar2(10)
APPOINTMENT TABLE
Column name Data type Nullable Key
Appointment_Number varchar2(20) Not null PRIMARY KEY
Patient_Number varchar2(10) FOREIGN KEY
Consultant_Number varchar2(20) FOREIGN KEY
Appointment_Datetime date
Exam_Room varchar2(10)
Ward_number Number FOREIGN KEY
Implementation
Local Doctor
CREATE TABLE Local_Doctor (
provider_number VARCHAR(20) primary key,fullname VARCHAR2(60),
Street VARCHAR(20), suburb VARCHAR2(20), State VARCHAR2(20), Post_code
NUMBER,
t_number NUMBER);

11
DATABASE CONCEPTS
Patients
CREATE TABLE patients (
Patient_Number VARCHAR2(10) primary key, First_Name VARCHAR(50),
Last_Name VARCHAR2(50), T_Number NUMBER, dob DATE, Gender VARCHAR2(1),
Marital_Status VARCHAR2(10), date_registered date,
provider_number VARCHAR2(20), constraint fk_pdp foreign
key(provider_number) references Local_Doctor(Provider_Number));
Next Of Kin
CREATE TABLE NextOfKin (
patient_number VARCHAR(20) primary key,first_name VARCHAR2(30),
last_name VARCHAR2(30),
relation_with_patient VARCHAR2(20),
street VARCHAR2(50), suburb VARCHAR(20),
state VARCHAR2(20), post_code NUMBER,
t_number NUMBER,
CONSTRAINT fk_pnok FOREIGN KEY (patient_number)
REFERENCES patients (patient_number));

12
DATABASE CONCEPTS
Ward
CREATE TABLE ward (
Ward_number NUMBER primary key, ward_name VARCHAR(50),
ward_location VARCHAR(50), total_beds NUMBER,tele_ext NUMBER);
Staff
CREATE TABLE STAFF (
staff_number VARCHAR(20) primary key, firstname VARCHAR(50),
lastname VARCHAR(50), street VARCHAR2(50),
suburb VARCHAR(20),state VARCHAR(20),post_code NUMBER, t_number
NUMBER,
dob DATE, gender VARCHAR2(5), tax_file_number number,
position_held VARCHAR(20), current_salary VARCHAR(20),salary_scale
VARCHAR2(3));

13
DATABASE CONCEPTS
Contract
create table contract( staff_number varchar(20) primary key,
work_hr_per_week number,
mode_of_payment varchar(30), constraint fk_sc foreign key
(staff_number) references staff(staff_number));
Qualification
CREATE TABLE qualification (
staff_number VARCHAR(20) primary key,
qualification_date DATE,
qualification_type VARCHAR2(20),
institution_name VARCHAR(50),
constraint fk_sq foreign key (staff_number) references
staff(staff_number));

14
DATABASE CONCEPTS
Experiences
CREATE TABLE experiences (
staff_number varchar(20), position varchar2(20),organization
varchar2(20),
start_date date, finish_date date,
CONSTRAINT staffno_pk_pk primary KEY
(Staff_number,position,organization),
CONSTRAINT se foreign key (staff_number) references
staff(staff_number));
Charge Nurse
create table chargenurse (staff_number varchar(20) primary key,
CONSTRAINT fk_scn foreign key (staff_number) references
staff(staff_number));
Personnel Officer
create table Personnelofficcer (staff_number varchar(20) primary key,

15
DATABASE CONCEPTS
CONSTRAINT fk_spo foreign key (staff_number) references
staff(staff_number));
Medical Director
create table MedicalDirector (staff_number varchar(20) primary key,
CONSTRAINT fk_smd foreign key (staff_number) references
staff(staff_number));
Specialist Staff
create table SpecialistStaff (staff_number varchar(20) primary key,
speciality varchar2(100),
CONSTRAINT fk_sss foreign key (staff_number) references
staff(staff_number));
Roster
create table roster(roster_number varchar2(10) primary key,
roster_date date, nurse_number varchar2(20), ward_number number,
CONSTRAINT fk_cnr foreign key (nurse_number) references
staff(staff_number),

16
DATABASE CONCEPTS
CONSTRAINT fk_wr foreign key (ward_number) references
ward(ward_number));
Staff Roster
create table staffroster(staff_number varchar2(20), roster_number
varchar2(20),
is_present varchar2(20), primary key(staff_number, roster_number),
CONSTRAINT fk_ssr foreign key (staff_number) references
staff(staff_number),
CONSTRAINT fk_rsr foreign key (roster_number) references
roster(roster_number));
Appointment
create table appointment( appointment_number varchar(20) primary key,
patient_number varchar2(10), consultant_number varchar2(20),
appointment_datetime date, exam_room varchar2(10), ward_number
number,
CONSTRAINT fk_wa foreign key (ward_number) references
ward(ward_number),
CONSTRAINT fk_pa foreign key (patient_number) references
patients(patient_number),
CONSTRAINT fk_sa foreign key (consultant_number) references
staff(staff_number));

17
DATABASE CONCEPTS
Outpatient
create table outpatient(appointment_number varchar(20) primary key,
CONSTRAINT fk_ao foreign key (appointment_number) references
appointment(appointment_number));
Bed
create table bed(bed_number number,ward_number number, is_occupied
varchar2(10),
primary key(bed_number), CONSTRAINT fk_wb foreign key (ward_number)
references ward(ward_number));
Inpatient
create table inpatient(appointment_number varchar(20) primary key,
placed_in_waiting_list date,
placed_in_ward date, stay_duration number, expected_leave date,
actual_leave date,
bed number,CONSTRAINT fk_bi foreign key(bed) references
bed(bed_number),

18
DATABASE CONCEPTS
CONSTRAINT fk_io foreign key (appointment_number) references
appointment(appointment_number));
Task
Queries – 5A
--A
INSERT INTO STAFF VALUES ( 'S4576', 'Moira','Samuels', '49 School
Road','Bedford', 'WA',6052,01504563357,TO_DATE('30/05/1990',
'dd/mm/yyyy'),
'F',4585475,'Charge Nurse','68,760','1C');
INSERT INTO qualification VALUES
('S4576',TO_DATE('22/07/2012','dd/mm/yyyy'), 'BSc. (Nursing)','Curtain
University');
INSERT INTO experiences VALUES ('S4576', 'CHARGE NURSE','EASTERN
HOSPITAL',
TO_DATE('23/01/2011','dd/mm/yyyy'),TO_DATE('1/05/2011','dd/mm/yyyy'));
INSERT INTO contract VALUES ('S4576',37.5, 'Monthly');

19
DATABASE CONCEPTS
Queries – 5B
--B
INSERT INTO local_doctor VALUES ('D00023','James SMITH', '77 VINCENT
STREET','Wellington','WA',
6856,021245896);
INSERT INTO patients VALUES
('P10234','Anne','Phelps',01313324158,TO_DATE('10/12/1955','dd/mm/
yyyy'),'F','Married'
, TO_DATE('01/09/2013','dd/mm/yyyy'),'D00023');
INSERT INTO nextofkin VALUES ('P10234','James','Phelps','Spouse','67
Wellmeaning Way','Wellington','WA',
6856,01313324158);
Queries – 5C
--C
INSERT INTO STAFF VALUES ( 'S0980', 'Carol','Cummings', '49 School
Road','Bedford', 'WA',6052,01504563357,TO_DATE('30/05/1990',
'dd/mm/yyyy'),
'F',4585475,'Staff Nurse','38,760','1D');
INSERT INTO STAFF VALUES ( 'S1257', 'Morgan','Russell', 'George
Road','Melbourne', 'VIC',6054,458425558,TO_DATE('16/09/1995',
'dd/mm/yyyy'),
'F',8545785,' Nurse','28,760','1D');
INSERT INTO STAFF VALUES ( 'S1458', 'Robin','Plevin', '49 School
Road','Bedford', 'WA',6052,0125332555,TO_DATE('18/03/1995',
'dd/mm/yyyy'),
'F',6585555,'Staff Nurse','38,760','1D');
INSERT INTO STAFF VALUES ( 'S2356', 'Amy','O`Donnell', 'Kings
Road','Melbourne', 'VIC',6053,012457855,TO_DATE('21/06/1990',
'dd/mm/yyyy'),
'F',2455855,'Consultant','48,760','1C');
INSERT INTO STAFF VALUES ( 'S3649', 'Laurence','Burns', '48 Peter
Road','Bedford', 'WA',6055,01232546654,TO_DATE('20/07/1990',
'dd/mm/yyyy'),
'F',5685955,'Nurse','28,760','1D');

20
DATABASE CONCEPTS
INSERT INTO chargenurse VALUES ('S4576');
INSERT INTO ward VALUES ( 11, 'Orthopaedic', 'Block E',16, 7711);
INSERT INTO Roster VALUES
('R102',TO_DATE('09/01/2013','dd/mm/yyyy'),'S4576',11);
INSERT INTO StaffRoster VALUES('S0980','R102','Late');
INSERT INTO StaffRoster VALUES('S1257','R102','Late');
INSERT INTO StaffRoster VALUES('S1458','R102','Early');
INSERT INTO StaffRoster VALUES('S2356','R102','Night');
INSERT INTO StaffRoster VALUES('S3649','R102','Early');
View – A
--A
create view ViewA as
select count(s.staff_number) as Staff, r.ward_number from roster r,
staffroster sr, staff s
where s.staff_number=sr.staff_number and
sr.roster_number=r.roster_number group by r.ward_number;

21
DATABASE CONCEPTS
View – B
--B
create view ViewB as
select p.patient_number,(p.first_name ||' '|| p.last_name) as
patientName from patients p, appointment a, inpatient i
where p.patient_number=a.patient_number and
a.appointment_number=i.appointment_number and
extract(month from i.placed_in_ward)= extract(month from sysdate);
View – C
--C
create view ViewC as
select p.patient_number,(p.first_name ||' '|| p.last_name) as
patientName from patients p, appointment a
where p.patient_number=a.patient_number and a.ward_number=11 and
extract(month from a.appointment_datetime)> extract(month from
sysdate-12);
View – D
--D
create view ViewD as
select l.provider_number, l.fullname,(l.street ||' '|| l.suburb ||'
'|| l.state ||' '|| l.post_code )
as Address, l.t_number, count(p.patient_number) as TotalPatient from
local_doctor l inner join patients p
on l.provider_number=p.provider_number

22
DATABASE CONCEPTS
group by(l.provider_number,l.fullname,l.street,l.suburb,l.state,
l.post_code,l.t_number);
Further Constraints
Part a
A patient can be allocated to a ward if there are beds available. In this scenario, an insert trigger
can be used on inpatient table. If count of the beds in a ward that is not occupied is zero, then the
trigger should invoke constraint on inpatient table. By this n new data can be added further in the
table until any bed become available.
Part b
Only Personnel Officer are permitted to update staff record. If the username and passwords can
be used for the role identification in the database system and the personnel officer should be
allowed to update privileges in the database. On other hand, storing the updates will need a
update trigger on staff table. The trigger can be expressed as:
CREATE OR REPLACE TRIGGER staffupdate
After Update ON Staff FOR EACH ROW
DECLARE v_username varchar2(20);

23
DATABASE CONCEPTS
BEGIN
SELECT USER INTO v_username
FROM dual;
INSERT INTO updates (first_col_name, second_col_name)
VALUES (:new.first_col_name, v_username);
END;
This will also require an extra table named updates which will store the update records.

24
DATABASE CONCEPTS
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Data, M.H., 2015. Database Design. Perancangan Basis Data) merupakan salah satu.
Din, A.I., 2014. Structured query language (SQL) A practical Introduction.
Kyte, T. and Kuhn, D., 2014. Expert Oracle Database Architecture. Apress.
O'neil, P., 2014. DATABASE: principles programming performance. Morgan Kaufmann.
Rossi, B., 2014. Entity relationship diagram.