trying to understand design from 1NF to 3NF

[tigertim71]

Could someone please explain this to me. I have extracted the data from an internet site but can't quite understand a few things relating to table relationships, design and content:

Students Table has following structure (1NF):

studentID
name
dateofbirth
advisor
advisor'stelephone
student
courseID1
coursedescription1
courseinstructor1
courseID2
coursedescription2
courseinstructor2

In 2NF this is split into:

Student table:
studentID
name
dateofbirth
advisor
advisor'stelephone

Student courses table:
studentID
courseID

Courses table:
courseID
coursedescription
courseinstructor

In 3NF it's:

Student table:
studentID
name
dateofbirth
advisorID

Student courses table:
studentID
courseID

Courses table:
courseID
coursedescription
courseinstructor

Advisor table:
advisorID
advisorname
advisortelephone

I just have a few questions relating to this...

1. Why is there a student courses table (is this known as a 'junction' table?) and if so what is it's purpose? What is the relationship between the student table and the student courses table, as well as the courses table to the student courses table (one-to-many??).

2. In the student courses table how are the fields populated in Access. Do all studentID's and courseID's need to be typed in manually (or using a lookup table referring to the original tables) or can this just be left blank?

3. I noticed that there is an Advisor table.advisorID and Student table.advisorID. Is the relationship one-to-many?

4. Would it be possible to skip the Student courses table altogether and just put a courseID as a foreign key in the Student table. If not, why not? Also I would like to know why the advisorID table does not need a similar table such as the Student courses table?

Many Thanks for any help,

Tim

 

[redneckgeek]

Since one student can take many courses, and one course contain many students, you have a "many-to-many" relationship. Thus, the "junction" table. Student 1 may take courses 1, 2, and 3; while student 2 may take courses 2, 3, and 6. Going back to the 1NF example, how would you show the 3rd course for each student? If you tried to put the course ID in the student table, you'd have to add 1 full record for each course the student takes, thus duplicating all of the other info in the record.

One student can only have on advisor, but one advisor can have many students. Your Advisor table is a "primary" table, with a one-to-many relationship to the student table.

 

[georgedwilkinson]

1. Why is there a student courses table (is this known as a 'junction' table?) and if so what is it's purpose? What is the relationship between the student table and the student courses table, as well as the courses table to the student courses table (one-to-many??).

Yes, it is a junction table. It's purpose is to relate many students to many courses. Since a student can have many courses, you cannot know how many columns to include in a Student record to hold the number of courses in a non-normalized (1NF) design. Same with courses. Since a course can be taken by many students, you cannot know how many columns to include in a Course record to hold the number of students in a non-normalized design.

2. In the student courses table how are the fields populated in Access. Do all studentID's and courseID's need to be typed in manually (or using a lookup table referring to the original tables) or can this just be left blank?

In a way, both the Students table and the Courses table are "lookup" tables when viewing the data from the point of view of the other. You definitely need to type in (or import) every Student and you definitely must type in (or import) every course. But when "relating" them to each other, you would simply select one from a list (usually a combo box) in relation to the other.

For an example of how this works, see Pat Hartman's sample of M:M here: Many-to-many example

In your case, you would have a Student in the main form and the junction table in the subform. Alternately, you will have another form with a Course in the main form and the junction table in the subform.

3. I noticed that there is an Advisor table.advisorID and Student table.advisorID. Is the relationship one-to-many?

Yes, it is a 1:M relationship. It is questionable to me whether that is true in real life. It really depends on whether you need to track a student's advisors over time. If you do, it should be a M:M relationship.

4. Would it be possible to skip the Student courses table altogether and just put a courseID as a foreign key in the Student table. If not, why not? Also I would like to know why the advisorID table does not need a similar table such as the Student courses table?

No. Because you cannot keep track of all the "relationships" in 2 tables, as explained in my response to point 1.

I covered my thoughts on the Advisors table in point 3...re-stated: if you only have one advisor at a time for a student and will not be tracking advisors over time, you can store the advisorID in the Students table. This is a design I would never implement because it is likely to break in the future. The fact that you perceived there might be a problem gets you a kudo...kudos.

If you get this (normalization) right, you'll find that making your DB is a joy and that helping you to progress will be a joy. If you get it wrong, I expect to see a lot more of you on the forums and anticipate you'll have a pretty high frustration level.

 

[dkinley]

In 3NF, the advisor table is the link by which you can get the advisor info from the student perspective.

Would not the only way to historically track the different students' advisors over time is to create yet another table to hold AdvisorID and StudentID (and some ordering/dating field)?

Geo, in respect, my question to your comment:

I covered my thoughts on the Advisors table in point 3...re-stated: if you only have one advisor at a time for a student and will not be tracking advisors over time, you can store the advisorID in the Students table. This is a design I would never implement because it is likely to break in the future.

I am a little cornfuzzled on the 'This is a design ...." meaning you recommend keeping a historical table or keeping the AdvisorID in the student table to track the current assigned advisor?

Not to put the screws to you, but asking to add your wisdom to my ignorance .... in your experience, how would it break?

