Database Design, 2NF, Getting rid of partial functional dependencies

In the previous article we started to normalize a data table in order to update it to the first normal form.

now we are going to examine the process of normalization further by trying to normalize a table to the 2NF form.

we extracted the Departments column from the Employees table because it had duplicate(repeating) values and also because it represented a many-to-many relationship between the Employees and Departments tables. 

we created a new table called Departments, with three columns, DepartmentID, EmployeeID and Department.

as you can see the DepartmentID and EmployeeID columns are declared to be **Composite Keys**. this means that together they uniquely identify each row or they form the primary key of the table. for example we cannot add a record to the table which has the following composite key values (DepartmentID 1, EmployeeID 6) because this combination of keys already identifies the first row.

you can see in the above table that EmployeeID 1 , is both a member of DepartmentID 3 and DepartmentID 4. this represents a many-to-many relationship well.

But our table can be further approved by being normalized to the 2NF.

In order for a table to be in the 2NF it must meet the following requirements:

 1. the table must already be in 1NF.

     - columns contain only atomic values.

     - there are no repeating(duplicate) groups of data.

     - each row is uniquely identified by the primary key.

 2. there should be no **Partial Functional Dependencies** in the table.

    - in the above table the department field is only dependant on one of the composite keys, the DepartmentID. this is called a partial functional dependency.

3.each table only has a single Primary Key.(composite keys are not allowed).

if we look at the above table we notice that each DepartmentID corresponds to an specific Department name, and because of that every time a DepartmentID is repeated so is the so is the corresponding department name.

now if we were to change the name of a department we would have to go through all the rows of the table and change all the instances of that department name. this is because of the current design of the table which not only makes the UPDATE procedures of the table extremely inefficient as the size of our table grows but also it can lead to errors.

so, we abide by the rules of 2NF and extract the partial functional dependency and insert it into a separate table.

Now we have three tables, Employees, Employee_Department and Departments. the Employee_Department table acts as a junction table which enables the many-to-many relationship which is displayed below. as you can see the EmployeeID 1 is repeated twice in the Employee_Department table, and DepartmentIDs 2 and 4 are also repeated indicting a many to many relationship.

 

if we wanted to retrieve the departments that employee 1 is a member of we would use the following query.