Follow instructions


A medium-sized consulting company is starting a project to create a new relational database that will store the results of their business transactions. This first phase of the project includes a subset of the business: basic information on customers, suppliers and employees, the company’s organizational structure, and purchase orders that are used to buy supplies and equipment from the suppliers. The scope does not include consulting contracts with customers or the billing of customers for consulting services provided by the company. The main reason customer data is included in this first phase is because management wishes to replace an outdated CRM (customer relationship management) system where the customer data is currently stored. The reason suppliers and purchase orders are included in this phase is because the purchasing department currently does everything on paper and management expects to be able to trim expenses and reduce waste by converting to an automated purchasing solution.

You have been tasked with creating the logical data model for the initial set of requirements. Use an entity relationship diagram to document your model. Your solution must be in third normal form. (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. There are two types of customers: individual customers are people, while organizational customers are, as the name implies, organizations such as partnerships and corporations.
    • 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 (Supplier ID, Supplier Name, Business Phone, Mobile Phone, Mailing Address, Shipping Address, Billing Address)
  • Organizational Structure
    • Division (Division ID, Division Name, Division Manager Employee ID, Division Manager Name). There is only one manager per division.
    • Department (Department ID, Department Name, Division ID, Division Name, Department Manager Employee ID, Department Manager Name). There is only one manager per department.
    • Work Group (Work Group ID, Work Group Name, Department ID, Department Name, Work Group Leader Employee ID, Work Group Leader Name, Employees Assigned to Work Group). A work group has only one leader. However, every employee is assigned to a work group, and many employees can be assigned to each work group.
  • Purchase Orders (Supplier ID, Order Date, Purchase Order Number, Item Description, Quantity, Price Each, Item Subtotal [Quantity * Price Each], Order Status Code). Note: these represent orders the business places with suppliers. Each purchase order may have many items on it.  

Follow instructions

We offer the best custom writing paper services. We have answered this question before and we can also do it for you.


Leave a Comment