Databases Reference
In-Depth Information
The fundamental point is that hierarchies are asymmetric; thus, while they might make some tasks “easier,”
they certainly make others more difficult.
As a specific illustration of the previous point, queries in particular are asymmetric, as well as being more
complicated than their symmetric counterparts. For example, consider what's involved in formulating the
queries “Get part numbers for parts supplied by supplier S2” and “Get supplier numbers for suppliers who
supply part P2” against the relation of Fig. 4.1. The natural language versions of these queries are symmetric
with respect to each other, but their formulations in SQL—or
Tutorial D
, or some other formal language—
most certainly aren't (exercise for the reader).
Similar remarks apply to integrity constraints.
Similar remarks apply to updates, but more so.
There's no guidance, in general, as to how to choose the “best” hierarchy.
Even “natural” hierarchies like organization charts and bill of materials structures are still best represented,
usually, by nonhierarchic designs.
Well, by now you might be wondering, if all relvars are in 1NF by definition, what it might possibly mean
not
to be in 1NF. Perhaps surprisingly, this question does have a sensible answer. The point is, today's commercial
DBMSs don't properly support relvars (or relations) at all—instead, they support a construct that for convenience
I'll call a
table
, though by that term I don't necessarily mean to limit myself to the kinds of tables found in SQL
systems specifically. And tables, as opposed to relvars, might indeed not be in 1NF. To elaborate:
Definition:
A table is in
first normal form
(1NF)—equivalently, such a table is
normalized
—if and only if
it's a direct and faithful representation of some relvar.
So of course the question is: What does it mean for a table to be a direct and faithful representation of a
relvar? There are five basic requirements, all of which are immediate consequences of the fact that the value of a
relvar at any given time is (of course) always a relation specifically:
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 of the applicable type, and nothing else.
5.
All columns are regular (see below).
Requirements 1-3 are self-explanatory,
4
but the other two merit a little more explanation, perhaps. Here's an
example of a table that violates Requirement 4:
4
Though I note in passing that Requirement 2 in particular effectively means SQL tables are
never
normalized─except, possibly, if they happen
to have just one column. However, the disciplines recommended in
SQL and Relational Theory
allow you, among other things, to treat such
tables (for the most part) as if they were normalized after all.
