Database Reference
In-Depth Information
9
Q&A
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 customers,
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 Premiere
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,
rectangles 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
Column 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
part of the
one-to-many relationship between two entities. You will learn more about E-R diagrams in Chapter 6.
“
many
”
DATABASE MANAGEMENT SYSTEMS
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 manipula-
tion 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.
