ACME Accounts

Details of the database.

Some items from the database.

Invoices and receipts

Preparing accounts

  10-JUL-1998 Delivery       100.00
  15-JUL-1998 Cheque                       100.00
  22-JUL-1998 Delivery       210.00
  23-JUL-1998 Delivery        45.00
  23-JUL-1998 Cash                         255.00
  

The following VIEW, accountline has been created (if not create it yourself). It shows one line for each payment or shipment, money out is negative (a debit), money in positive (a credit).

CREATE VIEW accountline AS
  SELECT shipped.badguy AS badguy, shipped.sdate AS LineDate,
        'Delivery' AS Legend, -[price]*[quantity] AS amount
    FROM product, shipped 
    WHERE product.id = shipped.product
UNION
  SELECT receipt.badguy, receipt.rdate,notes, amount
    FROM receipt
    ORDER BY linedate;

What is wrong with this database.

1. There is no mechanism for recording returns. Suggest what action should be taken when faulty goods are returned and money refunded.
2. The price of item P001 is to be increased. What effect will this have on the calculated balance for customers who previously purchased the item? Suggest how the structure of the database should be amended to prevent this problem.
3. All goods must be purchased at list price. In practice discounts may be given on an ad-hoc basis. Suggest how such discounts might be recorded.
4. As it stands the data will just grow and grow. It may be desirable to have a consolidation routine which removes old records and leaves an outstanding balance for each customer. This is unlikely to be worth doing for technical reasons. You could store hundreds of years worth of data for a small company at only a marginal cost of processing time or disk space.

Tutorial Six: Edinburgh Buses concerns self joins and includes some ridiculously complex SQL queries