ICT285 Databases Assignment 2: Hospital Database Design and SQL

This assignment solution presents a comprehensive hospital database design, encompassing an Entity-Relationship Diagram (ERD), data dictionary, and SQL implementation. The ERD, created by the author, models patient registration and staff allocation within a hospital setting, with specific modifications and explanations provided. The data dictionary details the structure of each table, including column names, data types, nullability, and primary/foreign keys. The solution includes SQL statements for table creation, data insertion, and granting permissions. Furthermore, the assignment incorporates the creation of SQL views to retrieve specific data subsets, along with explanations of the design choices and implementation strategies. The solution includes detailed SQL code for creating tables, inserting data, and defining views. The document concludes with a bibliography of relevant database systems resources.

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

1Database
Table of Contents
1. Entity Relationship Diagram:.............................................................................................2
2. Explanations:......................................................................................................................2
3. Data dictionary:...................................................................................................................3
4. Implement the database:...................................................................................................10
5. SQL statements:................................................................................................................21
6. SQL VIEWS:....................................................................................................................23
7. Explanations:....................................................................................................................26
Bibliography:............................................................................................................................28

2Database
1. Entity Relationship Diagram:
Figure 1: The Entity Relationship Diagram of hospital Felicity Stillwell Hospital
(Source: Created by Author)
2. Explanations:
The changes I have made in this ERD is discussed in this section of this report. This
ERD support patient registration form and staff allocation fir each ward. Many changes have
been made in this ERD. Every change is discussed below:
 Short stay and long stay patient entity are removed in this ERD, because inpatient
table capable enough to hold every information for inpatient.
 Outpatient clinic is removed in this ERD. This ERD is mostly support the patient
registration form so outpatient is not necessary part in this ERD.

3Database
 Appointment table hold the information of local doctor as well as patient information.
 Bed number is added in the ward entity. Bed number can indicate the total number of
beds in each ward.
 Charge nurse, director entity has been removed because they are all staff. Staff table
can manage every staff information efficiently. in this scenario generalization is not
mandatory.
 Requisitions entity is removed in this ERD, because It this ERD support only patient
form and ward allocation form.
 Supplier and different items are removed from this ERD.
 Detailed kin information is stored in the next of kin entity.
 Inpatient entity can hold more essential information than before.
 Staff allocation table can store every information that hospital authority required.
3. Data dictionary:
Ward table:
Column_name Data_type Nullabl
e
Key Column id
WARDNO NUMBER NO PRIMARY
KEY
1
WARDNAME VARCHAR2 (50) YES 2
LOCATION VARCHAR2(50) YES 3
EXTENTIONNUMBER NUMBER YES 4
TOTALBEDS NUMBER YES 5
Appointment Table:

4Database
Column_name Data_type Nullabl
e
Key Column
id
APPOINMENTNO NUMBER NO PRIMARY KEY 1
APPOINTDATE DATE YES 2
PROVIDERNO VARCHAR2(50) YES FOREIGN KEY 3
PATIENTNO VARCHAR2(50
BYTE)
YES 4
Inpatient Table:
Column_name Data_type Nullable Key Column
id
PATIENTNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
ADMITDATE_INWARD DATE YES 2
EXPECTED_DURATION DATE YES 3
EXPECTEDLEAVE_DAT
E
VARCHAR2(50
BYTE)
YES 4
LEAVEDATE DATE Yes 5
WARDNO NUMBER YES FOREIGN
KEY
6
LOCALDOCTOR TABLE:

5Database
Column_name Data_type Nullable Key Column
id
PROVIDERNO VARCHAR2(50
BYTE)
NO PRIMARY KEY 1
FULLNAME VARCHAR2(50
BYTE)
YES 2
STREETADDRESS VARCHAR2(50
BYTE)
YES 3
SUBURB VARCHAR2(20
BYTE)
YES 4
STATE VARCHAR2(20
BYTE)
Yes 5
POSTCODE NUNUMBERMBER YES 6
TELEPHONENUMBER NUMBER Yes 7
REFERDATE DATE Yes 8
NEXT OF KIN TABLE:
Column_name Data_type Nullable Key Column
id
PROVIDERNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
NAME VARCHAR2(50 YES 2

6Database
BYTE)
STREETADDRESS VARCHAR2(50
BYTE)
YES 3
SUBURB VARCHAR2(20
BYTE)
YES 4
STATE VARCHAR2(20
BYTE)
Yes 5
POSTCODE NUNUMBERMBER YES 6
RELATIONSHIP VARCHAR2(20
BYTE)
Yes 7
TELEPHONE_NUMBER NUMBER Yes 8
PATIENT TABLE:
Column_name Data_type Nullable Key Column
id
PATIENTNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY, FOREIGN
KEY
1
FAMILYNAME VARCHAR2(50
BYTE)
YES 2
GIVENNAME VARCHAR2(50
BYTE)
YES 3
STREETADDRESS VARCHAR2(50
BYTE)
YES 4

