Page 27
In the depicted relationships, remember that the primary key in the first table is the foreign key in the second table.
Figure 2.11 depicts a simplified version of a table relationship diagram. It illustrates the
concepts discussed in this section of the book. The diagram contains two entities:
manufacturers and transformers. There are many transformers for one manufacturer, but there does not
have to be a transformer record for each manufacturer. The Manufacturer table contains records
for other electrical equipment manufacturers, and not all manufacturers build transformers.
FIG. 2.11
Table relationship
diagram depicting a
transformer database.
The primary key for the Manufacturers table is the mfg_code field. To relate the table to Transformers, the mfg_code is included in the table. Mfg_code is a foreign key in the table. The primary key of the Transformers table is Ser_#. This table has a one to many but optional relationship to the Transformer Text and Transformer Test tables. The relationships are optional because the transformer does need to have text recorded or a test performed. It is not optional the other way. A transformer does have to exist in order for text to be recorded about it or a test performed.
The Ser_# field, which is the primary key in the Transformers table, is a foreign key in the Transformer Text and Transformer Tests tables. Each of these tables has a composite primary key. The primary key in the Transformer Text table consists of Ser_# and Line_#. Transformer_Tests has a primary key of Ser_# and Test_date.
Now that you have learned some of the database terms you are ready to discuss design principles. The next section covers relational database design principles and normalizing data.
A properly designed set of database tables makes the applications easier to build, improves the quality of the data, and makes the maintenance of the data easier for the user. You should keep some important things in mind when you are designing the database tables and applications. The first and most important is: Will the database support the business needs of the organization? If it doesn't, it should not be built. Does the system allow the applications to quickly access the data? Have you built constraints into the database that protect the data? Do the tables contain redundant data? Does the organization have similar databases? Is the data protected against unauthorized persons? Some of these issues require political decision within the organization. Groups often resist when attempts to combine databases occur. Fortunately, Oracle7
Page 28
and the Developer 2000 product has many features that nullify many of these concerns. These features, such as the database constraints, are discussed in later chapters in this book.
When designing your database tables structure, you should take the following steps:
Page 29
When you are satisfied with the table design, create the tables and begin application development.
Normalization is a procedure where the developer analyzes the data and establishes the table structure. Completion of the exercise ensures that redundant data is eliminated, the files will be as small as possible, and the records will be easy to identify. You normalize your data by using the Rules of Normalization. This consists of several steps. Each step places the data into a specific form or format. The data is rearranged during each step to meet the criteria of the form.
The normalization procedures are as follows:
First Normal FormAnalyze your unnormalized data and identify the attributes that will
repeat. Remove these rows from the table and place them in their own tables. Figure 2.12
illustrates this procedure. The unnormalized set of data has two fields for eyeglass
purchases. These attributes are removed and placed in a table of their own called Eyeglasses
Purchase. The table needs to have a payroll_number field that relates the records back to the
Employee table.
FIG. 2.12
Normalizing data to the
first normal form.
This step alleviates the database of some potential problems. Inexperienced developers often create this repeating row feature in databases. They don't understand the techniques used to combine data from multiple tables, so they try to create one table that can be used for all the data. Consider, for example, a secretary using a table that has 50 fields used to record past employee wage increases. Every time an employee receives a raise, the secretary runs a program that places the value in raise field 49 into raise field 50, and continues the chain until raise field 1 is empty. She then enters the new information into raise field 1.
This procedure is bad because the secretary needs to run a complex, time-consuming program to perform a simple data entry function. Secondly, many of the fields in the employee records