Databases Reference
In-Depth Information
Chapter 3
N o r m a l i z a t i o n :
S o m e G e n e r a l i t i e s
Normal:
see
abnormal
—from an early IBM PL/I reference manual
In this chapter, I want to clarify certain general aspects of further normalization before we start getting into specifics
(which we'll do in the next chapter). I'll begin by taking a closer look at the sample value of relvar S from Fig. 1.1
(repeated for convenience in Fig. 3.1 below).
S ┌───────┐
┌─────┬───────┬────▼───┬───┼────┐
│ SNO │ SNAME │ STATUS │ CITY │
├═════┼───────┼────────┼────────┤
│ S1 │ Smith │ 20 │ London │
│ S2 │ Jones │ 30 │ Paris │
│ S3 │ Blake │ 30 │ Paris │
│ S4 │ Clark │ 20 │ London │
│ S5 │ Adams │ 30 │ Athens │
└─────┴───────┴────────┴────────┘
Fig. 3.1: The suppliers relvar—sample value
Recall now that the functional dependency (FD)
{ CITY }
→
{ STATUS }
holds in this relvar (I've included an arrow in the figure to suggest this fact). Because that FD holds,
1
it turns out
that the relvar is in second normal form (2NF) but not third (3NF). As a consequence, the relvar suffers from
redundancy; to be specific, the fact that a given city has a given status appears many times, in general. And the
discipline of
further normalization
—which from this point on I'll abbreviate most of the time to just
normalization
,
unqualified—would therefore suggest that we decompose the relvar into two relvars SNC and CT of lesser degree,
1
And, to be precise about the matter, because no other FDs hold apart from ones implied by the sole key {SNO}. See Chapter 4.
