Lab: Use the following statement from Mountain View's CEO in completing the following lab:
Customers log on to our Web site and place an order or call an employee who places the order on the customers' behalf. All orders contain the customer information, the order detail, which has information about the products, and quantities that the customer purchased, and the payment method. When we receive the order into the system, the customer information has already been checked and crucial bits, such as the customer's address, have been verified by the site. The first thing we do is process the order items. We make sure that the products being purchased are in stock and we place a hold on those products. If a product is not in stock, we place that item or the entire order on back order, depending on the customer's preference. Products that are in stock have a hold placed on them. Once the products are on hold, we process the payment for the order. By law, once we accept payment, we must ship within 30 days. This is why we make sure the product is on hold before we process the payment. For payment, we take credit cards, gift cards, and direct bank draft via an electronic check. After the payment has been cleared, we send the order to the warehouse where is it picked, packed, and shipped by our employees. We do this for about 1,000 orders per week.
1. Below are the possible entities we have come up with so far, with one missing. Identify the missing entity.
Order
Order Detail
Product
Payment
Employee
2. Each of the entities listed in question 1, including the identified missing entity, describes something that the business works with and needs to store data about. Most attributes are easy to pick out, but some are harder for various reasons. For example, parts of the statement need clarification, and therefore the captured data and its attributes would need to be defined. However, at this point, based on the statement and the list of entities, list each entity's attributes (aka fields in each table). Make sure that you identify any and all keys (primary and foreign).
3. Now, having drawn the following rules from the statement, determine the type of relationships (0:1, 1:1, 1:M, M:N) that exist between our entities, as it stands:
Customers place orders
Employees place orders
Orders contain order details
Order details have some quantity of products
Orders contain payments
4. Last, use MS Visio to draw an entity-relationship diagram (ERD).
Note: MS Visio is available to you within VDI. If you need assistance on how to get started with this tool, go to the references section at the end of this document.
Submit the following:
Answer to question 1
Answer to question 2
Answer to question 3
Copy of the ERD you produced in question 4