A01 chaf6542 06 se fm indd

*

+

1

M

Order hdr

• Order ID *

• Order date

• Dispatch date

• Total amount

• Shipping cost

• Order credit

card number

• Customer ID +

places

contains

contains

Order line

• Line ID *

• Order ID +

• Quantity

• Price

• Product ID +

Product

• Product ID *

• Short description

• Long description

• Picture

• Size

• Category

• Manufacturer ID +

• Standard price

• Number in stock

• Reorder level

• Next available date

three tables. The fields that are used to relate tables are referred to as ‘key fields’. A 

primary 

key

 is used to uniquely identify each instance of an entity and a 

secondary key

 is used to 

link to a primary key in another table. In Figure 11.6 the primary key of the customer table 

is customer ID, but the field customer ID in the order table is here a secondary key that links 

Primary key

The field that uniquely 

identifies each record in 

a table.

M11_CHAF6542_06_SE_C11.indd   532

7/23/14   1:26 PM

---

533

Chapter 11  Analysis and design

back to the customer table. This relationship is an example of a one-to-many relationship 

since each customer may place many orders over the lifetime of the relationship.

Normalisation is an additional stage, not covered here, used to optimise the database to 

minimise redundancy or duplication of information. If you have previous experience in 

analysis and design for databases, complete Activity 11.3 to develop an ER diagram for a 

typical B2C company. If you do not have this experience then refer to the generic answers to 

gain an appreciation of how databases are structured.