Design and refine a database to record and manage information relating to a company's premises, as well as human resources and ongoing projects. The company is structured in departments (e.g. 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 its activities. 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 works on only one project at a time. In addition, each employee holds a position within the company (e.g. human resources technician, accountant) and works under the supervision of another employee.

This work consists of two parts. In the first part, you have to design the relational model directly from the set of attributes, using functional dependency analysis and normalization. In the second part, you must give the SQL queries to perform certain operations.

 

Part 1: Logical diagram and relational model

1-State the attributes of the model.
2-Identify all basic functional dependencies from the statement.
3-Find the minimum coverage of functional dependencies.
4-Find the logic diagram in third normal form from the minimum cover.
5-Produce the diagram of the complete relational model 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)

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.

1-Display the names, salaries and functions of all employees of the "Finance" department.

2-Check that the salaries of all employees are between the minimum salary and the maximum salary of the function they occupy. Indication: it is possible to search if there are employees who do not verify this condition.

3-Display for each function in the company the total number of projects in progress, in descending order.
4-Display the names and locations of all employees recruited after 2015.
5-Display the names and departments of all employees installed in building "B1".

Answer rating: 100% (QA)

Part 1 Logical Diagram and Relational Model Attributes of the Model Buildings BuildingID Primary Key Location Departments DepartmentID Primary Key Nam... View the full answer