Database Reference
In-Depth Information
In addition to the student number, the semester must be identified to determine credits taken and earned,
grade point average (GPA), and total points each semester. The combination of a department name (such as
Computer Science) and a course number (such as 153) determines a course title and the number of credits.
Finally, the student number, the semester (semester and year), the department, and the course (depart-
ment and course number) are required to determine an individual grade in a course, the credits earned from
the course, and the grade points in a course. (The semester is required because the same course might be
offered during more than one semester at Marvel College.)
327
NOTE
There is a parenthetical comment after CreditsTaken in the section determined by DepartmentName and CourseNum. It indi-
cates that CreditsTaken is the same as NumCredits, which is a column already in the cumulative design. Documenting that the
name you have chosen is a synonym for a name already in the cumulative design is a good practice.
The next step is to create a collection of tables that will support this user view. A variety of approaches
will work. You could combine all the attributes into a single table, which you then would convert to third
normal form. (In such a table, the combination of department, course number, course title, grade, and so on,
would be a repeating group.) Alternatively, you could use the functional dependencies to determine the fol-
lowing collection of relations:
Student (StudentNum, LastName, FirstName, PermStreet, PermCity,
PermState, PermZip, LocalStreet, LocalCity, LocalState,
LocalZip, CreditsTaken, CreditsEarned, GPA, TotalPoints)
StudentSemester (StudentNum, SemesterCode, CreditsTaken,
CreditsEarned, GPA, TotalPoints)
Course (DepartmentCode, CourseNum, CourseTitle, NumCredits)
StudentGrade (StudentNum, SemesterCode, DepartmentName,
CourseNum, Grade, CreditsEarned, GradePoints)
All these relations are in third normal form. The only change you should make involves the
DepartmentName attribute in the StudentGrade table. In general, if you encounter an attribute for which
there exists a determinant that is not in the table, you should add the determinant. In this case,
DepartmentCode is a determinant for DepartmentName, but it is not in the table, so you should add
DepartmentCode. In the normalization process, DepartmentName will then be removed and placed in
another table whose key is DepartmentCode. This other table will be merged with the Department table
without the addition of any new attributes. The resulting StudentGrade table is as follows:
StudentGrade (StudentNum, SemesterCode, DepartmentCode,
CourseNum, Grade, CreditsEarned, GradePoints)
Before representing this design in DBDL, examine the StudentSemester entity. Some of the attributes it
contains (CreditsTaken, CreditsEarned, GPA, and TotalPoints) refer to the current semester, and all appear
on a report card. Assume after further checking that you find that all these attributes are easily calculated
from other fields on the report card. Rather than storing these attributes in the database, you can ensure that
the program that produces the report cards performs the necessary calculations. For this reason, you will
remove the StudentSemester table from the collection of tables to be documented and merged. (If these attri-
butes are also required by some other user view in which the same computations are not as practical, they
might find their way into the database when that user view is analyzed.)
