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:
ch08accessDB.accdb

Developed on Access07 + tested on Access10.

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

MySQL

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:
  ​ch08sqlDB.sql
• Also included are SQL scripts for the restrict, project and join queries:
  ch08sqlDBre.sql
  ch08sqlDBpr.sql
  ch08sqlDBjo.sql
• There is a further SQL script for a more complex order query:
  ch08sqlDBqu.sql

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.