AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Link the tables

Week 18 · 2.3 Creating databases · 3 lessons
2

Keys that link tables

Computing 9: Topic 2.3, pp. 217–256
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.

TableKey fieldKind of keyWhy
CustomersCustomer_IDprimary keydifferent for every customer
OrdersOrder_IDprimary keydifferent for every order
OrdersCustomer_IDforeign keyholds a Customer_ID from the Customers table, so it links the two tables
Worked examples
Customer_IDNamePhoneArea
1Diego555-0142Eastside
2Aisha555-0177Hilltop
3Yuki555-0120Lakeside
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_IDItemQtyCustomer_ID
1Olives21
2Bread32
3Honey11
4Bread23
5Cheese12
6Olives41
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_IDItemQtyCustomer_ID
1Olives21
2Bread32
3Honey11
4Bread23
5Cheese12
6Olives41
7Honey29
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.
TableFields
BooksBook_ID, Title, Shelf_ID
ShelvesShelf_ID, Room
2
In the Members and Loans tables, one record in Loans is an orphan. Find it and explain why.
Loan_IDBookMember_ID
11Maps2
12Poems1
13Birds2
14Stars4
15Tales3
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?
4
Explain how splitting one table into two tables stops an update anomaly.