Databases Reference

In-Depth Information

Chapter 4

F D s a n d B C N F

( I n f o r m a l )

It is downright sinful to teach the abstract before the concrete

—Z. A. Melzak:
Companion to Concrete Mathematics

As we saw in the previous chapter, Boyce/Codd normal form (BCNF for short) is defined in terms of functional

dependencies. In fact, BCNF is really
the
normal form with respect to functional dependencies (just as—to get

ahead of ourselves for a moment—5NF is really
the
normal form with respect to join dependencies). The overall

purpose of the present chapter is to explain this observation; as the chapter title indicates, however, the various

explanations and associated definitions are all (intentionally, of course) a little informal at this stage. (Informal, but

not inaccurate; I won't tell any deliberate lies.) A more formal treatment of the material appears in the next chapter.

FIRST NORMAL FORM

To begin at the beginning: Let relation
r
have attributes
A1
, ...,
An
, of types
T1
, ...,
Tn
, respectively. By definition,

then, if tuple
t
appears in relation
r
, then the value of attribute
Ai
in
t
is of type
Ti
(
i
= 1, ...,
n
). For example, if
r
is

the current value of the shipments relvar SP (see Fig. 1.1 in Chapter 1), then every tuple in
r
has an SNO value that's

of type CHAR, a PNO value that's also of type CHAR, and a QTY value that's of type INTEGER.

Now I can give a precise definition of first normal form:
1

Definition:
Let relation
r
have attributes
A1
, ...,
An
, of types
T1
, ...,
Tn
, respectively. Then
r
is in
first

normal form
(1NF) if and only if, for all tuples
t
appearing in
r
, the value of attribute
Ai
in
t
is of type
Ti

(
i
= 1, ...,
n
).

In other words,
every
relation is in 1NF, by definition! To say it in different words, 1NF just means each

tuple in the relation contains exactly one value, of the appropriate type, for each attribute. Observe in particular that

1NF places no limitations on what those attribute types are allowed to be.
2
They can even be relation types; that is,

relations with
relation valued attributes
—RVAs for short—are legal (you might be surprised to hear this, but it's

true). An example is given in Fig. 4.1.

1
One reviewer accused me of rewriting history with this definition. Guilty as charged, perhaps─but I do have my reasons; to be specific, earlier

“definitions” of the concept were all, in my opinion, either too vague to be useful or flat out wrong. See
SQL and Relational Theory
for further

discussion.

2
The relational model does, though. To paraphrase the answer to Exercise 2.2 in Appendix D, there are two small exceptions, both of which I'll

simplify just slightly here: First, no relation in the database can have an attribute of any pointer type; second, if relation
r
is of type
T
, then no

attribute of
r
can itself be of type
T
(think about it!). However, these exceptions have nothing to do with 1NF as such.