Database Reference
In-Depth Information
First, consider one of the simpler user views, the course report. (Technically, you can examine user
views in any order. Sometimes you take them in the order in which they are listed. In other cases, you may
be able to come up with a better order. Often, examining some of the simpler user views first is a reasonable
approach.)
Before you proceed with the design, consider the following method. First, with some of the user views,
you will attempt to determine the relations involved by carefully determining the entities and relationships
between them and using this information when creating the relations. This process means that from the
outset, the collection of tables created will be in or close to third normal form. With other user views, you
will create a single relation that may contain some number of repeating groups. In these cases, as you will
see, the normalization process still produces a correct design, but it also involves more work. In practice, the
more experience a designer has, the more likely he or she is to create third normal form relations
immediately.
Second, the name of an entity or attribute may vary from one user view to another, and this difference
requires resolution. You will attempt to use names that are the same.
User View 1
321
Course report: For each course, list the code and name of the department that is offering
the course, the course number, the course title, and the number of credits awarded. This report also includes
the department and course number for each prerequisite course. Forgetting for the moment the requirement
to list prerequisite courses, the basic relation necessary to support this report is as follows:
—
Course (DepartmentCode, DepartmentName, CourseNum, CourseTitle, NumCredits)
The combination of DepartmentCode and CourseNum uniquely determines all the other attributes. In
this relation, DepartmentCode determines DepartmentName; thus, the table is not in second normal form.
(An attribute depends on only a portion of the key.) To correct this situation, the table is split into the fol-
lowing two tables:
Course (DepartmentCode, CourseNum, CourseTitle, NumCredits)
Department (DepartmentCode, DepartmentName)
The DepartmentCode attribute in the first relation is a foreign key identifying the second relation.
To maintain prerequisite information, you need to create the relation Prereq:
Prereq (DepartmentCode, CourseNum, DepartmentCode/1, CourseNum/1)
In this table, the attributes DepartmentCode and CourseNum refer to the course and the attributes
DepartmentCode/1 and CourseNum/1 refer to the prerequisite course. If CS 362 has a prerequisite of MTH
345, for example, there will be a row in the Prereq table in which the DepartmentCode is CS, the CourseNum
is 362, the DepartmentCode/1 is MTH, and the CourseNum/1 is 345.
NOTE
Because the Prereq relation contains two attributes named DepartmentCode and two attributes named CourseNum, you must
be able to distinguish between them. The software used to produce these diagrams makes the distinction by appending the
characters /1 to one of the names, which is why these names appear in the Prereq table. In this example, the DepartmentCode/1
and CourseNum/1 attributes represent the department code and course number of the prerequisite course, respectively. When it
is time to implement the design, you typically assign them names that are more descriptive. For instance, you might name them
PrereqDepartmentCode and PrereqCourseNum, respectively.
