Databases Reference

In-Depth Information

┌─────┬──────────────┐

│ SNO │ PNO │

├═════┼──────────────┤

│ S1 │ P1 , P2 │

│ S1 │ P2 │

│ S1 │ P2 , P4 , P5 │

└─────┴──────────────┘

The violation occurs because values in the PNO column aren't individual part numbers as such but, rather,
groups
of

part numbers (the group for S2 contains two part numbers, that for S3 contains one, and that for S4 contains three).

Note:
The violation of Requirement 4 in the foregoing example would perhaps be clearer if column PNO,

instead of being defined to be of type CHAR, was defined to be of some user defined type, perhaps also called PNO.

Then it might be more obvious that values in that column weren't of that type per se but were rather of type “PNO

group.” Such considerations point the way to a reasonably precise definition of the term
repeating group:

Definition:
Column
C
is a
repeating group
column (also known as a
multivalued attribute
) if and only if

it's defined to be of type
T
but the values that appear in that column aren't values of type
T
but are, rather,

groups
(in other words,
sets
or
lists
or
arrays
or ...) of values of type
T
.

If you're still confused over the difference between repeating group columns and RVAs, take another look at

Fig. 4.1. The RVA in that figure—viz., attribute PQ—is
not
a repeating group column (relations don't allow

repeating groups!). Rather, it's an attribute whose type happens to be a certain relation type—to be specific, and

using
Tutorial D
syntax, the relation type

RELATION { PNO CHAR , QTY INTEGER }

—and values of that attribute are, precisely, relations of this type. So the relation in that figure does abide by the

definition of 1NF.

Incidentally, Requirement 4 also means nulls are prohibited (nulls aren't values).

Turning now to Requirement 5 (“All columns are regular”): What this requirement means is, first, that every

column has a name, unique among the column names that apply to the table in question; second, that no row is

allowed to contain anything extra, over and above the regular column values prescribed under Requirement 4. For

example, there are no “hidden” columns that can be accessed only by special operators instead of by regular column

references (i.e., by column name), and there are no columns that cause invocations of regular operators on rows to

have irregular effects. In particular, therefore, there are no
identifiers
other than regular key values (no hidden row

IDs or “object IDs,” as are unfortunately found in some SQL products today), and no hidden
timestamps
as are

found in certain “temporal database” proposals in the literature.

To sum up: If any of the five requirements are violated, the table in question doesn't “directly and faithfully”

represent a relvar,
and all bets are off
. In particular, relational operators such as join are no longer guaranteed to

work as expected (as you'll already know if, as I assume, you're familiar with SQL). The relational model deals

with
relations
(meaning, more precisely, relation values and relation variables), and relations
only
.

FUNCTIONAL DEPENDENCIES

So much for 1NF; now I can begin to discuss some of the higher normal forms. Now, I've already said that

Boyce/Codd normal form (BCNF) is defined in terms of functional dependencies, and in fact the same is true of

second normal form (2NF) and third normal form (3NF) as well. Here then is a definition: