Database Reference
In-Depth Information
So What's the Problem?
As already mentioned, the nice thing about normalization is that it allows for easy updating
without any redundancy. Each fact about the application domain can be updated by changing
just one value, at one row-column intersection. The problem arises when you try to get the
data back
out
. For instance, in our phone book application, we may want to have a form that
displays acontact along with
all
ofhisorherphonenumbers. Incases like these, the relational
database programmer reaches for a
JOIN
:
SELECT
SELECT
name
,
phone_number
FROM
JOIN
numbers
OON
contacts
.
contact_id
=
numbers
.
contact_id
WHERE
FROM
contacts
LEFT
LEFT JOIN
WHERE
contacts
.
contact_id
=
3
;
The result of this query? A result set like that shown in
Table 1-7
.
Table 1-7. Result of JOIN query
name phone_number
Jenny 555-333-3456
Jenny 555-334-3411
Indeed, the database has given us all the data we need to satisfy our screen design. The real
problem is in what the database had to do to
create
this result set, particularly if the database
is backed by a spinning magnetic disk. To see why, we need to briefly look at some of the
physical characteristics of such devices.
Spinning disks have the property that it takes
much
longer to
seek
to a particular location on
the disk than it does, once there, to sequentially
read
data from the disk (see
Figure 1-1
). For
instance, a modern disk might take 5 milliseconds to seek to the place where it can begin read-
ing. Once it is there, however, it can read data at a rate of 40-80 MBs per second. For an ap-
plication like our phone book, then, assuming a generous 1,024 bytes per row, reading a row
off the disk would take between 12 and 25
microseconds
.
