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.