1

I need some guidance on normalisation from 1st Normal Form to 3rd Normal form. I have already set up my database which is working fine but needed to show some normalisation to show that I did do some design. MY only issue is that while going through the normal forms, have I actually normalised the database correctly or not?

Can somebody please have a look at my normalisation and show any changes I should make in their replies?

Screenshort of ER schema - For full size image click here:

ER

And where I started. Screenshot of 0NF:

0NF

Screenshot of 1NF:

1NF

Screenshot of 2NF:

2NF

Screenshot of 3NF:

3NF

Improve this question
10
  • 1
    Upload it to a web service like imgur.com and add the link here or in the question.
    – ypercubeᵀᴹ
    Feb 20, 2013 at 12:46
  • @carlbrooks I upvoted your question, so you con upload images :)
    – dezso
    Feb 20, 2013 at 13:16
  • @ypercube I just deleted my comments so page looks cleaner
    – carl brooks
    Feb 20, 2013 at 14:47
  • Hi, this is carl brooks, I just registered an account and as I already in Stack overflow, my points got carried into here which is cool. Anyway has anyone been tackling with this issue?
    – carl brooks
    Feb 20, 2013 at 18:10
  • read up on Table Inheritance, it will make your life much easier. Student and Teacher and Admin should have the same parent type (Party), and they play different roles.
    – Neil McGuigan
    Feb 20, 2013 at 19:21

1 Answer 1

Reset to default
1

  1. question.noofanswers. Why is it there? Cant you just pull this from answer table at run-time? Or are you enforcing rules on this?

  2. Your student table looks like it has transitive functional dependencies in it but I can't be sure. Is year dependent on courseid? At any rate you are likely to want to break off student records and what courses they take since a student could take multiple courses over time.

Those are the only clear normalization/Codd's rules issues I see with this. I could be missing something though.

Expanding on the second. Take your student table. Presumably there is a natural key somewhere but for now let's use StudentEmail as a candidate key because this can uniquely identify a given student (if there is a practical problem in your case, you can use StudentID as a substitute for argument's sake).

In this case StudentForename, StudentSurname depend functionally on StudentEmail. For every email address there is one and only one forename and surname associated with it. You might want to break away StudentAlias as well because that would give you an ability to have more than one alias per student, but if you will not have that, it breaks no normalization rules.

The problem is that Year depends on (StudentEmail, CourseID) meaning you have a 2NF violation here, where some attributes are dependent on part of the candidate key and others are dependent on the whole candidate key. This really needs to be fixed before you can call it 3NF.

Improve this answer
12
  • Hi, noofanswers is there because we want to determine how many answers a question has so we know if the student has selected less, equal or more answers than the number of answers there are. If more answers selected then negative marking comes into play. Year is just which year the student is in the course e.g. first year student, 2nd year student etc. I did not know a student can take multiple courses at one time. If so I will mention in improvements coz running out of time. Other than what have you mentioned is the normalization ok?
    – carl brooks
    Feb 21, 2013 at 8:16
  • 1
    Other than what I mentioned I don't see any issues with normalization. Also you might want to keep historical data which is another reason for student to be broken off. The year is a 3nf violation as far as I can tell. Updating answer to explain why.
    – Chris Travers
    Feb 21, 2013 at 8:37
  • Also if noofanswers refers to the number of expected replies for a question that's perfectly legitimate and no normalization/codd's rules issues there.
    – Chris Travers
    Feb 21, 2013 at 8:47
  • @carlbrooks Just a minor suggestion: since table names can be quite long, you'd better name this table numberofanswers.
    – dezso
    Feb 21, 2013 at 8:51
  • @ChrisTravers StudentAlias is suppose to be unique for each student, its full meaning is student number but university call it Alias. So each student belongs to an alias. It is possible that 2 students might have the same name so they are uniquely identified by alias
    – carl brooks
    Feb 21, 2013 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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