Datasheet

ManualsBrandsWiley ManualsSoftware978-0-7645-4228-2

SQL and Relational Database Management Systems 1

add 10 identical records for Wile Electronics Inc., completely redundant except for the shipping

address (see Figure 1-5). The programs would still have to be changed because otherwise they

may return incorrect results.

FIGURE 1-4

Multiple columns to resolve multiple addresses for CUSTOMER

MAGNETICS USA INC.

NAME

123 LAVACA ST.

411 LONDON AVE.

BILLADDR

444 PINE ST.

232 EEL ST.

SHIPADDR_1 SHIPADDR_2 SHIPADDR_N SHIPADDR_10

WILE ELECTRONICS INC.

454 OAK ST. ... 999 ELK AVE.

FIGURE 1-5

Multiple records to resolve multiple addresses for CUSTOMER

MAGNETICS USA INC.

WILE ELECTRONICS INC.

NAME

123 LAVACA ST.

411 LONDON AVE.

BILLADDR

444 PINE ST.

232 EEL ST.

454 OAK ST.

456 WILLOW ST.

678 MAPLE AVE.

332 WALNUT ST.

531 DEER ST.

865 CEDAR AVE.

911 MYRTLE ST.

777 SITKA AVE.

999 ELK AVE.

SHIPADDR

As you can see, most problems are actually rooted in the structure of the database, which usu-

ally consisted of just one ﬁle with records of a ﬁxed length. The solution is to spread data across

several ﬁles and reassemble the required data when needed. As discussed earlier in this chapter,

hierarchical and network database models attempted to move in this direction, but they still had

had too many shortcomings, so the relational model became the most popular technique. The

problem just discussed would not be a problem at all in a relational database, where

CUSTOMER

and ADDRESS are separate entities (tables), linked via the primary/foreign key relationship (see

Figure1-6).Allyouhavetodoistoaddasmany

ADDRESS records as you want with a foreign

key that refers to its parent (see Figure 1-7).

This example might not look very convincing — it might appear that instead of

adding new shipping addresses to

CUSTOMER, were added the same records to a sep-

arate

ADDRESS table. In fact, the difference is huge. In a real-life legacy database, the CUSTOMER

ﬁle would not have just NAME and ADDRESS ﬁelds, but rather, it would contain tons of other infor-

mation: about orders, products, invoices, shipments, and so on. All that would be repeated every

time you accessed the database, even for something as simple as adding a new shipping address.