First normal form

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.[1] The first definition of the term, in a 1971 conference paper by Edgar Codd, defined a relation to be in first normal form when none of its domains have any sets as elements.[2]

First normal form is an essential property of a relation in a relational database. Database normalization is the process of representing a database in terms of relations in standard normal forms, where first normal is a minimal requirement.

First normal form enforces these criteria:

Examples

The following scenario illustrates how a database design might violate first normal form.[3][4]

Domains and values

Below is a table that records the names and telephone numbers of customers. One requirement though is to record multiple telephone numbers for some customers. The simplest way of satisfying this requirement is to allow the "Telephone Number" field in any given record to contain more than one value:

Customer
Customer ID First Name Surname Telephone Number
123 Pooja Patel 555-861-2025, 192-122-1111
456 Zhang San (555) 403-1659 Ext. 53; 182-929-2929
789 John Doe 555-808-9633

Note that the telephone number field simply contains text: numbers of different formats, and more importantly, more than one number for two of the customers. We are duplicating related information in the same field. If we would be satisfied with such arbitrary text, we would be fine. But it's not arbitrary text at all: we obviously intended this column to contain telephone number(s). Seen as telephone numbers, the text is not atomic: it can be subdivided. As well, when seen as telephone numbers, the text contains more than one number in two of our records. This representation of telephone numbers is not in first normal form: our fields contain non-atomic values, and they contain more than one of them.

A design that complies with 1NF

To bring the model into the first normal form, we split the strings we used to hold our telephone number information into "atomic" (i.e. indivisible) entities: single phone numbers. And we ensure no record contains more than one phone number.

Customer
Customer ID First Name Surname Telephone Number
123 Pooja Patel 555-861-2025
123 Pooja Patel 192-122-1111
456 Zhang San 182-929-2929
456 Zhang San (555) 403-1659 Ext. 53
789 John Doe 555-808-9633

Note that the "ID" is no longer unique in this solution with duplicated customers. To uniquely identify a record, we need to use a combination of (ID, Telephone Number).

A design that also complies with higher normal forms

Another design for the same data makes use of two tables: a Customer Name table and a Customer Telephone Number table.

Customer Name
Customer ID First Name Surname
123 Pooja Patel
456 Zhang San
789 John Doe
Customer Telephone Number
Customer ID Telephone Number
123 555-861-2025
123 192-122-1111
456 (555) 403-1659 Ext. 53
456 123-567-1234
789 555-808-9633

Indeed, fields do not contain more than one telephone number in this design. Instead, each Customer-to-Telephone Number link appears on its own record. Using Customer ID as key, a one-to-many relationship exists between the name and the number tables. A record in the "parent" table, Customer Name, can be associated with many telephone number records in the "child" table, Customer Telephone Number, but each telephone number belongs to one, and only one customer.[5] It is worth noting that this design meets the additional requirements for second and third normal form.

Atomicity

Edgar F. Codd's definition of 1NF makes 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."[6] Codd defines an atomic value as one that "cannot be decomposed into smaller pieces by the DBMS (excluding certain special functions)"[7] meaning a field should not be divided into parts with more than one kind of data in it such that what one part means to the DBMS depends on another part of the same field.

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.[8][9] 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:

.

Date suggests that "the notion of atomicity has no absolute meaning":[10] 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; for example, it would be more desirable to separate a Customer Name field into two separate fields as First Name, Surname.

First normal form, as defined by Chris Date, permits relation-valued attributes (tables within tables). Date argues that relation-valued attributes, by means of which a field within a table can contain a table, are useful in rare cases.[11]

1NF tables as representations of relations

According to Date's definition, a table is in first normal form if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:[12]

  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].

Violation of any of these conditions would mean that the table is not strictly relational, and therefore that it is not in first normal form.

Examples of tables (or views) that would not meet this definition of first normal form are:

References

  1. ↑ Elmasri, Ramez; Navathe, Shamkant B. (July 2003). Fundamentals of Database Systems, Fourth Edition. Pearson. p. 315. ISBN 0321204484. It states that the domain of an attribute must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute.
  2. ↑ E. F. Codd (Oct 1972), Further normalization of the database relational model, Courant Institute: Prentice-Hall, ISBN 013196741X, A relation is in first normal form if it has the property that none of its domains has elements which are themselves sets.
  3. ↑ studytonight.com
  4. ↑ stackoverflow.com
  5. ↑ In the "real" world, that would not be a good assumption.
  6. ↑ Codd, E. F. The Relational Model for Database Management Version 2 (Addison-Wesley, 1990).
  7. ↑ Codd, E. F. The Relational Model for Database Management Version 2 (Addison-Wesley, 1990), p. 6.
  8. ↑ 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).
  9. ↑ "[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. ["What First Normal Form Really Means"] in Date on Database: Writings 2000-2006 (Springer-Verlag, 2006), p. 108
  10. ↑ Date, C. J. ["What First Normal Form Really Means"] p. 112.
  11. ↑ Date, C. J. ["What First Normal Form Really Means"] pp. 121–126.
  12. ↑ Date, C. J. ["What First Normal Form Really Means"] pp. 127–128.
  13. ↑ Such views cannot be created using SQL that conforms to the SQL:2003 standard.
  14. ↑ "Codd first defined the relational model in 1969 and didn't introduce nulls until 1979" Date, C. J. SQL and Relational Theory (O'Reilly, 2009), Appendix A.2.
  15. ↑ 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.

Further reading

This article is issued from Wikipedia - version of the 12/2/2016. The text is available under the Creative Commons Attribution/Share Alike but additional terms may apply for the media files.