For example, there are discussions even on 6th Normal Form. The evolution of Normalization theories is illustrated below- Database Normalization Example: We will study normalization with the help of a case study. Assume, a video library maintains a database of movies rented out. Without any normalization, all information is stored in one table as shown below.
|Published (Last):||11 May 2007|
|PDF File Size:||11.52 Mb|
|ePub File Size:||3.47 Mb|
|Price:||Free* [*Free Regsitration Required]|
FIRST NORMAL FORM It is a property of a relation in a relational database wherein only when the domain of each attribute has only atomic values values that cannot be divided or simplified further and the value of each attribute has only one value from the selected domain. Edgar Codd, an English Computer Scientist, stated that a relation is said to be in the first normal form when none of its domains have any sets as elements. It enforces several criteria including: 1 Removing repeating groups in individual tables, 2 Creating separate tables for every set of related data and 3 Identifying related data using the primary key of a given set.
Example: Consider a table containing the details of a company. The fields to be included are employee name, employee ID no, employee location and employee contact no. For better understanding, this will be displayed in a table form. Table 1. It brings ambiguity to the database and can generate anomalies. Hence the need arises to maintain the uniqueness of the field.
So the correct first normal form will be obtained upon editing in such a manner. The correct table will be: Table 1. The extra contact numbers were removed to obtain the required form design. It states that the domain should have values in the relation which are impossible to be broken down into smaller contents of data, with respect to DBMS. In other words, it maintains two important criteria to be met in order to provide a normalized data with the second normal form tag.
To give more clarity to the statements said above, consider a table and two attributes within the table, A and B. Suppose attribute B is functionally dependent on A, but is not on a proper subset of A. Then B can be considered to be fully functional and dependent on A.
A table that is in 1st normal form and contains only a single key as the primary key is automatically in 2nd normal form. Example: Consider a toy shop that has three branches in three different locations. A table is prepared indicating the customer IDs, store IDs and store location. Table 2. Hence the table does not satisfy the second normal form. TABLE 2. By splitting the table, the partial functional dependency is removed and atomicity is achieved for both the tables thus realizing 1NF in the process.
Transitive functional dependency can be best explained with the relationship link between three tables. It can also be said that the transitive functional dependency of non-prime attribute on any super key is removed.
A super key is reduced to a minimum no of columns required to uniquely identify each row. Example: Consider a table that shows the database of a bookstore. The database is maintained to keep a record of all the books that are available or will be available in the bookstore. The table of data is given below. Table 3. Hence we can see that a transitive functional dependency has developed which makes certain that the table does not satisfy the third normal form.
To further explain the advanced step of the normalization process, we are required to understand the Boyce-Codd Normal Form and its comparison with the third normal form. It was developed in to address certain types of anomalies that were not dealt by 3NF.
A relational scheme, once prepared in BCNF, will remove all sorts of functional dependency though some other forms of redundancy can prevail. The details are filled in the rows and columns of the table below: In the above table, no non-prime attributes exist which means that all attributes belong to some candidate key. This justifies the table being of 2NF and 3NF.
However,the table does not follow BCNF because of the dependency of the type of membership in which the determining attribute,type of membership on which pool no: depends is neither a candidate key nor a superset of a candidate key. The design needs to be modified in order to conform to the BCNF. The significance of explaining the BCNF comes when the step of normalization is to be explained.
The 4NF came at a significant time period as the next level of normalization. This means that X is either a candidate key or a superset. A table that satisfies 4NF is hard to come by most of the business applications. The 5NF is also called the project-join normal form and is the highest level of normalization designed to reduce redundancy in relational databases which is done by recording multi-valued facts by isolating semantically related multiple relationships.
What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples
It is a database design technique we use after completing ER modeling. This process identifies relationships between attributes called functional dependencies and applies series of tests described as normal forms. There are many articles written on this and you can find examples for almost all normal forms. However many articles explains the theory with the same scenario, hence thought to make a post with different set of examples that I use for my lectures. As explained above, the purpose of normalization is to identify the best grouping for attributes that ultimately forms relations.
Normalization of Database