3.1 Data Redundancy

Redundancy Indicates the data is repeated. It increases the time for updating, adding and deleting data. The disk space and disk I/O operations are increased due to the redundancy.

Consider the following table STUDENT. 

 In the above table, the details of the students like STUDID, STUDENTNAME, and STUDENTADDRESS are repeated while adding the marks for each semester. If the user needs to modify the address for a student, it has to be modified in multiple rows for that student. If it is not done it could lead to data inconsistency across rows. 

Redundancy can cause the following issues:

1) Insertion, deletion and modification of data that can cause inconsistencies.
2) Errors can occur when the data is repeated.
3) Unnecessary utilization of the extra disk space.

The concept of normalization can be used to reduce redundancy.

3.2 Normalization Types

Normalization is a process of breaking the complex table structures into simpler table structures by using rules. Using the approach user can reduce the redundancy in a table and eliminate the problem of inconsistency. There is no loss of information.

Normalization has the following benefits:

1) It helps in maintaining data integrity
2) It helps in simplifying the structure of the table and thus makes the database more compact
3) It helps in reducing the null values which reduces the complexity if the data operations

With normalization, tables are formed that follow certain specified rules and represent certain normal forms. The forms are used to check that no error or inconsistency in data is introduced in the database.  A table structure is always in certain normal form. The widely used normal forms are as mentioned below:

1) First Normal Form (1NF)
2) Second Normal Form (2NF)
3) Third Normal Form (3NF)
4) Boyce Codd Normal Form (BCNF)

1) First Normal Form (1NF)

A table is said to be in 1NF when each cell of the table contains only one value.

The rules for converting a table to 1NF are as follows:

1) Place the related data values in a table. Define similar data values with the column name.
2) There should be no repeating group in the table
3) Every table must have a unique primary key

Consider the PROJECT table.

In the above table, the values are related and similar values are defined by common attribute. The table has a primary key as EMPLID. But the columns PROJECTCODE and TIME have multiple values and hence it is not in the normal form.

By applying the 1NF to the table, the new PROJECT table is mentioned below:

2) Second Normal Form (2NF)

A table is said to be in 2NF when 

1) It is in 1NF
2) No partial dependency exists between the non key and key attributes

The rules for converting a table into 2NF are as follows:

1) Remove the attributes that are functionally dependent on only a part of the key and not on the whole key. Place them in a different table.
2) Group the remaining attributes.

Consider the STUD_SUB_DETAILS table as shown below:

Consider the following points about the table.

1) The primary key of the preceding table is STUD_ID + SUB_CODE.
2) There are repeated values in the column STUD_FIRST_NAME, STUD_LAST_NAME and SUB_NAME. These attributes depend only on the part of the primary key.
3) The values in the column STUD_FIRST_NAME and STUD_LAST_NAME are dependent on STUD_ID.
4) The values in the SUB_NAME are only dependent on SUB_CODE and all non primary fields are not functionally dependent on the whole primary key.

Hence, to normalize the table in 2NF, user needs to break the table into smaller tables.

STUD_DETAILS

SUBJECT_DETAILS

STUD_SUBJECT_DETAILS

3) Third Normal Form (3NF)

A relation is said to be in third normal form if and only if: 

1) It is in 2NF
2) No transitive dependency exists between the non key attributes and key attributes.

The guidelines to convert the table into 3NF are as follows:

1) Find and remove all the non key attributes that are functionally dependent on attributes that are not in the primary key. Place them in different table.
2) Group the remaining attributes

Consider the following EMPLOYEE table.

In the above table, there is a single value in each cell. Hence it is in 1NF. 

The primary key in the table is EMPLID. For each value of EMPLID there is only one value of DEPTNAME. Hence, DEPTNAME is functionally dependent on the primary key EMPLID. For each value of EMPLID there is one DEPTHEAD. Hence all the attributes are functionally dependent on the whole key, EMPLID. Hence it is in 2NF.

To convert the table into 3NF, user must remove the DEPTHEAD column from the table as it is not functionally dependent on the primary key, EMPLID. 

The following table shows the 3NF form.

EMPLOYEE

DEPARTMENT

4) Boyce – Codd Normal Form (BCNF)

A relation is said to be in BCNF if and only if every determinant is a candidate key. 

The guidelines for converting a table into BCNF are as follows:

1) Find and remove the overlapping candidate keys. Place the part of the candidate key and the attribute, it is functionally dependent on, in a different table.
2) Group the remaining items in a table.

Consider the following PROJECT table.

The above table has redundancy. The PROJECT table has the following problems.

1) Multiple candidate keys that are ECODE+PROJCODE and NAME+PROJCODE.
2) Composite candidate keys
3) Candidate keys that overlap since the attribute PROJCODE is common between the two candidate keys.

The tables in BCNF are as shown below:

PROJECT

3.3 Understanding denormalization

The intentional introduction of redundancy in a table in order to improve performance is called denormalization.

Consider the following tables as ORDERS and PRODUCTS.

ORDERS

PRODUCTS

If a store keeper wants to calculate the total sale of a toy, there are thousands of rows. The server will take a lot of time to process the query and return the result. Hence, to speed up the query, the user can store the values of TAX, ORDERCOST, and PRODUCTCOST in one table as shown below.

ORDERS