MIS 5501 Database Management Systems

Assignment 2: ER diagram

Draw the Crows Foot database notation entity-relationship diagrams (ERD) to represent the databases, based on the three narratives below. Use Lucid Chart or another ERD diagramming tool to create your diagram. You do not need to create the databases, but to design it using applicable business rules according to your judgment. Submit a PDF of the Entity Relationship Diagram for each database.

Remember to identify in your diagrams:

• All the main entities and the main attributes for each entity
• The relationship between the entities, whether they are optional for example.
• The attributes which are primary keys and foreign keys, noted as +, and # respectively in the diagrams.
• Use any feature of the software you can (think sticky notes or comments) to explain the relationships/cardinality between entities.

Submit to Moodle as a PDF format.

1. Oliver Wymans Human Capital Department

You are the Director of Human Capital at Oliver Wyman. You need to keep track of the many divisions, departments and positions within the departments. Employees have a position within the department and division, and you need to keep track of how long each employee has worked at the company, who the supervisor of each department is, what projects employees are working on, and the percentage of the time they allocate to each projects.

Some of the business rules include (this is not complete list of business rules; you may need to identify or define more business rules):

• A division has many departments, and each department is operated by one division.
• A department has one or many positions.
• One position can have zero, one or many employees, and employees can have one or more positions.
• An employee can be assigned to multiple projects, and a project may involved many employees. A project must have at least one employee assigned to it.

2. Ford Motor Company

Ford Motor Company produces regular and specialty vehicles by contract (police cars, ambulances, service trucks, RVs). You are to focus on the specialty division. The specialty division operates several departments, each one of which builds a particular vehicle such as police cars, ambulances, a truck, a van, or an RV. When a new vehicle is built, the department places an order with the Purchasing Department to request specific components. Fords purchasing department is responsible for acquiring components that are used in building a vehicle from their suppliers. The Purchasing Department is interested in creating a database to keep track of orders and to accelerate the process of delivering components. Each order received by the Purchasing Department can contain several different components and each component might be used by any of the departments. An inventory is maintained so that the most frequently requested components are delivered almost immediately. When an order comes in, it is checked to determine whether the requested item(s) is (are) in inventory. If an item is not in inventory, it must be ordered from a supplier. Each item may have several suppliers. You need to keep track of contact information about the supplier in the database.

3. Buffalo Wild Wings

Buffalo Wild Wings is a regionally-managed restaurant operation with many employees per store, and many stores per region. You need to maintain information about the store such as its annual sales and annual expenses. You would also need to maintain some basic information about the employees and the jobs they hold such as the job description and salary. Each employee can hold more than one job. Draw an ERD diagram to describe this situation at Buffalo Wild Wings.

Lucid Chart or any other preferable ERD diagramming software (like Visio) may be used.