SQL Database | Assignment-1

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

Database
1
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

Database
2
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.

Database
3
 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 Nulla
ble
Key Column
id
WARDNO NUMBER NO PRIMARY
KEY
1
WARDNAME VARCHAR2 (50) YES 2
LOCATION VARCHAR2(50) YES 3
EXTENTIONNUMBE
R
NUMBER YES 4
TOTALBEDS NUMBER YES 5

Database
4
Appointment Table:
Column_name Data_type Nulla
ble
Key Colum
n 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 Nulla
ble
Key Colum
n 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

Database
5
LOCALDOCTOR TABLE:
Column_name Data_type Nulla
ble
Key Colum
n 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 NUNUMBERMBE
R
YES 6
TELEPHONENUMBE
R
NUMBER Yes 7
REFERDATE DATE Yes 8
NEXT OF KIN TABLE:
Column_name Data_type Nulla
ble
Key Colum
n id
PROVIDERNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
1