Normalization rules divides larger tables into smaller tables and links them using relationships. The purpose of Normalisation in SQL is to eliminate redundant repetitive data and ensure data is stored logically. The inventor of the relational model Edgar Codd proposed the theory of normalization of data with the introduction of the First Normal Form, and he continued to extend theory with Second and Third Normal Form.
Later he joined Raymond F. Boyce to develop the theory of Boyce-Codd Normal Form. For example, there are discussions even on 6 th Normal Form. However, in most practical applications, normalization achieves its best in 3 rd Normal Form. The evolution of Normalization in SQL theories is illustrated below-.
Database Normalization Example can be easily understood with the help of a case study. Assume, a video library maintains a database of movies rented out. Without any normalization in database, all information is stored in one table as shown below. Here you see Movies Rented column has multiple values.
An SQL KEY is a single column or combination of multiple columns used to uniquely identify rows or tuples in the table. One of the driving forces behind database normalization is to streamline data by reducing redundant data.
Redundancy of data means there are multiple copies of the same information spread over multiple locations in the same database. This article is an effort to provide fundamental details of database normalization. The concept of normalization is a vast subject and the scope of this article is to provide enough information to be able to understand the first three forms of database normalization.
A database is considered third normal form if it meets the requirements of the first 3 normal forms. In the following example, the first table clearly violates the 1 NF. It contains more than one value for the Dept column. So, what we might do then is go back to the original way and instead start adding new columns, so, Dept1, Dept2, and so on.
In order to bring this First Normal Form, split the table into the two tables. This has the one-to-many relationship to the employee table. An entity is in a second normal form if all of its attributes depend on the whole primary key. In our Student table, two different informations are kept together, Student information and Branch information.
Hence, at the end of the academic year, if student records are deleted, we will also lose the branch information. This is Deletion anomaly. In the next tutorial, we will discuss about the First Normal Form in details. To understand what is Partial Dependency and how to normalize a table to 2nd normal for, jump to the Second Normal Form tutorial. Here is the Third Normal Form tutorial. But we suggest you to first study about the second normal form and then head over to the third normal form.
This form deals with certain type of anomaly that is not handled by 3NF. For a table to be in BCNF, following conditions must be satisfied:.
Here is the Fourth Normal Form tutorial. But we suggest you to understand other normal forms before you head over to the fourth normal form. Definition: A relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on each candidate key of the relation.
Notice that the customer and total amount are dependent upon the order number -- this data is specific to each order. However, the contact person is dependent upon the customer. In the first table, contact person is dependent upon the primary key -- customer name. The second table only includes the information unique to each order. Someone interested in the contact person for each order could obtain this information by performing a Join Operation.
Transitive Dependency A type of functional dependency where an attribute is functionally dependent on an attribute other than the primary key.
Thus its value is only indirectly determined by the primary key. Create a separate table containing the attribute and the fields that are functionally dependent on it. The tables created at this step will usually contain descriptions of either resources or agents. Keep a copy of the key attribute in the original file.
A relation is in third normal form if it is in 2NF and every non-key attribute of the relation is non-transitively dependent on each candidate key of the relation.
From these FDs, we may derive A-C. This dependence A-C is transitive. In this example, the city and state are dependent upon the ZIP code. To place this table in 3NF, two separate tables would be created -- one containing the company name and ZIP code and the other containing city, state, ZIP code pairings. Database designers should always keep in mind the tradeoffs between higher level normal forms and the resource issues that complexity creates.
BCNF is based on the concept of a determinant. A determinant is any attribute simple or composite on which some other attribute is fully functionally dependent.
0コメント