Am I Normal? Normalization 2

[baggyboy26]

Hello!

I'm sure I'm one of the users of this forum that is not very popular as I'm completely self taught! As a result I have left learning about normalization to last. I know, I know, after reading MANY articles today i now realise how stupid i've been. I think I now have a basic understanding of normalization and as a result, I want to rebuild one of my projects. I've spent a while designing my tables/relationships and was wondering whether someone could spend ten minutes checking to see whether I have understood correctly.

Thanks in advance!

BaggyBoy

Sometimes the answer stares you right in the face!!!

 

[fneily]

Here's the standard reference for normalization:
Fundamentals of Relational Database Design

http://r937.com/relational.html

The more you study, it starts to sink in.

Every table is a single topic. For your case:
tblPupils
PupilID Primary Key
Firstname
Lastname
DOB

tblStaff
StaffID Primary Key
Firstname
Lastname
other person info

tblSubjects
SubjectID Primary Key
Description
Any other subject info

Those are your main tables. Now to join them. Staff teaches subjects so;
tblStaffSubjects
SSID Primary Key
StaffID Foreign Key
SubjectID Foreign Key

Some staff are supervisors to students. So
tblStudentSupervisors
StuSupID Primary Key
PupilID Foreign Key
StaffID Foreign Key

You want to track registration. So
tblRegistration
RegID Primary Key
PupilID Foreign Key
SubjectID Foreign Key
CalendarYear

You will give students assessments. So
tblAssessment
AssessID
PupilID
DateOfAssessment

tblAssessDetails
ADID
AssessID
SubjectID
Score

An assessment will have many test thus the one-to-many relationship between tblAssessment and tblAssessDetails

You tblAssessment violates the first normal form. You have repeating column headings: Autumn1, Autumn2, etc. Drop the number and you'll see the repeating. This leads to empty cells and variable length records. Violation of first normal form.

In your tblRegistrationInformation, you have column headings of Supervisor, MathGroup, EnglishGroup. This looks like, and is, a category/table. You can't have a table within a table in a relational database. So this table is structured incorrectly.

But look at my presentation and see what you think. Maybe I missed something or didn't understand an item.

 

[baggyboy26]

Thanks fneily, I REALLY appreciate your help.

Since your post I have now reread, and then read again the Fundamentals of Relational Database Design Document. I think things are starting to sink in. My delay in responding is because I've been thinking about what you said. I have attached a picture of my desired outcome, with my annotations on it to help aid understanding.

I would look at it before reading on.

For this project, I 'think' my main tables should be tblPupils and tblCalendarYear because our school will have an ever growing list of pupils coming and going in different Calendar Years. My first objective is being able to show which children were on roll in what years.

Secondly, in England, primary schools are mainly judged on children's progress in 4 areas: Maths, Writing, Reading and Science. So each child would have assessments for each one of those 4 subjects, each calendar year. Hence my third table tblSubject. I believe I have also set this up so that I am able to add additional subjects if I choose to.

For each of these subjects, as a school we collect a level of attainment from these assessments six (and always six) times each calendar year. Giving tblAssessments. Now I know that this table violates the first normal form but could be very easily rectified.

And the final table tblRegistrationInformation, I agree, is structured incorrectly. Each school year a child would have a different class teacher, maths teacher, English teacher, and would also be in a different year group (grade?). I'm not sure of the best way to organise this as if I had a table like tblStaff, I'm unsure how to join this so that I could tell an inspector which teacher taught which child in a certain year.

Basically my response/question is, based on the information that I have told you are the tables tblPupils, tblCalendarYear and tblSubject ok? Is it absolutely necessary that tblAssessments does not violate 1NF? And finally, how might the organisation of tblRegistrationInformation be better organised?

Thanks once again for you input, I really appreciate it.

BaggyBoy.


Sometimes the answer stares you right in the face!!!

 

[fneily]

Is it absolutely necessary that tblAssessments does not violate 1NF?" That depends on if you want to learn Access and creating Relational Databases correctly. Will you self-destruct if it isn't? No. Will you have an Access Table if it's not normalized? No. You'll have an Excel List.

You're fighting Normalization. Don't. Relax, study it, accept it, let it become part of you. Notice the structural simplicity of what I presented compared to your web. That's what normalization strives for...the beauty of simplicity.

Years/time do not make good tables. Take your tblCalendarYear. You have PupilID. Now, third normal form asks a simple question. Does all the non-primary keys relate to the primary key? So does PupilID relate to CalendarYearID? No. How is a year, any year, related to a person? How is a person related to a year? Which year - Christian, Jewish, Chinese, etc.? And if the pupil dies what happens to the year? Nothing. Does 2008 go away? No. So year and pupil have nothing to do with each other. So not in table. What you'll be left with is just a bunch of years.

"I'm unsure how to join this" Tables are joined from the primary key in one to a foreign key in another. One-to-one relationship an obvious exception. So in my tables, just take your index finger and follow the connection from one table to another. "I could tell an inspector which teacher taught which child in a certain year." Take your finger starting on tblStaff. That connects to tblStaffSubjects. That connects to tblRegistration. That shows who taught which students for a specific subject in a certain year. Etc for any other analysis.

" Each school year a child would have a different class teacher, maths teacher, English teacher, and would also be in a different year group (grade?)." Again, my tables deal with all this. Especially tblregistration.

 

[baggyboy26]

fneily,

I think your right; I'm fighting normalization, not intentionally, but due to my lack of understanding. I think I'm beginning to see/understand how your latest post reaffirms your original design. Ok, I'll give it my best shot!

Many thanks for your assistance in further my knowledge of normalization.

BaggyBoy


Sometimes the answer stares you right in the face!!!