Normalisation

[Slice'N'Dice]

Hi. I would just like to check with you all whether my entity relationship is to 3rd form normalisation.

My tables are as follows:

Student(Student ID,Student Forename,Student surname, tutor ID, House, Form, Form Room, Year Group)

Card(Student ID,Week ID, number of merits, number of demerits, number of crosses, number of lates)

House(Student ID,number of house points, extra co points)

Tutor(Tutor ID, title, tutor forename, tutor surname, tutor room, form)

Many thanks.

 

[Andrew Fergus]

Hi
I'm not familiar with '3rd form normalisation' but reading up about it here makes me think the relationship between the students and tutors and possibly the relationship between the students and houses do not conform.
Just my opinion
Andrew

 

[Slice'N'Dice]

Oh yes, those points are quite true. I'll go over my tables a few times more.

Thanks.

 

[SydneyGeek]

My 2 cents...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Students		Cards		Weeks		Houses		Student Houses		Tutors		Tutorials
2	Student ID		Student ID		Week ID		House ID		ID		Tutor ID		Class ID
3	Student Forename		Week ID		Term		House Name		Student ID		Title		Tutor ID
4	Student surname		Merit		Week No		Housemaster		House ID		Tutor forename		Student ID
5	Form		Demerit						House Points awarded		Tutor surname
6	Form Room		Cross						Extra co points awarded		Tutor room
7	Year Group		Late								Form
Sheet1

You need a couple of join tables to handle the many-to-many relationships like Students to Tutors. I've also changed some of the field names -- you shouldn't try to create calculations in tables. One method that works for me is this:
1. Create a sample table in Excel
2. Remove any duplication in the rows
3. Remove any duplication in the columns (you may need to create additional fields -- eg Months instead of Jan, Feb, Mar...)
4. Any fields not directly related to the table name / key field should go in another table.
5. Create join table(s) to resolve many-to-many relationships. (eg Students / Tutors needs Tutorials to link them).

Here's a link to a Microsoft download, which goes through the process of normalising the Northwind database

Hope this helps

Denis

 

[Slice'N'Dice]

Thanks SydneyGeek.

That really helped loads!

(Thanks for the link!)