Demonstrate proficiency in data handling in SQL (using MySQL).

Synopsis:
VivaK is a successful retail chain in the fashion industry, the head office of which is located in Southlake, Texas,
USA. The HR Manager of the VivaK head office has decided a hire a group of analysts (you) to support their HR
analytics by devising an Online Analytical Processing (OLAP) Database. Unfortunately, the information
management of the HR department has not been robust so far, and it maintains its data in various data/file formats.
The data is also known to have anomalies.

The HR manager narrates the following story:
We are in a tough situation with this data, and our analysts find it highly challenging to perform their job
without a well-designed OLAP database. Allow me to clarify how our organizational structure works. We
operate in several regions and countries, but we manage our operations via seven office locations in
different parts of the world. We have three offices in the US, one in Canada, two in the UK, and one in
Denmark. The President sits at the head office in Southlake, Texas, USA. All the other locations have the
same well-structured management team reporting to two Vice Presidents at each location: VP
Administration and VP Operations. Each location has 11 departments, 10 of which are managed by a
dedicated Manager; the other employees report to these managers. We pay our employees on the USD
salary scale, and you may find the monthly salary range per job title and each employee’s monthly salary
in the sample dataset we have provided to you. We also keep track of each employee’s dependents. Our
data designer made an error and called the “location_id” column in employees data “department_id”.

Task:
Create a Schema based on VivaK_Data_Model below and call it VivaKHR. You must include all the
statements in your SQL file and only the important statements/ outputs in your presentation.

+-----------------+ +----------------+ +----------------+ +-----------------+ +-----------------+
| Employees | | Departments | | Locations | | Job Titles | | Dependents |
+-----------------+ +----------------+ +----------------+ +-----------------+ +-----------------+
| employee_id (PK)| | department_id | | location_id | | job_title (PK) | | dependent_id (PK)|
| first_name | | department_name| | location_name | | salary_min | | employee_id |
| last_name | | manager_id | | | | salary_max | | dependent_name |
| email (Unique) | | location_id (FK)| | | | | | relationship |
| phone | +----------------+ +----------------+ +-----------------+ +-----------------+
| hire_date |
| job_title (FK) |
| salary |
| department_id (FK)|
+-----------------+

1. All salary-related columns must be double. Data must be recorded in 2 decimals.
2. All ID columns and their related foreign keys must be Integer.
3. The employee table MUST contain the report_to column to record the ID of the employee’s manager.
4. The data type of All the date columns MUST be DATE.
5. Include a column called experience_at_VivaK in the appropriate table to include the number of months
that each employee has worked at VivaK.
6. Include a column called last_performance_rating in the appropriate table to include the performance
rating (0-10) of each employee after the annual performance appraisal.
7. Include a column called salary_after_increment in the appropriate table to record the salary anticipated
after the annual performance appraisal.
8. Include a column called annual_dependent_benefit in the appropriate table to record the dependent
bonus that each employee receives per dependent.

Answer rating: 100% (QA)

To create a schema for VivaK using MySQL we can use the following statements CREATE SCHEMA VivaKHR USE VivaKHR Employees table CREATE TABLE employees ... View the full answer