(known as the primary key ), along with additional information needed to describe the entity
completely. Looking again at the customer table, the cust_id column holds a different number for each
customer; George Blake, for example, can be uniquely identified by customer ID #1. No other customer
will ever be assigned that identifier, and no other information is needed to locate George Blake's data in
the customer table.
NOTE
Every database server provides a mechanism for generating unique sets of numbers to use as
primary key values, so you won't need to worry about keeping track of what numbers have been
assigned.
While I might have chosen to use the combination of the fname and lname columns as the primary key
(a primary key consisting of two or more columns is known as a compound key ), there could easily be
two or more people with the same first and last names that have accounts at the bank. Therefore, I
chose to include the cust_id column in the customer table specifically for use as a primary key column.
NOTE
In this example, choosing fname /lname as the primary key would be referred to as a natural key
, whereas the choice of cust_id would be referred to as a surrogate key . The decision whether
to employ natural or surrogate keys is a topic of widespread debate, but in this particular case
the choice is clear, since a person's last name may change (such as when a person adopts a
spouse's last name), and primary key columns should never be allowed to change once a value
has been assigned.
Some of the tables also include information used to navigate to another table; this is where the
"redundant data" mentioned earlier comes in. For example, the account table includes a column called
cust_id , which contains the unique identifier of the customer who opened the account, along with a
column called product_cd , which contains the unique identifier of the product to which the account will
conform. These columns are known as foreign keys , and they serve the same purpose as the lines that
connect the entities in the hierarchical and network versions of the account information. If you are
looking at a particular account record and want to know more information about the customer who
opened the account, you would take the value of the cust_id column and use it to find the appropriate
row in the customer table (this process is known, in relational database lingo, as a join ; joins are
introduced in Chapter 3 and probed deeply in Chapters Chapter 5 and Chapter 10 ).
It might seem wasteful to store the same data many times, but the relational model is quite clear on
what redundant data may be stored. For example, it is proper for the account table to include a column
for the unique identifier of the customer who opened the account, but it is not proper to include the
customer's first and last names in the account table as well. If a customer were to change her name,
for example, you want to make sure that there is only one place in the database that holds the
customer's name; otherwise, the data might be changed in one place but not another, causing the data
in the database to be unreliable. The proper place for this data is the customer table, and only the
cust_id values should be included in other tables. It is also not proper for a single column to contain
multiple pieces of information, such as a name column that contains both a person's first and last
names, or an address column that contains street, city, state, and zip code information. The process of
refining a database design to ensure that each independent piece of information is in only one place
(except for foreign keys) is known as normalization .
Getting back to the four tables in Figure 1-3 , you may wonder how you would use these tables to find
George Blake's transactions against his checking account. First, you would find George Blake's unique
identifier in the customer table. Then, you would find the row in the account table whose cust_id
column contains George's unique identifier and whose product_cd column matches the row in the
product table whose name column equals "Checking." Finally, you would locate the rows in the
transaction table whose account_id column matches the unique identifier from the account table.
This might sound complicated, but you can do it in a single command, using the SQL language, as you
will see shortly.