Take the 2-minute tour ×

For a relational database that represents the current term enrollment at a large university, what is the ER diagram for a schema that takes into account all the assertions given:

• 100 instructors, 200 courses, and 800 students.
• An instructor may teach one or more courses in a given term (average is 2.0 courses).
• An instructor must direct the research of at least one student (average = 2.5 students).
• A course may have none, one, or two prerequisites (average = 1.5 prerequisites).
• A course may exist even if no students are currently enrolled.
• Each course is taught by exactly one instructor.
• The average enrollment in a course is 30 students.
• A student must select at least one course per term (average = 4.0 course selections).

I have attempted several times to arrive at the answer, but I cannot. All help is valuable to me. I will greatly appreciate an MS Visio diagram (if time can be spared on this question) to help me visualize the solution.

NOTE: I am not looking for RTFM responses.

share|improve this question
This feels a lot like a homework assignment. Any particular aspect of the ERD you're having trouble with? –  opello Nov 4 '09 at 4:03
In the real world, a course (especially a freshman course) may be taught multiple times by different instructors in the same semester. Is this a homework assignment? –  Glenn Nov 4 '09 at 4:04
Glenn: And don't forget the situations in some schools where a course could be taught by grad students (they are both an instructor and a student) and some tutorials for 1st year students may be led or assisted by students much further along in the program. And there's sometimes courses where a single lecture section is shared by multiple instructors. –  FrustratedWithFormsDesigner Nov 4 '09 at 4:19
add comment

3 Answers

up vote 1 down vote accepted

You could start with this, I have re-worded some of your requirements to fit one-to-one, one-to-many and many-to-many relationships.
1. Instructor and student are types of a person.
2. One instructor can teach many courses, a course can be taught by only one instructor.
3. One instructor can mentor many students, a student has only one mentor.
4. One student can enroll in many courses, a course has many students attending.
5. One course can have many prerequisites, a course can be a prerequisite for many other courses.
alt text

share|improve this answer
add comment

The actual counts (100, 200, 800) should be irrelevant on the final ER Diagram, but they should give you a good clue as to the types of relationships (1 to n, n to 1, n to m) that the entities have with each other.

share|improve this answer
add comment

(I'm not doing your homework, but I'd like to help all the same)

Start by listing out what all the 'entities' are. e.g.

  • Instructors
  • Courses
  • etc.

Then think about the relationship between each pair:

e.g. "How do instructors relate to courses? and vice-versa"

  • Does an instructor have one course or many?
  • Does a course have one instructor or many?

Draw each relationship as a line, with the relevant symbol on each end to show the type of relationship.

Tip: If two entities have a many-many relationship between them, then think about whether there is actually some kind of intermediate thing that sits between them.

Then, draw it up neatly, with all extraneous entities and relationships removed, sticking with the exact symbology dictated by your course. (E.g. there are different standards for how ER's are drawn -- your course will have already settled on one of them).

share|improve this answer
Thanks, this is the sort of guidance I was looking for. Makes more sense to me now. –  Israel ANY Nov 4 '09 at 6:03
add comment

Your Answer


