Bristol Dairy
The Bristol Dairy has a product
list that includes a wide variety of milk, butter, cream, eggs, ice cream,
bread products and special products such as eggnog offered on a seasonal basis.
Many products come in a variety of sizes. There are approximately 100 products
in the inventory when you consider that a pint, quart, half-gallon and gallon
are all separate products.
Customers include both
residential and commercial. The customer list is large enough to mean that
multiple trucks are used to deliver to homes and businesses. Customers can have
delivery on a M/Th or Tu/F or W/S schedule depending on their geographic
location.
Customers place a standard order
- the order can be different for the two days. Changes to the standard order
are frequent. To make a permanent change, the customer should call, send
notification or give notification to the delivery person at least one delivery
day ahead. To make a temporary change, the customer can either call the
office 24 hours ahead of time and request a change or leave a note for the
delivery person and hope for the best. Because of last minute changes, the
trucks are stocked to handle orders and meet a certain percent of over orders -
the percents are based on historic information.
Delivery people are given
two-part delivery sheets containing the standard order and any modifications
that were called in 24 hours ahead of time. If changes are made at the door,
the delivery person will note the changes on the delivery sheet, leave one
sheet for the customer and take one sheet back to the office to be used for
billing. Note that you need to keep track of orders and actual deliveries to
make this work.
Bills are sent out on a monthly
basis and a 5% discount is allowed for payment within 10 days. Overdue is kept
on a 30, 60, 90, 120+ day basis and interest is charged on balances left
outstanding. Payments must be sent to the office, they cannot be left for the
delivery person.
You are the analyst and I am the
owner of the dairy. Therefore ask the questions that need to be asked and make
the judgments and assumptions that are valid to make from your position as
analyst.
Your assignment is to analyze
the system and determine the output that should be produced and the input that
will be required. You should make a lit
of output and input. You should then design the database for the dairy.