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:
Primary key: custNo
Primary key: ordNo
Secondary key: custNo
- Order Line (OrdLne)
Primary key: ordNo + ordLneNo
Secondary keys: ordNo, prodCode
- Product Category (ProdCat)
Primary key: prodCatCode
Primary key: prodCode
Secondary key: prodCatCode
Primary key: prodCode + priceSDate
Secondary key: prodCode
This data structure has been implemented as a database – files are available for:
- 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.