IT Lab: Entity Relationship Diagrams

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, which is available to you within VDI.
• For all diagram submissions, use MS Visio, which is available to you within VDI.

o Note: If you need assistance on how to get started with this tool, go to the references section at the end of this document.

• 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.

5. Submit the compressed file in Blackboard.

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.