Database Reference
In-Depth Information
Q&A
328
Question: Determine the tables and keys required for User View 3. Merge the result into the cumulative
design and draw the E-R diagram for the new cumulative design.
Answer: Figure A-13 shows the new cumulative design.
Course
Major
Department
MajorNum
DepartmentCode
CourseNum
DepartmentCode (FK)
Description
DepartmentCode (FK)
DepartmentName
CourseTitle
NumCredits
has prereq
is prereq
Ofice
Advises
Prereq
CourseNum (FK)
DepartmentCode (FK)
CourseNum/1 (FK)
DepartmentCode/1 (FK)
OficeNum
MajorNum (FK)
StudentNum (FK)
Phone
FacultyNum (FK)
Faculty
FacultyNum
LastName
FirstName
Street
City
State
Zip
CurrentRank
StartDate
OficeNum (FK)
DepartmentCode (FK)
Student
StudentNum
LastName
FirstName
LocalStreet
LocalCity
LocalState
LocalZip
PermStreet
PermCity
PermState
PermZip
CreditsTaken
CreditsEarned
GPA
TotalPoints
Semester
SemesterCode
StudentGrade
CourseNum (FK)
DepartmentCode (FK)
StudentNum (FK)
SemesterCode (FK)
Grade
CreditsEarned
GradePoints
FIGURE A-13
Cumulative design after User View 3
Class list: The system must produce a class list for each section of each course. Space is
provided for the grades. At the end of the semester, the instructor enters each student
User View 4
—
s grade and sends a
copy of the class list to the records office. Assume that, after examining the sample class list report (see
Figure A-2), you decide to create a single table (actually an unnormalized table) that contains all the attri-
butes on the class list, with the student information (number, name, class standing, and grade) as a repeating
group. (Applying the tips for determining the relations to support a given user view would lead more
directly to the result, but for the sake of developing the example, assume you haven
'
'
t done that yet.) The
unnormalized table created by this method would be as follows:
ClassList (DepartmentCode, DepartmentName, SemesterCode,
CourseNum, CourseTitle, NumCredits, SectionLetter,
ScheduleCode, Time, Room, FacultyNum, FacultyLastName,
FacultyFirstName, (StudentNum, StudentLastName,
StudentFirstName, ClassStanding, Grade) )
