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.”
