Page 30
do not contain values because the records were established to hold increases over a 25-year period. These blank records take up space in the database that would be better used for other purposes.
By normalizing the secretary's employee file to the first normal form, you can alleviate all of these problems.
Second Normal FormThis step looks at the keys to the tables. You must verify that all
the fields in the table are dependent on the entire key. If you find fields that are not, these
fields should be placed in their own table. In Figure 2.12 the Employees table contains a field for
the department name. The department name is not dependent on the primary
key, payroll_number. Department name is dependent on dept field. This violates the rule that
all values must be dependent on the key. To achieve the second normal form, you should
remove the dept_name field from the Employee table and create a new table called Departments.
The primary key to the Departments table is dept. The Employee table is related to the
Departments table by the common dept field. Figure 2.13 shows the database after the above has
been performed. The database is now in second normal form.
FIG. 2.13
Normalizing data to the
second normal form.
Performing this step in the normalization process removes redundant or excess data from the database. In the first normal form, the department name is carried in each employee record. The department name is only recorded once in the second normal form. It is recorded in the department's record only.
Third Normal FormNormalizing the data to the third normal form means that all the nonkey columns of a table must be dependent on the primary key and are independent of each other. The model in this discussion meets this criteria.
Performing the Rules of Normalization helps you ensure the proper setup of your database.
Page 31
The database design approach based on the Rules of Normalization is the time-tested method of designing a relational database. Now that Oracle8 is ready to enter the business computing environment with its new object technology, changes to design approach will likely occur. In the relational approach the entity and its attributes are identified. These attributes are then placed in a series of related tables. The developer worried about data redundancy, minimizing disk space usage, and data integrity. The result of the procedure is a set of tables related by primary and foreign keys.
As you will see in the next several chapters, acquiring and modifying data from multiple tables can be complex. Novice users sometimes have difficulty writing the script or statements needed to retrieve the data from multiple tables. A database based on objects eliminates some of these difficulties for the user and developer.
When designing a database based upon an object, the developer does not have to worry about multiple tables and their relationships. The developer identifies the attributes needed by the object. These objects are linked together through the definition of the object. When the object is created, Oracle places the attributes in different areas. They are related through a pointer rather than primary and foreign keys. The attributes in the database can be identified and extracted through the use of a common object. This reduces the complexity of data retrieval for the user and developer. The user and developer no longer need to know the names of the tables and how they are related. The developer does not use the Rules of Normalization and does not create related tables. Oracle8 may do this, but it is transparent to the developer. The retrieval query and update scripts will become less complex as a result.
The problem with the object approach is maintenance of the database. A true relational database such as Oracle7 is very forgiving to the designer. If the designer forgets a field, it is very easy to add it to the table. Oracle8 is reintroducing the pointer. The pointer is similar to the one used in heirarchial databases. This possibly reintroduces into Oracle8 some of the problems caused by hierarchial databases. It is my belief that DBA's and developers will have a much more difficult time restructuring the database by using Oracle8. This does not mean I am not anxious to use the object-oriented features of Oracle8. It does mean that we as designers and developers need to do our homework up front.
The purpose of this book is to offer a practical approach to the design of an Oracle database. This approach is the one portrayed in this chapter that uses the Rules of Normalization. When Oracle8 begins to enter the mainstream computing environment, I believe you will see more emphasis of object-oriented design. Until then, it is best for the novice developer to stick with proven methods.
A database consists of the database objects and the applications that affect them. There are a large variety of database objects. The tablespace defines the amount of disk storage allowed. The table contains a record for each instance or occurrence of the entity. The record contains a number of attributes that have a specified data type and length.
Databases use mini-tables called indexes to enforce uniqueness and increase performance. Other useful database objects are views, which are virtual database tables, and synonyms, which are alternate names for a table. Before a user can create any of these items, a user account must be created and privileges granted to the user account.
Page 32
Relational database table records have unique keys that make the record unique from all others in the database. This unique key is called a primary key. If the primary key contains more than one field, it is also called a composite key. Fields that have a corresponding value that is a primary key in another table are called foreign keys.
To design a database, you must take a number of steps. You identify the entities and their associated attributes, place the attributes into logical sets, perform a normalization exercise, and then document the results.
In this chapter, you learned database terminology, the steps in designing a database, how to normalize the data, and how to graphically represent the design. In the next chapter, you will begin to learn the SQL language. This language is the heart and soul of all the Designer 2000 products. Before you proceed to that chapter, you may want to set up the tables used in the exercises. Appendix D, "Practice Database Installation Instructions," contains the steps to perform this task.
Figure 2.14 depicts a Transformer database composed of one table. The table contains both current and historical records. The current records pertain to the current location of the transformer. The historic records record previous locations of the transformer. The current record is identified by a status of "CURRENT." Previous location records have a status of "HISTORY."
FIG. 2.14
Existing transformer records.
When the transformer is moved to a new location, the clerk creates a new record and copies information from the previous record into the new one. Transformers are periodically tested. The table has fields for two of these tests. A novice developer that did not understand relational databases designed this database. You should analyze this database. Identify problems with the current design, opinion, and offer an alternate design.