First normal form


First normal form

First normal form (1NF or Minimal Form) is a normal form used in database normalization. A relational database table that adheres to 1NF is one that meets a certain minimum set of criteria. These criteria are basically concerned with ensuring that the table is a faithful representation of a relation" [T] he overriding requirement, to the effect that the table must directly and faithfully represent a relation, follows from the fact that 1NF was originally defined as a property of relations, not tables." Date, C. J. [http://www.dbdebunk.com/page/page/629796.htm "What First Normal Form Really Means"] in "Date on Database: Writings 2000-2006" (Springer-Verlag, 2006), p. 128.] and that it is free of repeating groups."First normal form excludes variable repeating fields and groups." Kent, William. [http://www.bkent.net/Doc/simple5.htm "A Simple Guide to Five Normal Forms in Relational Database Theory"] , "Communications of the ACM" 26 (2), Feb. 1983, pp. 120-125.]

The concept of a "repeating group" is, however, understood in different ways by different theorists. As a consequence, there is not universal agreement as to which features would disqualify a table from being in 1NF. Most notably, 1NF as defined by some authors (for example, Ramez Elmasri and Shamkant B. Navathe,Elmasri, Ramez and Navathe, Shamkant B. "Fundamentals of Database Systems, Fourth Edition" (Addison-Wesley, 2003), p. 315.] following the precedent established by Edgar F. Codd) excludes relation-valued attributes (tables within tables); whereas 1NF as defined by other authors (for example, Chris Date) permits them.

1NF tables as representations of relations

According to Date's definition of 1NF, a table is in 1NF if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:

Quotation
:1. There's no top-to-bottom ordering to the rows.:2. There's no left-to-right ordering to the columns.:3. There are no duplicate rows.:4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).:5. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps] .

:—Chris Date, "What First Normal Form Really Means", pp. 127-8Date, C. J. [http://www.dbdebunk.com/page/page/629796.htm "What First Normal Form Really Means"] pp. 127-128.] Violation of any of these conditions would mean that the table is not strictly relational, and therefore that it is not in 1NF.

Examples of tables (or views) that would not meet this definition of 1NF are:

*A table that lacks a unique key. Such a table would be able to accommodate duplicate rows, in violation of condition 3.
*A view whose definition mandates that results be returned in a particular order, so that the row-ordering is an intrinsic and meaningful aspect of the view.Such views cannot be created using SQL that conforms to the standard.] This violates condition 1. The tuples in true relations are not ordered with respect to each other.
*A table with at least one nullable attribute. A nullable attribute would be in violation of condition 4, which requires every field to contain exactly one value from its column's domain. It should be noted, however, that this aspect of condition 4 is controversial. It marks an important departure from Codd's original vision of the relational model, which made explicit provision for nulls.The third of Codd's 12 rules states that "Null values ... [must be] supported in a fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type." Codd, E. F. "Is Your DBMS Really Relational?" "Computerworld", October 14, 1985.]

Repeating groups

Date's fourth condition, which expresses "what most people think of as "the" defining feature of 1NF",Date, C. J. [http://www.dbdebunk.com/page/page/629796.htm "What First Normal Form Really Means"] p. 128.] is concerned with repeating groups. The following example illustrates how a database design might incorporate repeating groups, in violation of 1NF.

Example 1: Domains and values

Suppose a novice designer wishes to record the names and telephone numbers of customers. He defines a customer table which looks like this:

This representation, however, makes use of nullable columns, and therefore does not conform to Date's definition of 1NF. Even if the view is taken that nullable columns are allowed, the design is not in keeping with the spirit of 1NF. Tel. No. 1, Tel. No. 2., and Tel. No. 3. share exactly the same domain and exactly the same meaning; the splitting of Telephone Number into three headings is artificial and causes logical problems. These problems include:

