Sales ProceduresCustomers place orders for baby items through Tobey Babys website, or by calling an 800 number between 6:30 AM and 12:30 PM EST Monday-Friday. Carmel estimates that 90% of sales volume will be through the website. Online orders are entered into the computer system automatically. Angela, the customer service representative, enters telephone orders into the computer system. Tobey Baby has a very active marketing program and would like to keep track of customers (both current and potential) addresses, phone numbers and email addresses for marketing purposes. Tobey Baby engages in print advertising and internet advertising and keeps track of the start date, end date, and total cost for each advertising campaign. All ad campaigns are created and managed by Carmel, so there is no need to link employees to ad campaigns in the database. When customers visit the website they are encouraged to register (800 customers are automatically registered). When a customer places an order, they are asked how they learned of Tobey Baby and the appropriate ad campaign number is noted on the sales order. Sales attributable to the different marketing campaigns are accumulated and used to evaluate the various marketing programs. To date almost all sales can be traced back to one of the ad campaigns.Customer registration forms contain separate lines for each of the following: 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 only one customer per order and of course, a customer can place many orders. When customers call the 800 number, the telephone system routes the calls to either Angela, the customer service representative or Anya, the on-call mom who helps when Angela is overwhelmed with taking orders. Each order (telephone and Web) has a unique sales order number. The company would like to keep track of the date the order was placed, the expected shipping date and the amount the customer will be charged. Additionally, each order indicates one or more items desired by the customer and the quantity of each product ordered. Both Angela and Anya have the ability to check inventory at the time the order is placed and the Web site does not permit ordering of out-of-stock items. Thus, orders are placed only for in-stock products. Customer orders are processed only on weekdays, Monday-Friday. All orders are processed and shipped within 2 business days. The inventory records include a SKU number which identifies each inventory item by color and size (i.e. identical items in different colors and/or sizes have different SKUs.) In addition, brand name (e.g. New Native, Peekaroo, Maya), style name, color, cost price, MSRP, and size are other attributes that must be tracked. Normally, the actual sales price of an item is exactly the same as the MSRP; however, periodically Tobey Baby has specials that temporarily reduce the actual sales price for an item to an amount that is less than the MSRP. As soon as each order is processed, an email is sent to the customer. This order acknowledgment does not constitute an invoice - customers receive an invoice when items are delivered. To ensure prompt customer service, all inventory items are organized and stored by category type (e.g. Just 4 Dad, Baby wearing, Sleep Time, Play Time, Bath Time, Diaper Duty and Potty Training, etc.). Each product category has a unique category identification number. A new product category is added only when a related product is added to the inventory table, and an item is assigned exactly one category identification number.Once a sales order is recorded, Kirsten, the packing specialist receives order details from the system. Then, she picks the products on each order from the storeroom shelves, and packages them to be shipped to the customer. She then creates an invoice and a shipping document indicating the products packaged and the shipper who will actually deliver the products to the customer. Tobey Baby primarily uses any one of five local shippers. Some shippers are used more often than others, (depending on the discount offered by the shipper), but each shipment is assigned to only one shipper. Some of the products sold by Tobey Baby are drop-shipped (shipped directly from the moms who supply the items), and Tobey Baby does not keep track of those shippers. This is always noted in the description of each item on the website, and in the email sent to customers when each order is processed. Each shipment from Tobey Baby is always related to only one order. Carmel estimates that local shippers are used 85% of the time. Tobey Baby does not accept partial payments. The company also does not currently accept payments with checks. Customers provide their credit card information to Tobey Baby when an order is placed. When the order is fulfilled (picked, packed, and shipped), Tobey Baby charges the customers credit card for the total amount of the sale.Naturally, sales are the primary source of cash receipts for Tobey Baby. However, Tobey Baby also occasionally receives checks for interest income (less than 20 per year). Erin, who is in charge of website changes and who is also the cashier records the payment in the system and generates a receipt for each sale; the receipts are emailed to the customer when items are shipped. Although Tobey Baby has three bank accounts, only one is used for cash receipts. Regarding all 5 employees, Tobey Baby would like to keep track of the date the employee was added to the team of moms and her monthly salary.The zip code table is included in this model but we only need to record address information for Tobey Babys customers.Note: This database is well-designed; thus all agents, resources and other related entities are added prior to recording events involving them.

Required

1.Complete the REA diagram: Using the partially completed REA model, identify the remaining entities and cardinalities. There is an REA Data Modeling Quiz #1 Part A in Canvas where you are to record your REA entities and the cardinalities. After you submit this portion of the quiz, you will have access to the correct responses. You will use the correct responses to complete requirement #2 below. You will need to do Part A well before the assignment is due in order to complete Part B.

2.In a Microsoft Word or Excel file, use the list of attributes provided below to convert your corrected REA model diagram into a set of relational database tables using the format below. Indicate the primary key(s), foreign keys(s), and other attributes for each table/class.

Table/Class Name Primary Key(s) Other Attributes Foreign Key(s) Actual Sales Price Actual Ship Date Ad Campaign Number Ad End Date Ad Start Date Ad Type Cash Account Number Bank Account Type Bank Name Brand name Campaign cost Receipt Date Receipt Number Category Type Cost Price Color Customer Last Name Customer Address 1 Customer Address 2 Product Category ID Size Shipper Name Customer First Name Customer Number Customer Phone Number Suggested Retail Price Amount Received Date Hired Shipper Discount Customer Email address Employee First Name Employee ID Expected Ship Date Invoice Number Order Date City Shelf Number Quantity Sold/Shipped Quantity Ordered Salary Sales Order Total Sales Order Number Employee Last Name Shipper Number Shipper Phone Number Item SKU Number Style Name Zip Code State Last Edit Date 10/1/2020 2:45 PM TOBEY BABY Entity #4 A B B Entity #2 Inventory 2. 8 5 B Entity #8 B B Entity #5 9 10 Entity #1 Entity #12 6 B 11 B B . Entity #9 B A B 15 Entity #6 A 12 13) B 00 B Entity #10 A B B B Entity #3 Entity #7 14 Entity #11 Employee Table/Class Name Primary Key(s) Other Attributes Foreign Key(s) Actual Sales Price Actual Ship Date Ad Campaign Number Ad End Date Ad Start Date Ad Type Cash Account Number Bank Account Type Bank Name Brand name Campaign cost Receipt Date Receipt Number Category Type Cost Price Color Customer Last Name Customer Address 1 Customer Address 2 Product Category ID Size Shipper Name Customer First Name Customer Number Customer Phone Number Suggested Retail Price Amount Received Date Hired Shipper Discount Customer Email address Employee First Name Employee ID Expected Ship Date Invoice Number Order Date City Shelf Number Quantity Sold/Shipped Quantity Ordered Salary Sales Order Total Sales Order Number Employee Last Name Shipper Number Shipper Phone Number Item SKU Number Style Name Zip Code State Last Edit Date 10/1/2020 2:45 PM TOBEY BABY Entity #4 A B B Entity #2 Inventory 2. 8 5 B Entity #8 B B Entity #5 9 10 Entity #1 Entity #12 6 B 11 B B . Entity #9 B A B 15 Entity #6 A 12 13) B 00 B Entity #10 A B B B Entity #3 Entity #7 14 Entity #11 Employee