As you can see we have removed the partial functional dependency that we initially had. Now, in the table, the column Office Location is fully dependent on the primary key of that table, which is Department ID. Now that we have learnt 1st and 2nd normal forms lets head to the next part of this Normalization in SQL article. The other condition is there should be no transitive dependency for non-prime attributes. Now in order to achieve third normal form, we need to divide the table as shown below: As you can see from the above tables all the non-key attributes are now fully functional dependent only on the primary key.
|Published (Last):||1 March 2010|
|PDF File Size:||17.6 Mb|
|ePub File Size:||15.6 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.
Normalization of Database
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.
What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples
As you can see, we have also added some sample data to the table. In the table above: One student can enrol for multiple subjects. And, there can be multiple professors teaching one subject like we have for Java. What do you think should be the Primary Key? One more important point to note here is, one professor teaches only one subject, but one subject may have two different professors. Hence, there is a dependency between subject and professor here, where subject depends on the professor name.
Boyce–Codd normal form