Help with normalisation, 3NF

Hi guys, at the moment I'm doing an assignment to find out how to normalize data from 1NF to 3NF, iv gotten it at 2NF so far, my tutor tells me that I only need to change a few parts to my draft table for it to be 3NF

Here is a page of the IVAdata(un-normalized data sheet) Click

From the data sheet you can see it has a lot of redundant data(repeated data), so all I have to do is find out how to make it into 1NF to 3NF, at the moment iv managed to get it to 2NF:

And here is a picture of my 2NF result



Ok at the moment I haven't made my database yet, not until I find out how to make it into 3NF (third normal form)



Oh btw I will be using Microsoft Access to make this database.

 

Damned DBMS! Just had this subject the previous semester...f'kinly boring and crazy.

I wish I could help...why not try wikipedia or google?

 

LOL, dam thought ur post was going to help me , last semester? lol I got it this semester , its not that really i like it, its just explaining it lol, and doing some parts well all google says is I have to remove any non Dependant keys??

 

I don't open files I'm not sure of so I'll go by the info you are showing.

Using Access makes no difference.

Assumptions, Tables are:
Person
course
Grades (also ties the two others)

Logic: each Person can have multiple courses, each course can have multiple persons so you need the third to tie the many to many relation

Therefore you do not need the person ID in the course table.

in the third table I would have a primary key.

Also in the third table, I'm not sure of the Q's (grades?) It's likely I would put those in their own table (#4) and tie to the third table with it's primary key. That way you do not need a bunch of columns in the table that may not be used.

Based on the info you show this should reach 3nf

 

OK iv had another look, I have a few small issues I hope someone could help me with,

When you use a Primary key you can't have repeated data,

It wont let me put the same public ID in twice, (dam hard to explain,)

The only way I could have repeated data is to not set the pubic ID as primary, but I need it to be a primary key.


My heads all over the place, my tutor says for 3NF all I need is 4 tables, I have a few ideas of how to get to 3NF but I dont know how to sort out the problem I have above.,

 

post up that table structure of what you have now, there is no public id in the one above.

 

I meant Person ID, dam i messed up there :/

 

Hmm sound interesting,

the Q's are questionnaires

Ok so my first table is good yeah?

So if I dont have a Person ID in table 2 how can I show the results for the person and their course?

 

First table:
Question. What is Residential and commuter?
If that just means does the person live on campus or does he commute, then I would make that one column, something like Resident Y/N (use a bit or bool or what ever access has for a true false)

If you have a set number of questionnaires you could probably get away with leaving it in the tying table (the real name for this type of table is escaping me ) otherwise put them in their own table.

Table 4
QuestionnaireID
Person_CourseID
QuestionnaireName
QuestionnaireResult
What ever info you need on the individual questionnaire

Table 3
Add a Primary Key

The whole reason for the third table is to handle the Many to Many relation between the person and the course.
As such you will use joins to table three to to get a specific Person/course combination.

I added courseName to the course table This gets all courses for someone named "MyName"

Change the where from looking at Person to
Where Course.CourseName = "SomeGreatCourse" will give you all the people who are taking this course.


SELECT Person.Name, Course.CourseName
FROM Person INNER JOIN (Course INNER JOIN Person_Course ON Course.CourseID = Person_Course.CourseID) ON Person.PersonID = Person_Course.PersonID
WHERE (((Person.SurName)="MyName"));

 

Thanks dude, but iv made it in my own way but ty for helping