Vivian’s Fashion Factory - I
Vivian’s Fashion Factory (VFF) was named for its founder, a Lansing entrepreneur. VFF makes and markets multiple types of clothing lines for both men and women. Each clothing line (such as men’s business suits, men’s casual suits, women’s business skirts, etc.) is somewhat standardized in the sense that VFF has developed a standard plan for manufacturing items of that line. However, the actual clothing items sold (always identified by an item#) do differ substantially, principally on the basis of the different types of raw materials used to manufacture those items. VFF is designing new items all the time, although most new items do not venture outside of their established clothing lines. Once every 2-3 years, VFF does create a new clothing line, but they never delete old clothing lines from their database. Once a new line is established, the company can populate that line with any number of new items. VFF does most of its selling to various classes of retail outlets who in turn often make minor adjustments to the clothing items for their own final customers.
On the pages that follow, two different parts of VFF’s operations are described. Each part has a more detailed narrative description and a list of attributes to be used in solving that particular part of the problem. Try to do an entity-relationship diagram and a set of relational tables for part (a) first. Then, go on to attempt the same for part (b). In both cases, limit yourself to the attributes given on just that part.
SIMPLIFYING ASSUMPTIONS:
In order to simplify the solution of this modeling problem, you may make the following assumptions:
· You may assume that all “economic agents,” all “economic resources,” and all types are put into the database before any relationships with other entities are instantiated.
· VFF keeps all of it s employees in separate classes like buyers, cashiers, etc., all of whom are keyed on “employee#.” Don’t try to model a generalized class called just “Employee.” Don’t worry about modeling other classes of employee even though they would clearly exist in the company. Just model the ones you need in each section of the exam, and restrict yourself to the attributes given for that section.
Vivian’s Fashion Factory (a)
The composition and quality of raw materials are two of the most important factors that govern VFF’s financial success, so the company manages their acquisition very carefully. Most (but not all) raw material purchases are done under the auspices of purchase orders (PO) negotiated by buyers. Each purchase order has multiple projected deliveries, each of which is for a single type of raw material, and each projected delivery corresponds to a single delivery payment voucher, each of which has a projected dollar amount to be paid for a delivery. Since scheduled deliveries and scheduled vouchers are governed exclusively by purchase orders, VFF does not track the buying employee or vendor for either of them.
A scheduled delivery can be fulfilled by either an actual receipt of the designated type of raw material or by an actual receipt of an approved substitute type of raw material. Not all of VFF’s raw materials have accepted substitutes, but most have 1-2 other types approved. Both the original and the substitute raw materials are identified by number-codes controlled entirely by VFF. Sometimes, a certain raw material can be designated as a substitute for multiple others. Projected delivery quantities are always the same as delivery quantities actually received because VFF does not allow projected shipments to be partially filled. When vendors find that they have multiple projected deliveries due on the same date, they most often aggregate them into one actual shipment. Projected deliveries are identified by a delivery-lot#.
Scheduled payments are not tagged to specific funds, but their actual dollar amount may differ from their scheduled amount because substitutes often have slightly different unit prices. All cash disbursements are done by check; however, check numbers are only unique within a certain cash account. If multiple vouchers are due to the same vendor on a certain day, the cashier will cut just one check. In a small minority of cases, VFF makes immediate raw material purchases “on the fly” (that is, not under the auspices of a purchase order). When this done, the goods arrive immediately, and the payment can be made either immediately or in installments.
Vivian’s Fashion Factory (a) –continued
REQUIRED: Using the data items listed on this page, construct an E-R diagram (entities, relationships, participation cardinalities) plus a minimal relational database for VFF (a). By “minimal,” it is meant that the option to post a key into an existing table because of either required participation or a discernible high load should be exercised where appropriate. Make sure you have good reasons for putting model components in or for keeping model components out. With the exception of posted keys, please do not add or delete items from this list. Some data items on the list may be used more than once in building the E-R model and the relational database.
-projected-$-amount-of-delivery-payment -scheduled-delivery-date
-buyer-for-this-purchase-order -PO-dollar-amount
-delivery-payment-voucher# -delivery-lot#
-projected-delivery-quantity-of-raw-material -projected-date-of-delivery-payment
-actual-$-amount-of-delivery-payment -raw-material-for-this-delivery-lot
-approved-unit-price-for-this-raw-material -raw-material#
-present-quantity-on-hand-of-raw-material -employee#
-actual-received-quantity-of-raw-material -buyer-rating
-timestamp-of-this-purchase -vendor-for-this-purchase
-dollar-amount-of-purchase -check#
-cash-account# -cashier-for-this-disbursement
-vendor-for-this-disbursement -vendor-name
-present-accounts-payable-balance -vendor#
-purchase-order-date -check-amount
-actual-delivery-quantity-of-raw-material -purchase-order#
Vivian’s Fashion Factory (b)
VFF employs a legion of salespeople who push its products to various customers (which again are usually retail outlets). Most customers have a presently assigned salesperson, and all sales are credited to a single salesperson. Sales are identified by an invoice#, and they may consist of multiple items. All items belong to clothing lines.
All VFF customers are grouped by categories designed to identify the demographics of the populations who frequent that type of retail outlet. On a rotating basis, the company conducts sales campaigns, each designed to appeal to either a single category of customer or to a group of clothing lines. VFF does not link actual sales directly to their periodic campaigns. However, they do track budgets and accumulated costs for campaigns, and they do assess the effectiveness of campaign managers.
There is often more than just one sales campaign going on at a certain time; they last from 4-8 weeks. Each campaign is managed in its entirety by one salesperson who plots strategy (such as the amount of the advertising budget and the pattern of sales calling). All other salespeople are assigned by default to just one campaign at a time, and VFF notes the dates they rotate on and off that campaign. Once salespeople leave a campaign, they never come back to it. Campaign management duties rotate among the sales staff, and about 80% of the staff do it at one time or another.
Vivian’s Fashion Factory (b) –continued
REQUIRED: Using the data items listed on this page, construct an E-R diagram (entities, relationships, participation cardinalities) plus a minimal relational database for VFF (b). By “minimal,” it is meant that the option to post a key into an existing table because of either required participation or a discernible high load should be exercised where appropriate. Make sure you have good reasons for putting model components in or for keeping model components out. With the exception of posted keys, please do not add or delete items from this list. Some data items on the list may be used more than once in building the E-R model and the relational database.
-sales-campaign-theme- name -sales-campaign# -item#
-salesperson-commission-rate -campaign-budget -item-price
-sale-invoice # -sale-$-amount -customer#
-primary-age-group-that-this-category-appeals-to -individual-accounts-receivable
-date-rotated-on-a-sales-campaign -date-rotated-off-a-sales-campaign
-sale-date -employee#-for-sale -year-to-date-$-sales-of-clothing-line
-primary income level that this category appeals to -employee#
-accumulated-campaign-cost -salesperson-name -customer-category#
-actual-count-of-salespeople-assigned-to-a-campaign clothing-line-name
-year-to-date-$-sales-of-clothing-line-to-customer-category