Databases Reference
In-Depth Information
INTRODUCTION
As you've learned, several steps are involved in designing a database to support
a business and business applications. You must collect business data in the form
of business documents, employee interviews, and other data sources. You use
this data to generate an entity-relationship (E-R) diagram that describes the busi-
ness and its requirements. From this diagram you will create the logical and then
physical design of your database.
In this chapter, we'll focus on the next step in the process: identifying the
relational tables you will create based on the entities in the E-R diagram and
then going through a process known as normalization, which will help you iden-
tify duplicate data and optimize data storage. Your final result is a finished rela-
tional design from which you can identify the database objects you need to
implement your physical database.
4.1 Designing Relational Tables
The conversion of E-R diagrams to relational tables is a straightforward process.
Typically, each entity converts to a table. For many-to-many relationships, each
associative entity also converts to a table. Attributes become table columns, at
least those attributes that apply to business and application requirements. Dur-
ing the process, rules must be followed to ensure that foreign keys appear in
their proper places in the tables.
4.1.1 Converting a Single Entity
We'll start with the easiest example, converting a single entity to a table. Figure 4-1
shows a single entity from a representative E-R diagram.
The entity in this situation is named SALESPERSON. The process of creating
a table based on this entity is relatively simple. You create a table, typically with
Figure 4-1
SALESPERSON
*
Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire
SALESPERSON entity.
