Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm designing a database where I have a Course table and and Professor table. The business rules are :

1) A professor can teach many courses. (This is obvious).

However, I get stuck in the second rule.

2) On a given semester, a course can be offered on different schedules, each of those offerings can be taught by a different professor. For example a typical freshman course on calculus has about 100 students every semester. The course is divided into 4 sections, each has 25 students and a different professor.

I would like to treat each combination of (course,professor) as a unique entry.For example, (Calculus , Prof Joe) and (Calculus, Prof Jhon) are two different courses. How do I implement this? Thank you

share|improve this question

closed as off-topic by Jack Douglas Jul 25 '13 at 13:06

If this question can be reworded to fit the rules in the help center, please edit the question.

    
What about of having an entity/table (named Offerings or Sections) with the attributes/columns you mention (semester, course, professor, schedule)? The (semester, course, professor) combination seems good as a unique key. –  ypercube Jul 24 '13 at 12:09
    
This question is too basic for dba.se, I see you've asked on SO too, which is probably the better place. –  Jack Douglas Jul 25 '13 at 13:06
add comment

2 Answers

You are dealing with a many-to-many relationship, and that is where the uniqueness should be. Consider a course session table, joining professor and course. This gets more complicated if you have a team teaching a session. This would require an additional subtype or relational table.

The join table should contain all information unique to the session. It may have have relations to additional entities like semester, classroom timetable, etc.

share|improve this answer
    
so you mean something like this:Course { course_code course_name Primary key(course_code) } Professor { prof_id prof_name Primary key(prof_id) } Schedule { course_code prof_id Primary key(course_code, prof_id) } –  Joe Jean JJ Jul 24 '13 at 13:50
    
@JoeJeanJJ Yes, that would be a good starting point. –  BillThor Jul 24 '13 at 23:29
add comment

I think you need at least three tables:

  1. Professor
  2. Course
  3. Section

Professor A and Professor B can both be teaching different Sections of the Course Intro to Biology, one on MWF at 1pm in C102 and the other on TTh at 9am in C103. So:

  • Professor table: ProfessorID, Lastname, Firstname, etc.

  • Course table: Department (foreign key) and Course number (BIO 1001) as your key, with descriptions, etc.

  • Section table: Department, Course, and Section number (BIO 1001 01) as your key, with professor (foreign key), day, time, room (foreign key), etc.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.