Database help?

[Martyn.]

Hey,

I need to design a database for an assignment and I am having a bit of trouble with the whole entity relationship thing, and actually converting that setup to Access.

Basically, I am basing it around the Jujitsu club I attend at the moment. But after using my own knowledge and getting some help from my teacher (who does not really know what she is talking about), I need some of your help before I break my monitor in frustration.

There are about thirty students at the club, two instructors, and there are two classes (juniors and seniors).

I have four tables...

PK = Primary Key
FK = Foreign Key

tblStudent(StudentID (PK), StudentFirstName, StudentSurname, StudentAddress1, StudentAddress2, StudentAddress3, StudentPostCode, StudentPhoneNumber, StudentClass, StudentGrade)

tblInstructor(InstructorID (PK), InstructorFirstName, InstructorSurname, InstructorAddress1, InstructorAddress2, InstructorAddress3, InstructorPostCode, InstructorPhoneNumber, InstructorMobileNumber, InstructorGrade)

tblRegister(RegisterID (PK), StudentID (FK), ClassID (FK), StudentPresent)

tblClass(ClassID (PK), InstructorID (FK), DateOfClass, TimeOfClass, DurationOfClass, ClassType, CostOfClass)

The entity relationship diagram is meant to look something like this (I think?)..., but Access will not let me build it. I am at the stage now that I am just so confused with what is going on.

So basically, I would like someone to give me some advice/help me structure the tables and entity relationship diagram. If you need any more information just ask!

Cheers.

 

[MarcLister]

Think you want to be in the Windows forum not the GD. I've looked at your tables and they look right to me. Not sure about the relationship diagram but that looks OK.

Can you take some screenshots of what you've done in Access then upload them to ImageShack or the like and post the links to the images here so we can see what Access is doing?

 

[Martyn.]

Think you want to be in the Windows forum not the GD. I've looked at your tables and they look right to me. Not sure about the relationship diagram but that looks OK.

Can you take some screenshots of what you've done in Access then upload them to ImageShack or the like and post the links to the images here so we can see what Access is doing?

Cheers for the help.

I would take some screen shots but I could probably explain it better. Basically, there are meant to be "many" students to one "register" according to the ER diagram, bearing in mind this is the ER diagram my teacher gave me. But for some reason when you set the relationship in Access the primary key always has to be the "one" and not the "many".

 

[Mr^B]

You've got the Student/Register relationship round the wrong way, it should be one student to many register.

Register is a table to break up the many-many relationship between student and Class (Many students take Many classes), so the Many relationships always go to the joining table.

 

[Martyn.]

You've got the Student/Register relationship round the wrong way, it should be one student to many register.

Register is a table to break up the many-many relationship between student and Class (Many students take Many classes), so the Many relationships always go to the joining table.

I knew my teacher was wrong! I looked at it logically though, there would be many students on one register wouldn't there?

When you say many students take many classes that confuses me, because there are only two classes (seniors, for over 16's and juniors, for under 16's). So many students take one class?

 

[Mr^B]

Many Classes means that there is a "instance" of a class each week (or day, or however often the classes run) for each type of class (seniors, for over 16's and juniors, for under 16's).

There's only one TYPE of class that they attend, but that one class happens many times.

Any clearer?

 

[Martyn.]

Many Classes means that there is a "instance" of a class each week (or day, or however often the classes run) for each type of class (seniors, for over 16's and juniors, for under 16's).

There's only one TYPE of class that they attend, but that one class happens many times.

Any clearer?

Aha! Makes sense now. Thank you!

How about my table structures, are they ok?

 

[Mr^B]

I'd need more information about what the system is to be used for before commenting further on it.

Do students sign up for a class and then have their attendance checked against it?

If so you'll need to break out "tblStudentClasses" as a sort of attendance register (storing simply StudentID/ClassID, both fields together making a composite key).

This will be a definitive list of which students SHOULD be attending which classes) and match that against "tblRegister" to see when they were missing classes, etc

Have tblClass as a sort of meta-list of the different classes and then having a "tblSession" as an instance of each class (SessionID as a key here, with Date/Time fields too), linking to tblRegister (SessionID, StudentID) to check attendance.

If all that is confusing, don't panic - flesh out more details about what the system is designed to do, what reports/queries you want to run off against it (which is all a database is, after all), and I'll see if I can help you some more.

Welcome to the can 'o' worms that is "good" database design.

 

[Martyn.]

I'd need more information about what the system is to be used for before commenting further on it.

Do students sign up for a class and then have their attendance checked against it?

If so you'll need to break out "tblStudentClasses" as a sort of attendance register (storing simply StudentID/ClassID, both fields together making a composite key).

This will be a definitive list of which students SHOULD be attending which classes) and match that against "tblRegister" to see when they were missing classes, etc

