Learn to design relational database schema from description and realize it using SQL DDL.

Problem description:

A company's HR system wants to store the following information in its relational database system:

There are many departments in the company. Each department has a unique department id and a name. Each department is located in a Location. For these locations, each location has a unique location id and an address. Each location is located in a country. For these countries, each country has a unique country id and a name. Each country is located in a region. For these regions, each region has a unique region id and a name.

There are many employees work in the company. Each employee has a unique employee id, a name, a contact information, and a hire date.

Each department is managed by an employee. Each employee works in a department. Each employee (except the president) has a supervisor (manager) that this employee reports to. The supervisor (manager) doesn't necessarily be the manager of the employee's department.

There are a list of jobs available in the company. Each job has a unique job id, a title, and a range of allowed salaries represented by its minimum salary and maximum salary.

Each employee currently works on a job, with an assigned salary or a commission percentage. But historically, the employee can move from job to job. Each job history record has a start date and an end date. And of course, the start date of these different jobs for the same employee must be distinct. Each job history record should also record on which job and in which department this employee worked.

Your tasks:

Draw the ER diagram that captures the data described in the above problem description.

Translate your ER diagram to a relational database schema.

Create the tables in your relational database schema in SQLite.