Database Design, 3NF, Removing Transitive Dependencies

3NF, Removing Transitive Dependencies

A table is in the third normal form if it meets the following criteria:

1. It shoould be in 2NF.
2. It should not have any transitive dependencies. 

• a transitive dependency describes a situation in which changing a non-key column in a table affects another non-key column in that table . For example in the table below, if we decided to change either FirstName or LastName then we would have to also change the data in the Initials column.

In the above table the Initials column has a transitive functional dependency. therefore it is removed and inserted into a new table in order to achieve 3NF.

our tables are now in the third normal form.

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.

Database Design, 1NF, Starting Database Normalization

What is Database Normalization?

normalization is the process of organizing the fields and tables of a relational database by dividing larger tables into smaller and more manageable tables which are connected to each other using relationships.

through normalization we can reduce Data Redundancy which leads to anomolies and data corruption and reduce the amount of Data Dependency between the fields of a table which makes it hard to Add, Update and Delete fields that depend on each other.

Now, imagine that We are given the below datasheet which contains data about the employees of a company and we are asked to design a flexible database which can be easily queried and extended over time to accommodate more data and more tables.

the below table has a bad structural design but we can improve it by using the process of Normalization. we will start the process of nomrmalizing the below table by first bringing it to the first normal form(1NF).

the table above is breaking the these rules required to achieve 1NF:

 1. No repeating groups of data are permitted in a column.

• the Department column can contain repeated data. the first row shows that an employee by the name of Zak Ruvalcaba is a company Executive who also works in the Marketing department. this also represents a many-to-many relationship, meaning that a department can have many employees and an employee can be a member of many departments.

 2. Each column of data must contain Atomic values.

• The Name and address column contain non-atomic values. meaning that they must be broken down into more columns so that they can be queried easier. for example the name column can be broken down to FirstName and LastName and the Address column can be broken down to Street,City, State and Zip columns. this will make the querying of the data in our table easier and more efficient.

 3. The above table does not have a Primary Key(PK) to uniquely identify each row.

Lets update our table to 1NF

so by analysing our table we have found its flaws and can now start our normalization process. lets start by first giving the table a unique key.

the department column contains repeated data. which is in violation of the 1NF rule which states that there should be no repeating data in each column. in the department field of the first row we have Executive and marketing which are both of type department in a single column.

this also indicates a many-to-many relationship on the table level, meaning each employee can be a member of many departments and each department can have many employees.

through normalization we break each table into smaller and more manageable ones and relate them by using relationships which comprise of primary keys and foreign keys.

the discovery of the many-to-many relationship between Employees and Departments is a good indicator that the department column should be taken out and put into its own table. after doing so we are left with the following table.

we are still not in 1NF as the Name and Address columns contain non-atomic values. this means that there is too much related data in a single column for us to be able to write efficient queries. therefore we will separate these columns so that we can take full advantage of the data contained within them.

the table above is now in the first normal form, there are no duplicate data and each column contains atomic values. we also added a primary ke to uniquely identify each row.