Normalization In Databases

Why Normalize?

Sometimes, a database design that looks OK at first sight may have some hidden problems. One such kind of problem is Non-Atomic values. This means that the value stored in a single column in the database is actually a combination of multiple values which makes the database harder to work with because you’ll always need expressions to extract the individual value. And it comes with major impact to the performance as well.

• Non-Atomic Values
  
  
  • Complex Code Required
  • Performance Impact

A database design suffers from redundancy if it allows multiple copies of the same fact (data) to be stored, which is bad for several reasons, as mentioned below -

The third problem that may harm in the database is Modification Anomalies.

Suppose Dave wants to update the Phone Number; then we update it in the first place. So, we also need to update it in other places as well. This is an Update Anomaly.

Let’s suppose Joanna doesn’t want to participate in the tournament. So, we delete the 3^rd record (Joanna) from the table which means we lost the other information as well like Joanna's Phone Number etc. This is Delete Anomaly.

In order to resolve such kind of issues, we have to normalize our database. So, here, we have a few steps which we call as Normal Forms.

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Elementary Key Normal Form (EKNF)
• Boyce-Codd Normal Form (BCNF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)
• Domain/Key Normal Form (DKNF)
• Sixth Normal Form (6NF)

These normal forms always apply to an individual table. So within the single database or a single relational data model, some tables may be fourth or fifth normal form others may be on the 3^rd normal form. Everyone agrees that the table should be normalized at the 3^rd normal form at a minimum but here, we have different opinions as well.

When people mention the normal form of the design or of the database, they actually mean the lowest normal form of all its tables. Let’s say a database has 20 tables at the 5^th normal form, 3 tables on 4^th normal form, and 1 table on the 2^nd normal form. We would say the database is in 2^nd normal form.

Many people normalize their design after converting their Entity-Relationship model to Relational Database. However, because the ER diagram is the logical documentation this means that any changes made to the relational database design as a result of normalization should be ported back to ER model as well.

• Most Common Approach
  
  
  • Convert Entity-Relationship Model to Relational Tables
  • Normalize Relational Tables
  • Disadvantage - Changes must be ported back to ER Model
    
    
• Alternative Approach
  
  
  • Normalize the ER Model
  • Convert Normalized ER model to Relational Tables
  • Disadvantage - Normalization is Bit More Complicated

When We Should Normalize Our Database

We’re studying Normalization from the very beginning of our engineering which shows how important it is, like the essential part of the database. Which is wrong. If you have made your application's database with just your understanding, then it will be ok. But if you have applied Normalization Rules to make it normalize then we’ll make our database consistent of data and we can remove the repetition of data on multiple places. It was just a general point of discussion. Now let’s discuss technically where we really need to implement Normalization.

Applications are of 2 types.

• OLTP (Online Transaction Processing)
  This kind of application can have hundreds and thousands of transactions in a day. Most of the time we’re implementing insertion, updation, deletion and reading the records of a specific member. In such a database, we should normalize our model, which makes our data less redundant, more accurate and more inconsistent. Here our customers' data is very important, so we take much care of the data.

• OLAP (Online Analytics Processing)
  In OLAP applications, we mostly perform reporting. We use this model to implement the forecasting from the previous history of the data. Most of the time we just implement the read operation here. So in such a scenario, our denormalized database is OK.

To understand both of the above opinions, let me explain the reason. Actually when we normalize the table, we divide it into sub-multiple tables and when we cut the tables into pieces and again try to retrieve the records from these tables we need to implement a lot of joins to get the data and sometimes the query becomes more complex in these joins which affects our database performance with respect to the speed of database results back. But in OLAP, if we don’t normalize our tables, our tables are in their original form containing a lot of redundant data but here we don't have any chunks of small pieces of tables. So here we directly just select the records and display it on the screen which will be very fast in the denormalized state.

Now you can easily make the decision, when to normalize the database and when we should not.

Note
To implement the Normalization in the database, there are 2 important things. You should have the knowledge of Functional Dependency and you should have the knowledge of Keys. Without this prior knowledge, you can’t implement the Normalization in the database.

Functional Dependency

A functional dependency is a relationship between 2 or more attributes. It means if the value of one attribute is known, it is possible to obtain the value of another attribute.

Suppose there is a relation STUDENT with following fields:

STUDENT (RegistrationNo, StudentName, Class, Email)

If the value of RegistrationNo is known, it is possible to obtain the value of StudentName. It means that StudentName is functionally dependent upon RegistrationNo. An attribute B is functionally dependent on A if the value of A determines the value of B.

Functional Dependency is written as follows,

RegistrationNo --> StudentName

An attribute can also functionally dependent upon two or more attributes instead of a single attribute.

COURSE(STUDID, CRSNO, CRSDATE)

The attribute CRSDATE is functionally dependent on both STUDID and CRSNO in the above example.

The functional dependency in the relation is represented as,

STUDID, CRSNO --> CRSDATE

So here course completion date CRSDATE can be determined only if both STUDID and CRSNO are known. No single attribute can be used to determined CRSDATE.

