Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: http://i.imgur.com/V41pmKh.png:

enter image description here


And where I started. Screenshot of 0NF:

enter image description here


Screenshot of 1NF:

enter image description here


Screenshot of 2NF:

enter image description here


Screenshot of 3NF:

enter image description here

share|improve this question
1  
Upload it to a web service like imgur.com and add the link here or in the question. –  ypercube Feb 20 '13 at 12:46
    
@carlbrooks I upvoted your question, so you con upload images :) –  dezso Feb 20 '13 at 13:16
    
@ypercube I just deleted my comments so page looks cleaner –  carl brooks Feb 20 '13 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 '13 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 '13 at 19:21

1 Answer 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.

share|improve this answer
    
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 '13 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 '13 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 '13 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 '13 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 '13 at 9:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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