Databases Reference
In-Depth Information
Because there is no table in the cumulative design with the BranchNum column as its primary key, you
can add the Branch table to the cumulative design during the merge step, as shown in Figure 6-10. Again, there
is no need for an E-R diagram with this simple design.
Publisher (PublisherCode, PublisherName, City)
SK PublisherName
Branch (BranchNum, BranchName, BranchLocation, NumEmployees)
SK BranchName
192
FIGURE 6-10
DBDL for Book database after second user view
User View 3:
For each book, list its code, title, publisher code and name, and price and whether it is
paperback. To satisfy this user requirement, you'll need to create entities for publishers and books and estab-
lish a one-to-many relationship between them. This leads to the following:
Publisher (PublisherCode, PublisherName)
Book (
BookCode
, Title, PublisherCode, Price, Paperback)
The PublisherCode column in the Book table is a foreign key identifying the publisher. Merging these tables
with the ones you already created does not add any new columns to the Publisher table, but it does add columns to
the Book table. The result of merging the Book table with the cumulative design is shown in Figure 6-11. Assum-
ing Ray will need to access books based on their titles, you'll designate the Title column as a secondary key.
Publisher
entity
Branch entity
(
n
ot related to other
entities at this
point)
Publisher
PublisherCode
PublisherName (SK)
City
Branch
BranchNum
BranchName (SK)
BranchLocation
NumEmployees
Secondary
keys
Book
entity
Book
BookCode
Title (SK)
Paperback
PublisherCode (FK)
Relationship
Foreign key
FIGURE 6-11
Cumulative design after third user view
User View 4:
For each book, list its code, title, price, and type. In addition, list the book'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 book's cover. There are two entities in the user view for books and authors. The rela-
tionship 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, Price, Type)
Wrote (BookCode, AuthorNum)
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, Price)
Wrote (BookCode, AuthorNum, Sequence)
