SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Thursday, April 27, 2006

what is another word for database cell

SQL Apprentice Question
What is another word for database cell?
ie the single atomic units of data that a rdbms stores.

Celko Answers
>> the single atomic units of data that a rdbms stores. <<

Cell??? You are thinking about spreadsheets. A spreadsheet is made up
rows and columns which define a cell. A cell is either a value or a
declarative program element. they are both required and are of equal
status to get to the basic unit of work -- the cell.

A table is made up of ROWS, then rows are made up of columns. The unit
of work is the row, not the column within a row. You insert a whole
row; you delete a whole row and (beleive it or not) you update a whole

The ANSI model of an UPDATE is that it acts as if

1) You go to the base table. It cannot have an alias because an alias
would create a working table that woudl be updates and then disappear
sfter the statement.

2) You go to the WHERE clause. All rows (if any!) that test TRUE are
marked as a subset. If there no WHERE clause, then the entire table is
marked. The name of this set/pseudo-table is OLD in Standard SQL.

3) You go to the SET clause and construct a set/pseudo-table called
NEW. The rows in this table are build by copying values from the
columns are not mentioned from the original row to the NEW row. The
columns that are assigned all at once. That is, the unit of work is a
row, not one column at a time.

4) The OLD subset is deleted and the NEW set is inserted. This is why

SET a = b, b = a;

Swaps the values in the columns a and b. The engine checks constraint
and does a ROLLBACK if there are violations.

In full SQL-92, you can use row constructors to say things like:

SET (a, b)
= (SELECT x, y
FROM Floob AS F1
WHERE F1.keycol= Foobar.keycol);

What is the right term for the legs of an automobile? Answer: the
concept of a cell does not exist in RDBMS.

We have rows, which are made up of (unordered) columns. A column can
only hold a scalar value. A column can exist only within a row; a row
exists only within a table.

A spreadsheet cell is fixed in its location by its (row, column, sheet)
co-ordindates. It can hold a scalar value or declarative code.

I have run into this problem before when teaching accounting people.
There are always a few who have trouble making the leap. On the good
side, they are used to declarative programmng and do not have to learn
the "There are no FOR-loops in SQL!" stuff.

No comments: