Databases Reference
In-Depth Information
The table is not in second normal form because some columns depend on just a portion of the primary
key. Converting to second normal form gives the following:
Branch (BranchNum, BranchName)
Book (BookCode, Title)
Inventory (BranchNum, BookCode, OnHand)
You can name the new table Inventory because it represents each branch's inventory. In the Inventory
table, the BranchNum column is a foreign key that identifies the Branch table and the BookCode column is a
foreign key that identifies the Book table. In other words, for a row to exist in the Inventory table,
both
the
branch number
and
the book code must already be in the database.
You can merge this Branch table with the existing Branch table without adding any new columns or rela-
tionships to the database. After adding the Inventory table to the existing cumulative design, you have the
design shown in Figure 6-13.
194
Publisher
PublisherCode
PublisherName (SK)
City
Branch
BranchNum
BranchName (SK)
BranchLocation
NumEmployees
Book
BookCode
Title (SK)
Paperback
Price
Type
PublisherCode (FK)
Author
AuthorNum
AuthorLast (SK)
AuthorFirst
Wrote
BookCode (FK)
AuthorNum (FK)
Sequence
Inventory
BookCode (FK)
BranchNum (FK)
OnHand
FIGURE 6-13
Cumulative design after fifth user view
NOTE
When you are using a software tool to produce these diagrams, the software may reverse the order of the columns that make
up the primary key from the order you intended. For example, the diagram in Figure 6-13 indicates that the primary key for the
Inventory table is BookCode and BranchNum, even though you intended it to be BranchNum and BookCode. This change in order
is not a problem. Indicating the fields that make up the primary key is what is significant, not the order in which they appear.
