Funcionarios
1.0.0
A company's employee management system.
The project was developed using ASP.net for application development and SQL Server as a storage database. A registration of employees (person + cargo) was made where it is possible to make the 4 actions of CRUD standards. Read, create, edit and delete. In addition, the salary recalculation option was also implemented, where the database makes the relationship again between person and office, populating the table of person_salary dictated in the test description.
In the Officials/ Backup/ Has a SQL Server Backup file that I used with all the procedures used and data already entered. Therefore, you just need to make a Bank restore.
NOTE : After doing the restore you need to change the connection string present in the employees class within the employee file.aspx.cs. Changing by the connection string of your machine.
After configuring the database, open the solution to some code editor of your choice (I used Visual Studio) and run the application.
To make it easier to view the procedures used in the application, I put their creation files in the employees/SQLS/.
During development I found some strange things about the proposed architecture. Mainly in relation to the database.
It is said in the test description that is to create a table called Pessoa_Salario that would make the connection of the Person table with position. However, the existence of this table is not necessary. For the need to create an intermediate table is only necessary when there is a relationship of many for many between the two tables analyzed. Which is not the case between person and office. After all one person can only be in a position at a time and so there is one field called Position_id in person. And from this, the unique way of the position table already exists and consequently it is already possible to access the position and salary of that person, without the need to create a table to do this.
The way you are described in the test, there is a duplicate problem in the bank. Which besides being wrong architecturally, as it can generate inconsistency in these data at some point, a "correction" of this was also needed in the application that was the creation of the salary recalculation button. After all if the value of an intern's salary in the future, for example, changed, the position table would have the value updated, but the Sanal Sale table would still have the old value (data inconsistency problem) and it would be necessary to go manually on the employee screen and trigger the salary recalculation action. Which would not need to be done if there was no person's table, because besides having only this data in a single place, preventing inconsistencies, the consultation would be taking the value correctly. Both before modifying the salary amount and after.
Proof of this is to analyze the consultations that the two architectures generate.
This is the consultation to return all the data of person, cargo and person_salary used in the listing by the architecture proposals:
SELECT p . ID , p . Nome as Pessoa, Cidade, Email, CEP, Endereco, Pais, Usuario, Telefone, Data_Nascimento, c . Nome as Cargo, ps . Salario FROM Pessoa as p
INNER JOIN Pessoa_Salario as ps on p . ID = ps . Pessoa_ID
INNER JOIN Cargo as c on p . Cargo_ID = c . IDAnd this has the same function as the above consultation, but only involves person and office and returns exactly the same data in a simpler and more optimized way:
SELECT p . ID , p . Nome as Pessoa, Cidade, Email, CEP, Endereco, Pais, Usuario, Telefone, Data_Nascimento, c . Nome as Cargo, c . Salario FROM Pessoa as p
INNER JOIN Cargo as c on p . Cargo_ID = c . IDNote : My solution proposals was developed using the proposed architecture. This comment was just to show the way I found it most interesting and optimized by architecture.
