1	Normalisation AS ICT
2	What is Normalisation To normalise a data structure means to organise it in the most efficient way. Benefits: Helps eliminate duplicate data. Allows the structure to be easily amended. Speeds up processing.
3	An example data structure Ward_number Ward_name Number_of_beds Nurse_name Nurse_staff_number Patient_number Patient_name Patient_address Patient_tel Patient_DOB Consultant_number Consultant_name Consultant_specialism
4	Un-normalised data To begin with all the attributes are gathered in one entity. Focus of the entity is very important. In this case it is best to base our focus around the patient, because the patient is integral to the situation. Taken in isolation, nurse could be related to ward and to patient. Ward could be related to nurse and to patient. Consultant could be related to patient, but not nurse or ward. Patient could be related to consultant, nurse and ward. Therefore, patient is the focus.
5	Un-normalised entity: PATIENT PATIENT (Patient_number, Patient_name, Patient_address, Patient_tel, Patient_DOB, Ward_number, Ward_name, Number_of_beds, Nurse_name, Nurse_staff_number, Consultant_number, Consultant_name, Consultant_specialism)
6	Un-normalised structure in Access
7	1^st Normal Form Taking the structure to 1^st Normal Form involves separating any repeating attributes and putting them in entities of their own. A repeating attribute is one where more than one entry may be required within a single record. E.g. items in an order. In this case, a patient would have more than one nurse, and so nurse is a repeating group. We must also include a foreign key in the new entities to link the new entities to the first entity (PATIENT).
8	1^st Normal Form PATIENT (Patient-number, Patient_name, Patient_address, Patient_tel, Patient_DOB, Ward_number, Ward_name, Number_of_Beds, Consultant_number, Consultant_name, Consultant_specialism) PATIENT-NURSES (Patient_number, Nurse_staff_number, Nurse_name)
9	1^st Normal Form in Access
10	2^nd Normal Form To take the structure to 2^nd Normal form we need to focus on the new entities created in the last step. Examine the new entity (PATIENT-NURSES) and remove any attributes which do not depend on the key attributes of that entity. The removed attributes are placed in an another entity. The primary key of this new entity should be the foreign key in the previous entity.
11	1^st Normal Form Reviewed PATIENT (Patient-number, Patient_name, Patient_address, Patient_tel, Patient_DOB, Ward_number, Ward_name, Number_of_Beds, Consultant_number, Consultant_name, Consultant_specialism) PATIENT-NURSES (Patient_number, Nurse_staff_number, Nurse_name)
12	2^nd Normal Form PATIENT (Patient-number, Patient_name, Patient_address, Patient_tel, Patient_DOB, Ward_number, Ward_name, Number_of_beds, Consultant_number, Consultant_name, Consultant_specialism) PATIENT-NURSES (Patient_number, Nurse_staff_number) NURSE (Nurse_staff_number, Nurse_name)
13	2^nd Normal Form in Access
14	3^rd Normal Form To take the structure to 3^rd Normal form we now examine the original entity (PATIENT). Remove any attributes which do not depend on the primary key (Patient_No) and place them into their own respective entities. In this case, we need to separate out WARD and CONSULTANT, as none of their attributes (except for Ward_No and Consultant_No) depend on Patient_No. Foreign Keys must exist in the original entity (PATIENT) to link the new entities to the original one.
15	2^nd Normal Form Reviewed PATIENT (Patient-number, Patient_name, Patient_address, Patient_tel, Patient_DOB, Ward_number, Ward_name, Number_of_beds, Consultant_number, Consultant_name, Consultant_specialism) PATIENT-NURSES (Patient_number, Nurse_staff_number) NURSE (Nurse_staff_number, Nurse_name)
16	3^rd Normal Form PATIENT(Patient-number, Patient_name, Patient_address, Patient_tel, Patient_DOB, Ward_number, Consultant_number) PATIENT-NURSES(Patient_number, Nurse_staff_number) NURSE(Nurse_staff_number, Nurse_name) WARD(Ward_number, Ward_name, Number_of_beds) CONSULTANT(Consultant_number, Consultant_name, Consultant_specialism)
17	3^rd Normal Form in Access
18	Student Task. Normalise: Pupil_Number Pupil_Name Pupil_Address Teacher_Number Teacher_Name Teacher_Address Course_Number Course_Name Number_of_Pupils_on_Course Classroom
19	Solution: 1^st Normal Form: COURSE (Course_No, Course_Name, No_of_Pupils_on-Course, Teacher_No, Teacher_Name, Teacher_Address, Classroom) PUPILS_ON_COURSE (Course_No, Pupil_No, Pupil_Name, Pupil_Address)
20	Solution: 2^nd Normal Form COURSE (Course_No, Course_Name, No_of_Pupils_on-Course, Teacher_No, Teacher_Name, Teacher_Address, Classroom) PUPILS_ON_COURSE (Course_No, Pupil_No) PUPILS(Pupil_No, Pupil_Name, Pupil_Address)
21	3^rd Normal Form COURSE(Course_No, Course_Name, No_of_Pupils_on-Course, Teacher_No, Classroom) PUPILS_ON_COURSE(Course_No, Pupil_No) PUPILS(Pupil_No, Pupil_Name, Pupil_Address) TEACHER(Teacher_No, Teacher_Name, Teacher_Address)
22	Another Example: Customer_Number Customer_Name Customer_Address Customer_Tel DVD_Number DVD_Title Rental_Number Date_Borrowed Date_Returned Rental_Charge
23	Solution: 1^st Normal Form: RENTAL (Rental_Number, Date_Borrowed, Date_Returned, Rental_Charge, Customer_Number, Customer_Address, Customer_Tel) RENTS_DVD (Rental_Number, DVD_Number, DVD_Title)
24	Solution: 2^nd Normal Form: RENTAL (Rental_Number, Date_Borrowed, Date_Returned, Rental_Charge, Customer_Number, Customer_Address, Customer_Tel) RENTS_DVD (DVD_Number, Rental_Number) DVD (DVD_Number, DVD_Title)
25	Solution: RENTAL (Rental_Number, Date_Borrowed, Date_Returned, Rental_Charge, Customer_Number) RENTS_DVD (DVD_Number, Rental_Number) DVD (DVD_Number, DVD_Title) CUSTOMER (Customer_Number, Customer_Address, Customer_Tel)
26	3^rd Example Reg_No Make Model Year Cust_No Surname Initial Address Hire_No Date_Hired Date_Returned Fee_Due
27	Solution: 1^st Normal Form: HIRE (Hire_No, Date_of_Hire, Date_Returned, Fee, Cust_No, Surname, Initial, Address) HIRES_CAR (Hire_No, Reg_No, Make, Model, Year)
28	Solution: 2^nd Normal Form: HIRE (Hire_No, Date_of_Hire, Date_Returned, Fee, Cust_No, Surname, Initial, Address) HIRES_CAR (Hire_No, Reg_No) CAR (Reg_No, Make, Model, Year)
29	Solution: 3^rd Normal Form: HIRE (Hire_No,, Date of Hire, Date Returned, Fee, Customer_No) HIRES_CAR (Hire_No, Reg_No) CAR (Reg_No, Make, Model, Year) CUSTOMER (Customer_No, Surname, Initial, Address)
30	4^th Example Animal_name Animal_id Animal_type D_O_B Gender Description Owner's_name Address Postcode Tel Vaccine Batch_no Date Next_vacccine Vet
31	Solution: 1^st Normal Form: ANIMAL (Animal_id, Animal_name, Animal_type, D_O_B, Gender, Description, Owner's_name, Address, Postcode, Tel) TREATMENT (Animal_id, Vaccine, Batch_no, Date, Next_vacccine, Vet)
32	Solution: 2^nd Normal Form: ANIMAL (Animal_id, Animal_name, Animal_type, D_O_B, Gender, Description, Owner's_name, Address, Postcode, Tel) TREATMENT (Animal_id, Vaccine) VACCINE (Vaccine, Batch_no, Date, Next_vacccine, Vet)
33	Solution: 3^rd Normal Form: ANIMAL (Animal_id, Animal_name, Animal_type, D_O_B, Gender, Description, Owner’s_name) OWNER (Owner's_name, Address, Postcode, Tel) TREATMENT (Animal_id, Vaccine) VACCINE (Vaccine, Batch_no, Date, Next_vacccine, Vet)