Question

you are required to develop a simple HR application for a small accounting firm that wishes
to keep track of all the employees at the firm; storing details about their salary, phone numbers
and Date of Birth. The firm has many departments and there are 5 to 20 employees in each
department. The department information includes department name, description and total
number of employees in that department. The company also provides vehicles for some of its
employees. An employee maybe allocated one car. To ensure timely maintenance of vehicle,
the company would like to store the following details of the vehicle: make, model,
next_maintenance_date.
Each employee has a position (manager, accountant, administrator, clerk, etc.). Various
allowances are allocated to each position. For example, the managers have fuel allowance,
house allowance, social allowance and managers allowance. On the other hand, the
administrators have house allowance and social allowance. Clerks have social allowance and
uniform allowance. The name and description of each allowance needs to be stored.
The HR system keeps track of all Projects in the organization. Each department is assigned to
one or more project. Project can be assigned to one or more department (Interdisciplinary
projects).
Each HR Employee has his own credentials to login into the system to manipulate the database.
The HR credentials are stored in a table (Login). You will also need a table to store transactions
(Transaction Date/Time and User Name).
The system must store employees’ grades. Each employee will have a grade (Example A, B,
C). The grade should have a predefined salary range.
You need to have the following constraints in your system: The employee’s salary should be
between the grade range (Example: Grade A - Salary between 30000-50000). Employee’s
phone number and Date of Birth should not be empty. Vehicle next maintenance is within 6
month of current date. Each position should not exceed the predefined allowances.

Part A:
Include the following before drawing the ERD:
a. Write the business rules.
b. Identify all Entities and attributes
c. Identify Primary key and Foreign Key in each table/Entity
d. Identify the Relationships between Entities.
e. Resolve any M:N relationship between the entities.
Part B:
Draw the ERD using any Data Modelling Software

Answer 1

Part A:

1. Business Rules: The given system will have below rules:

Rule #1: Department will have minimum 5 to maximum 20 employees

Rule #2: Employees will have unique Emp_ID

Rule #3: Some employees of HR_Employee type will have username and password.

Rule #4: Car can be assigned to one or none employee.

Rule #5: Employee can have 0 or one car.

Rule #6: Multiple allowances will be given to multiple employees

Rule #7: Multiple departments will handle multiple projects.

Rule #8: A project can be assigned to multiple departments.

Rule #9: An employee will has assigned Grade.

Rule #10: Transactions will be stored for HR_Employees

2. Entities and attributes:

• Department(Department_Name, Description , Total_Employees)
• Employees(Emp_ID, salary, Phone_numbers, DateofBirth, Position, Department_Name)
• HR_Employee(username, password); it is a sub-entity of the entity Employee
• Car(Car_ID, Make, Model,Next_maintenance_date, Emp_ID)
• Allowance(Name, Description, Amount )
• Project(Project_ID, Name)
• Transaction(Transaction_ID, DateTime, Username)
• Grade(Grade_Name, Min_Salary, Max_Salary)

3. Primary key and Foreign keys of tables/entities:

• Department:
  • Primary key: Department_Name
• Employees:
  • Primary key: Emp_ID
  • Foreign key: Department_Name
• Car:
  • Primary key: Car_ID
  • Foreign key: Emp_ID
• Allowance:
  • Primary key: Name
• Project:
  • Primary key: Project_ID
• Transaction:
  • Transaction_ID
  • Foreign key: Username
• Grade:
  • Primary key: Grade_Name

4. Relationship between entities:

• Department to Employee: A department can have minimum 5 to maximum 20 employees. It is a 1:M relationship.
• Employee to Car: A Car is assigned to 1 employee whereas an employee can have 0 or 1 car. It is a 1:1 relationship.
• Employee to Allowance: An employee can have one or more allowance whereas an allowance can be allocated to one or more employees. It is a M:N relationship.
• HR_Employee to Transation: An HR employee can have one or many transactions whereas a transaction deals with one employee record. It is a 1:M relationship.
• Project to Department: A department can have many projects similarly a project can be assigned to many departments. It is a M:N relationship.
• Employee to Grade: Each employee will have one Grade whereas many employees can have same grade. It is a M:1 relationship.

5. Resolving M:N relationship:

The listed relationships under d) has below relationships of M:N cardinalities:

• Employee to Allowance: The same needs to be stored in separate entity to resolve the M:N relationship. Please find below entity/table to store the allowances for employees:

Emp_Allowance(Emp_ID, Allowance_Name)

Primary key: (Emp_ID, Allowance_Name)

Foreign key: Emp_ID from Employee

Allowance_Name from Allowance

• Project to Department: The same needs to be stored in separate entity to resolve the M:N relationship. Please find below entity/table to store the departments and corresponding projects:

Department_Projects(Department_name, Project_ID, start_date, end_date)

Primary key(Department_name , Project_ID)

Department_name foreign key from Department

project_ID foreign key from Project

Part B: Drawing ERD

Below are the final Entities obtained in Part A:

**Primary keys are underlined and bold. Foreign key has (fk) next to column name.

1. Department(Department_Name, Description , Total_Employees)
2. Employees(Emp_ID, Salary, Phone_number, DateofBirth, Position, Department_Name(fk), Grade(fk))
3. HR_Employee(Emp_ID(fk), username, password)
4. Car(Car_ID, Make, Model,Next_maintenance_date, Emp_ID(fk))
5. Allowance(Name, Description, Amount )
6. Project(Project_ID, Name)
7. Transaction(Transaction_ID, DateTime, Username)
8. Grade(Grade_Name, Min_Salary, Max_Salary)
9. Department_Projects(Department_name(fk), Project_ID(fk), start_date, end_date)
10. Emp_Allowance(Emp_ID(fk), Allowance_Name(fk))

ERD for the above entities is given as below: