Type of BB Inventory Has a Unique Item #. an Individual Sale Or Purchase Can Consist Of

Boston Bottle (BB) buys and sells a variety of glass and plastic bottles nationwide. Each

type of BB inventory has a unique item #. An individual sale or purchase can consist of

multiple inventory types which are each listed as a line item on the sale invoice or the

purchase order. Data about inventory items are added to the database when a supplying

vendor can be found for the new item who will provide a quoted price to BB. Vendors

can supply many products and vice-versa.

Customers are not assigned to any particular salesperson; anyone can service them. The

same is true for vendors and buyers. A sale, a purchase, or a cash transaction involves

just one BB employee. Customers and vendors are added to the database before any

transactions with them take place.

Cashiers handle cash receipts and disbursements, they are bonded, and they put all the

receipts from any one day into just one account, but these accounts vary daily. New cash accounts are added to the database when they are opened. Employees are added to the database on the day they are hired.

All purchases are paid for immediately with a check drawn on a single account, but

multiple accounts are used in purchasing activity. Customers pay on the 25th of the

month for all sales during the previous month. Cash receipts and disbursements take

place within other cycles of BB.

Required: Using the list of data elements on the following page (don’t add or subtract any), construct an E-R diagram for Boston Bottle with entities, relationships, and participation cardinalities specified. Additionally, you should use the information in the problem to construct a minimal relational database for BB. When considering posting decisions for implementation of relations, try to assess as well as you can the effect of load on efficient implementation.
DATA ELEMENTS (Don’t add or delete any)

-inventory item #-item weight-item color

-sale invoice #-quantity of an item sold on this invoice

-purchase order #-quantity of an item purchased on this order

-purchase $ amount-sale $ amount-sale date

-check #-check amount-cash account #

-remittance advice #-cash receipt amount-cash account balance

-customer #-customer name-customer address

-individual accounts payable-individual accounts receivable

-buyer employee #-buyer telephone #-vendor #

-vendor name-vendor’s current price quote for an item

-salesperson employee #-vendor’s own product number for an inventory item

-salesperson commission rate-cashier fidelity bond plan

-cashier employee #-warehouse #-warehouse location

-quantity of a particular bottle item at this warehouse

W.E. McCarthy 2002