On Balboa Island near San Diego, in the shadow of the famous Hotel Del Coronado, where Marilyn Monroe starred in one of her most famous roles in Some Like It Hot, lies a unique beauty salon called Marilyn Monroe Makeovers or MMM for short. MMM's founders, Lucy and Ethel, got the idea for their business from TV talk shows where they observed that ordinary people delighted in letting a team of beauty consultants "make them over" in a new image by changing their hair, their grooming, their cosmetics, their clothes, and sometimes even their demeanor. Lucy and Ethel at first targeted only women who wanted to look and feel like Marilyn Monroe for their business, but they discovered over time that there was a market for men also who wanted to be made over in the image of some of the men in Marilyn's life like Arthur Miller, Joe DiMaggio, or Clark Gable. Based on their robust business in Southern California, they were thinking of expanding to other locations (like Michigan and Wisconsin) and even to other stars (like Claudette Colbert and Audrey Hepburn), but for the present, their booming Balboa business took all their energies. MMM grossed over ten million dollars and employed more than 50 people.
When MMM first does a makeover for a client, they assign a client# and note the person's address, and telephone#. Clients can either pay for all consultations received in a particular month on the 20th of the following month or they can spread their payments for each consultation over three months. Makeover consultations last 1-5 hours and are always finished on one day. When clients leave the shop, they are given remittance advices for their payments which they mail back in with their checks. Repeat business is a major part of the MMM marketing plan, and many clients use MMM multiple times, although Lucy and Ethel's exorbitant price structures do encourage them to use other establishments for more routine beauty and grooming services. Sometimes for marketing purposes, MMM relates one consultation to a single other that caused it (like a "Joe DiMaggio" who wants to match his "Marilyn"), but this is only done in 20-30 % of the cases, and payments are always tracked by individual consultation.
A consultation is always directed by one employee who plans the makeover and performs some or all of the work. This "director" is assisted by 1-3 other employees in about 70% of the consultations. Employees often serve as both directors and assistants on a given day, depending upon the flow of customers. For costing and quality control purposes, MMM notes the directing or assisting roles and times of each person who works on a particular makeover. Consultations come in various types, ranging from the "Major Marilyn Makeover" to the "Joe DiMaggio Haircut and Trim." Each consultation type has a standard base fee to which is added the labor and product costs needed to make that individual consultation successful. At present, MMM offers 17 of these types, but that number goes up and down based upon fashion and fads. Lucy and Ethel figure out the composition of a new consultation type before they offer it. Product costs involving items such as cosmetics, clothing, jewelry, wigs, hairpieces, Marilyn paraphernalia, etc. are incurred in over 90 % of the consultations, and MMM tracks them in detail because consultation directors are paid commissions on them. Obviously, directors push as many products as they can on to each client, although some clients resist products altogether. Products are never sold without consultations.
Products are purchased from various vendors around the country. Each product type is given a product# and is usually supplied by multiple people. Before any purchases take place with a vendor, that supplier is asked to provide an undiscounted price quote for each item they intend to supply to MMM. Buying employees negotiate quantity discounts for all the different products on each purchase separately, and MMM tries to buy in large lots if they feel they can anticipate a need for "hot items." Purchase payment terms depend upon the vendor and range from all payments due at the end of the next month to one year installments. Lucy and Ethel use multiple cash accounts for their receipts and disbursements, although each transaction involves just one account. Buyers are often the same people who do the makeovers, and MMM does not distinguish between these different types of employee. (**narrative needs more cardinality hints**)
REQUIRED: Using the attributes below (don't add or subtract), build a data model (classes, associations, attributes, multiplicities) and a minimal relational database for MMM. Also, diagram as complete a value chain as you can for MMM given its minimal description (just two business processes).
-check# -disbursement-amount -purchase-order-$amount
-purchase-order# -quantity-of-product-purchased -product#
-undiscounted-price-quote -product-description -vendor#
-vendor-address -consultation-type -employee#
-employee-name -YTD-sales-of-this-consultation-type -client#
-individual-accounts-payable -individual-accounts-receivable
-base-consultation-fee -client-address -client-tel#
-employee#-for-this-cash-transaction -actual-unit-cost-of-purchased-item
-consultation-projected-length -quantity-of-product-used -consultation#
-disbursement-voucher# -client-check# -cash-account#
-consultation-actual-length -time-spent-on-makeover-by-assisting-employee
-time-spent-on-makeover-by-director -total-$amount-due-for-consultation
-remittance-advice# -receipt-amount -cash-account-balance
-account#-for-this-client-payment
1.complete the REA models for the two implied processes;
2.combine the two processes;
3.add the cardinalities
4.add required relationship entities
5.add attributes to entities
6.create tables in a database
7.connect the tables in the database