# Does 1NF require that there can be no duplicate rows?

From Database System Concepts, by Silberschatz, Korth and Sudarshan :

A domain is atomic if elements of the domain are considered to be indivisible units. We say that a relation schema R is in ﬁrst normal form (1NF) if the domains of all attributes of R are atomic.

1NF is the most basic of normal forms - each cell in a table must contain only one piece of information, and there can be no duplicate rows.

If I am correct, "there can be no duplicate rows" in the second definition means exactly that there must exist at least one candidate key in the relation schema.

I wonder if the two definitions of 1NF above are the same?

In particular, is it correct that "there can be no duplicate rows" can't be implied by "the domains of all attributes of R are atomic"?

Thanks.

Updated:

This book seems to say that having a primary key implies atomic and is it correct?

In first normal form (INF), related attributes are organized into separate tables, each with a primary key. A primary key is an attribute or set of attributes that uniquely defines a tuple. Thus, if a table is in INF, entities within the data model contain no attributes that repeat as groups. W. Kent has explained that in INF, all occurrences of a record must contain the same number of fields. In INF, each data cell (defined by a specific tuple and attribute) in the table will contain only atomic values.

What was called 1NF in the past is considered nowadays part of the definition of the Relational Data Model itself: each attribute must be a single value, neither composed, nor repeated. When we talk about relations we assume implicitly this fact, since structures with non-flat attributes are not considered proper relations.

Note, however, that there exists alternative models, called non-1NF models or NRC which are based on the concept of relations with possibly other relations as values inside tuples (so it is a kind of Nested Relational Model). Such models, presented in the scientific literature but not implemented in commercial systems, are in some sense a sort of “bridge” towards the late object-oriented data model, that allow more complex kind of data (and that have a concept of association which differs from that of foreign key).

A different aspect is that relations are sets, and not multisets (that is they cannot contains duplicate rows). This is a basic principle of the Relational Data Model: we assume that a relation is just a set, stop. For more details, see this answer.

But you should keep in mind the these are mainly theorical aspects, useful when reasoning about relations, about languages to operate on them (for instance the Relational Algebra), and about theories developed on them (for instance the Normalization Theory).

In practice, however, things can be quite different: for instance in Relational Database Management Systems you can have “relations” without keys or unique attributes, so that they can have duplicate rows. Also, you can have composed data types for attributes of records, like arrays (or, worse, people sometimes put multiple values in an attribute maybe as a single string with commas separating the intended values).

• I would shift the emphases a bit. As you say, duplicates are not allowed in the relational model because we're talking about relations, so it's not so much 1NF that requires this, but the use of the relational model itself. I would view things like the Nested Relational Calculus (NRC) which allows relation-valued attributes as being reasonably "relational" but allowing violations of 1NF. If you view the relational model set-theoretically, then NRC is a perfectly fine relational model. If you view it logically, it's the distinction between a first-order and a higher-order logic. – Derek Elkins left SE Jan 14 '18 at 23:22
• Thanks. I have found a book which mentions both being atomic and having a primary key for first normal form. See my update to my post. Does the book imply having a primary key implies being atomic, and is it correct? – Tim Jul 6 '18 at 6:07
• Having a primary key is independent from the atomicity of attributes. It simply expresses the condition that a relation must be a set. You can have set of tuples with non atomic fields. If you have both, this a definition of the concept of relation in the relational model. – Renzo Jul 7 '18 at 17:01

With continuation to your previous question Does 2NF require 1NF?

Theoretically aspects say that each cell (value) in a table (relation) must contain only one piece of information, and there can be no duplicate rows.

From Wiki

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:

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.

You argument: "...is it correct that "there can be no duplicate rows"
can't be implied by "the domains of all attributes of R are atomic"?..."


Basic definition states that no cell in given attribute(s) should be multivalued and composite, or in other words they should be atomic. But being atomic doesn't mean that they are also in non-duplicate state. Duplicity can exist, but there are 5 explicit constraints and out of that one tells you to avoid duplicity. So, actually with basic definition of 1NF follows five other constraints.

The requirement for unique rows is not part of the definition of 1NF, rather it is a prerequisite for normalizing to 1NF.

There is a step before first normal form which is called unnormalized relations (described in Codd, E.F. "Further Normalisation of the Data Base Relational Model"). In this step the data is described as relations, which means rows have to be unique, per the definition of relation. But there is no constraint on the domains, so you can have attributes which themselves contain relations, in arbitrary depth.

First normal form means eliminating non-simple domains, which means an attribute should not have relations as values. Normalizing is the process of eliminating nested tables by turning nested tables into stand-alone tables, instead connected to the parent row through a foreign key. (described in Codd, E.F (1970). "A Relational Model of Data for Large Shared Data Banks".)

Note that SQL and hence most relational databases does not allow or support nested tables in the first place, so data have to be in 1NF before it can even be entered.

is it correct that "there can be no duplicate rows" can't be implied by "the domains of all attributes of R are atomic"?

It is implied by the definition of relation. Since relations are sets of tuples it follows that tuples are distinct. So the rows are distinct whether or not the domains are atomic.

As for the three quotes:

The first quote is more or less correct, although "indivisible units" is somewhat misleading - it just means anything which is not a relation.

The second quote is just wrong. Codd specifically describes normalization to first normal form as requiring relations with primary keys as a starting point.

The third quote is ambiguous. It implies that for a table to be in 1NF it need have primary keys. This is technically correct, but it is not the definition of 1NF, it is a prerequisite. "No attributes that repeat as groups" is also rather ambiguous. A relation cannot have a varying number of attributes, that would be against the definition of relation, but I'm not sure what "repeat as groups" is supposed to mean.