Have tblClass as a sort of meta-list of the different classes and then having a "tblSession" as an instance of each class (SessionID as a key here, with Date/Time fields too), linking to tblRegister (SessionID, StudentID) to check attendance.

If all that is confusing, don't panic - flesh out more details about what the system is designed to do, what reports/queries you want to run off against it (which is all a database is, after all), and I'll see if I can help you some more.

Welcome to the can 'o' worms that is "good" database design.

Well I will be producing a report of the students attendance (which I am hoping to compare against the number of lessons needed to take the next belt eg: 18 to progress to yellow belt), and I will be also making a financial report (just of revenue generated then I will deduct the fixed constant of the rent from that), and any other reports that may seem appropriate nearer the the time. I do not want to make the system that complicated to be honest.

My teacher did mention about adding another "lesson" table, but lesson and class sort of conflicted and I did not really see the need in it at the time.

Thanks very much for the help, appreciate it .

 

[Martyn.]

Is that ER diagram now correct? I am not sure if the one to many relationship with the instructors is the right way round, because I was going to have it so that one instructor teaches one class, for example one instructor teaches the juniors and one instructor teaches the seniors, in which case wouldn't it be a one to one relationship?

Sorry if I seem pretty n00bish with databases by the way!

 

[Martyn.]

 

[Mr^B]

I'd agree with a separate "tblLesson" entity, which acts as an instance (or occurrence) of a Class.

Remove the Date/Time from tblClass and put them in tblLesson:

tblLesson (PK LessonID, Date, Time)

So tblClass holds information which is the same every time the Class is held, and tblLesson holds details about an individual Lesson OF that Class (if you follow).

You can then link Students to an individual lesson in tblRegister by adding Lesson ID in there somewhere:

(StudentID FK, LessonID FK)

No real need to add a generated PK here as the two FKs act as a composite one.

The relationship between Instructor and Class, if one instructor only teaches one Type of class is one-to-one (and therefore should be just one table), but may be worth splitting out for ease of updating, etc.

No problem about seeming noobish (which you don't really) - I've been designing them for 15 years and I still get things wrong/change my mind.

 

[Martyn.]

I'd agree with a separate "tblLesson" entity, which acts as an instance (or occurrence) of a Class.

Remove the Date/Time from tblClass and put them in tblLesson:

tblLesson (PK LessonID, Date, Time)

So tblClass holds information which is the same every time the Class is held, and tblLesson holds details about an individual Lesson OF that Class (if you follow).

You can then link Students to an individual lesson in tblRegister by adding Lesson ID in there somewhere:

(StudentID FK, LessonID FK)

No real need to add a generated PK here as the two FKs act as a composite one.

The relationship between Instructor and Class, if one instructor only teaches one Type of class is one-to-one (and therefore should be just one table), but may be worth splitting out for ease of updating, etc.

No problem about seeming noobish (which you don't really) - I've been designing them for 15 years and I still get things wrong/change my mind.

Thanks for the help. Would the relationship diagram I posted above be right? For some reason I cant get the InstructorID relationship to be one to one when it goes to the tblClass table. I was also thinking, should the InstructorID go in the tblLesson table or remain in the tblClass table, for example Chris would take the Senior Class and Derek would take the Junior Class?

 

[Mr^B]

You don't need ClassID in register, it should go into tblLesson.

Then a link from Register to Lesson, then Lesson to Class.

If the instructor for each Class doesn't change, then I*nstructorID should go into Class, otherwise it should go into Lesson.

 

[Martyn.]

You don't need ClassID in register, it should go into tblLesson.

Then a link from Register to Lesson, then Lesson to Class.

If the instructor for each Class doesn't change, then I*nstructorID should go into Class, otherwise it should go into Lesson.

That ok? You said I should put the InstructorID into the class table if it does not change for each lesson, but I do not particular want to do that as I want all the instructor details and stuff.

Cheers

 

[Mr^B]

That looks about right to me.

The best way to test it all out is, obviously, to start populating it with data and seeing if you can write queries to bring back the information you require.

A simple attendance query for a particular student (eg 31) for a particular class (eg 3) would be something like:

Select Count(StudentID) FROM tblRegister R INNER JOIN tblLesson L ON R.LessonID = L.LessonID WHERE L.ClassID = 3 and StudentID=31 AND StudentPresent = 3

 

[Martyn.]

That looks about right to me.

The best way to test it all out is, obviously, to start populating it with data and seeing if you can write queries to bring back the information you require.

A simple attendance query for a particular student (eg 31) for a particular class (eg 3) would be something like:

Select Count(StudentID) FROM tblRegister R INNER JOIN tblLesson L ON R.LessonID = L.LessonID WHERE L.ClassID = 3 and StudentID=31 AND StudentPresent = 3

Thanks, it seems to be working quite well now. If I have any more questions I will post here again though.

Thanks again, Merry Christmas