-dK

 

[tigertim71]

Thanks: 1NF-3NF

Many Thanks George and to redneckgeek. I'll try to find some time to set up this db. over the weekend, so I get an idea of what it's like with data added.

It's quite important that I understand these concepts as I'll want to set up a Formula 1 motor racing db. at a later date (and won't be wanting to see any crashes!).

Tim

 

[georgedwilkinson]

Many Thanks George and to redneckgeek. I'll try to find some time to set up this db. over the weekend, so I get an idea of what it's like with data added.

It's quite important that I understand these concepts as I'll want to set up a Formula 1 motor racing db. at a later date (and won't be wanting to see any crashes!).

Tim

Tim, I'm really thrilled to see you are trying to go about this the right way. Properly normalized design is a passion of mine and I think that many of the posts we get here would go away if more people took the time to understand and build their foundations correctly.

If you need any help, give a yell.

 

[georgedwilkinson]

I am a little cornfuzzled on the 'This is a design ...." meaning you recommend keeping a historical table or keeping the AdvisorID in the student table to track the current assigned advisor?

Not to put the screws to you, but asking to add your wisdom to my ignorance .... in your experience, how would it break?

Um, I hope you don't think it would be anything like putting the screws to me, asking a question about my preferences.

I like to watch the show "House". The main character frequently says "everybody lies". I'm not quite as cynical as he is (I hope), but you can be assured that when you get the "requirements" for a system, they either don't think of or don't think they need to tell you all of the requirements.

So, I always err on the side of caution. My assumption is that a Student is going to have multiple advisors throughout their career. Further, I assume that an advisor somewhere is going to get sick and have his/her work load handled by another advisor or co-advisor. Then the user base is going to come back to me and say, is there any way we can keep track of both of these advisors...could you add a field to the Students table to track an "alternate" advisor? Sure, I can, I say, immediately breaking the rules of normalization. And then next year, the Dean is gonna come to me and say, we need to track a student's advisor for every year he goes to school, can you add fields to the Students table for each year? Oh, and we need a co-advisor for every year, too. 4 years later, the system barely works because of all the missed requirements and the Dean of the graduate school calls you and says, you've done a great job tracking the advisors for these new student's undergraduate years, can you add a column for the student's graduate advisor? And so on.

My rule: if I create a table, I only include columns in that table that are "about" the "being" of the contents of the table. If it is born with it, gets one (and only 1) of it along the way and dies with it, or it is integral to the identification of the object stored in the table, then it is a column...if not, it probably goes in another table. Another way of stating that is, data in a table should not change very often.

Does that help?

 

[dkinley]

Hehehe .. thanks ... yes!

I think the politically correct way of saying 'everyone lies' is defined as 'scope creep' but in the ditches we use the term 'idiots'.

I didn't put the two thoughts together when you were referring about the quantity of columns to set aside.

Thanks for the response.

-dK

 

[gemma-the-husky]

tim

we only watch F1 for the crashes!

 

[gemma-the-husky]

seriously, normalising data structures is a pretty logical thing, although it requires a good understanding of what you are tring to model.

from the point of view of a F1 example, as a start say, you need a drivers table, and a teams table. Now consider how drivers are linked to teams

1 team has several drivers, but a driver only has one team. So to model this and link drivers to teams, you then need an entry in the drivers table referring to the team.

This is over simplistic however, since it limits the driver to one team only - if you want to store driver changes over time, then in fact you DO need to allow for multiple teams per driver. Hence a juction table that matches the drivers to teams, and vice versa

Now it starts to get interesting, and you have to consider how you want to model your data in more detail. Do you want to have an annual driver's roster, so you have an entry against each team for drivers in 2007, 2008 etc - or do you merely store the fact the eg Schumacher went to Ferrari in 1998 (or whatever) and left in 2005. Both of these are normal forms, so it is more a matter of taste I think

Is it possible for a driver to drive for multiple teams in a season - could a transfer take place in certain circumstances, as will affect your design, and have general implications for caclulating things like driver points/team points over a season

Overall you are trying to mirror a real-world situation in a way that enables you to manage the data and extract information in the simplest way possible. The "catches" are the occasional out-of-the-ordinary events that your system needs to allow for - as fixing them down the line is many times harder than fixing them at the start

 

[tigertim71]

Hi Gemma,

Thanks for pushing me to do this Formula1db for 2008. I'm not entirely happy with it (see db attached), but it's a start.

1. Can you see any reduntant information or an unnormalised structure and does it conform to 1-3NF? General feedback also welcome...

2. Do I need to turn the two fields (driver and team) in the linkbetweendriversandteams table into primary keys? If so why?

3. I set up a query adding two fields together for totaldriverpoints but when it gets populated with all the 18 races, I don't want to add them all together. Is there another way using a sum function?

4. I could not get the total team points through my total team points query. Any ideas?

5. Relating to the pivot table, I am looking to get total team points and total driver points through this (as an alternative way to queries). I've set up a pivot table for this but it's not exactly giving me what I want. Any ideas?

Thanks,

Tim