Normalisation Task

Our task was to take a table of data and "normalise" it. I found it very useful to apply my learning about database normalisation forms to an actual table of data - it really helped me consolidate my learning. This will help me use database normalisation forms in future projects, which may be much larger and more complicated.

Here is the original table of data.

Student Number	Student Name	Exam Score	Support	Date of Birth	Course Name	Exam Boards	Teacher Name
1001	Bob Baker	78	No	25/08/2001	Computer Science Maths Physics	BCS EdExcel OCR	Mr Jones Ms Parker Mr Peters
1002	Sally Davies	55	Yes	02/10/1999	Maths Biology Music	AQA WJEC AQA	Ms Parker Mrs Patel Ms Daniels
1003	Mark Hanmill	90	No	05/06/1995	Computer Science Maths Physics	BCS EdExcel OCR	Mr Jones Ms Parker Mr Peters
1004	Anas Ali	70	No	03/08/1980	Maths Physics Biology	AQA OCR WJEC	Ms Parker Mr Peters Mrs Patel
1005	Cheuk Yin	45	Yes	01/05/2002	Computer Science Maths Music	BCS EdExcel AQA	Mr Jones Ms Parker Ms Daniels

I demonstrated 1NF, 2NF and 3NF by splitting the data into 3 tables.

The first table is for students.

Student Number	Student Name	Exam Score	Support	Date of Birth
1001	Bob Baker	78	No	25/08/2001
1002	Sally Davies	55	Yes	02/10/1999
1003	Mark Hanmill	90	No	05/06/1995
1004	Anas Ali	70	No	03/08/1980
1005	Cheuk Yin	45	Yes	01/05/2002

The second table is for courses.

Course Number	Course Name	Exam Board	Teacher Name
1	Computer Science	BCS	Mr Jones
2	Maths	EdExcel	Ms Parker
3	Physics	OCR	Mr Peters
4	Maths	AQA	Ms Parker
5	Biology	WJEC	Mrs Patel
6	Music	AQA	Ms Daniels

The third table is for which students take which courses.

Student Number	Course Number
1001	1
1001	2
1001	3
1002	4
1002	5
1002	6
1003	1
1003	2
1003	3
1004	4
1004	3
1004	5
1005	1
1005	2
1005	6

Finally I wrote SQL to create the tables.

CREATE TABLE students (
  student_number int UNSIGNED NOT NULL,
  student_name varchar(30) NOT NULL,
  exam_score int UNSIGNED DEFAULT NULL,
  support bool NOT NULL,
  date_of_birth date NOT NULL,
  PRIMARY KEY (student_number)
)

CREATE TABLE courses (
  course_number int UNSIGNED NOT NULL,
  course_name varchar(30) NOT NULL,
  exam_board varchar(30) DEFAULT NULL,
  teacher_name varchar(30) NOT NULL,
  PRIMARY KEY (course_number)
)

CREATE TABLE student_courses (
  student_number int UNSIGNED NOT NULL,
  course_number int UNSIGNED NOT NULL,
  PRIMARY KEY (student_number, course_number),
  FOREIGN KEY (student_number) REFERENCES students(student_number),
  FOREIGN KEY (course_number) REFERENCES courses(course_number)
)