关系数据库正规化定义Defining Normal Forms

节选自 'Beginning Database Design'.

Defining Normal Forms

Defining Normal Forms the Academic Way
The following are the precise academic definitions of Normal Forms.
❑ 1st Normal Form (1NF)—Eliminate repeating groups such that all records in all tables can be
identified uniquely by a primary key in each table. In other words, all fields other than the
primary key must depend on the primary key.
❑ 2nd Normal Form (2NF)—All non-key values must be fully functionally dependent on the
primary key. No partial dependencies are allowed. A partial dependency exists when a field is
fully dependent on a part of a composite primary key.
❑ 3rd Normal Form (3NF)—Eliminate transitive dependencies, meaning that a field is indirectly
determined by the primary key. This is because the field is functionally dependent on another
field, whereas the other field is dependent on the primary key.
❑ Boyce-Codd Normal Form (BCNF)—Every determinant in a table is a candidate key. If there is
only one candidate key, 3NF and BCNF are one and the same.
❑ 4th Normal Form (4NF)—Eliminate multiple sets of multivalued dependencies.
❑ 5th Normal Form (5NF)—Eliminate cyclic dependencies. 5NF is also known as Projection
Normal Form (PJNF).
❑ Domain Key Normal Form (DKNF)—DKNF is the ultimate application of normalization and is
more a measurement of conceptual state, as opposed to a transformation process in itself.

Is “beyond 3NF” unnecessary? It might be, but probably in many commercial situations it is
unnecessary. Remember that application SDKs are just as powerful as database engine structural and
functional capabilities. Extreme implementation of normalization using layers beyond 3NF tends to
place too much functionality into the database. Why not use the best of both worlds—both database
and application capabilities? Use the database to store data and allow applications to manipulate and
verify data to a certain extent.

Defining Normal Forms the Easy Way

How can normalization be made simple? Why is it easy? I like to offer a simplified interpretation of
normalization just to get the novice started. In a perfect world, most relational database model designs
are very similar. As a result, much of the basic database design for many applications from accounting to
manufacturing (and anything else you can think of) is all more or less the same. Some of the common
factors are separation of repeated fields in master-detail relationships using 1NF, pushing static data into
new tables using 2NF, and doing various interesting things with 3NF (such as uniquely identifying
repetitions between many-to-many relationships).

The following defines the Normal Forms in an easy to understand manner:
❑ 1st Normal Form (1NF)—Removes repeating fields by creating a new table where the original
and new table are linked together with a master-detail, one-to-many relationship. For example,
a master table could contain parent records representing all the ships owned by a cruise line. A
detail table would contain detail records, such as all the passengers on a cruise to the Caribbean.
Create primary keys on both tables where the detail table will have a composite primary key
containing the master table primary key field as the prefix field of its primary key. That prefix
field is also a foreign key back to the master table.
❑ 2nd Normal Form (2NF)—Performs a seemingly similar function to that of 1NF, but creates a
table where repeating values (rather than repeating fields as for 1NF) are removed to a new
table. The result is a many-to-one relationship rather than a one-to-many relationship, created
between the original and the new tables. The new table gets a primary key consisting of a single
field. The master table contains a foreign key pointing back to the primary key of the new table.
That foreign key is not part of the primary key in the original table.
❑ 3rd Normal Form (3NF)—It is difficult to explain 3NF without using a mind bogglingly confusing
technical definition. Elimination of a transitive dependency implies creation of a new table for
something indirectly dependent on the primary key in an existing table. There are a multitude of
ways in which 3NF can be interpreted.
❑ Beyond 3NF—Many modern relational database models do not extend beyond 3NF. Sometimes
3NF is not used at all. The reason why is because of the generation of too many tables and the
resulting complex SQL code joins, with resulting terrible database response times. One common
case that bears mentioning is removal of potentially NULL valued fields into new tables, creating
a one-to-one relationship. In modern high-end relational database engines with variable record
lengths, this is largely irrelevant. Disk space is cheap and, as already stated, increased numbers
of tables leads to bigger SQL joins and poorer performance.