CS351: Database Design
Exam 1 Case
Fall Quarter, 1998
Advising Management System (AMS)
In the School of Computer Science, Telecommunications, and Information Systems, all students are assigned to a faculty advisor upon matriculation. Faculty advisors are responsible for assisting students with schedule and course planning, career planning, and life. Faculty advising appointments are most often made by the front desk in the CTI main office. Because the School is growing so quickly, it has become necessary to develop a database that will:
- House faculty pre-set advising hours each quarter (e.g., MW, 2-4pm). These will change each quarter.
- Store students assigned to each faculty member
- Log appointments made for each student with the faculty member so that:
- The Dean can see how many student advising appointments a given faculty member has had during a given period of time (month, 3 months, year, etc.)
- A staff advisor can see how many times a given student has seen his/her advisor during a given period of time (month, 3 months, year, etc.)
- Track student requests to change advisors (e.g., from Johnsonbough to Kalin)
- Track advisee load for each faculty member (e.g., # of students assigned to faculty)
- Track advising areas for each faculty member (e.g., CS, HCI, TDC, etc.)
- Trigger a request for the student file to be pulled and placed in the faculty mailbox 24 hours before the scheduled appointment when an appointment is made.
- Provide comment space for faculty to enter advising notes for each student.
- Trigger an e-mail note to the faculty with appointment information for each appointment that is created.
You should be prepared to:
- Develop classification, generalization, and aggregation abstractions for the entities needed in the database
- Develop or evaluate schema from a top-down, bottom-up, inside-out, and mixed strategy.
- Develop view designs from forms and/or a given data set, including relationships and cardinality
- Integrate views as necessary
- Improve the quality of the schema developed or given to achieve optimal completeness, correctness, minimization, expressiveness, and normalization.