LOGICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 160

network-type database. EcoNet accesses this ''up-
to-the-minute'' information using screen scrapping
technology against the IBM mainframe computer
rather than migrating the data in real

3. Summarized Sales tables and Key Performance
Indicators are also bridged to Microsoft SQL Server
relational databases.

time to a

relational DBMS.
2. Completed transaction data is bridged nightly to a
data warehouse holding seven years of sales data in
IBM DB2 Unix.

Ecolab is continually looking for additional informa-
tion to add to the EcoNet application in order to provide
their sales and service people with valuable information
when interacting with customers.

SALESPERSON
PK

Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire

FIGURE 7.1
The entity box from Figure 2.1

SALESPERSON

FIGURE 7.2
Conversion of an E-R diagram entity
box to a relational table

Salesperson Salesperson Commission Year

Number Name Percentage of Hire

Converting Entities in Binary Relationships
One-to-One Binary Relationship Figure 7.3 repeats the one-to-one binary relation-
ship of Figure 2.4a. There are three options for designing tables to represent this
data, as shown in Figure 7.4. In Figure 7.4a, the two entities are combined into one
relational table. On the one hand, this is possible because the one-to-one relationship
means that for one salesperson, there can only be one associated office and con-
versely, for one office there can be only one salesperson. So a particular salesperson
and office combination can fit together in one record, as shown in Figure 7.4a. On
the other hand, this design is not a good choice for two reasons. One reason is that
the very fact that salesperson and office were drawn in two different entity boxes
in the E-R diagram of Figure 7.3 means that they are thought of separately in this
business environment and thus should be kept separate in the database. The other
reason is the modality of zero at the salesperson in Figure 7.3. Reading that diagram
from right to left, it says that an office might have no one assigned to it. Thus, in
the table in Figure 7.4a, there could be a few or possibly many record occurrences
that have values for the office number, telephone, and size attributes but have the
four attributes pertaining to salespersons empty or null! This could result in a lot of
wasted storage space, but it is worse than that. If Salesperson Number is declared