In this exercise, we will design a database for a small college.
We want to keep basic information about courses, instructors, and departments. We need to keep information about students' schedules for the current semester, and their grades from prior courses (we may also store their schedules from prior semesters if we wish). We also want to store instructors' schedules for both the current semester and past ones.
The college consists of departments, identified both by their full names and by unique three-letter codes such as ENG, MTH, CIS, MAN, and so forth. One particular instructor is designated as the chairperson of a department. We want our system to be able to store the current chairperson for each department (we don't need information about past chairpersons).
Each instructor has a home department. We wish to store the home department, and to keep basic contact information on each instructor, including name, office address, email, and office phone.
Courses are identified by a three-letter department code and a three-digit course number, such as ENG101. Each course has a name and a description.
Courses may have multiple sections, which are identified by one additional letter appended to the course name, as in CIS223A. Sometimes there is only one section of a course (in which case it is usually "A"), but sometimes there are multiple sections, as in ENG101A, ENG101B, and ENG101C.
Each semester, a single instructor is responsible for each section. This instructor is usually in the same department as the course, but does not have to be. Each section also has a room number and a time slot, such as MWF10AM, for Monday/Wednesday/Friday at 10 AM. You want to store a table of time slot codes and their meaning. Sometimes a course can have more than one section in the same time slot.
Each semester, a student can take multiple courses. You want to use the database to store student schedules, that is, the sections being taken by each student each semester. Once the semester is over, you want to store the student's grade in each class.
Finally, you want to store contact information for each student (name, campus address, phone number, and e-mail). After their first few semesters, the students declare themselves as having a single major in a department. They may also declare a single minor in a different department. You want the system to store major and minor information for all students. Neither multiple majors nor multiple minors are allowed.
Draw a database outline and an entity-relationship diagram.
Additional complication 1: (straightforward) Add to the database a table of classrooms, and have it remember what room each section is in (this enhancement is straightforward).
Additional complication 2: (more complicated) Modify the database to accommodate "cross listing" of courses -- for example, the same course may be offered as both CIS230 and MTH251.
Additional complication 3: (challenging) Add to the database the ability to remember which time slots conflict with one another. That is, the MWF1PM time slot conflicts with the MW1-2:30PM time slot.