Database Reference
In-Depth Information
NOTE
When you are using a software tool to produce these diagrams, the software may change the order of the columns that make
up the primary key from the order you intended. For example, the diagram in Figure 6-13 indicates that the primary key for
the Copy table is BookCode, BranchNum, and CopyNum, even though you intended it to be BranchNum, BookCode, and
CopyNum. This change in order is not a problem. What is significant is the collection of fields that make up the primary key,
not the order in which they appear.
200
Q&A
Question: How would the design for this user view turn out if you began with two entities, Branch and Book,
instead of just the single entity Branch?
Answer: In the first step, you would create the following tables:
Branch (
Book (
Adding the primary keys would produce the following:
Branch (BranchNum,
Book (BookCode,
Adding the other columns would produce the following:
Branch (BranchNum, BranchName)
Book (BookCode, Title)
Finally, you have to implement the relationship between the Branch and Book tables. Because a branch
can have many books and a book can be in stock at many branches, the relationship is many-to-many. To
implement a many-to-many relationship, you add a new table whose primary key is the combination of the
primary keys of the other tables. Doing this, you produce the following:
Branch (BranchNum, BranchName)
Book (BookCode, Title)
Copy (BranchNum, BookCode)
Finally, you add any column that depends on both the BranchNum and BookCode columns to the Copy
table, which would appear to give the following:
Branch (BranchNum, BranchName)
Book (BookCode, Title)
Copy (BranchNum, BookCode, Quality, Price)
There is a problem with the Copy table, however. For the same reasons discussed earlier, the combina-
tion of BranchNum and BookCode is not sufficient for the primary key. To uniquely identify a row in this
table also requires the CopyNum field. Thus, the correct collection of tables is:
Branch (BranchNum, BranchName)
Book (BookCode, Title)
Copy (BranchNum, BookCode, CopyNum, Quality, Price)
As you can see, you end up with exactly the same collection of tables as before, which illustrates a point
made earlier: There is more than one way of arriving at the correct result.
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 book.
This user view leads to precisely the same set of tables that were created for User View 5.
You have satisfied all the requirements, and the design shown in Figure 6-13 represents the complete
information-level design.