*Difficulty in querying the table. Answering such questions as "Which customers have telephone number "X"?" and "Which pairs of customers share a telephone number?" is awkward.
*Inability to enforce uniqueness of Customer-to-Telephone Number links through the RDBMS. Customer 789 might mistakenly be given a Tel. No. 2 value that is exactly the same as her Tel. No. 1 value.
*Restriction of the number of telephone numbers per customer to three. If a customer with four telephone numbers comes along, we are constrained to record only three and leave the fourth unrecorded. This means that the database design is imposing constraints on the business process, rather than (as should ideally be the case) vice-versa.

Example 3: Repeating groups within columns

The designer might, alternatively, retain the single Telephone Number column but alter its domain, making it a string of sufficient length to accommodate multiple telephone numbers:

Repeating groups of telephone numbers do not occur in this design. Instead, each Customer-to-Telephone Number link appears on its own record.

Atomicity

Some definitions of 1NF, most notably that of Edgar F. Codd, make reference to the concept of atomicity. Codd states that the "values in the domains on which each relation is defined are required to be atomic with respect to the DBMS."Codd, E. F. "The Relational Model for Database Management Version 2" (Addison-Wesley, 1990).] Codd defines an atomic value as one that "cannot be decomposed into smaller pieces by the DBMS (excluding certain special functions)."Codd, E. F. "The Relational Model for Database Management Version 2" (Addison-Wesley, 1990), p. 6.]

Hugh Darwen and Chris Date have suggested that Codd's concept of an "atomic value" is ambiguous, and that this ambiguity has led to widespread confusion about how 1NF should be understood.Darwen, Hugh. "Relation-Valued Attributes; or, Will the Real First Normal Form Please Stand Up?", in C. J. Date and Hugh Darwen, "Relational Database Writings 1989-1991" (Addison-Wesley, 1992).] " [F] or many years," writes Date, "I was as confused as anyone else. What's worse, I did my best (worst?) to spread that confusion through my writings, seminars, and other presentations." Date, C. J. [http://www.dbdebunk.com/page/page/629796.htm "What First Normal Form Really Means"] in "Date on Database: Writings 2000-2006" (Springer-Verlag, 2006), p. 108] In particular, the notion of a "value that cannot be decomposed" is problematic, as it would seem to imply that few, if any, data types are atomic:

*A character string would seem not be atomic, as the RDBMS typically provides operators to decompose it into substrings.
*A date would seem not to be atomic, as the RDBMS typically provides operators to decompose it into day, month, and year components.
*A fixed-point number would seem not to be atomic, as the RDBMS typically provides operators to decompose it into integer and fractional components.

Date suggests that "the notion of atomicity "has no absolute meaning":Date, C. J. [http://www.dbdebunk.com/page/page/629796.htm "What First Normal Form Really Means"] p. 112.] a value may be considered atomic for some purposes, but may be considered an assemblage of more basic elements for other purposes. If this position is accepted, 1NF cannot be defined with reference to atomicity. Columns of any conceivable data type (from string types and numeric types to array types and table types) are then acceptable in a 1NF table—although perhaps not always desirable. Date argues that relation-valued attributes, by means of which a field within a table can contain a table, are useful in rare cases.Date, C. J. [http://www.dbdebunk.com/page/page/629796.htm "What First Normal Form Really Means"] pp. 121-126.]

Normalization beyond 1NF

Any table that is in second normal form (2NF) or higher is, by definition, also in 1NF (each normal form has more stringent criteria than its predecessor). On the other hand, a table that is in 1NF may or may not be in 2NF; if it is in 2NF, it may or may not be in 3NF, and so on.

Normal forms higher than 1NF are intended to deal with situations in which a table suffers from design problems that may compromise the integrity of the data within it. For example, the following table is in 1NF, but is not in 2NF and therefore is vulnerable to logical inconsistencies:

The table's key is {Customer ID, Telephone Number}.

If Jane Wright changes her surname by marriage, the change must be applied to two rows. If the change is only applied to one row, a contradiction results: the question "What is Customer 456's name?" has two conflicting answers. 2NF addresses this problem.

