I normalized a Database to the 3rth Normal Form.
I was given a dataset in an Excel spreadsheet, that was unnormalized:
For First Normal Form I made sure there were no repetitive groupings in the data.
and no repetitive data within each cell.
For Second Normal Form I made sure that All columns depend on the Unique ID.
For Third Normal Form I made that there were no transitive dependencies in the data:
The conceptual ERD Looks like this:
For normalization I needed to remove repetitions in the data, so the most important part is that the employees have data history in the information I received, so one important Query that I needed to be made is:
- Write a Query that returns current and past jobs (including employee name, job title, department, manager name, start and end date for position) for employee any employee,
So i needed an employee_history table that could keep the information I needed for this query:
The logical ERD keeps a table for employee, normalizing its data, there is another for company locations, Education and Salaries.
This design achieves the query that I want and also keeps data integrity in the employee data.
The manager can also be grabbed from the employee table as a second connection to employee_history.
The data types for this Physical ERD are for PostgreSQL.