CS计算机代考程序代写 SQL data structure database flex Hive 2021/8/8 First normal form - Wikipedia

2021/8/8 First normal form – Wikipedia

https://en.wikipedia.org/wiki/First_normal_form 1/7

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 no attribute domain have relations as elements. [1] Or more informally, that no
table column can have tables as values. Database normalization is the process of representing a database
in terms of relations in standard normal forms, where first normal is a minimal requirement. SQL does
not support creating or using table-valued columns, which means most relational databases will be in
first normal form by necessity. Database system which does not require first normal form are often called
no sql systems.

Overview

Rationale

Drawbacks and criticism

History

Examples

Designs that violate 1NF

Designs that comply with 1NF

Atomicity

1NF tables as representations of relations

Further reading

In a hierarchical database like the IBM Information Management System a record can contain sets of
child records, known as repeating groups or table-valued attributes. If such a data model is represented
as relations, a repeating group would be an attribute where the value is itself a relation. First normal
form eliminates nested relations by turning them into separate “top-level” relations associated with the
parent row through foreign keys rather than through direct containment.

The purpose of this normalization is to increase flexibility, data independence, and simplify the data
language. It also opens the door to further normalization which eliminate redundancy and anomalies.

Most relational databases management systems do not support nested records, so tables are in first
normal form by default. In particular, SQL does not have any facilities for creating or exploiting nested
tables. Normalization to first normal form would therefore be a necessary step when moving data from a

Contents

Overview

https://en.wikipedia.org/wiki/Relation_(database)
https://en.wikipedia.org/wiki/Relational_database
https://en.wikipedia.org/wiki/Database_normalization
https://en.wikipedia.org/wiki/SQL
https://en.wikipedia.org/wiki/No_sql
https://en.wikipedia.org/wiki/Hierarchical_database
https://en.wikipedia.org/wiki/IBM_Information_Management_System
https://en.wikipedia.org/wiki/Data_independence
https://en.wikipedia.org/wiki/SQL

2021/8/8 First normal form – Wikipedia

https://en.wikipedia.org/wiki/First_normal_form 2/7

hierarchical database to a relational database.

The rationale for normalizing to 1NF:[2]

Allows presenting, storing and interchanging relational data in the form of regular two-

dimensional arrays. Supporting nested relations would require more complex data structures.

Simplifies the data language, since any data item can be identified just by relation name,

attribute name and key. Supporting nested relations would require a more complex language

with support for hierarchical data paths in order to address nested data items.

Representing relationships using foreign keys is more flexible, where a hierarchical model only

can represent one-to many relationships.

Since locating data items is not directly coupled to the parent-child hierarchy, the database is

more resilient to structural changes over time.

Makes further normalization levels possible which eliminate data redundancy and anomalies.

Performance for certain operations. In a hierarchical model, nested records are physically

stored after the parent record, which means a whole sub-tree can be retrieved in a single read

operation. In a 1NF form, it will require a join operation per record type, which can be costly,

especially for complex trees. For this reasons document databases eschews 1NF.

Object-oriented languages represent runtime state as trees or graphs of objects connected by

pointers or references. This does not map cleanly to a 1NF relational database, a problem

sometimes called the Object-Relational Impedance Mismatch and which ORM libraries try to

bridge.

1NF has been interpreted as not allowing complex data types for values. This is open to

interpretation though, and C.J.Date have argued that values can be arbitrarily complex objects.

First normal form was introduced by E.F. Codd in the paper “A Relational Model of Data for Large
Shared Data Banks”, although it was initially just called “Normal Form”. It was renamed to “First Normal
Form” when additional normal forms were introduced in the paper Further Normalization of the
Relational Model [3]

The following scenarios first illustrate how a database design might violate first normal form, followed by
examples that comply.

Rationale

Drawbacks and criticism

History

Examples

Designs that violate 1NF

https://en.wikipedia.org/wiki/Document_database
https://en.wikipedia.org/wiki/Object-oriented_language
https://en.wikipedia.org/wiki/Object-Relational_Impedance_Mismatch
https://en.wikipedia.org/wiki/Object_relational_mapper

2021/8/8 First normal form – Wikipedia

