Assume that you run an oil company. For this example we are going to assume that a customer and a house are the same,
in other words a customer only has one house and a house is owned by one customer. The information that you have
includes:
- customer id number
- customer name
- customer address(need easy access to street, city, state and zip)
- capacity of tank
- house type code
- for each house type code you need to carry the average summer, fall, spring and winter use
- amount past due
- delivery information for the year including
- the date of the delivery
- the number of gallons delivered
- the price per gallon for each delivery that is made
Note that the house type is a code to help determine the predicted usage. The system will have an average use for
capes or ranches in the summer, fall etc. So for example you might have a house type that is for 6 room capes and
would tell the average usage for that type of house in each of the four seasons. A customer is assigned that code
to help predict the usage for that type of house.
Your assignment is to design the tables that you would need to develop to implement this database. Show me the tables,
the fields/columns contained in each table and the designate the field or fields that would be used as the primary keys.