Question

EMPLOYEE MANAGEMENT SYSTEM

Administration:

This entity will consist of details of the people working in the administration. It is the head of

all other entity if we create a flow chart of the system. This entity has the following

attributes:

Staff_id: This is the primary key for this particular entity. It will be assigned to each staff

person and will be used to uniquely identify the individual working person.

Name: The name is required for the verification of the person as the documents contained

the name of the person.

Contact: For the immediate communication with the staff persons, there is contact phone

number it will vary from person to person or department.

Email: It is helpful in formal communication and making an announcement to every other

staff person. It will also be helpful for another employee to communicate with staff person

with their, email address.

Department_id: This will give the idea about that the staff person is handling which

department. The department_id will comprise of their job details like designation and

department name.

The administration will hire the employees and decides their salary and make entry

according to that so the system can generate the amount every time it requires to that's they

are in a direct relationship.

Employee:

The employee entity contains all the information about the employees working in the

company. These details will help in managing the employee's interest in the assignment of

the department.

This will enable them to manage the employee data easily. Searching the information will

just a few clicks.

It will have all the necessary attributes required for the employer. The employee entity will

have following attributes:

Employee_id:This will be the primary key for this entity. It will be unique for every employee

hired by the administration. It will help in identifying the particular employee easily. This

gets useful when two employee has a similar name like conditions.

Employee_name: This is required for the verification purpose. Usually on government issued

id cards it has a name as identification. Also, it is easy to remember a name than the id.

Department_id: The department will have a different id for the manager, worker and other

designation so we can find the job profile of the employee. It will also help in getting the

other department information where and what the employee's working is.

Employee_phone: It is good to have employee's contact number to have immediate

communication regarding the work.

Employee_email: It is required when a bank needs to make an announcement. So, they will

select the employee's email and simply broadcast the announcement. It can also be helpful

in verifying the employee's identity online.

Employee_address: In any case, the employee address needed for example sending any

email or documents at the employee's residence. It is also required for verification if the

employee's given personal information is right or wrong.

The employees will be hired by the administration and further, it has a relationship with the

department entity because all the employee will be working in particular department.

Departments:

This entity will help in keeping the track of the job profile of the employee. The departments

are the inner fragments of the company. Each Employee profile will be assigned to one

department in which will have the details about their job. The department entity will have

following attributes:

Department_id: This is the primary key for the table it will help identify each job profil

uniquely in with the department. This will be unique for every designation of departments.

Department_name: It will contain the name of the department to avoid confusion to clear

and avoid the confusion from the id. Usually, the department names are unique themselves

but creating it is a good option that because it uses less storage space than a name.

Head_of_departemnt: It will take the employee id as a value which will define that a

particular person/employee is the head of that department. So, the HOD will be responsible

for all the work done in his/her department.

Employee/Staff: This attribute defines if the person works in administration or as an

employee of the company.

Designation: To know about the designation of the employee like if the employee hold

manager or HOD or any other designation. The department will be assigned to the employee

as well as the administration. The administration will manage the department and

employees.

Salary:

This is the important entity because the purpose of this system is to calculate the salary. This

will have all the information of the employee to calculate the employee salary and generate

the payment slip.

Employee_id: It will take the employee id as value. So later it can be used to identify whose

salary it has stored.

Deduction_id: Every employee will have some deduction in salary based on the different

factors for ex: attendance can be one of them. It will check the deductions based on the

values of particular id before calculation.

Basic: It will hold the basic salary of the employee. Before any deduction or additional

incentive or taxes etc.

HRA(House Rent Allowance): If you are getting House Rent Allowance then it will add that in

the salary and what is the base of calculating HRA etc.

Medical Allowance: If the employee is eligible for the medical allowance then it will add the

amount which is decided by the administration

DA(Dearness Allowance): It you are eligıble tor Dearness Allowance then this attrıbute will

that amount.

TA(Travelling Allowance): If you have travelled for the company work and applied for the

refund then you will get the amount in this attribute and will be added to the final salary.

This entity has the direct relationship with the administration as the salary is actually

calculated by the administration and deduction will be based on the factors which are

present in deduction entity.

Deductions:

This entity will contain the factors on which will be used to deduct the amount from the final

salary of the employee.

Employee_id: This will be the primary key for this entity so that the information can be

uniquely identified for all the employee and also in searching and sorting.

Attendance: It will have the value that in the total working day how many days the employee

has come to work and on the based on a daily basis the salary can be calculated.

Loan: If the employee has taken any loan from the company if yes then the instalment will be

also be deducted from the salary of the employee.

Tax: The taxes for which employee is eligible for it will be automatically deducted from the

salary that could be different from government and personal industry.

The deduction entity has the direct relationship with the salary table so it can make a

decision while calculating the salary. The payroll management will surely decrease the

workload of the employee and increase the speed at the same time.

Question: Draw an ER diagram for requirements:

-weak, strong, attributes, relationships, etc.

Draw a schema Diagram

Answer 1

E-R Diagram-

• This Er model has has been converted into 3NF
• Here each entity and its attributes are in tabular form
• Data types of each attribute has been mentioned with it
• The primary key as PK and foriegn key as FK has been mentioned there.

  If any doubt, please feel free to ask and comment, i would like to answer them.
  Thank you