7Database
SUBRUB VARCHAR2(20
BYTE)
Yes 5
STATE NUNUMBERMBER YES 6
POSTCODE NUMBER Yes 7
GENDER VARCHAR2(1
BYTE)
Yes 8
DATEOFBIRTH DATE Yes 9
MARTIALSTATUS VARCHAR2(1
BYTE)
Yes 10
TELEPHONENUMBER NUMBER Yes 11
STAFF TABLE:
Column_name Data_type Nullable Key Column
id
STAFFNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
FAMILYNAME VARCHAR2(50
BYTE)
YES 2
GIVENNAME VARCHAR2(50
BYTE)
YES 3
STREETADDRESS VARCHAR2(50
BYTE)
YES 4

8Database
SUBRUB VARCHAR2(20
BYTE)
Yes 5
STATE NUNUMBERMBER YES 6
POSTCODE NUMBER Yes 7
GENDER VARCHAR2(1
BYTE)
Yes 8
DOB DATE Yes 9
TELEPHONE_NUMBER NUMBER Yes 10
POSITION VARCHAR2(20
BYTE)
Yes 11
WARDNO NUMBER Yes FOREIGN
KEY
12
CURRENTSALARY VARCHAR2(20
BYTE)
Yes 13
HOURSPERWEEK NUMBER Yes 14
SALARYSCALE VARCHAR2(3
BYTE)
Yes 15
PERMANENR_TEMPORARY VARCHAR2(5
BYTE)
Yes 16
PAIDWEEKLYMONTHLY VARCHAR2(5
BYTE)
Yes 17
STAFF QUALIFICATION TABLE:
Column_name Data_type Nullable Key Column

9Database
id
STAFFNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
QUALIFICATION_TYPE VARCHAR2(20
BYTE)
YES 2
DATE_AWARDED DATE YES 3
INSTITUION VARCHAR2(50
BYTE)
YES 4
STAFF WORK EXPERIENCE TABLE:
Column_name Data_type Nullable Key Column
id
STAFFNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
WARDNO NUMBER YES 2
ORGANAIZATION VARCHAR2(20
BYTE)
YES 3
STARTDATE DATE YES 4
FINISHDATE DATE Yes 5

10Database
WARD STAFF ALLOCATION TABLE:
Column_name Data_type Nullable Key Column
id
STAFFNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
WARDNO NUMBER YES 2
STAFF_NAME VARCHAR2(50
BYTE)
YES 3
POSITION VARCHAR2(20
BYTE)
YES 4
SHIFT VARCHAR2(20
BYTE)
Yes 5
4. Implement the database:
Creating ward table:
CREATE TABLE WARD (
WardNo NUMBER, WARDNAME VARCHAR(50),lOCATION VARCHAR(50),
EXTENTIONNUMBER NUMBER, TOTALBEDS NUMBER,
CONSTRAINT WARDNO_PK PRIMARY KEY (WARDNO));

11Database
Creating table PATIENT:
CREATE TABLE PATIENT (
PATIENTNO VARCHAR2(50), FAMILYNAME VARCHAR(50),
GIVENNAME VARCHAR2(50), STREETADDRESS VARCHAR2(50),SUBRUB
VARCHAR2(20),
STATE VARCHAR2(20), POSTCODE NUMBER,GENDER VARCHAR2(1),
DATEOFBIRTH DATE, MARTIALSTATUS VARCHAR2(1),TELEPHONENUMBER
NUMBER,
CONSTRAINT PATIENTNOPK PRIMARY KEY (PATIENTNO)
);