Identify the normal form of database tables
In this we will learn about the normal form of database tables. However, a basic understanding of this terminology is helpful when discussing the design of a relational database.
Description of normalization
- The process of structuring data in a database is known as normalization. This involves generating tables and developing links between them based on predetermined rules.
- This is done to safeguard the data while also allowing the database to be more flexible by removing redundancy and inconsistent dependencies.
- Additionally, duplicated data consumes disc space and causes maintenance issues. If data that exists in many locations has to be modified, it must be changed in the same way in all locations.
- If data is only saved in the Customers table and nowhere else in the database, changing a customer’s address is significantly easier.
Following that, there are a few database normalization rules. A “normal form” is the name given to each rule. The database is considered to be in “first normal form” if the first rule is followed. The database is said to be in “third normal form” if the first three requirements are followed. Although there are further degrees of normalization, the third normal form is regarded as the most important for most purposes.
First normal form:
- Firstly, eliminate repeating groups in individual tables.
- Secondly, create a separate table for each set of related data.
- Thirdly, identify each set of related data with a primary key.
However, storing comparable data in several fields in a single database is not a smart option. An inventory record could have entries for Vendor Code 1 and Vendor Code 2 to track an inventory item that could come from two different sources.
Second normal form:
- Firstly, create separate tables for sets of values that apply to multiple records.
- Secondly, relate these tables with a foreign key.
Records should not depend on anything other than a table’s primary key (a compound key, if necessary). For example, consider a customer’s address in an accounting system. The Customers table, as well as the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables, require attention. Instead of keeping the customer’s address in each of these tables as a distinct entry, keep it in one place, either in the Customers table or in a separate Addresses table.
Third normal form:
- Eliminate fields that do not depend on the key.
Values in a record that aren’t part of the key aren’t supposed to be in the table. In general, whenever the contents of a collection of fields may apply to more than one record in a table, consider separating those data into their own table.
Other normalization forms:
Although the fourth normal form, commonly known as the Boyce Codd Normal Form (BCNF), and the fifth normal form exist, they are rarely used in practical design. Disregarding these guidelines may result in a less-than-ideal database architecture, but it should have no impact on functioning.
Normalizing an example table
These steps demonstrate the process of normalizing a fictitious student table.
Unnormalized Table:
First normal form: No repeating groups
Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble.
The third dimension is frequently used in spreadsheets, although it should not be used in tables. Another approach to look at this problem is from the perspective of a one-to-many connection; however, the one side and the many side should not be placed on the same table. Instead, remove the repeating group (Class#) and build a new table in first normal form, as seen below:
Second normal form: Eliminate redundant data
Note the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.
The following two tables demonstrate second normal form:
Third normal form: Eliminate data not dependent on key
In the last example, Adv-Room (the advisor’s office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:
Reference: Microsoft Documentation