In Mathematics, the relationship between variables is called a function. And functional dependency is when some attributes are dependent upon some other attributes. There are 2 types of functional dependency.

Suppose A, B, and C are the attributes (columns) of a table.

• Trivial Dependency

Let’s discuss this type with example. Suppose,

AB --> A

Suppose A and B are 2 columns of a table. And with the help of A and B we’re not getting any new value back which means even the combination of 2 columns in a table don’t identify the new record values.

• Non-Trivial Dependency

Non-Trivial dependency gives us different values in response.

AB --> C

And here A and B are 2 columns of a table and A & B pointing to a unique record and gives us the new value in response which is C.

This is the general concept of functional dependency.

Keys

Now let’s discuss the concept of keys. Suppose,

R (ABCD)

Here R is a table containing 4 columns. Now the functional dependency in this relation is,

A --> BC

Which means if we have the value of A we can conclude the values of BC columns as well. Columns B and C depend upon the column A. So what would you say, Can we use A as the key here? Absolutely No, because the key in a table is which identifies our all the records in a table.

Now, let’s suppose we have some more dependencies.

ABC --> D, AB --> CD, A --> BCD

1. In the first dependency (ABC --> D), D depends upon ABC. We have already ABC and with the help of ABC, we can get D. So now we have ABCD which means our table is completed. So, we can use ABC as a key.
2. Similarly, in the 2^nd dependency (AB --> CD), CD depends upon AB. We have already AB and we can conclude CD in response. So we can also use AB as a key.
3. Now the 3^rd functional dependency is (A --> BCD). So with the help of only A, we can get all the remaining columns of the table. So we can also use A as a key.

This key is called Super Key. Once again, a Super key is the key with the help of which we can uniquely identify a row or a tuple in a table or we can uniquely identify all the remaining attributes of the table.

(S.K)⁺ = R

Now let’s suppose you’re a Database Administrator. Just ask yourself, what key you’ll use in your table among these dependencies?

ABC --> D, AB --> CD, A --> BCD

Now obviously we’ll use (A --> BCD) because it is more efficient than all the dependencies and it is uniquely identifying the complete record. So A is the Candidate Key. Candidate Key is actually the most efficient version of Super Keys and in our remaining dependencies (ABC & AB) are just the SuperKeys only.

Now let’s see another example, let’s suppose

R (ABCD) & B --> ACD, ACD --> B

So in the first functional dependency, we can get all the remaining attributes of a table from B column. So B is the Super Key.

In the second dependency (ACD --> B), we have already ACD and with the help of ACD, we can get the value of B because B is dependent upon ACD. So ACD is also a Super Key.

Now, what about Candidate Keys?

B is also a Candidate Key because it is very efficient and helps us to identify all the remaining attributes of a column.

ACD is also a Candidate Key because with the help of ACD we can get the value of B and our table will become complete. So ACD is also a Candidate Key.

Now it is the time of Primary Key.

Now you can see we have 2 candidate keys in a table.

R (ABCD) & B --> ACD, ACD --> B

Now we can think about the functional dependency in the relation which is more efficient. Obviously, we’ll say B is a more efficient candidate key. So Database Administrator will make B as a primary key in the table which will point to all the remaining data in the table. And if he chooses B as the primary key then ACD will become our Alternate Key.

And sometimes to find the B is quite critical. Let me explain, suppose you have purchased something online. The transaction is generated and the amount is deducted from your account but you didn’t get the things. Now you make the call to the helpline of that company, he asks you the transaction id of your transaction. Of course transaction id is the primary key in his system database but now you don’t remember the transaction id. Then he asks you please tell us when you purchase the products (Date Timing), what’s your name? What are the products you purchased? Now with the help of this information, he can easily find your record in his system. So don’t think always B is the best option for the primary key, we should also think about the different real-world scenarios as well.

When you make the primary key with the help of 1+ attributes, it will become the composite key of the table.

An attribute or set of attributes that is the basis for data retrieval is known as a secondary key. It can be a non-unique attribute. One secondary key value may refer to many records. For example, an attribute Address in Student table can be used to display all students who live in a particular city. In this case, Address will be used as a secondary key.

First Normal Form

A relationship is in 1NF if every intersection of row and column contains atomic values only. It means that the relation doesn’t contain any repeating group. A repeating group is a set of one or more data items that may occur a variable number of times in a tuple. Each cell in a relationship should contain only one variable.

A relation can be converted into 1NF using two methods,

Example

Suppose we have the following un-normalized relation DEPARTMENT,

DEPARTMENT(Dept_No, Dept_Name, Emp_No, Emp_Name)

Suppose the data in the relation is as follows,

Applying Method 1

The above relation contains the repeating groups. The attributes Emp_No and Emp_Name are being repeated for a single occurrence of Dept_No. The repeating groups are removed by entering proper data in the blank fields as follows.

The above relation is now in 1NF as each field contains a single value. It will have a composite key that consists of Dept_No and Emp_No attributes.

Applying Method 2

The second method to remove the repeating groups and make the cells atomic is by creating a new relation. It means the above un-normalized table will be divided into 2 relations. And then we’ll make the relationship between these two tables.

Department