BACKGROUND INFORMATION

Tic Toc Pop Dance Studio (TTP) is a new dance studio located in the metropolitan Orlando, Florida area.  TTP is owned and operated by two former United States Dance Champions.  TTP has no employees; 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.  Each class is taught by one instructor only. 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.  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 on-site 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.). Recurring expenses are placed in a separate table from inventory. For simplicity, these recurring expenses are only tracked as they are paid. 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.

It is your job to develop an REA diagram and conversion chart. The REA diagram MUST be completed in Visio.

With the exception of foreign keys and/or primary keys for relationship tables no attribute should appear more than once on your conversion chart.  Some attributes may not be used at all if they are derivable volatile attributes.  Translation: DO NOT put volatile attributes in the data model.  ATTRIBUTES – don’t add or subtract any attributes from the list provided.