The relational model organizes data into tables and allows you to create relationships among tables by referencing columns that are common to both—the primary and foreign keys. It is easiest to understand this concept of relationships between tables with a common business example.
A hypothetical business needs to track information about customers and their orders. The business' database, as shown in Figure 2, includes the following tables:
The Customer table — The Customer table shows four rows, one for each individual customer. Each row has two columns: Cust Num and Name. To uniquely identify each customer, every customer has a unique customer number. Each column contains exactly one data value, such as C3 and Jim Cain. The primary key is Cust Num.
The Order table — The Order table shows five rows for orders placed by the customers in the Customer table. Each Order row contains two columns: Cust Num, from the Customer table, and Order Num.The primary key is Order Num. The Cust Num column is the foreign key that relates the two tables. This relationship lets you find all the orders placed by a particular customer, as well as information about a customer for a particular order.
The Order-Line table — The Order-Line table shows seven rows for the order-lines of each order. Each order-line row contains three columns: Order-Line Num; Item Num, from the Item table; and Order Num, from the Order table. The primary key is the combination of Order Num and Order-Line Num. The two foreign keys, Order Num and Item Num, relate the Customer, Order, and Item tables so that you can find the following information:
All the order-lines for an order
Information about the order for a particular order-line
The item in each order-line
Information about an item
The Item table — The Item table shows four rows for each separate item. Each Item row contains two columns: Item Num and Description. Every item in the Item table has a unique item number. Item Num is the primary key.
Figure 2. Example of a relational database
Suppose you want to find out which customers ordered ski boots. To gather this data from your database, you must know what item number identifies ski boots and who ordered them. There is no direct relationship between the Item table and the Customer table, so to gather the data you need, you join four tables using their primary/foreign key relationships, following these steps:
1. Select the Item table row whose Description value equals ski boots. The Item Number value is I1.
2. Next, locate the Orders that contain Item I1. Because the Order table does not contain Items, you first select the Order-Lines that contain I1, and determine the Orders related to these Order-Lines. Orders 01 and 04 contain Item Number I1.
3. Now that you know the Order Numbers, you can find out the customers who placed the orders. Select the 01 and 04 orders, and determine the associated customer numbers. They are C1 and C3.
4. Finally, to determine the names of Customers C1 and C3, select the Customer table rows that contain customer numbers C1 and C3. Don Smith and Jim Cain ordered ski boots.
The following figure illustrates the steps outlined in the previous procedure.
Figure 3. Selecting records from related tables
By organizing your data into tables and relating the tables with common columns, you can perform powerful queries. The structures of tables and columns are relatively simple to implement and modify, and the data is consistent regardless of the queries or applications used to access the data. Figure 3 shows the primary key values as character data for clarity, but a numeric key is better and more efficient.
The Customer table — The Customer table shows four rows, one for each individual customer. Each row has two columns: Cust Num and Name. To uniquely identify each customer, every customer has a unique customer number. Each column contains exactly one data value, such as C3 and Jim Cain. The primary key is Cust Num.
