Database Reference
In-Depth Information
There are some differences between the E-R diagram shown in Figure 6-8 and the ones you have seen so
far. The OrderLine entity appears as a rectangle with curved corners. Further, the relationships from Orders
to OrderLine and from Part to OrderLine are represented with solid lines instead of dashed lines.
Both of these differences are due to the fact that the primary key of the OrderLine entity contains for-
eign keys. In the OrderLine entity, both columns that compose the primary key (OrderNum and PartNum)
are foreign keys. Thus, to identify an OrderLine, you need to know the order and the part to which the order
corresponds.
This situation is different from one in which the primary key does not contain one or more foreign keys.
Consider the Customer table, for example, in which the primary key is CustomerNum, which is not a foreign
key. The Customer table does contain a foreign key, RepNum, which identifies the Rep table. To identify a
customer, however, all you need is the customer number; you do not need to know the rep number. In other
words, you do not need to know the sales rep to which the customer corresponds.
An entity that does not require a relationship to another entity for identification is called an independent
entity, and one that does require such a relationship is called a dependent entity. Thus, the Customer entity
is independent, whereas the OrderLine entity is dependent. Independent entities have square corners in the
diagram, and dependent entities have rounded corners.
A relationship that is necessary for identification is called an identifying relationship, whereas one that is
not necessary is called a nonidentifying relationship. Thus, the relationship between the Rep and Customer
entities is nonidentifying, and the relationship between the Orders and OrderLine entities is identifying. In an
E-R diagram, a solid line represents an identifying relationship and a dashed line represents a nonidentifying
relationship.
196
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 and location. 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 do not 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 follow-
ing 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, and location.
User View 3 Requirements: For each book, list its code, title, publisher code, publisher name, and
whether it is a paperback.
User View 4 Requirements: For each book, list its code, title, 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 topic's cover. The author order is not always alphabetical.
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 condition of the topic, and the price. A branch may
have multiple copies of the same book, each with a different quality (condition) and price. The copies of the
same book in a branch are assigned numbers to distinguish one copy from another.
User View 6 Requirements: For each book, list its code and title. In addition, for each branch that cur-
rently has a copy of the topic in stock, list the branch number, copy number, quality, and price of the topic.
To transform each user view into DBDL, examine the requirements and create the necessary entities,
keys, and relationships.
User View 1 Requirements: For each publisher, list the publisher code, publisher name, and city in
which the publisher 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 will need to
specify the PublisherName column as a secondary key.
