Normalization is the process of efficiently organizing data in a database with eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (for example, only storing related data in a table).
Benefits of Normalization
· Less storage space
· Quicker updates
· Less data inconsistency
· Clearer data relationships
· Easier to add data
· Flexible Structure
Types of Normalization:
1NF, 2NF, 3NF, BCNF, 4NF, 5NF are examples of Normal forms.
| Normalization Normalization is the process of efficiently organizing data in a database with eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (for example, only storing related data in a table). Benefits of Normalization · Less storage space · Quicker updates · Less data inconsistency · Clearer data relationships · Easier to add data · Flexible Structure Types of Normalization: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF are examples of Normal forms. 1NF (First Normal Form): If all attribute values are atomic, ie, no repeating group, no composite attributes. Table is not in 1NF
Table in 1NF
All attribute values are atomic because there are no repeating group and no composite attributes. 2NF (Second Normal Form): Second normal form (2NF) further addresses the concept of removing duplicative data: A relation R is in 2NF if; (i) R is 1NF , and (ii) All non-prime attributes are fully dependent on the candidate keys. Which is creating relationships between these new tables and their predecessors through the use of foreign keys.
Example: The following relation is in First Normal Form, but not Second Normal Form:
In the table above, the order number serves as the primary key. 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. An alternative way to accomplish this would be to create two tables:
The creation of two separate tables eliminates the dependency problem experienced in the previous case. 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. More Details : http://www.databasedev.co.uk/2norm_form.html 3NF (Third Normal Form): An entity is in Third Normal Form (3NF) when it meets the requirement of being in Second Normal Form (2NF) and additionally:
A row is in third normal form if and only if it is in second normal form and if attributes that do not contribute to a description of the primary key are move into a separate table. An example is creating look-up tables. Examples: The following table is NOT in Third Normal Form:
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. Table 1:
Table 2:
This may seem overly complex for daily applications and indeed it may be. Database designers should always keep in mind the tradeoffs between higher level normal forms and the resource issues that complexity creates. More Details: BCNF (Boyce-Codd Normal Form) A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a candidate key. Example BCNF:- Take the following table:
Note : no two buildings on any of the university campuses have the same name, thus ROOM/BLDG----->CAMPUS. As the determinant is not a candidate key this table is NOT in Boyce-Codd normal form. This table should be decomposed into the following relations: R1(course, class, room/bldg, time)
R2(room/bldg, campus)
4NF (Fourth Normal Form): Fourth normal form requires that a table be BCNF and contain no multi-valued dependencies. 4NF solves the problem of multivalued dependencies, which we would encounter with our above Sales table when a customer purchased multiple products with a single order - we would have numerous rows that were dependent on one another without any clear definition of that dependency in the table. 5NF (Fifth Normal Form): Fifth normal form, also known as join-projection normal form (JPNF), states that no non-trivial join dependencies exist. 5NF states that any fact should be able to be reconstructed without any anomalous results in any case, regardless of the number of tables being joined. A 5NF table should have only candidate keys and it's primary key should consist of only a single column. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |
No comments:
Post a Comment