Database Concepts Theory Assignment

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

DATABASE CONCEPTS
1
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

DATABASE CONCEPTS
2
Entity Relationship Diagram

DATABASE CONCEPTS
3
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

DATABASE CONCEPTS
4
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

DATABASE CONCEPTS
5
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 )