Database Reference
In-Depth Information
You convert this table to first normal form by removing the repeating group and expanding the primary
key. This gives the following:
Branch (BranchNum, BranchName, BookCode, Title, CopyNum, Quality, Price)
Q&A
Question: Why is CopyNum part of the primary key?
Answer: A branch can have more than one copy of the same book in stock. The Branch entity, as currently
designed, could include multiple rows with the same branch number and the same book code. To uniquely
identify a specific book, you also need the copy number. Thus, CopyNum must be part of the primary key.
199
In this table, you have the following functional dependencies:
BranchNum
fi
BranchName
BookCode
fi
Title
BranchNum, BookCode, CopyNum
fi
Quality, Price
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)
Copy (BranchNum, BookCode, CopyNum, Quality, Price)
You can name the new table Copy because it represents information about individual copies of books. In
the Copy 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 Copy 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, and you can merge this topic table with the existing Book table without adding any
new columns or relationships to the database. After adding the Copy table to the existing cumulative design,
you have the design shown in Figure 6-13.
Publisher
PublisherCode
PublisherName (SK)
City
Branch
BranchNum
BranchName (SK)
BranchLocation
Book
BookCode
Title (SK)
Paperback
Type
PublisherCode (FK)
Author
AuthorNum
AuthorLast (SK)
AuthorFirst
Wrote
BookCode (FK)
AuthorNum (FK)
Sequence
Copy
BookCode (FK)
BranchNum (FK)
CopyNum
Quality
Price
FIGURE 6-13
Cumulative design after fifth user view
