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));