Databases Reference
In-Depth Information
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—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:
319
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 following 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 there are two attributes named DepartmentCode and two attributes named CourseNum, you must be able to distin-
guish 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 imple-
ment the design, you typically assign them names that are more descriptive. For instance, you might name them
PrereqDepartmentCode and PrereqCourseNum, respectively.
The DBDL version of these tables is shown in Figure A-7.
Department (DepartmentCode, DepartmentName)
Course (DepartmentCode, CourseNum, CourseTitle, NumCredits)
FK DepartmentCode
→
Department
Prereq (DepartmentCode, CourseNum, DepartmentCode/1,
CourseNum/1)
FK DepartmentCode, CourseNum
Course
FK DepartmentCode/1, CourseNum/1
→
→
Course
FIGURE A-7
DBDL for User View 1
The result of merging these relations into the cumulative design appears in the E-R diagram shown in
Figure A-8. Notice that the Department and Course tables have been merged with the existing Department and
Course tables in the cumulative design. In the process, the attribute DepartmentName was added to the
Department table and the attributes CourseTitle and NumCredits were added to the Course table. In addi-
tion, the attribute DepartmentCode in the Course table is a foreign key. Because the Prereq table is new, it
was added to the cumulative design in its entirety. Notice also that you do not yet have any relationships among
the entities Student, Major, Faculty, and Semester.
