Databases Reference
In-Depth Information
SELF-CHECK
•
Describe the information you need to convert related entities into
related tables and how you determine primary and foreign key
columns.
•
Explain why an additional table is needed when converting a many-
to-many entity relationship into related tables.
•
In a binary one-to-zero-or-one relationship with no other related
entities, explain how can you determine which should be the refer-
enced and which should be the referencing table.
4.2 Comparing Relational Designs
So far, we have (for the most part) taken entity relationships out of context to
focus on the conversion process. You might find it easier to understand the
conversion process between entities and relational tables if you could see exam-
ples based on more complete E-R diagrams. It also puts the process into more
of a real-world context, letting you see how it relates to business models you
might need to develop. We're going to convert the E-R models of two ficti-
tious companies, General Hardware Company and Good Reading Bookstores.
This chapter assumes that you can read and understand the E-R diagrams used
in each case.
Keep in mind that you are still working in a pencil-and-paper mode, either
literally or using a design or draw program. This is where you should work
through your “what if . . .” scenarios and try out different data and relation-
ship combinations. Your only real expense when making changes now is time.
However, if you need to make changes to the model after you start imple-
menting the physical database, the situation can become very expensive.
Depending on where you are in the process, it might mean having to recreate
the database or reload data. It might also mean having to redesign and even
recode your application.
4.2.1 Designing General Hardware
Figure 4-19 is the General Hardware E-R diagram. It is convenient to begin the
database design process with an important, central E-R diagram entity, such as
salesperson, that has relationships with several other entities.
Thus the relational database in Figure 4-20 includes a SALESPERSON table
with the four Salesperson attributes from the SALESPERSON entity box (plus
the Office Number attribute, which we will return to shortly). Looking to the
