Boston Bottles Essays

Boston Bottles Essays Boston Bottles Essay Boston Bottles Essay Boston Bottles Data Modeling Problem Draw an REA diagram with cardinalities and tables with primary keys, foreign keys, and non-key attributes for the following business: Boston Bottle (BB) buys and sells a variety of glass and plastic bottles nationwide. The proprietor of the company assigns each glass and bottle to an inventory type and assigns a unique # for each type (a type can contain numerous glasses and bottles). An individual sale or purchase can consist of multiple types of inventory. Inventory types are not tracked until they are purchased and put in a warehouse. A warehouse can have many inventory types, but it doesnt have to have any. An inventory type can be found in more than one warehouse. Customers are not assigned to any particular salesperson: anyone can service them (a relationship is not needed). However, vendors typically deal with one and only one buyer, unless the buyer quits or leaves the employment of BB, in which case the vendor works with a different buyer. Buyers do not have to work with any vendors, if they just started, but they can work with many vendors. Each sale or purchase involves just one BB employee: a salesperson or buyer, respectively. Sales and purchases are only for inventory. These two types of employees are tracked in separate database tables. Customers and vendors are added to the database without any transactions taking place (including being assigned to a buyer in the case of a vendor), however they can be involved in many transactions over time. Each employee fills out one timecard each week, therefore each timecard belongs to one and only one employee. The timecard allows the employee to get a paycheck on the last day of the following week. A separate check is written for each timecard and is made out to only one employee (or vendor in the case of a purchase). Timecard #’s for each employee change from week to week. Employees get a timecard immediately when they are hired. The employee in payroll who collects the timecards and the cashier who distributes the paychecks are not modeled in the database. Cash receipts and cash disbursements are from one and only one cash account. New cash accounts are added to the database when they are opened with a deposit. Sometime after this checks can be written from them. Employees are added to the database on the day they are hired (but before they are involved in a purchase or sale, or involved with a vendor). Cash accounts can have many receipts and many disbursements. A single purchase must be partially paid for immediately and the remainder can be paid within 30 days. However one check cannot pay for more than one purchase (just as one cash receipt from a customer cannot pay for more than one sale). The only payment terms BB has for a customer is to pay immediately in cash in full. Only one vendor or customer is involved in a transaction, however cash receipts and disbursements take place within other cycles of BB. All employees can be involved in many of their respective transactions over time. Here is the data of interest: Inventory type # Sales invoice # Purchase order # Vendor quality rating Remittance advice # Customer # Buyer employee # Vendor name Date buyer started working with vendor Salesperson employee # Timecard # Hours worked per employee per pay period Inventory type description Purchase date Salesperson commission rate Inventory type selling price Total sale dollar amount Sale date BB Check amount Cash account # Total cash receipt amount Quantity of inventory type in warehouse Cash account balance BB Check # Customer name Customer address Buyer telephone # Vendor # Warehouse # Warehouse location Quantity of an inventory type sold