Requirement Analysis
Patient – Ward
Database
The aim of this project is to model a database on the relationship between a hospital patient and the hospital ward to which they are assigned. I.T is very important within the health system and a well designed database should ensure that there is no duplication of data, or data redundancy, thus helping to reduce the possibility of mistakes, which can have devastating consequences.
The patient must be identifiable by a unique number or code, and my database must also record their name. It would also be possible to expand the number of attributes at any time, to include such attributes as blood type, medication, etc, but for now a name and unique identifier will suffice.
The ward must also have a unique identifier, and most wards do by using a ward number. They also have a name, and their capacity is also very important. It is a huge issue in the health service today. Lastly, each ward needs a location, as many hospitals have a number of wings, depending on the treatments in which they specialise, such as maternity unit, paediatrics, and others.
Initial attempt at two tables, modelling the above relationship.
Here I have made Patient_Code the primary key for the PATIENTS table, and WardNumberthe primary key for the WARDS table.
The PATIENTS table also records the patients name, and can record whichever ward the patient is assigned to. There are 10 wards in this hospital, and so they can potentially be assigned to any one of them, that is, ward1, ward2 ..up to ward10.
PATIENTS
(Patient_Code, Patient _Name, Ward1, Ward2, Ward10)
WARDS
(WardNumber, WardName, Location, Capacity,
Doctor_ID, DoctorName,AssistantNumber, AssistantName)
The problem with this model is that it does not adhere to Normalisation rules as it contains repeating attributes. In the PATIENTS table ward is repeated 10 times, this is bad design and MUST be rectified.
NORMALISATION
1STNORMAL
No repeating attributes.
In order to solve this problem I must take the repeating attributes from the PATIENTS table and create a 3 rd table, which models the relationship between the first 2 tables, thus solving the problem.
PATIENTS_ WARDS
(Patient_Code, Patient _Name, WardNumber, WardName, Location, Capacity)
UPDATED TABLES IN 1stNORMAL FORM
PATIENTS_ WARDS
(Patient_Code, Patient _Name, WardNumber, WardName, Location, Capacity, Doctor_ID, DoctorName,AssistantNumber, AssistantName)
PATIENTS
(Patient_Code, Patient _Name)
WARDS
(WardNumber, WardName, Location, Capacity,
Doctor_ID, DoctorName,AssistantNumber, AssistantName)
NORMALISATION
2nd NORMAL
Attributes must depend on the WHOLE key..
In order to move to 2ndNormal I must ensure that each attribute depends on the WHOLE key, which in this case is a combination of Patient_CodeandWardNumber. This is called a composite key. However I have found that none of the non-key attributes rely fully on the composite key, and so the must be removed.
PATIENTS_ WARDS
(Patient_Code, WardNumber)
UPDATED TABLES IN 2ndNORMAL FORM
PATIENTS_ WARDS
(Patient_Code, WardNumber)
PATIENTS
(Patient_Code, Patient _Name)
WARDS
(WardNumber, WardName, Location, Capacity,
Doctor_ID, DoctorName,AssistantNumber, AssistantName)
NORMALISATION
3rd NORMAL
No Non-Key dependencies.
In order to move to 3rd Normal I must ensure that there are no attributes that rely on a “non-key” attribute. However when I examine the WARDS table I find that both Doctor Name and Assistant Namecontravene this rule. So I must create a new table for both, making the “non-keys” new primary keys.
UPDATED TABLES IN 3rd NORMAL FORM
PATIENTS
(Patient_Code, Patient _Name)
WARDS
(WardNumber, WardName, Location, Capacity)
PATIENTS_ WARDS
(Patient_Code, WardNumber)
DOCTORS
(Doctor_id, DoctorName)
ASSISTANTS
(Assistant_id, AssistantName)
Entity – Relationship
Diagram
Data Dictionaries
PATIENTS
Field Number / Field Name / Type / Size / CommentB001 / PatientCode / Integer / 10 Bytes / Unique Number assigned to each patient
B002 / Patient_Name / String / 20 Bytes / Name of the patient
WARDS
Field Number / Field Name / Type / Size / CommentA001 / WardNumber / Integer / 10 Bytes / Unique Number assigned to each ward
A002 / WardName / String / 20 Bytes / Name of the Ward
A003 / Location / String / 20 Bytes / The floor on which the ward is located
A004 / Capacity / Integer / 10 Bytes / The maximum number of patients allowed
PATIENTS_WARDS
Field Number / Field Name / Type / Size / CommentC001 / WardNumber / Integer / 10 Bytes / Unique Number assigned to each ward
C002 / PatientCode / Integer / 10 Bytes / Unique Number assigned to each patient
DOCTORS
Field Number / Field Name / Type / Size / Commentd001 / Doctor_id / Integer / 10 Bytes / Unique Number assigned to each doctor
d002 / DoctorName / String / 20 Bytes / Name of the Doctor
ASSISTANTS
Field Number / Field Name / Type / Size / Commente001 / Assistant_id / Integer / 10 Bytes / Unique Number assigned to each Assistant
e002 / Assistant Name / String / 20 Bytes / Name of the Assistant