must interview people who understand the way in which the organization operates. Don't rely solely on existing data to validate your understanding of the primary key.

A primary key is the set of attributes that uniquely define a row.

No Part of the Primary Key Is Null

A basic tenet of relational theory is that no part of the primary key can be null. If you think about that idea for a moment, it seems intuitive. The primary key must uniquely identify each row in an entity; therefore, if the primary key (or a part of it) is null, the primary key wouldn't be able to identify anything. For example, a course that has no Course ID cannot be identified or processed in any way.

Referential Integrity

Tables are related to one another through foreign keys. A foreign key is one or more columns for which the set of possible values is found in the primary key of a second table. Referential integrity is achieved when the set of values in a foreign key column is restricted to the primary key that it references or to the null value. Once the database designer declares primary and foreign keys, enforcing data and referential integrity is the responsibility of the RDBMS.

A foreign key is one or more columns whose values must exist in the primary key of another table.

Relationship

The association between two entities is defined by a relationship. In a relationship, one entity is identified as the parent and the other entity is identified as the child. A relationship is defined by the following characteristics:

• Identifying or non-identifying: An identifying relationship is one in which the primary key of the parent forms a portion of the primary key of the child. A non-identifying relationship is one in which the primary key of the parent entity is not part of the primary key of the child entity.
• Cardinality: A relationship's cardinality is defined by the number of rows in the child entity that a single row in the parent entity must have. Examples of cardinality are zero, one, or more. For example, an instructor who is engaged in basic research may teach zero or more classes. However, a department must offer one or more courses or it ceases to be a department.

Cardinality describes the number of rows in a child entity that a single row in a parent entity may have. The cardinality of a relationship between a parent entity and a child entity may be mandatory—for example, a row in a parent entity must have one and only one row in a child entity. Or, the cardinality may be less restrictive—for example, a row in a parent entity may have zero or more rows in a child entity.

• Mandatory or optional: A relationship is mandatory when a row in the parent entity must have some number of rows in the child entity. A relationship is optional when a row in the parent entity can exist without related rows in the child entity.
• Integrity rules: These rules define what action should be taken when a row in the parent or child entity is changed in some way. There are six possibilities: a row in the parent entity is added, changed, or removed, or a row in the child entity is added, changed, or removed. For each of these possibilities, there are several possible actions—restrict the action, set the key value in the parent or child entity to null, cascade the operation to the child entity, or set the key value in the parent or child entity to a default value.

The Concept of the Null Value

A major difference between a relational database and older database technologies is the concept of the null value. In a non-relational database, a special value indicates the absence of a value in a character or numeric field.

The word null indicates that a value for a column or expression is either not applicable or has not been assigned.

In a relational database, a null value for a column represents different concepts:

• A value for this column is not applicable for the row in question.
• The column has not yet been assigned a value.

A relational database enables you to set the value of a column to null or to test a column to see if it is null.

Normalization

It's very easy to create a table in an Oracle database. You can choose from a variety of tools to accomplish this—SQL*Plus, SQL Worksheet, and others; you'll learn more about these tools beginning on Day 4, "Implementing Your Logical Model: Physical Database Design." However, it's crucial that you take the time to study the optimal design for your application's database.

A facet of relational database theory comes into play in this discussion. Normalization theory is the study of relations (tables), attributes (columns), and the dependency of attributes upon one another. The goals of normalization include the following:

• Minimizing redundant data
• Avoiding update anomalies
• Reducing inconsistent data
• Designing data structures for easier maintenance

Because this is theory, you need to understand some essential terminology. Table 3.1 breaks these terms into three categories—theoretician, analyst, and developer. As you read about databases, whether the material is academically or commercially oriented, you'll come across terms that are easily exchanged. As you read the material in this lesson, you'll see these terms used interchangeably. You can use whichever terms you prefer, as long as you use them appropriately and understand what they represent. For example, a professor of computer science may write about relation XYZ; an application developer may refer to the same thing as table XYZ.

Table 3.1. Orientation of database terminology.

Normalization theory describes the desired arrangements of tables and columns as Normal Forms. This chapter discusses the First, Second, and Third Normal Forms, which are often cited as 1NF, 2NF, and 3NF. Although these terms sound theoretical and abstract, they are actually quite intuitive. Other Normal Forms—Boyce-Codd, 4th, and 5th Normal Forms—address more complex normalization issues. Those topics are beyond the scope of this book.

Normalization Rule 1: All Columns Should Contain
a Single Piece of Information

An entity (table) is in First Normal Form (1NF) if all of its attributes are atomic. The term atomic implies that each attribute consists of a single fact about the entity. For instance, if an entity is used to store employee information, you would not use a single attribute, Dependents, to store the names of an employee's dependents. Some employees may have no dependents, whereas others may have many dependents.