Example of Normalisation Conversions

Normalisation techniques

Following are the techniques for Normalisation.

1NF 

The first normal form indicates that each cell in the Table must contain just one value. As a result, every intersection of rows and columns must include atomic values. 

Example of 1NF: 

Suppose a school hostel wants to store the names and contact details of its students. It creates a table that is following:

We can see that two students have two mobile numbers, so the hostel staff saved that detail. But now we have a problem as 1NF says, "each attribute of a table must have atomic (single) values". So, here Student_Mobile violates that rule. Now, the Table that follows 1NF will be like the following Table.

2NF

We discussed candidate key before, and now this is where it comes into play. The 2NF rule indicates that none of the Table's non-prime characteristics is reliant on any of the candidate keys. In other words, it must follow two conditions. i.e., Table is in 1NF, and No non-prime attribute is dependent on the proper subset of any table candidate key. An attribute that is not part of any candidate key is known as a non-prime attribute.

Example for 2NF:

In the school hostel, teachers' data. So after the Table is following 1NF

Because each attribute has atomic values, the Table is in 1 NF. However, it is not in 2NF since the non-primary attribute Teachers_Age is reliant on Teachers_ID, which is a valid subset of the candidate key. Together Teachers_ID and Teachers_Subjects are forming a candidate key for this table. Which in turn can be the primary key for the table.

As the rule states, no non-prime attribute is dependent on the proper subset of any candidate key of the Table. This is a violation of the 2NF rule. So the Table that will follow 2NF will look like this.

To comply with 2NF, we can divide the table into two tables as follows:

Subject Table:

Now we move to the next type, 3NF

3NF

This rule specifies that tables must be in the 2NF format and that each table should only include columns that are not transitively dependent on their own table's primary key. The attribute unrelated to the candidate key is a non-prime attribute and vice-versa. 

A table is in 3NF if it is in 2NF, and for each functional dependency, X-> Y, at least one of the following conditions hold:

• X is the super key of the Table
• Y is a prime attribute of the Table

Example of 3NF:

The hostel details of students contain a ZIP code along with the address. The Table will have to follow the 2NF rule as the rule for 3NF states.

Super keys: {Student_ID}, {Student_ID, Student_Name}, {Student_ID, Student_Name, Student_ZIP}…so on

Candidate Keys: {Student_ID}

Non-prime attributes: all attributes except Student_ID are non-prime as they are not part of any candidate keys.

Here, Student_Address and Student_Vill are dependent on Student_ZIP. And, Student_ZIP is dependent on Student_ID that makes non-prime attributes (Student_Address and Student_Vil) transitively dependent on super key (Student_ID). This violates the rule of 3NF.

So, now to make this Table in 3NF, we remove the transitive dependency.

Student table:

Stud_ZIP table:

Now we will see the last one, BCNF.

BCNF

Since it is a more advanced variant of 3NF, it is sometimes known as 3.5NF. BCNF is more no-nonsense than 3NF. A table conforms with BCNF if it is in 3NF, and X is the Table's super key for every functional dependence X->Y.

Example of BCNF:

In the hostel, students play more than one sport, so now in the Table:

Functional dependencies in the table above: Student_ID -> Student_Address

Student_Game -> Sports_Type

Candidate key: {Student_Name, Student_Game}

The Table is not in BCNF as neither Student_ID nor Student_Game alone are keys.

To make the Table comply with BCNF, we can break the Table into three tables like this:

Stud_Add table:

Stud_Sport Table:

Sports table:

Functional dependencies:

Student_ID -> Student_Address

Student_Game -> Sports_Type

Candidate keys:

For the first Table: Student_ID

For the second table: {Student_ID, Student_Sports}

For the third Table: Sports_Type

This is now in BCNF as in both the functional dependencies left side part is a key.

So, with this, we move to FAQs.

FAQs

1. What is Normalisation?
   Normalisation is the process of structuring data in a database in order to eliminate data redundancy.
    
2. What is Primary Key?
   Each entry in a table is uniquely identified by the Primary key. This value cannot be duplicated within a table and cannot include null values.
    
3. How many types of Anomalies are there, and what is their name?
   There are three types of Anomalies. The three anomalies are as follows:
   Update Anomalies
   Insertion Anomalies
   Deletion Anomalies
    
4. What is BCNF also called?
   BCNF is also called 3.5NF. Since it is a more advanced variant of 3NF, it is sometimes known as 3.5NF. A table conforms with BCNF if it is in 3NF, and X is the Table's super key for every functional dependence X->Y.
    
5. What is Data redundancy?
   Data redundancy is a phenomenon that occurs inside a database or data storage system when the identical piece of data is stored in two different locations. This can refer to two distinct fields inside a single database or two distinct locations across numerous software environments or platforms.

Key Takeaways

In this article, we have extensively discussed Normalisation conversions. We started with the need for Normalisation, and then we learned about the types of anomalies. Well, after knowing about anomalies, we learnt about techniques of Normalisation. There are three types of Normalisation 1NF, 2NF, 3NF and BCNF, respectively. We also saw examples of them and how the tables are separated. In the last section, we learnt about the FAQs. 

Also, check out - Anomalies In DBMS.

We hope that this blog has helped you enhance your knowledge regarding Normalisation conversions and if you would like to learn more, check out our articles on Top 100 SQL Problems and if you would like to learn more, check out our articles on Coding Ninjas Studio. Do upvote our blog to help other ninjas grow. Happy Coding!