Question

A new database needs to be developed. You are required to draw the related Entity Relationship Diagram (ERD) that includes the following information:

(a) Employee (the company has several employees)

(b) Department (each employee is assigned to one department)

(c) Payroll (each employee receives his/her pay for the period)

(d) Customer (employees sell products to customer)

(e) Products (the company offers several products)

Note that an employee can sell products to several customers and that every customer can be served by multiple employees. Include relationship types only if necessary and specify suitable primary keys for all elements. Include all primary keys and all foreign keys in the ERD.

Use PowerPoint to draw the ERD and then upload the file. Alternatively, you can use another software to draw the ERD and submit a PDF file.

Answer 1

Entity Relationship Diagram (ERD)

An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as people, objects or concepts relate to each other within a system.

The components and features of an ER diagram defined hereunder.

Entity keys: Refers to an attribute that uniquely defines an entity in an entity set. Entity keys can be super, candidate or primary.

Candidate key: A minimal super key, meaning it has the least possible number of attributes to still be a super key. An entity set may have more than one candidate key.

Primary key: A candidate key chosen by the database designer to uniquely identify the entity set.

Foreign key: Identifies the relationship between entities.

Relationship: How entities act upon each other or are associated with each other.

Attribute:     A property or characteristic of an entity.

Eg. Attributes of Employee: Employee First Name, Last Name, Address, Email ID

Cardinality:

Defines the numerical attributes of the relationship between two entities or entity sets.   The three main cardinal relationships are one-to-one, one-to-many, and many-many. A one-to-one example would be one Employee associated with one Department. A one-to-many example (or many-to-one, depending on the relationship direction): One employee can sell products to several customer. Many-to-many example: Several customers can be served by several employees.

In the given Problem:

Entities are Employees, Departments, Products, and Pay

Primary Key are Employee ID, Department ID, Product ID, Customer ID and Pay period

Foreign Key are Customer ID in the case of every customer can be served by many employees and Employee ID is the foreign key employee can sell several products to several customers