Question

1. Design an EER diagram for Motor, Inc.:

Motor, Inc. is a car dealer that sells both new cars and second-hand cars. You are the technical consultant of Motor Inc. and are currently helping the company to develop a database system. Your job is to draw an ERD to help them design the database.

• A car has a unique car ID, selling price, make, model, year, color, and car type (used to indicate a car is new or used).
• Motor Inc. has approximately 100 employees. An employee has an employee ID, job title, gender, salary, list of certifications, date of birth, and age. If an employee is currently married to another employee of Motor Inc., the date of marriage and who is married to whom must be stored; however, no record of marriage is required if an employee’s spouse is not an employee. An employee can handle none to multiple cars depending on his/her job title. A car can be handled by one to at most three employees.
• There are seven branches at this company, and each of which has a unique name. Each branch is located at different addresses, which contains information such as street, city, state, and zip code. Besides that, each branch has its own email address, and three phone numbers. Each branch has many employees. If there are less than three employees, the branch would be closed. An employee must belong to one and only one branch. Moreover, some employees are the branch heads for these branches. Each branch only needs one branch head, but an employee can manage multiple branches.
• The company also wants to track its customers’ information. A customer must provide SSN when registered with the company. The company would typically ask a customer to report their household income, and occupation. A customer can visit none or multiple branches, and a branch can be visited by none to multiple customers.

Answer 1

Solution: EER Diagram developed for Motor Inc, Car sale Company using Microsoft Visio.

List of Entities:

1. Customer

2. Car

3. Motor Inc Company

4. employee

5. Branch

6.Sale

7.Car Dealer

Each entity has many attributes shown in EER Diagram

The ralationships between entities also specified with Multiplicity.

Multiplicity Notation: 1 - 7 between Company and Branch means The company has 7 Branches

1-100 between Company and Employee means Company has 100 employees

1- N means one to Many relationship

Primary Key for Each Entity also Specified by an attribute with Underlined notation.

Example: Primary key for Customer is Customer ID( denoted as ID)