Converting to 3NF
3NF, popularly known as Third Normal Form is a principle in SQL and database management that supports data integrity. 3NF builds upon the principles of database normalization that are provided by the First Normal Form (1NF) and Second Normal Form (2NF).
The requirements of Third Normal Form
For a database to be in 3NF, it has to meet two basic requirements. They are:
- The database must already be conforming to the requirements of First Normal Form and Second Normal Form
- The database columns must be primary key dependent. This means that any of the values of the column can be derived only from the primary key.
We can say that an object is dependent on another when it relies upon it. In SQL and databases, when we say that a column depends on another column, we mean that the values of that column can be derived from the other.
Transitive dependence simply means the values of a column relies upon another column through a second intermediate column.
Primary Key Dependence Explained
What do we mean when we say that all the columns of the database must depend on the primary key? Well, well a column’s value that can be derived from both the primary key and another column in the table violates the requirements of 3NF.
Suppose that we have a table for employees with these columns:
The question we would have to ask yourself to understand this concept better is: Do the FirstName and LastName depend only on the value of employeeID? Well, LastName cannot depend on FirstName because there is nothing inherent in LastName that would suggest the value of FirstName. Again, FirstName cannot depend on LastName because of the same reason. In simple terms, regardless of what the last name is, it cannot provide a hint to the value of the FirstName. For this reason, we can say that our table is 3NF compliant because the other columns depend on the primary key.
Now consider this table for vehicles:
In our table, the VehicleID can give us a hint of the Manufacturer. Also, the Model could hint on the manufacturer since a vehicle model is made by a specific manufacturer. For this reason, this table does not meet the second requirement of the 3NF. You can update the manufacturer and not the model, resulting in inaccuracies.
Non-3NF compliant tables can result in data anomalies. In our example, we can move the additional dependent column to another table and reference it using a foreign key. This would make it 3NF compliant. However, it would result in two tables.
The ModelID in our Vehicles table below is a foreign key to the Models table:
Our new Models table maps tables to manufacturers. We would bring about any update on vehicle information specific to a model on this table, instead of the vehicles table.
Derived Fields in the 3NF Model
A derived field is one that is computed based on other columns in the table. Let us use an example of this table of widget orders:
We can derive the total by multiplying the UnitPrice by the Quantity. For this reason, it breaks the 3NF compliance because it is not fully dependent upon the primary key. For the table to comply with the Third Normal Form, we must remove total from the table. Since it is a derived field there is no need to store it in the database at all. We can simply compute it on the fly when we are performing database queries.
Normalization can get out of hand. There are times when normalizing a database is not worth your time and effort. It is recommended that you normalize if you feel that introducing UPDATE or Insert anomalies could severely impact the accuracy or performance of your database application. Otherwise, determine if you can rely on the user to recognize and update the fields together
Additionally, there are times when you will be required to denormalize data. You may need to present a summarized or compiled data to a user. Such data may be time-consuming or require intensive resources to create. In this case, the sensible thing is to maintain this data separately.
At ProgrammingAssignmentHelper.com, we boast of experienced SQL assignment help experts. Contact us at your convenience for first-class homework help.