Chapter 8 uses a simple order processing data structure to illustrate the use and workings of a database – the data structure is reproduced below.

The entities and relationships are: 

  • Customer
    Primary key: custNo
  • Order
    Primary key: ordNo
    Secondary key: custNo
  • Order Line (OrdLne)
    Primary key: ordNo + ordLneNo
    Secondary keys: ordNo, prodCode
  • Product Category (ProdCat)
    Primary key: prodCatCode
  • Product
    Primary key: prodCode
    Secondary key: prodCatCode
  • Price
    Primary key: prodCode + priceSDate
    Secondary key: prodCode

This data structure has been implemented as a database – files are available for:

  • MS Access
  • MySQL

MS Access

The MS Access DB is in file:

Developed on Access07 + tested on Access10.

Includes forma and reports but no program logic to allocate stock or to calculate invoices.


The MySQL files are collected into the zip file: ch08sql.zip, which contains the following items:

  • There is an SQL script to create and load the database in MySQL:
  • Also included are SQL scripts for the restrict, project and join queries:
  • There is a further SQL script for a more complex order query:

The scripts have been written for MySQL – an open source database that you can download for free (there are paid-for versions that you probably want to avoid). In theory the scripts should also work with other sql databases (Oracle, MS Access) but in practice you might have to make minor modifications. You are also welcome to amend / expand the scripts for your own purposes if you so wish.