Data Model Instructions Create a data model for a medium-sized business that uses the common patterns discussed in this module. Use an entity relationship diagram to document your model. Your solution...

Data Model

Instructions

Create a data model for a medium-sized business that uses the common patterns discussed in this module. Use an entity relationship diagram to document your model. Your solution must be normalized to third normal form as described in Module 3 (Topic 3.2)(which basically talks about the normalization process, 1NF, 2NF, 3NF) (Normalization is a best practice for databases used for business transactions.)

Include the following entities and attributes (listed in parentheses):

• Employees (Employee ID, Given Name, Middle Name, Family Name, Birth Date, Pay Grade Code, Pay Grade Name. Home Phone, Business Phone, Mobile Phone, Home Address)


• Customers:

• Individual Customers (Customer ID, Customer Given Name, Customer Family Name, Business Phone, Mobile Phone, Billing Address, Mailing Address, Shipping Address, Work E-Mail Address)


• Organizational Customers (Customer ID, Customer Name, Business Phone, Billing Address, Mailing Address, Shipping Address, E-Mail Address, Web URL)

• Suppliers, which can also be customers. (Supplier ID, Supplier Name, Business Phone, Mobile Phone, Billing Address, Mailing Address, Shipping Address)


• Organizational Structure

• Division (Division ID, Division Name, Division Manager Employee ID, Division Manager Name)


• Department (Department ID, Department Name, Division ID, Division Name, Department Manager Employee ID, Department Manager Name)


• Work Group (Work Group ID, Work Group Name, Department ID, Department Name, Work Group Leader Employee ID, Work Group Leader Name, List of Employees Assigned to Work Group)

• Contact Methods – Generalize all the phone numbers, e-mail addresses, and home/shipping/mailing/billing addresses into common Contact Methods.


• Purchase Orders (Supplier ID, Order Date, Purchase Order Number, Item Description, Quantity, Price Each, Item Subtotal [Quantity * Price Each], Order Status Code)

Note: All physical addresses include Address Line 1, Address Line 2, City, State/Province, Country, Postal Code

Prefer to use some data modeling and drawing tools likeErWin, ER/Studio, PowerDesigner, MySQL Workbench, SQLEditor and so on.