What is atomicity in dbms
atomic, database, database-normalization, sql
Solution
"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.
- An Introduction to Database Systems, 8th ed, p 113. Emphasis in the original.
- Ibid, p 358.
- In the case of a "user-defined" type, the "user" is presumed to be a database programmer, not a client of the database.
Problem
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?