Database Reference
In-Depth Information
s
author(s) and the name(s) of the author(s). If a book has more than one author, all names must appear
in the order in which they are listed on the topic
User View 4 Requirements: For each book, list its code, title, and type. In addition, list the book
'
s cover. The author order is not always alphabetical.
There are two entities in the user view for books and authors. The relationship between them is many-
to-many (one author can write many books and one book can have many authors). Creating tables for each
entity and the relationship between them gives the following:
'
Author (AuthorNum, AuthorLast, AuthorFirst)
Book (BookCode, Title, Type)
Wrote (BookCode, AuthorNum)
198
The third table is named Wrote because it represents the fact that an author wrote a particular book. In
this user view, you need to be able to list the authors for a book in the appropriate order. To accomplish this
goal, you will add a sequence number column to the Wrote table. This completes the tables for this user
view, which are as follows:
Author (AuthorNum, AuthorLast, AuthorFirst)
Book (BookCode, Title, Type)
Wrote (BookCode, AuthorNum, Sequence)
The Author and Wrote tables are new; merging the Book table adds nothing new. Because it may be
important to find an author based on the author
'
s last name, the AuthorLast column is a secondary key. The
result of the merge step is shown in Figure 6-12.
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
FIGURE 6-12
Cumulative design after fourth user view
User View 5 Requirements: For each branch, list its number and name. In addition, for each copy of a
book in the branch, list the code and title of the topic, the quality of the book, and the price. A branch might
have multiple copies of the same book, each with a different quality and price. The copies of the same book
in a branch are assigned numbers to distinguish one copy from another.
Suppose you decide that the only entity mentioned in this requirement contains information about
branches. You would create the following table:
Branch (
You would then add the BranchNum column as the primary key, producing the following:
Branch (BranchNum,
The other columns include the branch name as well as the book code, book title, copy number, quality,
and price. Because a branch will have several books, the last five columns will form a repeating group. Thus,
you have the following:
Branch (BranchNum, BranchName, (BookCode, Title, CopyNum, Quality, Price) )
Search WWH ::




Custom Search