A column in a relation has the following properties.

A row in a relation has the following properties.

A relational database works with two types of tables. Base tables are relations that are actually stored in the database. These are the tables that make up your schema. However, relational operations on tables produce additional tables as their result. Such tables, which exist only in main memory, are known as virtual tables. Virtual tables may not be legal relations—in particular, they may have no primary key—but because virtual tables are never stored in the database, this presents no problem in terms of the overall design of the database.

The use of virtual tables benefits a DBMS in several ways. First, it allows the DBMS to keep intermediate query tables in main memory rather than storing them on disk, enhancing query performance. Second, it allows tables that violate the rules of the relational data model to exist in main memory without affecting the integrity of the database. Third, it helps avoid fragmentation of database files and disk surfaces by avoiding repeated write, read, and delete operations on temporary tables.

You will see instances of relations throughout this book used as examples. However, we do not usually include data in a relation when documenting that relation. One common way to express a relation is as follows:

For example, the Customer relation that you saw in Figure 5-1 would be written as:

The preceding expression is a true relation, an expression of the structure of a relation. It correctly does not contain any data. (As mentioned earlier, when data are included, you have an instance of a relation.)

What about a primary key to identify people? The first thing that pops into your mind might be a Social Security number (or, for those outside the United States, a national identification number). Every person in the United States is supposed to have a Social Security number. Parents apply for them in the hospital where a baby is born, right? And because they are assigned by the U.S. government, they must be unique, right? Unfortunately, the answer to both questions is “no.”

The Social Security Administration has been known to give everyone in an entire town the same Social Security number; over time, the numbers may be reused. However, these are minor problems compared to a Social Security number being null.

Consider what happens at a college that uses Social Security numbers as student numbers when international students enroll. Upon entry into the country, the international students do not have Social Security numbers. Because primary keys cannot be null, the international students cannot sign up for classes or even enroll in the college until they have a Social Security number.

The college's solution is to give them “fake” numbers in the format 999-99-XXXX, where XXXX is some number currently not in use. Then, when the student receives a “real” Social Security number from the government, the college supposedly replaces the fake value with the real one. Sometimes, however, the process does not work. A graduate student ended up with his first semester's grades being stored under the fake Social Security number but the rest of his grades under his real number. (Rather than changing the original data, someone created an entire new transcript for the student.) When the time came to audit his transcript to see if he had satisfied all of his graduation requirements, he was told that he was missing an entire semester's worth of courses.

This example leads us to two important desirable qualities of primary keys:

In addition, there is significant concern about security problems that can arise from the use of Social Security numbers as identifiers in a database. The danger of identity theft has made it risky to share a national identifier. Many U.S. state governments, for example, have mandated that publicly supported organizations use something other than the Social Security number as a customer/client/student ID to help protect individual privacy.

Although Social Security numbers initially look like good natural identifiers, you will be much better off in the long run using arbitrary numbers for people—such as student numbers or account numbers—rather than relying on Social Security numbers.

It can be very tempting to code meaning into a primary key. For example, assume that Antique Opticals wants to assign codes to its distributors rather than giving them arbitrary distributor numbers. Someone might create codes such as TLC for The Laser Club and JS for Jones Services. At first, this might seem like a good idea. The codes are short, and by looking at them you can figure out which distributor they reference.

But what happens if one of the companies changes its name? Perhaps Jones Services is renamed Jones Distribution House. Do you change the primary key value in the distributor table? Do you change the code so that it reads JDH? If the distributor table were all that we cared about, that would be the easy solution.

However, consider that the table that describes merchandise items contains the code for the distributor so that Antique Opticals can know which distributor provides the item. (You’ll read a great deal more about this concept in the next major section of this chapter.) If you change the distributor code value in the distributor table, you must change the value of the code for every merchandise item that comes from that distributor. Without the change, Antique Opticals will not be able to match the code to a distributor and get information about the distributor. It will appear that the item comes from a nonexistent distributor!

Meaningful primary keys tend to change and therefore introduce the potential for major data inconsistencies between tables. Resist the temptation to use them at all costs. Here, then, is yet another property of a good primary key:

