-1

So I've volunteer to create a Registering system for my local church's education ministry. It should be able to register new students and keep track of their progress. Here are the requirements I've managed to gather:

  1. The educational institution offers several courses.

  2. Courses have a name and description.

  3. Courses are organized in levels. There are several courses per level.

  4. Courses also have requirements (i.e. other courses that need to be taken first).

  5. A student graduates from a level when it has passed all courses of that level.

  6. If a student cannot pass a course, he may repeat it as many times as he wants/needs.

  7. Students can only take one course per semester.

  8. An inactive student is one that isn't enrolled in the current semester.

  9. Teachers will teach only one course per semester. Teachers may teach a different course each semester.

  10. There could be semesters a teacher doesn't teach.

Now, this is my relational model.

![https://dl.dropbox.com/u/10900918/rmodels.jpg][1]

My questions are:

  1. Are there any tables missing?

  2. Looking at the semester + semester_code_description: is this the best way to do this? Under the assumption that a year has 2 semesters and that each semester have the same start and end months (i.e. semester 1: Aug - Dec, semester 2: Jan - May), is semester_code_description table really necessary?

  3. How could I improve the design?

Sorry I didn't include any arrows. The program I'm using is a mess.

Thanks so much for your valuable time in advance.

Community
  • 1
  • 1
ecr
  • 391
  • 1
  • 3
  • 7

1 Answers1

0

1) Nice job on your design. I don't see any missing tables - it looks like you covered all of your requirements.

2) The semester_description table makes sense to me, whether or not you need it depends on whether you plan to do anything with that data.

3) The requirement "students can only take one course per semester" would imply that the Has_Taken relationship's primary key should be (student_id, semester_id). As it stands now, I could insert two different courses for the same student and semester. Similarly for the Has_Teached relationship.

Some other thoughts:

The "last_whatever" columns in some of your tables will force some extra processing on your actual application. You will need some mechanism to monitor/update those. Another option would be to derive them from your tables. I can get a student's last_semester by finding the semester with the max year/code.

One last consideration, how stable are these courses/descriptions/levels? I worked at a university for several years and our courses would change on a semester basis, forcing us to save an entire copy of course records for each change because we want a student's record to reflect what they actually took at that time.

Here's a little example in your app. Let's say I graduated level 1. Then a year later, the church adds a new course (Course A) to level 1. I will effectively be un-graduated b/c now there are level 1 courses I don't have (Course A).

This may not matter to you if your courses are pretty stable. Good luck!

jeff
  • 4,325
  • 16
  • 27