https://en.wikipedia.org/wiki/First_normal_form 3/7

This table over customers’ credit card transactions does not conform to first normal form:

Customer Customer ID Transactions

Abraham 1

Transaction ID Date Amount

12890 14-Oct-2003 −87

12904 15-Oct-2003 −50

Isaac 2
Transaction ID Date Amount

12898 14-Oct-2003 −21

Jacob 3

Transaction ID Date Amount

12907 15-Oct-2003 −18

14920 20-Nov-2003 −70

15003 27-Nov-2003 −60

To each customer corresponds a ‘repeating group’ of transactions. Such a design can be represented in a
Hierarchical database but not a SQL database, since SQL does not support nested tables.

The automated evaluation of any query relating to customers’ transactions would broadly involve two
stages:

1. Unpacking one or more customers’ groups of transactions allowing the individual transactions

in a group to be examined, and

2. Deriving a query result based on the results of the first stage

For example, in order to find out the monetary sum of all transactions that occurred in October 2003 for
all customers, the system would have to know that it must first unpack the Transactions group of each
customer, then sum the Amounts of all transactions thus obtained where the Date of the transaction falls
in October 2003.

One of Codd’s important insights was that structural complexity can be reduced. Reduced structural
complexity gives users, applications, and DBMSs more power and flexibility to formulate and evaluate
the queries. A more normalized equivalent of the structure above might look like this:

To bring the model into the first normal form, we can perform normalization. Normalization (to first
normal form) is a process where attributes with non-simple domains are extracted to separate stand-
alone relations. The extracted relations are amended with foreign keys referring to the primary key of the

Designs that comply with 1NF

https://en.wikipedia.org/wiki/Hierarchical_database_model

2021/8/8 First normal form – Wikipedia

https://en.wikipedia.org/wiki/First_normal_form 4/7

relation which contained it. The process can be applied recursively to non-simple domains nested in
multiple levels.[4]

In this example, Customer ID is the primary key of the containing relations and will therefore be
appended as foreign key to the new relation:

Customer Customer ID

Abraham 1

Isaac 2

Jacob 3

Customer ID Transaction ID Date Amount

1 12890 14-Oct-2003 −87

1 12904 15-Oct-2003 −50

2 12898 14-Oct-2003 −21

3 12907 15-Oct-2003 −18

3 14920 20-Nov-2003 −70

3 15003 27-Nov-2003 −60

In the modified structure, the primary key is {Customer ID} in the first relation, {Customer ID,
Transaction ID} in the second relation.

Now each row represents an individual credit card transaction, and the DBMS can obtain the answer of
interest, simply by finding all rows with a Date falling in October, and summing their Amounts. The data
structure places all of the values on an equal footing, exposing each to the DBMS directly, so each can
potentially participate directly in queries; whereas in the previous situation some values were embedded
in lower-level structures that had to be handled specially. Accordingly, the normalized design lends itself
to general-purpose query processing, whereas the unnormalized design does not.

It is worth noting that this design meets the additional requirements for second and third normal form.

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.”[5] Codd defines an atomic value as one that “cannot be decomposed into smaller pieces by the
DBMS (excluding certain special functions)”[6] meaning a column 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 column.

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

Atomicity

https://en.wikipedia.org/wiki/Primary_key
https://en.wikipedia.org/wiki/Second_normal_form
https://en.wikipedia.org/wiki/Third_normal_form
https://en.wikipedia.org/wiki/Edgar_F._Codd
https://en.wikipedia.org/wiki/DBMS
https://en.wikipedia.org/wiki/Hugh_Darwen
https://en.wikipedia.org/wiki/Chris_Date

2021/8/8 First normal form – Wikipedia

https://en.wikipedia.org/wiki/First_normal_form 5/7

A character string would seem not to be atomic, as the RDBMS typically provides operators to

decompose it into substrings.

A fixed-point number would seem not to be atomic, as the RDBMS typically provides operators

to decompose it into integer and fractional components.

An ISBN would seem not to be atomic, as it includes language and publisher identifier.

