Management Information Systems (33:623:370) 
Professor Eckstein
Economics Department and Honors Program In-Class Exercise 

For most record-keeping, the Economics department of Enormous State University (ESU) can rely on ESU's central information systems.  However, the department has had to keep paper records of special information pertaining to economics majors, especially honors program economics majors.  The department would like to develop its own auxiliary computer information system to keep track of such data.

For each student majoring in economics, the department would like to store a first name, middle name/initial, last name, e-mail address, expected graduation date, and actual graduation date (for those who have graduated).  Every economics major also has a single departmental academic advisor, who is a member of the economics faculty.  The system should be able to remember each student's advisor.  For each faculty member, the system should store a first name, middle name/initial, last name, office room number, telephone extension, and e-mail address.

About 4% of economics majors are enrolled in the department's honors program, which requires them to write a senior thesis.  The department wants to be able to keep track of which students are in the honors program.  For honors students, the department also wants to store the title of the thesis (up to 200 characters text), and the date the thesis was accepted by the department (blank for students who have not finished their theses yet).

  1. Design a database to hold this information, using only one-to-many relationships.
  2. Create a design that does not waste 200 blank characters of storage for the thesis title of each non-honors student.  This design introduces a new kind of relationship, called a subtype (sometimes called a one-to-one relationship, which is somewhat misleading).
  3. Now assume that each honor student has a senior thesis supervisor, who may not be the same as the student's departmental academic advisor.  Incorporate this information into the database.