what is the normal form in English

All relational databases require unique keys to identify primary table rows.

A key is required whenever we need to join tables together with SQL. In most cases, the key will be a unique value (e.g. social security number), but we may sometimes see non-unique key values.

Sometimes the table will not have a unique value, and your table will have a sequence to uniquely identify each table row.

We need these matching keys to use SQL to join tables, and we do this inside our SQL WHERE clause. However, Oracle also has the ability to hard link related rows together. This is Oracle’s object oriented feature, where Object ID’s are used instead of keys:

Now that we see the basics of SQL, let’s take a look at the normalization steps, so that we can understand table structure.

Un-normalized Form (0NF)

Essentially, an un-normalized relation is a relation that contains repeating values. An un-normalized relation can also contain relations nested within other relations, as well as all kinds of transitive dependencies. Sometimes un-normalized relations are signified by 0NF, but an un-normalized relation is not to be confused with a denormalized relation.

The un-normalized relation is any relation in its raw state, and they commonly contain repeating vales and other characteristics that are not found in denormalized relations. The process of denormalization is a very deliberate attempt to introduce controlled redundant items into an already normalized form.

First Normal Form (1NF)

In essence, any relation is in first normal form if it does not contain any repeating values. Here, we have taken our relations with repeating values and moved them to separate relations. When the new relations are created, we carry the primary key of the original relation into the new relation.

We start by creating a primary key to uniquely identify each row in the table. Any repeating data is moved to a new table. Finally, create a key for each of the rows of the new table and a reference to that key in the original table. This is like what we did with the FRIENDS and ADDRESS tables above. First normal form removes repeating data to separate tables. Each of the new tables may have repeating data that should be moved to another new table. This process continues until there is no longer redundant data.

Second Normal Form (2NF)

For second normal form, you must start in first normal form. Then, each column in a table must be dependent on the key for that table, or it should be move to a new table.

The purpose of the second normal form (2NF) test is to check for partial key dependencies. Partial key dependencies are created when we break off an un-normalized relation into first normal form by carrying the key thereby creating a concatenated key with several data items. The formal definition of second normal form is as follows:

A relation is in second normal form if and only if the relation is in first normal form and each non-key attribute is fully functionally dependent on the entire concatenated key.

However, I prefer the following definition:

A relation is in second normal form if each attribute depends on the key, the whole key, and nothing but the key, so help me Codd.

Third Normal Form (3NF)

The third normal form (3NF) test refers to transitive dependencies. A transitive dependency is a circumstance where one non-key attribute is functionally dependent on another non-key attribute. Whereas the 2NF test serves to check for dependencies between key fields and attribute fields, the 3NF test serves to check for dependencies between non-key attributes.

First, your relation must already be in second normal form. Any column that is dependent on another column that is not the key must be placed in a new table. Any column that is derived from another column (like a total) must be placed in a separate table.

The process of going to 3NF is all about eliminating redundant data inside tables. However, almost all databases are not in 3NF, and your tables will almost always have deliberately-introduced values in multiple tables! Let’s see why.

Denormalization

Now that you have an idea of how normalization is used to segregate redundant and non-dependent data, I have to admit that most databases are not all the way into third normal form. As database software had become more powerful and hardware less expensive (such as fast hard drives), many databases are denormalized to improve performance. The process of denormalization has to do with taking a normalized database and reintroducing selected redundancy to improve performance.

When normalization techniques were created, disks were very expensive, and you did not want to have any redundant data because it was very expensive. Since the 1970’s, disk have become thousands of times cheaper, and it is now common to go to third normal form and then go back and reintroduce redundant data!

Having data in more than one place can reduce the amount of SQL table joins, making our SQL run faster. The rules for denormalization relate to the size of the data item, and how frequently the data is updated:

In other words, a tiny data item that is seldom updated is a great candidate for denormalization. Below, we see a database model where redundant data items have been re-added to eliminate SQL table joins:

The greater the amount of duplicated data, the smaller the number of tables, and the faster the SQL will run.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/412/viewspace-900275/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/412/viewspace-900275/