1. Assign Tasks
   • Task A: Complete a UML Diagram & Table Listing to match Excel Data using the Glossary of Models as a quick start guide (two members suggested)
   • Task B: Complete the BPMN Diagram using the Glossary of Models as a quick start guide (two members suggested)
   • Task C: Complete the Access database import from Excel Data, build relationships, and required queries (one member suggested
   • 

COMPREHENSIVE EXERCISE: BAER BELLY BIKINIS' SALES TO RETAILERS Baer Belly Bikinis Inc. (BBB) is a small business located in Santa Monica, California. It sells swimwear and related products to Page '54 specialty stores throughout the United States. It also sells its products to individuals over a company website. Paige Eaer founded BBB almost 10 years ago after she graduated from the Fashion Institute of Design and Merchandising. She recognized the need for swimsuits sold as separates. Her business has grown rapidly, and now BBB has a Large following of customers who want to be able to mix and match to nd their idea] swimsuit. Currently, BBB products are carried in more than 1,000 specialty swimwear boutiques and online retailers. During an initial interview with Paige Baer, she outlined BEB's business processes. She acknowledged that she doesn't know much about accounting and information technology. As the business grew, her accounting suffered and information systems were added piecemeal. So, she is looking forward to an assessment of her requirements and recommendations that would position BBB for substantial further growth. First, Paige described BBB sales to independent boutique retailers. BBB uses a group of independent sales representatives to sell its products to boutique retailers around the country. These sales representatives are not EBB employees. They are paid commissions based on the dollar volume of sales. After working with the sales representatives, individual retailers call BBB to place their order for the upcoming season. BBB will then ship the products when they become available. The retailers are expected to pay for shipments within 30 days. BBB offers a prompt payment discount for payment within 10 days. The following material summarizes EBB's activities to prepare for and conduct the sales process: Summer - BBB nalizes designs for next year's products and prepares catalog materials with information on its future products. Fa]l to Spring . BBB sales representatives (independent agents working on commissions) visit retailers to develop sales. (BEB's payment of commissions to sales representatives is outside the scope of this case, although you should include the Sales Reps in your model and database.) . BEB's retailer customers place orders for one of two deliveries in season: (1} early spring {April} and [2] early summer [ManyuneL BBB records information on its retailers before they place any orders. - Upon receipt of nished goods from their manufacturer in April and May, BBB ships products to retailers. Retailers' payments are due 30 days after shipment and include shipping costs. Some retailers pay late. Some take advantage of the prompt payment discounts. BBB Finished Goods inventory BBB tracks its inventory by catalog number {catalogii}. Each product is identied by color code, use (eg.. tops or bottoms), and type Ife.g., the specific design of the piece). The color codes reect the color and fabric design options. and they can change each year. At the beginning of the season. the quantity on hand of each item is zero. The quantity on hand increases when BBB gets deliveries from its manufacturer If outside the scope of this case} and decreases as it ships the products to the retailers to ll orders. BBB Shipments EBB makes one shipment for each retailer order and records revenue when the products are shipped. 1|iifarehou se employees prepare the shipments. All shipments are made under HBE's contract with a shipping company. BBB charges customers for the cost of the shipment, so the amount due from the retailers depends on the wholesale price of each item, the quantity shipped. and the shipping cost for the shipment. Payments to the shipping company are outside the scope of this case. P3513155 333 Cash Receipts Retailer customers send payment by check according to the payment terms {BBB standard payment terms are 2% [0 days net 3|], meaning they receive a 2% discount on any amount they pay within 10 days with the net balance due within 30 days}. The payment from the retailer customer always applies to only one order, but sometimes the retailers send multiple checks for that order. A BBB employee [ accounts receivable} logs cash receipts from retailers. At the end of the day. the cash receipts are deposited intact into one bank account (BEH's main account). Each cash receipt is tracked by unique sequential cash receipt number. Exercise Requirements 1. Based on the preceding information and the following attributes list. prepare a UML class model and corresponding table listing BHB's sales to retailers. List the tables in the following order: resources, events, agents. type images, and linking tables. Identify the primary keys and foreign keys in each table. 2. [optional] Based on the preceding information, prepare a BPMN activity model that describes BEH's Sales to Retailers process. The model should begin with retailers placing orders and end when BBB collects payments for the sales to the retailers. 3. Your instructor will provide an Excel spreadsheet with the BBB information. Create a new Access database, and import each worksheet in the spreadsheet into the database. Set appropriate primary keys. 4. After importing all the data, create relationships among tables to implement your data model. 5. Prepare queries to answer the following questions: a. What was BBB's total revenue? b. What is BBB's account receivable balance for each retailer customer as of the end of June? c. Which BBB product generated the largest sales volume? {List all products in descending order of sales dollars}. M Account# Bank Account Description Balance_April_1 00579-09100 Bank of America Main Account $327,472.50 00579-09105 Bank of America Payroll $0.00 Step 3: Review the BBB Case Data in the Excel File provided 00579-09110 Bank of America Special Purpose $175,000.00 cash_rept# Rept Date Rept Amount customer# Order# Emp# Account# CR 50100 4/9/2022 $560.75 C900810 010107 E1025 00579-09100 CR_50101 4/10/2022 $1, 141.50 C901030 010116 E1025 00579-09100 CR_50102 4/12/2022 $998.50 C900270 010143 E1025 00579-09100 CR_50103 4/17/2022 $863.75 C901210 010128 E1025 00579-09100 CR_50104 4/21/2022 $655.25 0900540 010123 E1025 00579-09100 CR_50105 4/23/2027 $2.309.75 Can0480 010109 F1025 00579-09100 Emp# Last_Name First_Name Addr City Zip Phone Hire_Date Department Supy_Emp# Salary/Wage Pay_Amount E1001 Baer Paige 12416 Duchess Lane Huntington Beach 92647 714-846-7473 1/2/2002 Design E1001 Salary $100,000.00 E1002 Ajirotutu Alan 1805 Tintah Dr El Cajon 92019 714-393-2284 1/2/2002 Accounting E1001 Salary $70,000.00 E1003 Baxter Francis 29251 Sandalwood Ct Laguna Beach 92681 714-832-3336 1/2/2002 Customer Service E1001 Wage $19.25 E1004 Butler Barry 214 E Wyland Way Huntington Beach 92646 714-571-0308 1/2/2002 Marketing E1001 Salary $60,000.00 E1005 Cesaire Susan 32239 Zion Way Long Beach 90815 714-839-5962 1/2/2002 Production E1001 Salary $60,000.00 F1006 Flowl Becky 2498 F 7th St Santa Ana 92704 562-407-3969 1/2/2002 Shinning 61001 Wage $19 25 Color Code Color Name Fabric Cost Per Yard KORE Kona Reef $3.25 LABK Lahaina Black $3.00Catalog# Type_Code Use Description Size Color Code Color Name Retail_Price Wholesale_Price Standard_Cost BBB158-L-KORE 15B Bottom Adjustable Tie Side L KORE Kona Reef $50.00 $25.00 $11.25 BBB158-L-LABK 15B Bottom Adjustable Tie Side L LABK Lahaina Black $50.00 $25.00 $11.25 BBB158-M-KORE 15B Bottom Adjustable Tie Side KORE Kona Reef $50.00 $25.00 $11.25 BBB150-M-LABK 15B Bottom Adjustable Tie Side M LABK Lahaina Black $50.00 $25.00 $11.25 BBB158-S-KORE 158 Bottom Adjustable Tie Side S KORE Kona Reef $50 00 $25 00 411 25 Type_Code Use Description Yds Fabric Required 15B Bottom Adjustable Tie Side 0.9 25B Bottom Basic Sport 0.9 30B Bottom Seamless High Waist 1.25 41T Top Triangle 0.8 42T Top Beaded Halter 0.8 66T Top Banded Halter 0.8 68T Top V Neck Tankini 1.25 customer# customer name customer_address customer_csz customer_phone salesrep# C900040 ACTIVE 5491 C. PHILADELPHIA ST. CHINO, CA 91710 (909) 465-1600 SR1012 C900050 AMELIA ISLAND TRADING 3381 5. FLETCHER AVE. FERNANDINA BEACH, FL 32034 (904) 277-2919 SR1009 C900060 ANN FRANCES/SWIMLAND 3149 W. HALLANDALE BEACH BLVD. HALLANDALE, FL 33009 (954) 966-5055 SR1009 C900160 AQUA BEACHWEAR 14830 CAPTIVA DR. #340 CAPTIVA, FL 33924 (239) 395-5383 SR1009 C900180 BARE NECESSITIES 10751 FALLS RD. LUTHERVILLE, MD 21093 (410) 583-1383 SR1003 BEACU BOUTIQUE NCIADORT BEACH CA 03563Order# Order_Date Requested_Date Delivery_Date customer# Order_Emp# Ship_Emp Shipping Cost cash_rept# 010100 10/30/2021 4/8/2022 4/4/2022 C901530 E1026 E1010 $84.25 CR_50114 010101 10/30/2021 4/5/2022 4/5/2022 C900670 E1026 E1028 $74.00 CR_50120 010102 10/31/2021 5/30/2022 5/26/2022 C900700 E1011 E1016 $69.25 CR_50140 010103 11/1/2021 4/13/2022 4/12/2022 C900880 E1026 E1010 $124.50 CR_50126 010104 11/1/2021 5/26/2022 5/25/2022 C901100 E1005 E1018 $101.25 CR_50152 010105 11/2/2021 4/5/2027 4/4/2022 0900520 F1026 F1018 $115 75 CR 50111 Order# Catalog# Wholesale_Price Qty_Ordered 010100 BBB150-XL-LABK $25.00 4 010100 BBB258-5-KORE $26.00 10 010100 BBB42T-L-KORE $35.00 6 010100 BBB42T-5-LABK $35.00 4 010100 BBB6ST-S-LABK $39.00 10 SalesRep# Name Email_Address Phone Area SR1001 Amy Castlen ACastlen@gmail.com 702-259-4444 NV SR1002 Barbara Gilman BGilman@gmail.com 503-624-6104 WA, OR, ID, MT, AK SR1003 Janine Dean IDean@gmail.com 301-490-3923 NJ, MD, DE, PA, DC, NY, VA, NC SR1004 Leza Kloosterman LKloosterman@gmail.com 831-246-1854 Northern CA SR1005 Marty Mann MMann@gmail.com 508-740-3032 MA, CT, RI, VT, ME, NH SR1006 Marilyn Mckissick MMckissick@gmail.com 604-682-1899 Canada SR1007 Polly Mear PMear@gmail.com 936-598-2463 TX, OK, AR, LA, NM, KS, MO, MS SR1008 Rachel Reeve RReeve@gmail.com 303-482-1251 Mexico, Central and South America SR1009 Robert Salvador RSalvador@gmail.com 770-945-4181 FL, GA, AL, SC, TN, NC SR1010 Ron Scott RScott@gmail.com 808-778-3589 HI, South Pacific SR1011 Sally Springton SSprington@gmail.com 520-906-2395 AZ, IA, IL, IN, KY, MK, MN, ND, OH, SD, WI, NE SR1012 Terri Thouin IThouin@gmail.com 949-366-9616 Southern CA