Previous | Table of Contents | Next

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.

Database Tables

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:

  1. Decide what your database represents. For example, is the intent of the database to model transformers, or is it to model all electrical equipment with transformers being one of many types of equipment?
  2. Identify the entities in your database. Examples of an entity is a transformer, customer, or a vehicle.
  3. Identify the characteristics or attributes of the entities in the database. This includes size of the values and whether they are alphanumeric or numeric. If they are numeric, do the values contain decimals? Do the attributes contain specific values such as male or female?
  4. Place the attributes into logical sets. Place the unique attributes of the object into a base table and attributes that can be repeated into related tables. A unique attribute is the last name of the employee. A repeating attribute is the instances of sick leave taken by the employee.
  5. Decide how the various sets of data are related. Be certain that the sets have a value(s) that makes the record unique. The sets in the related tables should have values that relate the record to the base table.
  6. Perform a normalization exercise on the sets of data and establish your tables. See next section for the Rules of Normalization used to normalize the database
  7. Identify the fields that will be indexed. All primary key and foreign key fields should be indexed. You should also identify the fields that are not keys but are often used to identify the record. An example of this type of field is the location of a transformer. This attribute is often used to identify the particular transformer at that location. The identified nonkey fields that are often used should also be indexed. This will increase the performance of your queries.
  8. Identify fields that must always contain a value of a field that contains specific values. The primary keys are always fields that must contain a value. They are called "not null" fields because they cannot contain null or empty values. An example of a primary key is a payroll number. A field such as gender is an example of a field that contains specific values. This step will help you to identify constraints that can be placed on the data base.
  9. Prepare a table relationship diagram of the tables. Figure 2.11 contains an example of a simplified diagram.
  10. Study the diagram carefully. Think about the queries the users will execute against the tables. Queries are SELECT statements that retrieve data from the database. They are discussed in the next chapter. Complex queries will cause users a trouble. Think about complexities in the data structure that will cause application development problems. This step can cause you to avoid problems later on in the application development cycle. It is much easier to change your database design now rather than when you have several applications developed.

Page 29

When you are satisfied with the table design, create the tables and begin application development.

Normalizing Your Data

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

Previous | Table of Contents | Next