Using Postgresql and er diagram to create the database for the following scenario

Scenario

You were hired to design and implement a database for the company Great Canadian National Bank. They have hundreds of employees spread across several locations and need to keep track of these employees and their organizational information. There is information stored for every employee, including the employees name, gender, SSN, addresses, and phone numbers. Assume that an employee could have multiple addresses (either Home or Business) and phone numbers (either Home, Cell, or Business) stored in the system. They also want to keep track of the hire/termination/rehire dates for the employee.

As mentioned previously, the company has several locations, and each location can have several departments. However, each location has their own distinct departments; for example, the Saskatoon location will have its own IT department. The Calgary location will have an IT department as well, but the two are not affiliated. Each location has name, a code, and a single address. Each department also has a name and a code, as well as a field for which job is the top position (management) for that department.

There are many different jobs in the company (e.g. Teller I, IT Manager, Loans Officer, HR Generalist). For our purposes, assume that an employee can only have one job at a time. Each job has a name, a job code, an effective date and expiry date. There is also a reference to the job that this job reports to. Each job is associated with a department. Additionally, there is a pay frequency attribute associated with each job (e.g. weekly, biweekly, monthly). There are also pay types associated with each job (e.g. hourly, salaried).

Each employee has a job. All jobs will be paid using the same pay frequency and type but employees in the same job can be paid different salaries. For example, all jobs with the name Teller II, will be paid salaried and biweekly. However, two employees both in the Teller II job could be paid different salaries/rates.