Date suggests that “the notion of atomicity has no absolute meaning”:[9][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 may be more desirable
to separate a Customer Name column into two separate columns as First Name, Surname.

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:[11]

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:

A table that lacks a unique key constraint. 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 SQL:2003 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 column to contain exactly one value from its column’s domain. This

aspect of condition 4 is controversial. It marks an important departure from Codd’s later vision

of the relational model,[12] which made explicit provision for nulls.[13] 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 column within a table can contain a table, are

useful in rare cases.[14]

1NF tables as representations of relations

https://en.wikipedia.org/wiki/ISBN
https://en.wikipedia.org/wiki/Array_data_structure
https://en.wikipedia.org/wiki/Isomorphism
https://en.wikipedia.org/wiki/View_(database)
https://en.wikipedia.org/wiki/SQL
https://en.wikipedia.org/wiki/SQL:2003
https://en.wikipedia.org/wiki/Tuple
https://en.wikipedia.org/wiki/Null_(SQL)
https://en.wikipedia.org/wiki/Edgar_F._Codd
https://en.wikipedia.org/wiki/Relational_model

2021/8/8 First normal form – Wikipedia

https://en.wikipedia.org/wiki/First_normal_form 6/7

Date, C. J., & Lorentzos, N., & Darwen, H. (2002). Temporal Data & the Relational Model (http
s://archive.today/20121209052842/http://www.elsevier.com/wps/product/cws_home/680662)
(1st ed.). Morgan Kaufmann. ISBN 1-55860-855-9.

Date, C. J. (1999), An Introduction to Database Systems (https://web.archive.org/web/2005040
4010227/http://www.aw-bc.com/catalog/academic/product/0,1144,0321197844,00.html) (8th
ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.

Kent, W. (1983) A Simple Guide to Five Normal Forms in Relational Database Theory (http://ww
w.bkent.net/Doc/simple5.htm), Communications of the ACM, vol. 26, pp. 120–125

1. Codd, E.F (1970). “A Relational Model of Data for Large Shared Data Banks”. Communications
of the ACM. Classics. 13 (6): 377–87. p. 380-381

2. Codd, E.F (1970). “A Relational Model of Data for Large Shared Data Banks”. Communications
of the ACM. Classics. 13 (6): 377–87.

3. Codd, E. F. (1971). Further Normalization of the Relational Model. Courant Computer Science
Symposium 6 in Data Base Systems edited by Rustin, R.

4. Codd, E.F (1970). “A Relational Model of Data for Large Shared Data Banks”. Communications
of the ACM. Classics. 13 (6): 377–87. p. 381

5. Codd, E. F. The Relational Model for Database Management Version 2 (Addison-Wesley, 1990).

6. Codd, E. F. The Relational Model for Database Management Version 2 (Addison-Wesley, 1990),
p. 6.

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

8. Date, C. J. (2007). What First Normal Form Really Means. Date on Database: Writings 2000–
2006. Apress. p. 108. ISBN 978-1-4842-2029-0. “‘[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.'”

9. Date, C. J. (2007). What First Normal Form Really Means. Date on Database: Writings 2000–
2006. Apress. p. 112. ISBN 978-1-4842-2029-0.

10. Date, C. J. (6 November 2015). SQL and Relational Theory: How to Write Accurate SQL Code (h
ttps://books.google.com/books?id=BCjkCgAAQBAJ&pg=PA50). O’Reilly Media. pp. 50–.
ISBN 978-1-4919-4115-7. Retrieved 31 October 2018.

11. Date, C. J. (2007). What First Normal Form Really Means. Date on Database: Writings 2000–
2006. Apress. pp. 127–128. ISBN 978-1-4842-2029-0.

12. Date, C. J. (2009). “Appendix A.2”. SQL and Relational Theory. O’Reilly. “Codd first defined the
relational model in 1969 and didn’t introduce nulls until 1979”

13. Date, C. J. (October 14, 1985). “Is Your DBMS Really Relational?”. Computerworld. “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.” (the third of Codd’s 12
rules)

14. Date, C. J. (2007). What First Normal Form Really Means. Date on Database: Writings 2000–
2006. Apress. pp. 121–126. ISBN 978-1-4842-2029-0.

Related Posts