| Author: Muralee Krishnan.K 07 Sep 2008 | Member Level: Gold | Rating: Points: -20 |
Database normalization, sometimes referred to as canonical synthesis, is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies.
Normal forms
The normal forms (abbrev. NF) of relational database theory provide criteria for determining a table's degree of vulnerability to logical inconsistencies and anomalies. The higher the normal form applicable to a table, the less vulnerable it is to inconsistencies and anomalies. Each table has a "highest normal form" (HNF): by definition, a table always meets the requirements of its HNF and of all normal forms lower than its HNF; also by definition, a table fails to meet the requirements of any normal form higher than its HNF.
The normal forms are applicable to individual tables; to say that an entire database is in normal form n is to say that all of its tables are in normal form n.
First normal form
A table is in first normal form (1NF) if and only if it represents a relation.[3] Given that database tables embody a relation-like form, the defining characteristic of one in first normal form is that it does not allow duplicate rows or nulls. Simply put, a table with a unique key (which, by definition, prevents duplicate rows) and without any nullable columns is in 1NF.
Second normal form
Main article: Second normal form
The criteria for second normal form (2NF) are:
* The table must be in 1NF.
* None of the non-prime attributes of the table are functionally dependent on a part (proper subset) of a candidate key; in other words, all functional dependencies of non-prime attributes on candidate keys are full functional dependencies.[7] For example, consider an "Employees' Skills" table whose attributes are Employee ID, Employee Name, and Skill; and suppose that the combination of Employee ID and Skill uniquely identifies records within the table. Given that Employee Name depends on only one of those attributes – namely, Employee ID – the table is not in 2NF.
* In simple, a table is 2NF if it is in 1NF and all fields are dependent on the whole of the primary key, or 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.
* Note that if none of a 1NF table's candidate keys are composite – i.e. every candidate key consists of just one attribute – then we can say immediately that the table is in 2NF.
* All columns must be a fact about the entire key, and not a subset of the key.
Third normal form
Main article: Third normal form
The criteria for third normal form (3NF) are:
* The table must be in 2NF.
* Transitive dependencies must be eliminated. All attributes must rely only on the primary key. So, if a database has a table with columns Student ID, Student, Company, and Company Phone Number, it is not in 3NF. This is because the Phone number relies on the Company. So, for it to be in 3NF, there must be a second table with Company and Company Phone Number columns; the Phone Number column in the first table would be removed.if you have three columns in a single column. A COLUMN IS STUDENT ID , B COLUMN IS STUDENT NAME AND C COLUMN IS STUDENT ADDRESS
A -> B A -> C C -> B
For more details check the link
http://en.wikipedia.org/wiki/Database_normalization
Muralee Krishnan.K
|
| Author: Geetha 08 Sep 2008 | Member Level: Gold | Rating: Points: -20 |
DEFINITION - In creating a database, normalization is the process of organizing it into tables in such a way that the results of using the database are always unambiguous and as intended. Normalization may have the effect of duplicating data within the database and often results in the creation of additional tables. (While normalization tends to increase the duplication of data, it does not introduce redundancy, which is unnecessary duplication.) Normalization is typically a refinement process after the initial exercise of identifying the data objects that should be in the database, identifying their relationships, and defining the tables required and the columns within each table.
A simple example of normalizing data might consist of a table showing:
Customer Item purchased Purchase price
Thomas Shirt $40
Maria Tennis shoes $35
Evelyn Shirt $40
Pajaro Trousers $25
If this table is used for the purpose of keeping track of the price of items and you want to delete one of the customers, you will also delete a price. Normalizing the data would mean understanding this and solving the problem by dividing this table into two tables, one with information about each customer and a product they bought and the second about each product and its price. Making additions or deletions to either table would not affect the other.
Normalization degrees of relational database tables have been defined and include:
First normal form (1NF). This is the "basic" level of normalization and generally corresponds to the definition of any database, namely:
* It contains two-dimensional tables with rows and columns.
* Each column corresponds to a sub-object or an attribute of the object represented by the entire table.
* Each row represents a unique instance of that sub-object or attribute and must be different in some way from any other row (that is, no duplicate rows are possible).
* All entries in any column must be of the same kind. For example, in the column labeled "Customer," only customer names or numbers are permitted.
Second normal form (2NF). At this level of normalization, each column in a table that is not a determiner of the contents of another column must itself be a function of the other columns in the table. For example, in a table with three columns containing customer ID, product sold, and price of the product when sold, the price would be a function of the customer ID (entitled to a discount) and the specific product.
Third normal form (3NF). At the second normal form, modifications are still possible because a change to one row in a table may affect data that refers to this information from another table. For example, using the customer table just cited, removing a row describing a customer purchase (because of a return perhaps) will also remove the fact that the product has a certain price. In the third normal form, these tables would be divided into two tables so that product pricing would be tracked separately.
Domain/key normal form (DKNF). A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies. DKNF is the normalization level that most designers aim to achieve.
Reference:
http://searchsqlserver.techtarget.com/sDefinition/0,,sid87_gci212669,00.html
|
| Author: http://dotnetvj.blogspot.com 08 Sep 2008 | Member Level: Diamond | Rating:  Points: 2 |
Hi,
Check out the below link
http://databases.about.com/od/specificproducts/a/normalization.htm
Thanks -- Vj
http://dotnetvj.blogspot.com
Thanks -- Vj
http://dotnetvj.blogspot.com
|