Normalization to Third Normal Form (3NF)

Question

I have been given the example data and have been struggling to normalize to 3NF. I think the issue lies with the Tutor Name but this is my final chance at submitting so would like to get it right.

This is how I have normalised it,

3NF

Course

Course Code, Course Title, Level, Mode of Attendance, Course Fee, Start Date, End Date, Department*

Student/Course

Course Code, Student ID, Enrolment Date, Enrolled By

Student

Student ID, Student First Name, Student Last Name, Student Street, Student Town, Student Postcode, Student Landline, Student Mobile, Student DOB

Department

Department, Department Head Title, Department Head First Name, Department Name Last Name, Tutor First Name, Tutor Last Name

Thank you in advance

Answer 1

I feel that you could add an additional Entity in there, one that represents people as a part of this (school-like?) institution, call it "People" (yeah...)

So Tutors, Students, Teachers, Head of Departments, etc are all People. That table will hold Attributes that the sub classes/entities will share (because they're all people, right?): Firstname, Lastname, DoB, etc.

You then refer to this table on your other entities. Eg the Teachers by using the FK PeopleId (or whatever you call it =)

A student is then able to become a tutor and even a Teacher or a Head of Department at some point without being added to simultaneous tables within the database.

To, then, define a relationship between your Course, your Students and your Tutors you'll use a junction table in between that could look like:

------------------
|  CourseTutors  |
------------------
| FK | CourseId  |
| FK | StudentId |
| FK | TutorId   |
------------------

Thats very granular, it will even allow you to have one tutor per student.