Databases Reference
In-Depth Information
EXAMPLE 2
Ray Henry, the owner of a bookstore chain named Henry Books, gathers and organizes information about
branches, publishers, authors, and books. Each branch has a number that uniquely identifies the branch. In
addition, Ray tracks the branch's name, location, and number of employees. Each publisher has a code that
uniquely identifies the publisher. In addition, Ray tracks the publisher's name and city. The only user of the
Book database is Ray, but you don't want to treat the entire project as a single user view. Ray has provided you
with all the reports the system must produce, and you will treat each report as a user view. Ray has given you
the following requirements:
User View 1 Requirements:
For each publisher, list the publisher code, publisher name, and city in which
the publisher is located.
User View 2 Requirements:
For each branch, list the number, name, location, and number of employees.
User View 3 Requirements:
For each book, list its code, title, publisher code and name, and price and
whether it is a paperback.
User View 4 Requirements:
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. The author order is not always alphabetical.
User View 5 Requirements:
For each branch, list its number and name. In addition, list the code and title
of each book currently in the branch as well as the number of copies the branch has available.
User View 6 Requirements:
For each book, list its code and title. In addition, for each branch that cur-
rently has the book in stock, list the number and name of the branch along with the number of copies available.
191
To transform each user view into DBDL, examine the requirements and create the necessary entities, keys,
and relationships.
User View 1:
For each publisher, list the publisher code, publisher name, and city in which the pub-
lisher is located. The only entity in this user view is Publisher.
Publisher (PublisherCode, PublisherName, City)
This table is in third normal form; the primary key is PublisherCode. There are no alternate or foreign keys.
Assume Ray wants to be able to access a publisher rapidly on the basis of its name. You'll need to specify the
PublisherName column as a secondary key.
Because this is the first user view, there is no previous cumulative design. Thus, at this point, the new
cumulative design will consist only of the design for this user view, as shown in Figure 6-9. There is no need
for an E-R diagram at this point.
Publisher (PublisherCode, PublisherName, City)
SK PublisherName
FIGURE 6-9
DBDL for Book database after first user view
User View 2:
For each branch, list the number, name, location, and number of employees. The only entity
in this user view is Branch.
Branch (BranchNum, BranchName, BranchLocation, NumEmployees)
This table is in third normal form. The primary key is BranchNum, and there are no alternate or foreign keys.
Ray wants to be able to access a branch rapidly on the basis of its name, so you'll make the BranchName
column a secondary key.