It is not always possible to use completely meaningless primary keys. You may find, for example, that you need to include dates or times in primary keys to distinguish among events. The suggestion that you should not use meaningful primary keys is therefore not a hard and fast rule but a guideline you should follow whenever you can.

Some tables have no single column in which the values never duplicate. As an example, look at the sample order items table in Figure 5-2. Because there is more than one item on an order and because the same item can appear on more than one order, order numbers are repeated. Therefore, neither column by itself can serve as the table's primary key. The combination of an order number and an item number, however, is unique. We can therefore concatenate the two columns that form the table's primary key.

It is true that you could also concatenate all three columns in the table and still ensure a unique primary key. However, the quantity column is not necessary to ensure uniqueness and therefore should not be used. We now have some additional properties of a good primary key:

It is possible to have a table in which every column is part of the primary key. As an example, consider a library book catalog. Each book title owned by a library has a natural unique primary key: the ISBN (International Standard Book Number). Each ISBN is assigned to one or more subject headings in the library's catalog, and each subject heading is also assigned to one or more books. We therefore have a many-to-many relationship between books and subject headings.

A relation to represent this relationship might be:

All we need to do is pair a subject heading with a book identifier. No additional data are needed. Therefore, all columns in the table become part of the primary key.

There is absolutely no problem with having all-key relations in a database. In fact, they occur whenever a database design contains a composite entity that has no relationship data. They are not necessarily errors, and you can use them wherever needed.

The procedure described in the preceding paragraph works very well—unless for some reason there is no order number in the orders table to match a row in the order items table. This is a very undesirable situation because you can't ship the ordered items if you don't know which customer placed the order.

This relational data model therefore enforces a constraint called referential integrity, which states that every non-null foreign key value must match an existing primary key value. Of all the constraints on a relational database, this is probably the most important because it ensures the consistency of the cross-references among tables.

Referential integrity constraints are stored in the database and enforced automatically by the DBMS. As with all other constraints, each time a user enters or modifies data, the DBMS checks the constraints and verifies that they are met. If the constraints are violated, the data modification will not be allowed.

Foreign keys do not necessarily need to reference a primary key in a different table; they need only reference a primary key. As an example, consider the following employee relation:

A manager is also an employee. Therefore, the manager ID, although named differently from the employee ID, is actually a foreign key that references the primary key of its own table. The DBMS will therefore always ensure that whenever a user enters a manager ID, that manager already exists in the table as an employee.

A view is not stored with data. Instead, it is stored under a name in the database itself along with a database query that will retrieve its data. A view can therefore contain data from more than one table, selected rows, and selected columns.

The real beauty of storing views in this way, however, is that whenever the user includes the name of the view in a data manipulation language statement, the DBMS executes the query associated with the view name and recreates the view's table. This means that the data in a view will always be current.

A view table remains in main memory only for the duration of the data manipulation statement in which it was used. As soon as the user issues another query, the view table is removed from main memory to be replaced by the result of the most recent query. A view table is therefore a virtual table.

There are three good reasons to include views in the design of a database:

Like other structural elements in a relational database, views can be created and destroyed at any time. However, because views do not contain stored data but only specification of a query that will generate a virtual table, adding or removing view definitions has no impact on base tables or the data they contain. Removing a view will create problems only when that view is used in an application program and the program is not modified to work with a different view or base table.

The precise tables that make up a data dictionary depend somewhat on the DBMS. In this section you will see one example of a typical way in which a DBMS might organize its data dictionary.

The linchpin of the data dictionary is actually a table that documents all the data dictionary tables (often named syscatalog, the first few rows of which can be found in Figure 5-4). From the names of the data dictionary tables, you can probably guess that there are tables to store data about base tables, their columns, their indexes, and their foreign keys.

The syscolumn table describes the columns in each table (including the data dictionary tables). In Figure 5-5, for example, you can see a portion of a syscolumn table that describes the Antique Opticals merchandise item table.

Keep in mind that these data dictionary tables have the same structure and must adhere to the same rules as base tables. They must have non-null unique primary keys, and they must enforce referential integrity among themselves.