CS351: Database Design
Exam 3 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, 17 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 faxed to 773.325.7167. 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: email@example.com.
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.
- (60 points) Using the methodologies described in chapters 11 and 12, translate the ER schema below into a relational schema. Feel free to use a high-level, model independent logical schema as a stepping stone to the final relational schema.
- (40 points) Draw navigation schemas in ER form for the following queries:
- List all students assigned to a particular advisor.
- List all appointments for all faculty for a given day so that office workers can pull student files.
- List all available office hours for all faculty who have office hours on Tuesdays.
- List the average number of appointments each faculty advisor has each week for the Dean to review.
- (10 points-Bonus) Write the SQL statements to complete the queries in question 2 above.