Normalisation

NORMALISATION

You are sometimes asked in an exam to re-organise data from a flat file format into a normalised relational database with tables linked using primary and foreign keys. If you are asked to do this in an exam remember to make it clear which were the key fields and foreign keys. You would also have to state the table name.

Normalisation

• A staged (mathematical) process (1) which removes repeated groups of data and inconsistencies. (1)

• Simplifying data structures (1) so that attributes in each table only relate to the entity. (1)

e.g. Key Fields - Underlined

Foreign Keys - (#)

Medical Practice

A large medical practice uses a relational database management system for storing patient records and running their appointment systems. The practice has several doctors and many patients. Each patient can make one or more appointments with a doctor. The practice receptionist records which patient has made an appointment with which doctor and the time and date of the appointment.

One table in this database could be

DOCTOR (Doctor Code, Name, Room, Tel Num)

Other tables could be

PATIENT (Patient Code, Name, Address, DOB, Gender)

APPOINTMENT(Appointment Code, Patient Code#, Doctor Code#, Time, Date, Room)

WARD( Ward no, NumberOfBeds, StaffId)

PATIENT ( Patient no, Name, illness, admission date, consultant, address, contact, Ward no)

College

Student [Student No, Student Name, Date of birth, Gender, Course No#]

Course [Course No, Course Name, Lecturer No#, Lecturer Name]

Problems associated with data not being normalised

Data is duplicated

causing unnecessary waste of storage space.

Data inconsistency

if data is stored more than once there may be differences [eg in spelling] or transcription errors.

• Example J Evans might be mistyped as 'J Evins' or the course code 186 might be mistyped as 816. The computer will not know which one is correct.