Objectives: Decipher Customer Statement & Develop Entity Lis

Objectives1 Decipher Customer Statement2 Develop Entity List3 Ad

Objectives: 1. Decipher customer statement. 2. Develop entity list. 3. Add attributes for each entity. 4. Establish the business rules. 5. Establish relationship types between entities. 6. Using Microsoft Visio, draw an ERD. Submission requirements: · For all text and image submissions, use MS Word, which is available to you within the Virtual Desktop Infrastructure (VDI). · For all SQL code submissions, use MS Word · For all diagram submissions, use MS Visio · If the submission is more than one file: 1. Name each item appropriately. a. For example: LAB2-ERD-yourName.vsd, LAB2-Questions-yourName.docx 2. Save each item in a single folder. 3. This folder should also be named appropriately. a. For example: LAB2-yourName 4. Compress the folder. 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). Submit the following: Answer to question 1 Answer to question 2 Answer to question 3 Copy of the ERD you produced in question 4

Paper For Above instruction

The first step in developing a comprehensive database system for Mountain View’s order processing involves identifying the missing entity from the initial list. The entities already listed include Order, Order Detail, Product, Payment, and Employee. Given the context provided by the CEO’s statement, a crucial entity that facilitates tracking the status of products throughout the order process is missing. This entity is "Product Inventory" or "Stock," which records stock levels, availability, and status of each product. Its inclusion is vital for managing stock checks, holds, back orders, and product availability, which are central to order fulfillment and inventory management.

Deciphering the customer statement provides a detailed understanding of the data entities involved in the order processing workflow. The core entities identified are Customers, Orders, Order Details, Products, Payments, Employees, and the newly added Inventory. Each of these entities plays specific roles and requires specific attributes. For example, the Customer entity includes attributes like CustomerID, Name, Address, ContactInfo, whereas the Order entity tracks OrderID, OrderDate, CustomerID, and Status. The Product entity encompasses ProductID, Name, Description, Price, and StockLevel, which links directly to the Inventory entity. Payment records include PaymentID, PaymentMethod, PaymentDate, and Amount. Employees are characterized by EmployeeID, Name, Role, and ContactInfo, reflecting the personnel involved in processing orders.

Business rules derived from the statement help define the relationships among entities. Customers can place multiple orders (1:M), and each order is associated with exactly one customer. Employees process orders, establishing a 1:M relationship between Employees and Orders. Each order contains multiple order details, with each detail corresponding to a specific product and quantity, indicating an M:N relationship between Orders and Products that is resolved via the Order Details entity, which acts as a junction table. Payments are tied to orders, with one order having one payment (1:1), but multiple orders could potentially share a payment method, indicating a 1:M relationship, contingent upon the business rules. The stock and inventory statuses influence the order processing flow, making the Inventory entity essential for real-time stock management.

The ERD drawn using MS Visio illustrates these relationships with properly defined primary keys, foreign keys, and relationship types. The entities' attributes are represented along with connections indicating the nature of their relationships: one-to-many, many-to-many (resolved via junction tables), and one-to-one where appropriate. Correct cardinality ensures clarity for implementing an efficient database schema, supporting business operations such as order placement, inventory management, payment processing, and shipment tracking.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.