Database Reference
In-Depth Information
A
APPENDIX
COMPREHENSIVE DESIGN
EXAMPLE: MARVEL COLLEGE
Marvel College has decided to computerize its operations. In this appendix, you will design a database that
satisfies many user requirements by applying the design techniques you learned in Chapter 6 to a significant
set of requirements.
MARVEL COLLEGE REQUIREMENTS
Marvel College has provided you with the following requirements that its new system must satisfy. You will
use these requirements to design a new database.
General Description
Marvel College is organized by department (math, physics, English, and so on). Most departments offer more
than one major; for example, the math department might offer majors in calculus, applied mathematics, and
statistics. Each major, however, is offered by only one department. Each faculty member is assigned to a sin-
gle department. Students can have more than one major, but most students have only one. Each student is
assigned a faculty member as an advisor for his or her major; students who have more than one major are
assigned a faculty advisor for each major. The faculty member may or may not be assigned to the department
offering the major.
A code that has up to three characters (CS for Computer Science, MTH for Mathematics, PHY for Phys-
ics, ENG for English, and so on) identifies each department. Each course is identified by the combination of
the department code and a three-digit number (CS 162 for Programming I, MTH 201 for Calculus I, ENG 102
for Creative Writing, and so on). The number of credits offered by a particular course does not vary; that is,
all students who pass the same course receive the same amount of credit.
A two-character code identifies the semester in which a course is taught (FA for fall, SP for spring, and
SU for summer). The code is combined with two digits that designate the year (for example, FA13 represents
the fall semester of 2013). For a given semester, a department assigns each section of each course a four-digit
schedule code (schedule code 1295 for section A of MTH 201, code 1297 for section B of MTH 201, code
1302 for section C of MTH 201, and so on). The schedule codes might vary from semester to semester. The
schedule codes are listed in the school
'
s time schedule, and students use them to indicate the sections in
which they want to enroll. (You
ll learn more about the enrollment process later in this section.)
After all students have completed the enrollment process for a given semester, each faculty member
receives a class list for each section he or she will be teaching. In addition to listing the students in each
section, the class list provides space to record the grade each student earns in the course. At the end of the
semester, the faculty member enters the students
'
grades in this list and sends a copy of the list to the
records office, where the grades are entered into the database. (In the future, the college plans to automate
this part of the process.)
After an employee of the records office posts the grades (by entering them into the database), the DBMS
generates a report card for each student; then the report cards are mailed to the addresses printed on the
report card. The grades earned by a student become part of his or her permanent record and will appear on
the student
'
'
s transcript.
