Database Reference
In-Depth Information
Q&A
Question: In the Wrote table, Sequence is not part of the primary key. In the Copy table, CopyNum is part of
the primary key. These fields seem to play similar roles in tables. Why is there a difference?
Answer: In the Wrote table, there will only be one row with a given book code and author number. The
sequence number simply helps ensure that the authors for a given book appear in the correct order when
listed in queries and reports. It is not necessary in distinguishing one row from another. On the other hand,
in the Copy table, there can be multiple rows with the same branch number and book code combination, and
with the same or a different condition and price. The copy number is essential to distinguish one copy of a
given book at a given branch from another.
201
PHYSICAL-LEVEL DESIGN
After the information-level design is complete, you are ready to begin the physical-level design process by
implementing the design for the specific DBMS selected by the organization.
Because most DBMSs are relational and the final information-level design already exists in a relational
format, producing the design for the chosen DBMS is usually an easy task
you simply use the same tables
and columns. At this point, you also need to supply format details, such as specifying that the CustomerNum
field will store characters and that its length is three.
Most DBMSs support primary, alternate, secondary, and foreign keys. If you are using a system that sup-
ports these keys, you can use these features to implement the various types of keys that are listed in the final
DBDL version of the information-level design. When working in DBMSs that do not support these keys, you
need to devise a scheme for handling them to ensure the uniqueness of primary and alternate keys. In addi-
tion, you must ensure that values in foreign keys are legitimate; they must match the value of the primary
key in some row in another table. For secondary keys, you must ensure that it is possible to retrieve data
rapidly on the basis of a value of the secondary key.
For instance, suppose you are implementing the Employee table shown in Figure 6-1 and it has the fol-
lowing DBDL:
—
Employee (EmployeeNum, LastName, FirstName, Street, City,
State, Zip, WageRate, SocSecNum, DepartmentNum)
AK SocSecNum
SK LastName
FK DepartmentNum
fi
Department
The Employee table uses the EmployeeNum column as its primary key, the SocSecNum column as its
alternate key, the LastName column as its secondary key, and the DepartmentNum column as a foreign key
that matches the DepartmentNum column in the Department table. You must find a way for the DBMS to
ensure that the following conditions hold true:
Employee numbers are unique.
Social Security numbers are unique.
Access to an employee
s record on the basis of his or her last name is rapid. (This restriction
differs in that it merely states that a certain type of activity must be efficient, but it is an impor-
tant restriction nonetheless.)
'
Department numbers must match the number of a department currently in the database.
When the DBMS cannot enforce these restrictions, who should enforce them? Two choices are possible:
the users of the system or the programmers. If users must enforce these restrictions, they must be careful not
to enter two employees with the same EmployeeNum, an employee with an invalid DepartmentNum, and so
on. Clearly, this type of enforcement would put a tremendous burden on users.