Hi purplepit-ga,
Normalizing databases is very important for space and efficiency
reasons, and especially so when they become very large. Below are the
answers to the questions you have posed:
SINGLE TABLE UPDATE ANOMALIES:
Assume single table in this case consists of the following fields:
PatientNo
PatientFirstName
PatientLastName
PatientAddress
PatientTelNo
PatientDob
PatientMaritalStatus
PatientDateRegistered
DoctorFullName
DoctorAddress
DoctorTelNo
ClinicNo
PatientFullName
WardNo
BedNo
WardName
DrugName
DrugDescription
DrugDosage
DrugAdmin
DrugUnitsPerDay
DrugStartDate
DrugEndDate
StaffFirstName
StaffLastName
StaffAddress
StaffTel
StaffDob
StaffSex
StaffPosition
StaffNIN
StaffAllocatedToWard
StaffQualificationType
StaffQualificationDate
StaffQualificationInstitution
This implementation can cause many problems with insertion, deletion,
and modification:
Insertion:
Recording one piece of information, for example a new drug
prescription for a patient requires re-entry of all patient
information, doctor information, hospital-related information, drug
information, general staff information, and even staff qualification
information! Clearly, the only information required would be those
identifying the patient, doctor, drug, staff member. THis can not only
drastically increase the amount of time to complete one record (as a
result of having to find out unnecessary information) but can also
lead to inconsistencies in the data (typos, capitalization issues,
etc.).
Modification:
In order to modify a specific piece of information, such as a
patient's home address, will require making a change to every record
(corresponding to a visit) a patient has made. An even worse case
would be when a piece of data pertaining to a staff member needs to be
updated (such as a nurse who has finished a university degree part
time and has now become a doctor, or a doctor who has moved). In these
cases, every record where that staff member has interacted with a
patient needs to be updated with the new information. For a staff
member who has been working there for just a couple years, this could
mean thousands of record updates!
Deletion:
Deletion is similar to modification, however instead of changing a
piece of information, it is simply being removed altogether. Consider
a patient who has been refused service for some reason (ie. they were
stealing medical supplies when coming to the hospital) - the hospital
has no need for the previous records of this person. Rather than just
deleting patient related information, the length of the record to be
deleted includes far more data that has very little to do with the
patient (such as the qualifications of a staff member). All this sort
of information also must be deleted.
NORMALIZATION TO 3NF:
First, let's define what Third Normal Form actually requires:
1. Meet the requirements of 1NF and 2NF
2. Remove columns that are not fully dependent upon the primary key
1NF requirements:
No duplicative columns
Primary key required
2NF requirements:
No subsets of data can apply to multiple rows
http://databases.about.com/library/weekly/aa091601a.htm
Here are the tables that conform to the 3NF requirements:
**Note: tables with multiple "(primary)" indicators mean that these
fields combine to form a primary key
Patient
PatientNo (primary)
PatientFirstName
PatientLastName
PatientAddress
PatientTelNo
PatientDob
PatientMaritalStatus
PatientDateRegistered
PatientAdmission
PatientNo (primary)
ClinicNo (primary), (foreign)
WardNo (primary), (foreign)
BedNo (primary)
DateAdmitted
DateReleased
Wards
WardNo
WardName
Clinic
ClinicNo
ClinicName
Drug
DrugName (primary)
DrugDescription
DrugDosage
DrugAdmin
DrugUnitsPerDay
DrugPrescription
DrugName (primary), (foreign)
PatientNo (primary), (foreign)
StaffFullName (primary), (foreign)
DrugStartDate
DrugEndDate
Staff
StaffFullName (primary)
StaffAddress
StaffTel
StaffDob
StaffSex
StaffPosition
StaffNIN
StaffAllocatedToWard (foreign)
StaffQualification
StaffFullName (primary), (foreign)
StaffQualificationType
StaffQualificationDate (primary)
StaffQualificationInstitution
Assumptions of above normalization:
1. Patients are assigned a unique number upon their first visit to one
of the clinics
2. Upon admission, patients are assigned to a bed located in a ward,
which is within a clinic
3. Clinics and wards are uniquely identified by their ClinicNo and
WardNo respectively (even though there may be a ward of the same name
in different clinics)
4. Drug names are unique identifiers
5. Drug dosage, administration method, and units are standard for all patients
6. Staff are uniquely identified by their full names
7. Staff are allocated to the exactly one ward
8. Staff complete at most one qualification on a specific date
9.
Hopefully that gives you an understanding of how to convert a
non-normalized databased into one which follows the requirements of
3NF. Please let me know if you are having problems understanding the
above information :)
Cheers!
answerguru-ga |
