Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    8

    Question Need a opinion/advice about university students enrollement database system

    Hello,

    I was wondering if someone could help me a bit here. Im trying to
    desing an electronic university students enrollement system.

    The system requirements are as follows:
    1.Students can enroll themself for 1 or more courses. And a course can be taken from 1 or more students.
    2. There are 2 levels of courses. Level 1 is a course of 1 day and level 2 is a course of 5 days.
    3. The students will be divided in groups. So 1 students can be in 1 or more groups and a group can consist of 1 or more students.
    4. Teacher can teach 1 or more courses but a course can only be given by 1 teacher.
    5. The course will end with an exam.
    6. The students can take the exam only if they attend to the minimum required days of the course. If a student is taken a level 1 course, they must attend 1 day to the course. If a student is taken a level 2 course, they must minimum attend 3 days of the course.
    7. Students that passed the exam must receive an diploma.

    The relationships between student and course is a many to many relationship, so i introduced a table between them that i called course enrollment that have as PK student_id and course_id.

    The relationship between student and group I also introduced a table between them that I called StudentsGroup and I set student_id and group_id as PK.

    The relationship between Group and Course I have it as a 1 to many relationship, because 1 course can be given to many groups, ( example the course Basic of Accounting can be given to Group A and Group B) But in a group can only 1 course be given. (example in Group A can only Research be given)

    The relationship between teacher and course i have as 1 to many.

    And I have a entity named Level that has as attributes level_id and level_description that is attached to the attribute level_id in the course entity/table.

    Now I don't know where do I have to put the attendance issue and the level requirements issue to fit in the erd.

    Could someone PLEASE HELP ME with this erd.
    Attached Thumbnails Attached Thumbnails erd.JPG  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    attendance is a child of course_enrolment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    43
    I think you first need to clarify what is the relationship between student and courses and between a group and a course.
    I suppose that it is clear that a group has many students and a student can be in many groups.

    However if you relate group with course, student with course, and student with group, I think that you can't enforce the integrity of your database then.
    Lets say Group A will have only the course Science and Joe is in Group A. Nothing stops you at database level from making the mistake of connecting Joe with the course of Art, a course that he's not following because he's not in the Art Group (Group B).

    In your initial text you said,
    Quote Originally Posted by roxys
    1.Students can enroll themself for 1 or more courses. And a course can be taken from 1 or more students.
    ...
    3. The students will be divided in groups. So 1 students can be in 1 or more groups and a group can consist of 1 or more students.
    ...
    The relationship between Group and Course I have it as a 1 to many relationship, because 1 course can be given to many groups, ( example the course Basic of Accounting can be given to Group A and Group B) But in a group can only 1 course be given. (example in Group A can only Research be given)
    This is why I have the table Student_Group which is more or less the same as Student_Course. All that I'm saying is that you shouldn't have both.
    In your schema though you didn't have both.

    I changed the relationship between the Teacher and the Course as well.

    Please note that I'm no expert in Database Design, I know there are people on this forum with much more experience than me.
    Attached Thumbnails Attached Thumbnails StudentCourses.JPG  

  4. #4
    Join Date
    Oct 2008
    Posts
    8
    As far as the attendance goes, you need to represent the act of a student attending a single class day in your schema.

    I think you would be good with a linking table between students and classes that included date information.

    This is similar to the enrollment table, but the relationship is different.

    Your predicate for the enrollment table would be
    "Student A is enrolled in Class X.",
    whereas your predicate for the attendance table is
    "Student A attended Class X on Day 1/1/2000."

    You have several options, though:

    You could choose to leave these as separate tables. This model does not assume that a student must be enrolled in a class to attend it. (Useful if there is an auditing system, for example, where students attend classes but don't wish to receive credit via graded assignments, and so are not "enrolled" per se.)

    Or you could make this attendance table dependent on the enrollment table, thus requiring a student to be enrolled in a class before the database will accept the student attending it.

    With this approach, it's probably best to assign IDs to the enrollment table. So that "John Doe is enrolled in Basic Accounting" is Enrollment Record #1, and then your attendance table would have records like this:
    1 1/1/2000
    1 1/2/2000
    and so on.

    Though there's nothing wrong with
    John Doe - Basic Accounting - 1/1/2000
    John Doe - Basic Accounting - 1/2/2000
    as long as the PK of enrollment is made up of both attributes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •