ACCOUNTING 480 Midterm THE BROWN POLICY INSTITUTE REQUIRED: Using the narrative and the attributes below, prepare: 1) a UML class diagram (REA diagram with classes, associations, and multiplicities) and 2) a list of corresponding relational tables (list the tables with all their attributes, identify the primary keys [with PK or underline], and indicate the foreign keys [with an asterisk or FK]). The table definitions should only use the attributes listed at the end of this case (remember that there should be no entities without attributes). You may abbreviate attribute names if the abbreviation is clear. List the tables in the following order: Resources, Events, Agents, Types, and Linking tables. You should apply the standard REA patterns wherever the narrative does not clearly specify otherwise, but avoid adding extra relationships that are not discussed. If the narrative is not clear, note any assumptions that you make. You will have one hour and 45 minutes to complete this exam. You may use any notes or material, but your work must be your own. 1 Background: Jerry Brown will soon retire as Governor of California, but he wants to remain a political force. He wants to support a variety of political, social, and environmental issues that he dealt with as governor. So, he plans to open a unique institute catering to business and local, state, and national political leaders. He decided that the best place for this institute would be on his 2,500-acre ranch in Colusa County California. Since most of the events/seminars can be held outdoors, he recently completed construction of an outdoor amphitheater run entirely on solar power. The Brown Policy Institute (BPI) will offer a variety of seminars, workshops, and conferences focusing on important issues like education, taxation, climate change, drought, fracking, solar power, alternative fuel vehicles, and high-speed trains. Many expect the BPI to become very successful. Jerry now asks you to analyze his requirements and help him design a system to support his enterprise. Brown's business requirements: During your interview with Jerry, he describes his business requirements for his sales (workshops) and purchasing processes as follows: Workshops. We will offer many workshops such as the Higher Education Innovation, Climate Change Responder, Technology of Self-Driving Electric Vehicles, Drought Tolerant Landscaping Ideas, Financing for Solar Initiatives, Personal Sustainability Planning, and High-Speed Train Bond Financing workshops next year, and will we add to this list over time. Our workshop schedule lists the workshop #, workshop name, date(s) offered, prices, and a description of the contents. For planning, we break each workshop on the schedule into several elements, such as, virtual reality tours, guest speakers, field trips, simulations, video presentations, meditation sessions. The elements are the building blocks for planning each workshop on the schedule. For example, the Climate Change Responder Package includes a virtual reality tour, emergency response planning, video presentations, lectures, and a field trip. Each workshop on the schedule will include multiple elements and each element is included in multiple scheduled workshops. The price of each workshop depends on content, i.e., the specific elements included in the package. The clients only see one single price for the whole workshop and must register for the workshop as one whole event. In other words, our workshops are like courses that our students register to take. We also carefully plan the cost of each workshop. We determine the quantity and cost of each inventory item we expect to use in each element. We also estimate the labor cost for each element. These estimates establish the expected overall cost of each element. That helps determine what to charge for the element when it is offered in any workshop. To verify our estimates, we record the actual quantity and cost of each inventory item used in a workshop as well as the total costs for each workshop. Client Registration: Clients will register for workshops in advance; each registration applies to one specific workshop on our schedule. We record revenue when clients attend the specific workshops, which we track by client workshop registration number. Note that a workshop on our schedule is different from an actual workshop, much like an item on a menu is different from the actual food served to the customer (one menu item could be served many times). The workshop schedule is our list of coming attractions that we advertise and use for planning. The workshops are the actual seminars. We assign several employees to work with each client attending the workshop depending upon the content (the elements) and the duration of the workshop. We expect our employees to provide the highest quality educational service for our 2 clients and we plan to hold them accountable. We therefore track the number of hours each employee spends with the clients in the workshop. Clients make an initial deposit against the price of the workshop when they register. Then, they pay the remainder of the amount due when they arrive at the ranch to take the workshop. Each client payment applies to one workshop. Inventory. We maintain an inventorysuch as tables, chairs, overhead projectors, books, sustainability reports and studies, pamphlets, iPads, virtual reality headsets, videos, briefcases with the Brown logo, and a variety of other classroom materials that we use or distribute during the workshops. We categorize the inventory by inventory category, such as electronics, recycled paper items, e-books, leather goods, instructional materials, and so on. Purchasing. We buy our inventory from a number of vendors. We place purchase orders in advance, then we record purchases when they deliver the inventory items. One purchase order can result in several deliveries depending on when we need the items. All our vendors require payment by the end of the month, so we may issue one check to pay for multiple purchases from a vendor. Other information. We record information about elements before they are planned for any workshop, and we add new workshop plans before any client purchases them. We assign each employee an employee number. Every employee can perform several different jobs at the institute, so we keep all the employee information in one set of records. We record information about all agents, resources, and type images before they are linked to other entities. 3 ATTRIBUTE LIST Cash account # Cash account balance Cash receipt # Cash receipt amount Cash receipt date Check # Check amount Check date Client # Client name Client organization Cost of this inventory item received on this purchase Cost of this inventory item planned for use in this element Cost of this inventory item used in this workshop event Date this client attended this workshop Date this client registered for this workshop Element # Element description Element name Employee # Employee name Employee wage rate Hours for this employee in this workshop Inventory category description Inventory category name Inventory item # Inventory item description Inventory item standard cost Number of inventory items in this inventory category Number of clients registered for this workshop Number of clients attending this workshop Planned total cost of this element Planned workshop price Price of this element in this workshop Purchase # Purchase date Purchase order # Purchase order date Quantity of inventory items planned for use in this element Quantity of this inventory item received on this purchase Quantity of this inventory item used in this workshop Quantity on hand (QOH) of this inventory item Vendor # Vendor name Workshop # Workshop date Workshop description Workshop name Workshop price Workshop registration # Year-to-date (YTD) $ purchases from this vendor Year-to-date (YTD) $ revenue $ for this Client 4