Databases Reference

In-Depth Information

EXAMPLE 6

In a paper he wrote in 1979 (“Extending the Database Relational Model to Capture More Meaning,”
ACM TODS 4
,

No. 4, December 1979), Codd proposed a certain design discipline, which (simplifying slightly) can be described as

follows:

Let
E
be an “entity type,” and let
ID
be a data type such that every entity of type
E
has exactly one
primary

identifier
(my term, not Codd's), of type
ID
. For example,
E
and
ID
might be the entity type “suppliers” and

the data type “character string,” respectively.

Let
P1
, ...,
Pn
be a set of “property types” such that every entity of type
E
has at most one property of each of

the types
P1
, ...,
Pn
. For example, in the case of suppliers,
P1
,
P2
, and
P3
might be the property types

“name,” “status,” and “city” (so
n
= 3 in this example).
Note:
I'm assuming for the sake of the present

discussion (only) that a given supplier can have any subset of the three properties, including the empty set in

particular.

Then the database should contain:

a.

Exactly one
E-relvar
, containing
ID
values for those entities of type
E
that exist at any given time,

and

b.

Exactly one
P-relvar
for each
Pi
(
i
= 1, ...,
n
), containing (
ID
value,
Pi
value) pairs for each entity of

type
E
that exists at any given time and has a property of type
Pi
at that time.

I'll refer to this discipline as “the RM/T discipline,” since it's part of what Codd referred to, in that 1979

paper, as “the extended relational model RM/T” (T for Tasmania, where Codd first presented his ideas for that

extended model). Applying the discipline to the case of suppliers specifically, we obtain a design that looks like this

(here I ignore for simplicity the fact that there's supposed to an FD from {CITY} to {STATUS}):

S { SNO }

KEY { SNO } ;

SN { SNO , SNAME }

KEY { SNO }

FOREIGN KEY { SNO } REFERENCES S

ST { SNO , STATUS }

KEY { SNO }

FOREIGN KEY { SNO } REFERENCES S

SC { SNO , CITY }

KEY { SNO }

FOREIGN KEY { SNO } REFERENCES S

Each of these relvars is irreducible; equivalently, each is in 6NF.
11
Fig. 15.3 shows a set of sample values.

Note:
The values in question aren't meant to be the same as our usual sample values, though they're close. Observe

11
In the interest of historical accuracy, I note that P-relvars as described by Codd in his RM/T paper weren't necessarily in 6NF, because he

didn't insist that each P-relvar involve just a single “property.”