Prashant2329 Prashant2329 - 1 year ago 80
SQL Question

What is atomicity in dbms

I read something like below in 1NF form of DBMS.

There was a sentence as follows:

"Every column should be atomic."

Can anyone please explain it to me thoroughly with an example?

Answer Source

"Every column should be atomic."

Chris Date says, "Please note very carefully that it is not just simple things like the integer 3 that are legitimate values. On the contrary, values can be arbitrarily complex; for example, a value might be a geometric point, or a polygon, or an X ray, or an XML document, or a fingerprint, or an array, or a stack, or a list, or a relation (and so on)."[1]

He also says, "A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute."[2]

He generally discourages the use of the word atomic, because it has confusing connotations. Single value is probably a better term to use.

For example, a date like '2014-01-01' is a single value. It's not indivisible; on the contrary, it quite clearly is divisible. But the dbms does one of two things with single values that have parts. The dbms either returns those values as a whole, or the dbms provides functions to manipulate the parts. (Clients don't have to write code to manipulate the parts.)[3]

In the case of dates, SQL can

  • return dates as a whole (SELECT CURRENT_DATE),
  • return one or more parts of a date (EXTRACT(YEAR FROM CURRENT_DATE)),
  • add and subtract intervals (CURRENT_DATE + INTERVAL '1' DAY),
  • subtract one date from another (CURRENT_DATE - DATE '2014-01-01'),

and so on. In this (narrow) respect, SQL is quite relational.

  1. An Introduction to Database Systems, 8th ed, p 113. Emphasis in the original.
  2. Ibid, p 358.
  3. In the case of a "user-defined" type, the "user" is presumed to be a database programmer, not a client of the database.