Page 24
This section defines some relational database terms, and then describes how databases are designed and how to graphically portray them. It is important to understand these concepts (including the database objects information discussed in the preceding section), because they are used throughout the remainder of this book. The database design section of the chapter illustrates the Rules of Normalization. This technique is also used to determine the design of the Employee Information System project. The remainder of the book illustrates how to create the database objects and applications that manipulate the data in the objects.
Databases are generally modeled to represent a single real-world object. This object can be a transformer, customer, or as in the case of this book an employee. This object is called an entity. In a database, the entity is represented by a number of tables and the business rules that pertain to the entity. These rules form the table relationship and the validation procedures contained in the application.
Figure 2.8 illustrates the employee entity, and contains an instance of the entity. An instance is one of the objects that comprise the entity. Instances have various properties. These properties are called attributes.
FIG. 2.8
I'm Harry Truman!
In this example, you can identify a number of significant attributes about the employee. The employee's first name is "Harry," the last "Truman." He started work in the "POL" department on April 13, 1945 and has purchased several pairs of glasses. In the database table, each of these attributes would be recorded in a field or column. The sum of the attributes is a representation of the entity.
Each attribute has a data type. This means the attribute can be a number, integer, a set of characters, or even a date. The database developer needs to determine the data type values along with the size of the values. You need to determine the possible range of values, which is called the attribute domain. If the employee entity has a gender attribute, for example, you want only
Page 25
values of "M" or "F" in the field in the table. Oracle7 and Oracle Forms have built-in devices that help the developer to protect the database from containing data outside the attribute domains.
After you identify the entities' attributes, data types, and domains, you group them into logical sets of data. These groupings eventually become your tables, after the normalization process described at the end of the chapter. In Figure 2.9, you have a logical grouping of the attributes illustrated in Figure 2.8. The grouping is the beginning of Employee table definition. Each of the attributes has a name, a data type, and a size value.
FIG. 2.9
The Employee table attributes.
One major problem exists with the Employee table attribute definition. None of the attributes uniquely identify the employee. It is a common occurrence for two employees to have the same first and last name. Good table design requires that each record must have an identifier that differentiates the record from all other records in the table. This identifier is called a primary key. A likely attribute to uniquely identify an employee is a payroll number or possibly a Social Security Number. If you add one of these attributes, each record would have a primary key.
The majority of the attributes in the definition are unique to the employee. This means the particular attribute cannot have two or more values. The employee cannot have two last names or be in two or more departments. Tables that contain unique information about the entity and have the unique information (primary key) to identify the entity instance are called base tables. All entities have one of these tables.
Two attributes in the definition are not really unique: glasses_purchase1, and glasses_purchase2. These attributes hold the dates of eyeglass purchases by the employee. The table can hold dates for two purchases, but has problems when the third and subsequent purchases occur. The table does not have fields to contain these values. You can resolve this problem by identifying an employee eyeglass purchase entity and creating another table to hold these records. By placing these purchase records in their own table, you can record as many purchases as necessary. This type of table is called a relation table.
Relation tables do not have any real significance without a tie to a base table. This means that each record in the relation table must have the primary key attribute from the base table. This enables you to determine which base table record is related to the relation table record. This primary key attribute in the relation table is called a foreign key. Relation tables always have foreign key fields and a foreign key is part of their primary key. In Figure 2.9, the primary key of the relation table is the combination of payroll_number and purchase_date. You need two fields because one employee may make multiple purchases, and several employees can make purchases on the same date. It is unlikely that one employee will make two purchases on the same day. The combination of the two fields make this a good unique key to the table. One final
Page 26
word, when the unique key to a table consists of more than one field, it is called a composite key. Relation tables always have composite primary keys.
Before you can begin to design the database, you must understand how to describe the
design. Although you can use a number of different mechanisms such as entity relationship
diagrams, this discussion uses a table relationship
diagram. The diagram uses boxes to denote tables,
and lines between the boxes to denote relationships. At the end of the lines, symbols denote
the type of relationship that exists between the tables. Figure 2.10 illustrates the various
diagrams used to depict table relationships. The symbols used in the diagrams are
1, O, and M. The 1 symbol means one record. The M symbol means many records, and the
O means the relation is optional. When a relation is optional, the table does not have to contain a record(s) to
match the record at the other end of the relationship.
FIG. 2.10
Table relationship diagram symbols
The top-left diagram is the one to one not optional relationship. This relationship is used when the record in the first table must have only one record in the second table. When the second table does not have to have a matching record, the O symbol is placed on the line next to the optional table. This relationship is called one to one optional.
The one to many not optional diagram is located in the middle left. This relationship is used when one record in the first table must have one or more records in the second table. The adjacent diagram depicts the relationship when it is optional. Master detail relationships are usually one to many optional, with the single record table the master or base table and the many table the detail or relation table.
The many to many not optional relationship at the bottom left means many records in the first table are related to many records in the second table. This relationship is not very common and generally means the table design is poor. When you use this type of relationship to relate records, you are not certain which record is the one you want.