An Introduction to Information Systems

by David Whiteley

Database

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

database

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.