Databases Reference
In-Depth Information
Q&A
9
Question:
Why is the QuotedPrice column part of the OrderLine table? Can't you just use the part number
to look up the price in the Part table?
Answer:
If the QuotedPrice column didn't appear in the OrderLine table, you would need to obtain the price
for a part on an order line by looking up the price in the Part table. Although this might not be a bad prac-
tice, it prevents Premiere Products from charging different prices to different customers for the same part.
Because Premiere Products wants the flexibility to quote and charge different prices to different custom-
ers, the QuotedPrice column is included in the OrderLine table. If you examine the OrderLine table, you will
see cases in which the quoted price matches the actual price in the Part table and cases in which the quoted
price differs. For example, in order number 21608, Al's Appliance and Sport bought 11 irons, and Pre-
miere Products charged only $21.95 per iron, and not the regular price of $24.95.
A visual way to represent a database is with an
entity-relationship (E-R) diagram
. In an E-R diagram, rect-
angles represent entities, and lines represent relationships between connected entities. The E-R diagram for
the Premiere Products database appears in Figure 1-7.
Entity Names
Rep
Customer
RepNum
LastName
FirstName
Street
City
State
Zip
Commission
Rate
CustomerNum
CustomerName
Street
City
State
Zip
Balance
CreditLimit
RepNum
An entity appears
as a rectangle
Line indicates a
relationship
Orders
Part
OrderNum
OrderDate
CustomerNum
PartNum
Description
OnHand
Class
Warehouse
Price
C
olumn names
appear inside
rectangles
Absence of a dot
indicates the “one” part
of the relationship
Dot indicates the
“many” part of the
relationship
OrderLine
OrderNum
PartNum
NumOrdered
QuotedPrice
FIGURE 1-7
E-R diagram for the Premiere Products database
Each of the five entities in the Premiere Products database appears as a rectangle in the E-R diagram shown
in Figure 1-7. The name of each entity appears above the rectangle. The columns for each entity appear within
the rectangle. Because the Rep and Customer entities have a one-to-many relationship, a line connects these
two entities; similarly, a line connects the Customer and Orders entities, the Orders and OrderLine entities,
and the Part and OrderLine entities. The dot at the end of a line, such as the dot at the Customer end of the
line that connects the Rep and Customer entities, indicates the “many” part of the one-to-many relationship
between two entities. You will learn more about E-R diagrams in Chapter 6.
Managing a database is inherently a complicated task. Fortunately, software packages, called database man-
agement systems, can do the job of manipulating databases for you. A
database management system (DBMS)
is a program, or a collection of programs, through which users interact with a database. The actual manipu-
lation of the underlying database is handled by the DBMS. In some cases, users may interact with the DBMS
directly, as shown in Figure 1-8.
