Databases Reference
In-Depth Information
Figure 4-4
SALESPERSON/OFFICE
Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of
Hire
Office
Number
Telephone
Size
SALESPERSON/OFFICE table.
OFFICE entities. Each Salesperson works in one Office. Office, in this case,
identifies a single office cubicle.
There are at least three options for designing tables to represent this data.
The first option is shown in Figure 4-4, with the two entities combined into one
relational table. This design is possible because the one-to-one relationship
means that for one salesperson, there can only be one office associated with the
salesperson and conversely, for one office there can be only one salesperson.
Because of this relationship, a particular salesperson and office combination
could be stored as one record, as shown.
There are three reasons why Figure 4-4 is not a good data design. The first
two can be determined from the diagram in Figure 4-3. First, the very fact that
salesperson and office were drawn in two different entity boxes in the E-R dia-
gram indicates that they are thought of separately in this business environ-
ment. This means that they should be kept separate in the database. Second
is the modality of zero at the SALESPERSON entity. Reading Figure 4-3 from
right to left, it says that an office might have no one assigned to it. In the table
shown in Figure 4-4, a few or possibly many record occurrences could have
values for the office number, telephone, and size attributes but have the four
attributes pertaining to salespersons empty or null. A
null value
is an unde-
fined value, usually used to identify that no value is provided for that attribute.
Even though considered as an undefined value, it is still considered a valid
value and provides useful information by the fact that the attribute is not
defined. This would result in wasted storage space. It also means that the Sales-
person Number cannot be declared to be the primary key of the table, because
there would be records with no primary key values, which is not allowed.
Before going on, there are a couple of points about storage costs and the rela-
tionship between database design and data storage that you should be aware
of. Even though the cost per byte has dropped significantly over the years,
wasted space continues to be an issue deserving consideration in your data-
base design and implementation. Inefficient design and space use can lead to
inefficient indexes and could mean less than optimal performance. That's not
the only performance issue. As database tables (and the database as a whole)
grow, access performance tends to suffer because of the increased volume of
