Databases Reference
In-Depth Information
APPENDIX
A
COMPREHENSIVE DESIGN
INTRODUCTION
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 has provided you with the following requirements that its new system must satisfy. You will use
these requirements to design a new database.
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 single
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 fac-
ulty 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 Physics,
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 Cre-
ative Writing, and so on). The number of credits offered by a particular course does not vary; that is, all stu-
dents 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, FA10 represents the
fall semester of 2010). For a given semester, a department assigns each section of each course a four-digit sched-
ule code (schedule code 1295 for section A of MTH 201, code 1297 for section B of MTH 201, code 1302 for sec-
tion 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 fac-
ulty 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 gen-
erates 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 stu-
dent's transcript.
