2

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.

Example Data

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

  • Is the tutor meant to be a guy that helps each student? I mean, is it a tutor per student, a tutor per every X amount of students or a tutor per Course? (just asking out of sheer ignorance [where I come from there's no tutors, you're on your own, good luck. That's why]) – Nelz May 9 '17 at 14:19
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.