Question

Create ER diagram for the following:

A database has been designed for a Human Resources for a school in the UK. The database includes records of the teachers and their holidays. The Dean of this school has the power to approve those holidays for all teachers in the school.   

For each teacher, Human Resources keeps track of the Teacher's ID, name, Cell phone number(s), total number of holidays for each year, number of unemployed holiday days remaining in the present year, age, date of the employment, Department name.

Some teachers may work as supervisors. For those teachers, as well as the attributes mentioned above, we also keep track of the date when they are supervisors, in addition to the total time they spend to be supervisor and their categorical level (Category 1, Category 2, Category 3, Category 4 or Category 5).

The Human Resources Department in the school keeps track every holiday has been taken by each teacher. For each holiday, The Human Resources keep track, Unique holiday ID, the number of days taken (spent) in this holiday, the start day and end day of a holiday. Sometimes (in a few cases), an alternative phone number may be documented for a holiday.

The Human Resources Department in the school keeps track every reward has been taken by each teacher. For each reward, The Human Resources keep track, Unique Reward ID, the number of rewards, Date of a reward, teacher name and the amount of the reward.

Each holiday is documented for only one teacher. A teacher can take many holidays per year. Each holiday has been approved by many supervisors and each supervisor has the power to approve many holidays for many teachers. Each supervisor has the power to approve the holidays for many teachers and each teacher may have many supervisors and may not. Every supervisor has the power to give many rewards to each teacher, but does not have to reward any. Every teacher can get many rewards every year.

Answer 1

As per the question we have to draw ERD of Teachers Leave/Holiday Management system.

An entity–relationship model shows inter-related things of interest in a specific part/domain of Study.

As per the case study, we have several entities listed below:

1. Teachers

2. Human Resource Dept.

3. Supervisors

4. Permissions

5. Reward

6. Holidays

Each entity has various attributes like they are listed below in the ERD.

I am attaching ERD diagram below with proper entities with specific attributes.

ERD of Teachers Leave/Holiday Management System: