El Paso Coffee Roasters is a family-owned chain of coffee roasters. They have expanded from their humble beginnings in El Paso, to a $30,000,000 business with stores in 44 cities throughout the United States. They originally specialized in Brazilian coffee beans, but they now provide customers with over 50 types of coffee beans. This expansion has progressed relatively smoothly, but Louis is getting older and balder, and he does not want to spend 15 hours each day managing the businesses. Unfortunately, his three children don't have his strong work ethic, and he fears they won't put in even 15 hours each week. In order to head off disaster, Louis has hired you to help them develop a database to support their operations. Your first few days with Louis have been valuable. You have interviewed not only Louis and his children (who told you close to nothing), but also many of the employees who have years of experience and many good ideas. You have summarized the results of these conversations as follows: Each store can carry all types of coffee beans, but they usually specialize in local favorites. These coffee beans are purchased directly from local bean suppliers and are then marked up outrageously. Because of Louis's international reputation, customers are willing to pay almost anything for roasted beans with his name on it. All stores are very successful. Customers (usually small restaurants) are allowed to buy on credit, and all pay on the last day of the month. Louis is rumored to have "family" connections in Jurez, so no customer would consider returning beans or defaulting on a sale. Normal business proceeds as follows. An employee decides approximately how many beans are needed next month to service "his/her" customers. They purchase that amount during the current month, roast all of it, and deliver it before the beginning of the next month. This minimizes the onhand inventory of beans from month to month. Each supplier supplies beans to only the local store; beans are never transported between locations. Employees always use company trucks to visit suppliers and customers. Louis handles all of the hiring at each store. This allows him to select generalists who will not only be good for the store, but also be willing to work for very low wages. As a result, each employee can perform many functions such as purchasing, selling, and roasting beans. These employees earn a wage based on their hourly rate and the number of hours worked. The hours worked are recorded on weekly time cards which are identified by Time Card Numbers which are unique throughout the company. When employees sum their hours at the end of the week, they also note the % of their hours dedicated to roasting beans. Each employee is assigned to just one store. Although they may be transferred to another if Louis sees a need, that would simply change their current assignment and would not be considered them being assigned to multiple stores. El Paso Coffee Roasters also utilizes a fleet of trucks. Painted with their Coffee Bean logo imprinted with the Texas flag, the trucks are driven to pick up coffee beans from suppliers and to deliver the roasted beans to major customers such as coffee shops. The use of a particular truck on a purchase or sale is tracked, as is the time of the employee/driver. Each truck is uniquely numbered, and may be transferred between the stores. Company trucks are leased on yearly contracts from truck vendors. Each contract supplies one truck for one year on a negotiated lease fee. Lease payments against each contract are made monthly by Louis who will send a truck vendor just one check to cover all existing contracts with that supplier. Louis wants to track both the negotiated lease fee and the expected monthly payment amount for each lease.

MacBook Air El Paso Coffee Roasters uses The checking accounts for all of its disbursements and receipts. As a coffee beans and employees for their labor. One check (unique number) an cut for each coffee bean purchase submitted properly (it will N have a unique purchase numba ?hand each time card. Customer payments are assigned unique remittance advice numbers and they are deposited into just one account. You may assume that only Loan, has access to the cash accounts, therefore he is always the person who acts for the company on cash transactions. You may also assume that Louis is the agent for the company on the acquisition of employee service and the lease of trucks. Do not model Louis as an employee or as a separate class in this project. You have also kept a list of data elements that Louis wants accounted for in his system. You may account for a small number of them simply by creating relevant associations; for most you will need to use them as class or association attributes. The data elements Louis requires are as follows. Note that coffee bean quantities are measured in ounces. Location Code # Dollar amount of purchase $ amount of sale Employee Name Employee ID Supplier Name Check # for a cash disbursement Quantity of each coffee bean on a purchase Truck engine# Quantity of each coffee bean on a sale Cash account balance Current store assignment for employee Store telephone no. Bank account no. Wage rate Supplier reputation Truck mileage for this sale Time spent (in minutes) of purchasing employee Truck mileage for purchase of coffee bean Present store assignment for truck Coffee bean description Dollar amount of customer remittance Individual customer A/R balance Time card # Driver time for this sale Individual A/P balance for truck vendor Customer name Purchase # Check amount in dollars Total hrs worked this period, employee Total QOH of each type of coffee bean Customer # Location quantity of each coffee bean item Sale # Truck odometer EOM (end of last month) Remittance advice # (RA#) Date of sale Coffee bean product # Date of purchase Vendor# Lease#f Lease fee Truck model year Supplier# Coffee bean shelf life (long or short) Lease monthly payment Store address Truck vendor name Cash disbursement # Selling price for a type of coffee bean (standard across US) % of an employee's time spent this period roasting coffee beanshelp convince Louis's children that an enterprise database will he valuable to them, you plan to implement a pilot system using Microsoft Access. The summaris" interview notes and attribute list will provide an excellent base for this prototype. You have identified the following steps to guide your implementation. 1. Develop a REA business process UML class diagram for El Paso Coffee Roasters, including classes, associations, attributes, and multiplicities. In determining minimum multiplicities, you may assume that agents and resources need to be entered into the database before any relationships with other classes are established, unless you think evidence would indicate otherwise. 2. Convert your REA business process model into relational table structures. I strongly recommend that you use an index card for each table, sketching out the columns with several blank rows. 3. Use the class diagram and table structures to create a relational database with Microsoft Access. Name your database with your last name(s) followed by ElPaso. E.g., if I were completing this project I would name it BaldwinElPaso.accdb (Access will add the accdb extension). Create all of the tables, specifying appropriate primary keys and connecting the tables together as appropriate via the relationship layout. Enforce referential integrity for all connections on the layout. 4. Enter at least three, but no more than nine, rows of data in each table, making sure that your data is consistent with the multiplicities on your class diagram. Only enter more than three rows of data when necessary to comply with all multiplicities. I recommend strongly that you start with an index card for each table (see step 2) and fill out the data in pencil. 5. Submit your completed database on USAonline. 6. Note that database design is an iterative process. Often mistakes made at the conceptual model level are revealed by the inability to enter appropriate data at the physical implementation level or by the inability to generate queries post-implementation. Therefore, I will not preview your conceptual models before you convert them into logical models and physical implementations. I will answer questions about the company's operations as if I am Louis, so be sure to frame questions accordingly