Database Reference
In-Depth Information
A viable alternative is to post two copies of the grade: one copy will be associated with the student, the
term, and the section, and the other copy will be associated with only the student and the term. The first
copy would be used for the grade verification report; the second, for the full student information report.
Report cards would probably utilize the second copy, although not necessarily.
Thus, you would have the following two grade tables:
338
GradeSection (StudentNum, DepartmentCode, CourseNum,
ScheduleCode, SemesterCode, Grade)
GradeStudent (StudentNum, DepartmentCode, CourseNum,
SemesterCode, Grade)
Because the DepartmentCode and CourseNum in the GradeSection table depend only on the concatenation
of ScheduleCode and SemesterCode, they will be removed from the GradeSection table during the normaliza-
tion process and placed in a table whose primary key is the concatenation of ScheduleCode and SemesterCode.
This table will be combined with the Section table in the cumulative design without adding new fields. The
GradeSection table that remains will be merged with the StudentClass table without adding new fields. Finally,
the GradeStudent table will be combined with the StudentGrade table in the cumulative design without adding
any new fields. Thus, treatment of this user view does not change the cumulative design.
User View 12
Enrollment: When a student attempts to register for a section of a course, you must
determine whether the student has received credit for all prerequisites to the course. If the student is eligible
to enroll in the course and the number of students currently enrolled in the section is less than the maxi-
mum enrollment, enroll the student. With the data already in place in the cumulative design, you can deter-
mine what courses a student has taken. You can also determine the prerequisites for a given course. The only
remaining issue is the ability to enroll a student in a course. Because the system must retain information for
more than one semester, you must include the semester code in the table. (You must have the information
that student 381124188 enrolled in section 2345 in SP14 rather than in FA13, for example.) The additional
table is as follows:
—
Enroll (StudentNum, SemesterCode, ScheduleCode)
The primary key of this table matches the primary key of the StudentClass table in the cumulative
design. The fields occur in a different order here, but that makes no difference. Thus, this table will be
merged with the StudentClass table. No new fields are to be added, so the cumulative design remains
unchanged.
User View 13
Purge: Marvel College retains section information, including grades earned by the stu-
dents in each section, for two semesters following the end of the semester, at which time this information is
removed from the system. Periodically, certain information that is more than two terms old is removed from
the database. This includes all information concerning sections of courses, such as the time, room, and
instructor, as well as information about the students in the sections and their grades. The grade each student
received will remain in the database by course but not by section. For example, you will always retain the
fact that student 381124188 received an A in CS 162 during the fall semester of 2013, but once the data for
that term is purged, you will no longer know the precise section of CS 162 that awarded this grade.
If you examine the current collection of tables, you will see that all the data to be purged is already
included in the cumulative design and that you don
—
'
t need to add anything new at this point.
FINAL INFORMATION-LEVEL DESIGN
Now that you are finished examining the user views, Marvel College can review the cumulative design to
ensure that all user views have been met. You should conduct this review on your own to make certain that
you understand how the requirements of each user can be satisfied. You will assume that this review has
taken place and that no changes have been made. Therefore, Figure A-17 represents the final information-
level design.
At this point, Marvel College is ready to move on to the physical-level design process. In this process, the
appropriate team members will use the information-level design you produced to create the design for the
specific DBMS that Marvel College selects. After it has done so, it will be able to create the database, load the
data, and create the forms, reports, queries, and programs necessary to satisfy its requirements.
