You are asked to design and refine a database to record and manage information relating to a company's premises, as well as human resources and current projects. The company is structured in services (eg human resources, finance, production, etc.) and has several buildings. Each department is housed in a co-working room shared by department employees, in one of the buildings. However, some premises are not yet occupied, as the company has just started operations. Each department has a number of employees and carries out different projects. Note that in this database, we are only interested in current projects and that an employee is working on one project at a time. In addition, each employee occupies a position within the company (eg human resources technician, accountant) and works under the supervision of another employee.

Your job This work has two parts. In the first part, you have to design the relational model directly from the set of attributes of the world, using functional dependency analysis and normalization. In the second part, you must give the SQL queries allowing you to perform certain operations. Part 1: Logic diagram and relational model (10 points) State the attributes of the model. Identify all the basic functional dependencies from the statement. Find the minimum coverage of functional dependencies. Find the logic diagram in third normal form from the minimum coverage. Produce the complete relational model diagram corresponding to the third normal form. Identify the different keys and specify the cardinalities. Note: To make this diagram, you can use any office tool or a dedicated tool such as MySQL Workbench. Part 2: SQL queries (5 points) By considering the relational model in third normal form of part 1, give the SQL queries allowing to carry out the following operations. It is not required to create the database and run the queries using MySQL. Display the names, salaries and functions of all employees in the Finance department. Check that the salaries of all employees are between the minimum wage and the maximum wage for the position they occupy. Note: It is possible to search if there are employees who do not meet this condition. Display for each function in the company the total number of projects in progress, in descending order. Display the names and locations of all employees hired after 2015. Display the names and departments of all employees installed in building B1.