Databases Reference
In-Depth Information
WHEN MERGING TABLES
When you combine third normal form tables, the result might not be in third normal form. For example, both
of the following tables are in third normal form:
Customer (CustomerNum, CustomerName, RepNum)
Customer (CustomerNum, CustomerName, LastName, FirstName)
When you combine them, however, you get the following table:
213
Customer (CustomerNum, CustomerName, RepNum, LastName, FirstName)
This table is not in third normal form. You would have to convert it to third normal form before proceed-
ing to the next user view.
You can attempt to avoid the problem of creating a table that is not in third normal form by being cau-
tious when representing user views. This problem occurs when a column A in one user view functionally deter-
mines a column B in a second user view. Thus, column A is a
determinant
for column B, yet column A is not
a column in the second user view.
In the preceding example, the RepNum column in the first table determines the columns LastName and
FirstName in the second table, yet the RepNum column is not one of the columns in the second table. If you
always attempt to determine whether determinants exist and include them in the tables, you often will avoid
this problem. For example, when the second user indicates that the name of a rep is part of that user's view
of data, you should ask whether any special way has been provided for sales reps to be uniquely identified
within the organization. Even though this user evidently does not need the rep number, he or she might very
well be aware of the existence of such a number. If so, you would include this number in the table. Having done
this, you would have the following table in this user view:
Customer (CustomerNum, CustomerName, RepNum, LastName, FirstName)
Now the normalization process for this user view would produce the following two tables:
Customer (
CustomerNum
, CustomerName, RepNum)
Rep (RepNum, LastName, FirstName)
When you merge these two tables into the cumulative design, you do not produce any tables that are
not in third normal form. Notice that the determinant RepNum has replaced the columns that it determines,
LastName and FirstName, in the Customer table.
In this chapter, you examined the use of E-R diagrams to visually illustrate the relations and keys represented
in DBDL. The
entity-relationship (E-R) model
is an approach to representing data in a database. This model
uses E-R diagrams exclusively as the tool for representing entities, attributes, and relationships. The E-R model
is widely used and forms the basis of some computerized tools, so it is important that you understand how to
use it.
In 1976, Peter Chen of the MIT Sloan School of Management proposed the E-R model; and since then it
has been widely accepted as a graphical approach to database representation and database design. The basic
constructions in the E-R model are the familiar entities, attributes, and relationships, all of which are repre-
sented in E-R diagrams. This section focuses on the standard versions of these diagrams. The versions you
examined earlier in this chapter represent one of the common alternative forms of creating these diagrams
that is particularly convenient for use with DBDL.
In the standard E-R diagrams, entities are drawn as rectangles and relationships are drawn as diamonds,
with lines connecting the entities involved in relationships. Both entities and relationships are named in the
E-R model. The lines are labeled to indicate the type of relationship. For example, in Figure 6-34, the one-to-
many relationship between sales reps and customers is represented as “1” to “n.”
