Modeling a Uni course scheduler and sequencer

[tkamz]

I have looked at the databaseanswers site, but can't seem to find a model that addresses the issues I am having. My (school) project is to create a product that:

• Creates a schedule for a student's desired courses
• Determines the courses a student must take given the desired course sequence, courses completed, and courses enrolled

Currently I am working on the database. I actually won't be using Access in the end product (LAMP stack, mostly likely), but since I am (VERY) new to databases and database design, I am using it for practice and visualization.


I have come up with a list of "concepts" to help me with my database. However, I am having trouble with a few things:

• I am very scared that my model is totally wrong (this is more of a general issue, obviously)
• I am having a hard time modelling course prerequisites - that is, how do I make/use a Course entity that may have (0 to Many) Courses as prerequisites?
• I am also having difficulty with Course Sequences. At Uni, a student in a given department, with a given concentration, has a sequence that she or he must follow. Given the Course table, a Concentration table, and a Department table, how can I do the Course Sequences?

I have been working on this for a few days and have gotten an Access model done. If possible, I'd like some guidance with these three issues.

I am well under my post limit, so I have zipped my database and have attached it to this post. My original concept list is also in the zip.

I would really appreciate ANY help.

 

[ButtonMoon]

Here are some suggestions - with the obvious limitation that I don't have the opportunity to analyse your requirements in any detail. For course prerequisites I expect you would want a prerequisite table something like this (in SQL):

CREATE TABLE CoursePrerequisite
	(CourseId INT NOT NULL REFERENCES Courses (CourseId),
	 Prerequisite_CourseId INT NOT NULL REFERENCES Courses (CourseId),
	 PRIMARY KEY (CourseId, Prerequisite_CourseId),
	 CHECK (CourseId <> Prerequisite_CourseId));

For sequences perhaps the easiest way would be to add a sequence number attribute to the sequence table.

Regarding your design as a whole I suggest you pay careful attention to what candidate keys you need (e.g. in Course, Class, Department). Have you determined whether your design satisfies 5th Normal Form (in particular the Class table)? If it doesn't then why not?

I suggest you find a better way to do your design. MySQL workbench could be a sensible place to start if MySQL is your platform of choice. Access is missing or possibly incompatible with some of the features you'll find in other DBMSs and design tools. The Access UI doesn't support proper ER diagrams or have any standard notation or language for visualising and sharing data models. It's therefore pretty unhelpful as a design aid unless Jet is going to be your platform.

 

[tkamz]

Thank you for your suggestions. I was not aware of MySQL workbench (really didn't do a whole lot of research, frankly) but it looks like a nice tool. Definitely not using Jet for this project. I'll give workbench a go.

The model you came up with for the prerequisite table has cleared up some of the conceptual issues I was having - I will give this a shot as well.

I actually started reading about normalization a day or two after beginning this project; I think I understand the process and, more or less, the normal forms. The real issue that I have with normalization at the moment is how to start - the books I have been going through explain the process fairly clearly using simple example tables with very simple relationships, which are then normalized step-by-step. However, I have not really been able to figure out, so far, how these tables came into existence in the first place.

In any case, I have the weekend to work on this without distraction. I will follow your suggestions and post back here after working on this some more.

Thanks!