CS351: Database Design
Exam 2 Case
Fall Quarter, 1998
This is an exam. All work is to be completed individually, without discussion with or help from your classmates. The exam is due at 11:40am, Tuesday, 27 October, 1998. Exams may be hand-delivered to either of my offices (SAC 284 or CSTC 500) by that time, e-mailed by that time, or turned in at DPC 8012, the Tuesday classroom. Exams may be handwritten/hand-drawn, or may be computer generated. I can read Visio 5.0 files, GIF, JPEG, HTML, and Word files. Questions about the exam should be mailed directly to me at: firstname.lastname@example.org.
Advising Management System (AMS) Case
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.
- (35 points) Develop a data flow diagram (F-schema) of the scenario above.
- (35 points) Refine the F-schema you just created and the D-schema given below, using the Joint Data and Functional Analysis methodology described in section 9.2 of the text.
- (30 points) Derive operation and navigation schemas for the four operations in the case scenario.
Asterisks beside attributes indicate identifier. Thanks to Akiko Nojiri
for this diagram, from Exam 1.