Notes and references

ee also

*Attribute-value system
*Second normal form

Further reading

* [http://www.troubleshooters.com/littstip/ltnorm.html Litt's Tips: Normalization]
* [http://www.datamodel.org/NormalizationRules.html Rules Of Data Normalization]
* Date, C. J., & Lorentzos, N., & Darwen, H. (2002). " [http://www.elsevier.com/wps/product/cws_home/680662 Temporal Data & the Relational Model] " (1st ed.). Morgan Kaufmann. ISBN 1-55860-855-9.
* Date, C. J. (1999), " [http://www.aw-bc.com/catalog/academic/product/0,1144,0321197844,00.html An Introduction to Database Systems] " (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
* Kent, W. (1983) " [http://www.bkent.net/Doc/simple5.htm A Simple Guide to Five Normal Forms in Relational Database Theory] ", Communications of the ACM, vol. 26, pp. 120-125
* Date, C. J., & Darwen, H., & Pascal, F. " [http://www.dbdebunk.com Database Debunkings] "

External links

* [http://databases.about.com/od/specificproducts/a/normalization.htm Database Normalization Basics] by Mike Chapple (About.com)
* [http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html An Introduction to Database Normalization] by Mike Hillyer.
* [http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html Normalization] by ITS, University of Texas.
* [http://www.datamodel.org/NormalizationRules.html Rules of Data Normalization] by Data Model.org
* [http://phlonx.com/resources/nf3/ A tutorial on the first 3 normal forms] by Fred Coulson
* [http://www.marcrettig.com/poster/ Free PDF poster available] by Marc Rettig
* [http://support.microsoft.com/kb/283878 Description of the database normalization basics] by Microsoft


Wikimedia Foundation. 2010.

Look at other dictionaries:

  • first normal form — noun A stage in the normalization of a relational database in which repeating groups and attributes have been eliminated by putting each into a separate table connected by a primary key foreign key relationship. See Also: second normal form …   Wiktionary

  • Normal-form game — In game theory, normal form is a way of describing a game. Unlike extensive form, normal form representations are not graphical per se, but rather represent the game by way of a matrix. While this approach can be of greater use in identifying… …   Wikipedia

  • Second normal form — (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. CoddCodd, E.F. Further Normalization of the Data Base Relational Model. (Presented at Courant Computer Science Symposia Series 6, Data Base Systems, New… …   Wikipedia

  • second normal form — noun A stage in the normalization of a relational database in which it is in first normal form and every non key attribute is dependent upon the entire primary key. See Also: first normal form …   Wiktionary

  • fifth normal form — noun A stage in the normalization of a relational database in which a database is in fourth normal form and every join dependency is implied by the candidate keys. See Also: first normal form …   Wiktionary

  • third normal form — noun A stage in the normalization of a relational database in which a database is in second normal form and all non key attributes are mutually independent (no transient dependencies.) See Also …   Wiktionary

  • fourth normal form — noun A stage in the normalization of a relational database in which a database is in Boyce Codd normal form and all multi valued dependencies are functional dependencies. See Also: first normal form, second normal form, third normal form …   Wiktionary

  • Conjunctive normal form — In Boolean logic, a formula is in conjunctive normal form (CNF) if it is a conjunction of clauses, where a clause is a disjunction of literals. As a normal form, it is useful in automated theorem proving. It is similar to the product of sums form …   Wikipedia

  • Jordan normal form — In linear algebra, a Jordan normal form (often called Jordan canonical form)[1] of a linear operator on a finite dimensional vector space is an upper triangular matrix of a particular form called Jordan matrix, representing the operator on some… …   Wikipedia

  • Skolem normal form — A formula of first order logic is in Skolem normal form (named after Thoralf Skolem) if it is in conjunctive prenex normal form with only universal first order quantifiers. Every first order formula can be converted into Skolem normal form while… …   Wikipedia


Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”

We are using cookies for the best presentation of our site. Continuing to use this site, you agree with this.