What are the 8 entities, and the primary keys and attributes of those 8 entities in the
Question:
What are the 8 entities, and the primary keys and attributes of those 8 entities in the case problem below.
Sunshine Candy Shop (SCS) has been serving candy lovers, both wholesale and retail, from its Burnaby, British Columbia location since 1977. Originally, Bill Bellows specialized in providing hard candies and mints to restaurants, but recently, Bill and his daughter Miranda (recently graduated from school) expanded SCS to carry many confection types, specifically piece-candy, bar-candy, chocolates, jellies and fancies. Although not as large as competing shops in the B.C. lower mainland, they have developed a growing customer base due to their confections' freshness and appeal and their expertise in marketing to senior citizens and holiday gift-givers.
Over the past few years, SCS has expanded from its three Vancouver-area sales outlets to include one new retail store in each of Victoria B.C., Whistler B.C., Banff Alberta and Jasper Alberta. SCS's business has grown to such a size that Miranda is considering having a customized computer system built, to serve her base of 2900 current (mailing-list) customers. For each current store, Miranda tracks its SCS-generated store-number, its address (made of two separate address lines plus municipality, province-state, and postal code), the date it was first opened, the floor area in square meters, and the employee # of the store manager.
"Our sales reps respond to orders by phone, e-mail or in person", Miranda said recently. "The average mailing-list customer places just over six orders per month. An average order contains 8 order-items, each of which includes a sequence number which is unique within each order (each order-item cannot be uniquely identified without including the order number). For each order-item, if the quantity ordered is large, the customer may, for that order, negotiate an actual price for that confection that is lower than the suggested retail price. The customer is then given an order-number, a calculated sub-total cost for each distinct confection ordered, and a calculated total (including GST) for the entire order. Currently, we keep 6 years' worth of order data. Only half of our order volume is from tracked (mailing-list) customers: the rest is from anonymous (walk-in) customers. Consider the sample order-form below..."
Order #: 1944 | Placed Date : 2022-Jan-19
Shipped Date: _________ | Cust# : 701
Account#: ___ | Cust Name: Peters, Joe | Taking Emp#: 102 Store#: 4
| |
Order #: | Order-Item Sequence# | Confection # | Actual Unit Price
| Quantity Ordered | Calc'd Subtotal |
1944 | 1 | 4811 | .50 | 300 | 150 |
1944 | 2 | 2380 | 10.00 | 15 | 150 |
1944 | 3 | 15 | .80 | 50 | 40 |
1944 | 4 | 2944 | 1.50 | 80 | 120 |
1944 | 5 | 1313 | .05 | 2000 | 100 |
|
|
| TOTAL OF ITEMS | ---------------------> | $560.00 |
|
|
| ORDER TOTAL | including GST:---> | $588.00 |
Miranda's husband Barry manages inventory. "We identify each of our 400 confections with a unique number (generated by us)", said Barry, "and we also track a descriptive name, our suggested retail price for that confection, the quantity currently in stock and the code that identifies its producer. As well, we track a code (eg. 'piec','bar','choc','jell','fanc') to associate each confection with one of our confection-types: full descriptions ('piece candy', 'bar candy', 'chocolates', 'jellies', 'fancies') for each type are also kept. For each type, sales target amount (in dollars) is tracked. For pre-packaged confections, we track package-gram-weight under the confection (and also enter the units in the order-item's quantity-ordered field): for more "bulk" confections, we leave the confection's package-gram-weight field empty (and then enter grams in the order-item's quantity-ordered field). Also, for each of our 29 producers, we record their name, municipality and province/state code."
Louis, SCS's accountant and employee manager, describes the customer accounts as 'pretty simple'. "Each order can optionally be paid for by a "Sunshine" account", says Louis. "Each customer can be associated with one or more such accounts (290 in all). For each account, we track its SCS-generated account #, effective-date and its credit-limit."
"As for sales force management, we assign an employee number and a supervisor to each new sales rep. Sales reps can earn a commission at a fixed rate. The extract below shows information about some of our customers and their assigned sales rep ..."
Cust # | Max Credit Limit | Last Name | Given Names | Sales Rep Emp# | Rep Name | Rep Job Type | Rep Hire Date | Rep salary | Rep commission | Rep Supervisor Emp# |
1109 | 2000 | Johnson | John | 101 | Peters | SalesRep | 2005/01/14 | 1800 | 200 | 102 |
1114 | 2400 | Peters | Angie | 101 | Peters | SalesRep | 2005/01/14 | 1800 | 200 | 102 |
809 |
| Li | Wing | 102 | Alou | SalesRep | 2002/11/05 | 2200 | 700 | 89 |
219 |
| Jones | April | 102 | Alou | SalesRep | 2002/11/05 | 2200 | 700 | 89 |
332 |
| Rowan | John | 102 | Alou | SalesRep | 2002/11/05 | 2200 | 700 | 89 |
771 | 1000 | Glover | Penny | 101 | Peters | SalesRep | 2005/01/14 | 1800 | 200 | 102 |
"As for our employees in general (55 in all): employees who aren't sales reps are tracked with the same attributes as those who are... (but only sales-rep employees will be associated with a customer: eg. sales-rep employees 101 and 102 shown in the fifth column above)."
"Usually, during each customer's first order, any customer who agrees to be on the mailing list is assigned a sales rep with whom they can discuss complaints or discounts. At the same time, we obtain personal data from the customer, including "snail mail" address (made of two separate address lines, municipality, province-state, and postal code) and email address. We also assign each customer a SCS-generated customer number, and then we mail out a personalized customer card for their wallet. We might negotiate a maximum credit-limit amount for that customer. Customers ("walk-in") who do not identify themselves on orders (anonymous) are not tracked."
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George