Database Reference
In-Depth Information
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
197
User View 2 Requirements: For each branch, list the number, name, and location.
The only entity in this user view is Branch.
Branch (BranchNum, BranchName, BranchLocation)
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 will make the
BranchName column a secondary key.
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)
SK BranchName
FIGURE 6-10
DBDL for Book database after second user view
User View 3 Requirements: For each book, list its code, title, publisher code and name, and whether it is
paperback.
To satisfy this user requirement, you will need to create entities for publishers and books and establish a
one-to-many relationship between them. This leads to the following:
Publisher (PublisherCode, PublisherName)
Book (BookCode, Title, Paperback, PublisherCode)
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. Assuming Ray will need to access books based on their titles, you will designate the Title column
as a secondary key.
Publisher
entity
Branch entity
( n ot related to other
entities at this
point)
Publisher
Publisher Code
Publisher Name (SK)
City
Branch
BranchNum
BranchName (SK)
BranchLocation
Secondary
keys
Book
entity
Book
BookCode
Title (SK)
Paperback
PublisherCode (FK)
Relationship
Foreign key
FIGURE 6-11
Cumulative design after third user view
Search WWH ::




Custom Search