In this exercise, we will design a (somewhat abbreviated) database for a plumbing supply store. Later, we will use this database for various query and form exercises. The basic form of this database is a very standard, and occurs as a substructure in most sales operations.
Our plumbing supply store needs to keep track of its customers, including
their names, address, city, state, zip code, and phone number. For the
purposes of this exercise, assume that we do not have easy access to a
complete zip code table. We also need to keep track of the products we
stock, including name, units in stock, units on order, and list price. At
any given time, a customer may place an order which may consist of more
than one product – for example, an order could consist of three circulating
pumps, a fin, a valve, and two shower drains. We also want to store the
date of each order.
Design a database that will properly keep track of all products, customers and orders.