TIC TOC POP Dance Studio ACG 3401 Due: February 18, 2016 BACKGROUND INFORMATION To be done in 90 120 minutes Without o notes o help o TV or stereo Tic Toc Pop Dance Studio (TTP) is a new dance studio located in southwest Florida. TTP is owned and operated by two former United States Dance Champions. One partner (Tom) oversees the day-to-day studio operations and the other partner (Theo) is responsible for all of the accounting and financial reporting. Tom and Theo are equal partners, each having contributed $140,000 to start the business. In addition, TTP received $50,000 from SunBank in the form of a loan. TTP instructors are all independent contractors (vendors), thus TTP has no employees. TTP offers classes in ballroom, ballet, jazz, tap and hip-hop. Tom has excellent connections on Broadway, having received several Tony awards and Theo has equally stellar music video connections having directed numerous Grammy winning music videos, thus there has been a good bit of buzz related to the opening of TTP. Classes are offered at various levels (e.g. beginners, advanced, and competition) and are offered multiple times per week. Students can register for multiple classes (e.g. ballet, level 1, M, W, F @ 9:30 AM and ballroom level 3 T,TH @ 11:00 AM). There is a standard hourly charge for each level (i.e. beginners are $25 per hour, advanced is $45 per hour and competition, which is one-onone instruction, is $125 per hour). Each dance contract (order) specifies the classes the student has selected (including level, the number of sessions per week and the number of weeks contracted per class (classes for 15 or more weeks receive a 10% discount off the standard rate). In addition, students and walk-in customers may purchase dance paraphernalia and place special orders for items which are currently out of stock, such as leotards, dance shoes, costumes etc. TTP plans to have periodic clearance sales for slow moving items. Students and other customers have two options for scheduling classes (paraphernalia is only sold on-site), at TTPâ€™s dance studio or through its Website â€œTTP.Comâ€. TTP estimates that 92% of the class sales volume will be through the website and 10% of all orders will be for paraphernalia only. To simplify the record keeping Tom, who handles all onsite transactions including instructor and vendor payments and forwards the related paperwork to Theo, has decided to create dummy sales orders for on-site sales of paraphernalia, thus every sale is related to exactly one sales order. When customers visit the dance studio and/or the website they are encouraged to register (there is a registration kiosk in the dance studio). Customer registration captures the following information: address line 1, address line 2, city, state, zip code (9 digit), the customer’s phone number (with area code), and email address if applicable. There is one customer per order and of course a customer can place many orders. Each order has a unique â€œsales order number (contract number)â€. Each order includes at least one TTP class or product desired by the customer and documents the individual quantities of any products ordered. The company would like to keep track of the date the order was placed, the beginning date and number of weeks for each class/level, the expected completion date (if applicable), the expected availability date for any products ordered (if applicable), and the total amount the customer will be charged; the sales tax rate is 7%. Each type of class (e.g. advanced) has a unique code and a standard hourly rate and each product sold has a manufacturerâ€™s suggested retail selling price (MSRSP). The product inventory records include a “SKU” that uniquely identifies each product (e.g. RTUTU2 is a red tutu, size 2). After the order has been entered, a copy of the order is faxed (or emailed) to the customer as confirmation of the order. This order acknowledgment does not constitute an invoice– customers receive an invoice when they pick up the products and/or they attend the first class session. A customer may register for multiple classes on one sales order however, they are billed for classes on the date they first attend a class. Thus one sales order may generate multiple invoices; however multiple orders are never combined on one invoice and there are no refunds for paraphernalia or classes. Once a student attends the first class the contract is considered complete. In the case of on-site sales, when the customer is ready to â€œcheck-outâ€ Tom totals the class registration contract and any additional items that the customer is purchasing. Customers have several payment options, they may pay in full at the time of the sale, they may make three equal installment payments or they pay in six equal installments, with the last payment due before the last scheduled class; payments for products are due in full when received by the customer. When Tom enters the amount received from the customer for on-site sales, the system records the sale and the cash receipt; the customer receives a receipt detailing their purchase and the balance due if applicable. Finally, customers may pay any outstanding balance, which may include several different orders, in full at any time. Tom is also responsible for maintaining the appropriate inventory levels for all the dance paraphernalia sold on-site at TTP. Twice a year, Tom receives listings of available items from their suppliers; the suppliers also provide periodic updates (i.e. addition and deletions of specific items) as needed. TTP only stocks medium to high volume items but will special order other items based on customer requests. Tom uses class registration numbers to calculate the expected demand for paraphernalia and maintains a database listing of all available items even if they donâ€™t currently stock that item. Tom fills out purchase requisitions for the on-site items as needed. These purchase requisitions are forwarded to Theo who actually places all of the orders. A purchase requisition may be related to many different purchase orders (e.g., one PO for tap shoes and another for the costumes) Theo records any new items (Tutus, shoes etc) in the database prior to placing the actual order, including special orders and when TTP adds a new supplier, the supplier is added to the supplier dataset before Theo places any orders. To manage complexity, Theo places separate orders for each purchase requisition and instructs vendors not to combine multiple purchase orders on a single shipment. When the Theo places the order with the supplier, Theo records the quantity on order, and when the shipment is received, Tom records the quantity received. In most instances, the shipments received exactly reflect the quantity ordered, however, occasionally the supplier is unable to completely fulfill the request and a partial shipment is sent and the balance is shipped at a later date(s). TTP has high quality vendors and less than 1% of the paraphernalia purchased is returned due to manufacturersâ€™ defects. When goods are received, everything is thoroughly examined before it is placed in inventory, thus a purchase is never related to more than one purchase return and each purchase return is related to exactly one purchase. Tom processes cash disbursements monthly, issuing one check per supplier/instructor. These monthly disbursements include payments for recurring expenses (e.g. rent, utilities etc.), however, these payments are not related to purchases and are related to other expenses. Other expenses account for approximately 10% of total cash disbursements. Tom always pays the outstanding balance due on all accounts in full at the end of each month. Periodically, TTP receives checks in the mail from customers as payment of invoices; the company does not currently provide for credit card sales. A copy of the invoice is normally enclosed with the customerâ€™s check; however, occasionally customers simply indicate the invoice number on the face of their check. Naturally sales of services and products are the primary source of cash receipts for TTP. Tom records the cash receipts in the system and generates a receipt for each invoice that is paid; the receipts are mailed to the customer. Although TTP operates three bank accounts, only one is used for cash receipts. Note: 99% of all cash receipts are sales related. This database is well-designed; thus all agents, resources, typification (reference) tables are added prior to recording events involving them. Required 1. Using the template and the data items provided below, construct an UML including multiplicities. 2. There is a Take Home Quiz link in your Activities Week 7 where you are to record your UML Classes and Multiplicities. After you complete this portion of TH-Quiz you will have access to the correct responses. 3. Using the CORRECT responses from #2 above, design a relational database Tick Tock Pop. Use the following format for the DB design: Table Name Primary Key/s Attributes Foreign Key/s Make sure you have good reasons for putting model components in and for keeping model components out. With the exception of posted keys, please do not add attributes to this list. You must bring a hard (paper) copy of your conversion chart to class to receive credit for this assignment. Account Type Actual End Date – Class Actual Hourly Rate – Class Actual Start Date – Class Actual Unit Sales Price Amount Applied to Sale Bank Acct Number Bank Name Billed Amount Budgeted Amount Cash Acct Number Cash Disbursement Amount Cash Disbursement Date Cash Disbursement Number Cash Receipt Number Check Number City Class description Class Name Class schedule Class ID Commission Rate Condition Cost Per Unit Received Cost Per Unit Returned Credit Amount Customer First Name Customer Last Name Customer Address1 Customer Address2 Customer Phone Number Customer ID Description Estimated Unit Cost Expense Amount Applied Expected Start Date Expense ID Expense Item Description Instructor Number Invoice# Invoice Date Level Code Level description PO# PO Date Price Quoted Quantity In Sale Quantity Needed Quantity Ordered – PO Quantity Ordered -SO Quantity Received Quantity Returned Reason Returned Received Date Receipt Amount Receipt Date Receiving Number Requisition Date Requisition # Return Date Return ID Receiving Report Total Amount Sales Order # Sales Order Date SKU State STD hourly Rate Total PO Amount Total Sales Order Amount Type Code Type Description Unit MSRSP Vendor Address Vendor Category Vendor Name Vendor Number Weeks Zip Code Required Using the template and the data items provided below, construct an UML including multiplicities.