6

I have read the definition of 1NF which is, "If each attribute of relation is atomic". Please tell me what is Atomic.

1

8 Answers 8

15

1NF requires that every attribute position in every tuple in every relation contains a single value of the appropriate type. The types can be arbitrarily complex. In fact, the types can be relations. (CJ Date's book Database in depth: relational theory for practitioners treats this issue in a way that's pretty easy to understand.)

"Atomic" has never really meant "indivisible", which is why that term is finally falling out of favor. Loosely speaking, "atomic" means if a value has component parts, the dbms either ignores the existence of those parts, or it provides functions to manipulate them. For example, a timestamp value has these parts.

  • Year
  • Month
  • Day
  • Hours
  • Minutes
  • Seconds
  • Milliseconds

That kind of value is obviously divisible, and all database management systems provide functions to manipulate those parts. They also provide a way to select a timestamp as a single value. (Which, of course, it is.)

1
  • 1
    Well, atomic means indivisible. That's why atom was called atom in the first place, because they thought it was indivisible. Jun 24, 2021 at 8:28
9

"Atomic" refers to Codd's original notion from 1969 that each attribute in each tuple within a relation should consist of a single value and not allow multivalued structures of the kind supported in databases like the CODASYL model.

In modern SQL DBMSs atomicity isn't really an issue. SQL tables do not allow multivalued columns and values are always "atomic".

2
  • Technically true, but SQL tables do allow big strings as values, which is a loophole in practice. A string field that holds several comma-separate decimal integers, ASCII dates, etc., would certainly seem to violate the intent of atomicity, whether it follows the letter or not....
    – TextGeek
    Dec 16, 2020 at 18:59
  • @TextGeek: 1NF refers to domains, i.e. column types, not to individual values. A string type is atomic with regard to the relational model, it doesn't matter what you put into it. Putting comma separated integers into a single string means the the list is now atomic with regard to the relational operators. I don't know why you would do that, but it is not in violation of 1NF.
    – JacquesB
    Jun 24, 2021 at 9:19
3

Codd originally appears to have meant that no value is itself a set. This is a useful starting point but "atomic" has no ontological meaning when it comes to databases (something C. J. Date is right to point out). Codd tried to formalize the definition as something that could not be further decomposed outside of special database operations (i.e. a timestamp is atomic because extracting the year is a special database operation). In other words, if the database can break out sub-values, that is ok. But you should not have sets (unordered lists) or objects which require application logic to break down.

Over time and struggling with this issue in operational environments I would propose an intermediate definition of atomicity, one stricter than Codd and without the rabbit hole that Date discusses:

A value is atomic for purposes of first normal form if and only if:

  1. The value is not a set (yes, I know Date disagrees) and
  2. There are no foreign key references to any sub-portion of the field.

In particular, the representation of a value cannot determine its atomicity. Representing an IP address as "10.0.0.1" vs ARRAY[10,0,0,1] vs 167772161 does not matter for 1NF analysis since all three refer to the same atomic value.

In particular, common (and sometimes useful!) 1NF violations regarding atomicity include:

  1. Storing an array of tags to a blog post or comment.
  2. Storing tables within tables, where the inner table has a referential integrity requirement against another relation elsewhere.

These problems generate, more or less, the same data anomalies.

2

Atomic means data which cannot be divided further.

Rule of atomicity:

  • rule 1: a column with atomic data can't have several values of the same type of data in the same column.
  • rule2: a table with atomic data can't have several columns with the same datatype.

Like fullname column can't say that it could be atomic because it can be further divded into lastname, firstname. A column with interest could also be divided further, so a column which can't be divided is known as atomic.

1
  • 3
    rule2 is wrong, a table can certainly have multiple columns which the same data type. The full name example is also wrong: 1NF is a constraint on domains (i.e. data types) not on individual values. A full name is still a single string so does not violate 1NF, but a columns which allowed a set of strings as value would be a violation.
    – JacquesB
    Jun 24, 2021 at 7:21
0

It means that the key cannot be decomposed. Let's say that you have a table with three columns, forename, surname, and telephone_number. You declare a compound primary key on (forename, surname). That primary key is not atomic because it is actually composed of two columns. Now let's say you change your table to two columns, full_name and telephone_number, with a primary key on full_name. Is the key now atomic? No, because in your application you could split it into forename and surname still, on the space. Now let's make our table id, full_name and telephone_number, primary key on id (which is an integer). That is atomic, because the integer cannot be meaningfully decomposed.

1
  • 7
    your example however begins with the statament that atomicity "means that the key cannot be decomposed". That's not what it means. It means attribuites consist of a single value. There is nothing "non-atomic" about a key consisting of multiple attributes.
    – nvogel
    Apr 26, 2011 at 9:30
0

Consider a location on Earth. I can find it with (longitude, latitude) pairs or with the HTM (Hierarchical Triangular Mesh) code. Since they both measure the same data. This is a physical fact, which we can measure with a cell phone; it translates a grid number into [longitude, latitude] for you), they must both be either atomic or compound in your model.

Do not confuse the scale used for measurement and notation used to display the data with the nature of the data.

1
  • I edited this post for a couple of typos. I don't believe that this user is the real Joe Celko, but I searched for impersonation on meta.stackexchange.com and found this thread. Check out the responses of Messrs. Atwood and Spolsky, the founders of stackexchange. It appears to be permissible.
    – Vérace
    Sep 7, 2014 at 1:00
0

"Atomic" means a value that is not itself a relation. In SQL terminology, 1NF forbids tables with field values which are themselves tables. (As defined by E.F.Codd)

E.F.Codds use of the term "atomic" has caused some confusion, since taken out of context, you wouldn't intuitively consider a string or date value atomic - they can clearly be decomposed into parts. But in the context Codd uses the term, it refers to values that cannot be decomposed using only the relational operators. If a date is stored as a single value, you cannot use project or filter to pick only the month, so the value is atomic wrt relational operators.

Since SQL is (roughly) based on Codd's ideas, SQL does not allow nested tables, so a SQL database is always in first normal form. (One caveat is that SQL allows tables containing duplicate rows. Such a table is technically not a valid relation and therefore can't be said to be in 1NF either.)

-2

Atomic key is that kind of Primary key which cannot decomposed.which means that this key is not divided further,like Student_ID,Employee_ID.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.