Page 254 -

Chapter 6 Foundations of Business Intelligence: Databases and Information Management 253

entity-relationship diagramming, and database design in the Learning Tracks
for this chapter.
Relational database systems try to enforce referential integrity rules to
ensure that relationships between coupled tables remain consistent. When
one table has a foreign key that points to another table, you may not add a
record to the table with the foreign key unless there is a corresponding record
in the linked table. In the database we examined earlier in this chapter, the
foreign key Supplier_Number links the PART table to the SUPPLIER table.
We may not add a new record to the PART table for a part with Supplier_
Number 8266 unless there is a corresponding record in the SUPPLIER table
for Supplier_Number 8266. We must also delete the corresponding record in
the PART table if we delete the record in the SUPPLIER table for Supplier_
Number 8266. In other words, we shouldn’t have parts from nonexistent
suppliers!
Database designers document their data model with an entity-relationship
diagram, illustrated in Figure 6.11. This diagram illustrates the relationship
between the entities SUPPLIER, PART, LINE_ITEM, and ORDER. The boxes
represent entities. The lines connecting the boxes represent relationships. A
line connecting two entities that ends in two short marks designates a one-to-
one relationship. A line connecting two entities that ends with a crow’s foot
topped by a short mark indicates a one-to-many relationship. Figure 6.11 shows
that one ORDER can contain many LINE_ITEMs. (A PART can be ordered
many times and appear many times as a line item in a single order.) Each PART
can have only one SUPPLIER, but many PARTs can be provided by the same
SUPPLIER.
It can’t be emphasized enough: If the business doesn’t get its data model right,
the system won’t be able to serve the business well. The company’s systems
will not be as effective as they could be because they’ll have to work with data
that may be inaccurate, incomplete, or difficult to retrieve. Understanding the
organization’s data and how they should be represented in a database is perhaps
the most important lesson you can learn from this course.
For example, Famous Footwear, a shoe store chain with more than 800
locations in 49 states, could not achieve its goal of having “the right style of
shoe in the right store for sale at the right price” because its database was not
properly designed for rapidly adjusting store inventory. The company had an
Oracle relational database running on a midrange computer, but the database
was designed primarily for producing standard reports for management rather
than for reacting to marketplace changes. Management could not obtain
precise data on specific items in inventory in each of its stores. The company
had to work around this problem by building a new database where the sales
and inventory data could be better organized for analysis and inventory
management.

FIGURE 6.11 AN ENTITY-RELATIONSHIP DIAGRAM

This diagram shows the relationships between the entities SUPPLIER, PART, LINE_ITEM, and ORDER that might be used to model the
database in Figure 6.10.

MIS_13_Ch_06 Global.indd 253 1/17/2013 2:27:43 PM