GoalUse primary keys and foreign keys to link two tables, and explain what a relationship and an orphan record are.
New words
primary key · a field whose value is different in every record, so it identifies exactly one recordforeign key · a field in one table that holds the primary key values of another table, and so links the two tablesrelationship · the link between two tables, made through a primary key and a foreign keyorphan · a record whose foreign key value has no matching record in the linked table
Explanation
To remove the repeated facts, split the flat file into two tables: one for the customers and one for the orders. Each customer gets a Customer_ID, and the Customers table uses it as its primary key. An order only needs to store the Customer_ID of the person who made it. In the Orders table that field is a foreign key. The two tables are now linked by a relationship: one customer can have many orders. A phone number is typed once, so it can be changed in one place. Referential integrity is a rule that every foreign key value must match a primary key in the linked table, so there are no orphans.
Table
Key field
Kind of key
Why
Customers
Customer_ID
primary key
different for every customer
Orders
Order_ID
primary key
different for every order
Orders
Customer_ID
foreign key
holds a Customer_ID from the Customers table, so it links the two tables
Worked examples
Customer_ID
Name
Phone
Area
1
Diego
555-0142
Eastside
2
Aisha
555-0177
Hilltop
3
Yuki
555-0120
Lakeside
The Customers table holds each person once. Customer_ID is the primary key, because no two customers share a number. A name would be a poor key because two people can have the same name.
Order_ID
Item
Qty
Customer_ID
1
Olives
2
1
2
Bread
3
2
3
Honey
1
1
4
Bread
2
3
5
Cheese
1
2
6
Olives
4
1
The Orders table has its own primary key, Order_ID, and the foreign key Customer_ID. Order 3 has Customer_ID 1, so the table says the order belongs to Diego, and his phone number is found in the Customers table. If Diego’s phone changes, one cell is edited and all of his orders are still right. If order 4 is deleted, Yuki stays in the Customers table.
Order_ID
Item
Qty
Customer_ID
1
Olives
2
1
2
Bread
3
2
3
Honey
1
1
4
Bread
2
3
5
Cheese
1
2
6
Olives
4
1
7
Honey
2
9
An orphan. Someone types order 7 with Customer_ID 9, but the Customers table only holds 1, 2 and 3. Order 7 cannot be linked to anyone, so it is an orphan. Enforcing referential integrity stops the database from accepting it until customer 9 exists.
Class activity
Link the cards (no computer). Make five small cards for customers (each with an ID number, name and phone) and eight order cards that each show only an order number, an item and a customer ID. Use string or a coloured pen to link each order card to its customer card. Count how many order cards each customer has. Change one customer’s phone number on a customer card and show that every order is still linked to the right new number. Finally write an order card with a customer ID that has no card, and call it by its correct name.
Practice
1
A small library keeps a Books table and a Shelves table. Name the primary key of each table and the foreign key, and say what links them.
Table
Fields
Books
Book_ID, Title, Shelf_ID
Shelves
Shelf_ID, Room
Books: Book_ID is the primary key, and Shelf_ID is the foreign key. Shelves: Shelf_ID is the primary key. The relationship is made by Shelf_ID: one shelf holds many books.
2
In the Members and Loans tables, one record in Loans is an orphan. Find it and explain why.
Loan_ID
Book
Member_ID
11
Maps
2
12
Poems
1
13
Birds
2
14
Stars
4
15
Tales
3
Loan 14 is the orphan, because it has Member_ID 4 and there is no member 4 in the Members table, which holds only 1, 2 and 3.
3
A choir table has the fields Singer_ID, Name, Voice and Year. Which field is the best primary key, and why are Name and Voice poor choices?
Singer_ID is the best key, because every singer has a different number. Two singers can share a name, and many singers share a voice, so those fields would not identify exactly one record.
4
Explain how splitting one table into two tables stops an update anomaly.
Each fact is stored once, in one table. When it changes, only that one cell is edited, so no old copy is left behind that disagrees with the new one.