Chapter 2 Data Models
13
Chapter 2
Data Models
Discussion Focus
Although all of the topics covered in this chapter are important, our students have given us consistent
feedback: If you can write precise business rules from a description of operations, database design is
not that difficult. Therefore, once data modeling (Sections 2.1, The Importance of Data Models,” and
2.2, “Data Model Basic Building Blocks,”) has been examined in detail, Section 2.3, “Business Rules,”
should receive a lot of class time and attention. Perhaps it is useful to argue that the answers to questions
2 and 3 in the Review Questions section are the key to successful design. That’s why we have found it
particularly important to focus on business rules and their impact on the database design process.
What are business rules, what is their source, and why are they crucial?
Business rules are precisely written and unambiguous statements that are derived from a detailed
description of an organization’s operations. When written properly, business rules define one or more of
the following modeling components:
entities
relationships
attributes
connectivities
cardinalities – these will be examined in detail in Chapter 3, “The Relational Database Model.”
Basically, the cardinalities yield the minimum and maximum number of entity occurrences in an
entity. For example, the relationship decribed by “a professor teaches one or more classes”
means that the PROFESSOR entity is referenced at least once and no more than four times in the
CLASS entity.
constraints
Because the business rules form the basis of the data modeling process, their precise statement is crucial
to the success of the database design. And, because the business rules are derived from a precise
description of operations, much of the design’s success depends on the accuracy of the description of
operations.
Examples of business rules are:
An invoice contains one or more invoice lines.
Each invoice line is associated with a single invoice.
A store employs many employees.
Each employee is employed by only one store.
A college has many departments.
Each department belongs to a single college. (This business rule reflects a university that has
multiple colleges such as Business, Liberal Arts, Education, Engineering, etc.)
A driver may be assigned to drive many different vehicles.
Chapter 2 Data Models
14
Each vehicle can be driven by many drivers. (Note: Keep in mind that this business rule reflects
the assignment of drivers during some period of time.)
A client may sign many contracts.
Each contract is signed by only one client.
A sales representative may write many contracts.
Each contract is written by one sales representative.
Note that each relationship definition requires the definition of two business rules. For example, the
relationship between the INVOICE and (invoice) LINE entities is defined by the first two business rules
in the bulleted list. This two-way requirement exists because there is always a two-way relationship
between any two related entities. (This two-way relationship description also reflects the implementation
by many of the available CASE tools.)
Keep in mind that the ER diagrams cannot always reflect all of the business rules. For example, examine
the following business rule:
A customer cannot be given a credit line over $10,000 unless that customer has maintained a
satisfactory credit history (as determined by the credit manager) during the past two years.
This business rule describes a constraint that cannot be shown in the ER diagram. The business rule
reflected in this constraint would be handled at the applications software level through the use of a
trigger or a stored procedure. (Your students will learn about triggers and stored procedures in Chapter
8, “Advanced SQL.”)
Given their importance to successful design, we cannot overstate the importance of business rules and
their derivation from properly written description of operations. It is not too early to start asking students
to write business rules for simple descriptions of operations. Begin by using familiar operational
scenarios, such as buying a book at the book store, registering for a class, paying a parking ticket, or
renting a DVD.
Also, try reversing the process: Give the students a chance to write the business rules from a basic data
model such as the one presented in the text’s Figures 2.1 and 2.2. Ask your students to write the business
rules that are the foundation of the relational diagram in Figure 2.4 and then point their attention to the
relational tables in Figure 2.3 to indicate that an AGENT occurrence can occur multiple times in the
CUSTOMER entity, thus illustrating the implementation impact of the business rules
An agent can serve many customers.
Each customer is served by one agent.
Chapter 2 Data Models
15
Answers to Review Questions
1. Discuss the importance of data modeling.
A data model is a relatively simple representation, usually graphical, of a more complex real world
object event. The data model’s main function is to help us understand the complexities of the real–
world environment. The database designer uses data models to facilitate the interaction among
designers, application programmers, and end users. In short, a good data model is a
communications device that helps eliminate (or at least substantially reduce) discrepancies between
the database design’s components and the real world data environment. The development of data
models, bolstered by powerful database design tools, has made it possible to substantially diminish
the database design error potential. (Review Section 2.1 in detail.)
2. What is a business rule, and what is its purpose in data modeling?
A business rule is a brief, precise, and unambigous description of a policy, procedure, or principle
within a specific organization’s environment. In a sense, business rules are misnamed: they apply to
any organization — a business, a government unit, a religious group, or a research laboratory; large
or small — that stores and uses data to generate information.
Business rules are derived from a description of operations. As its name implies, a description of
operations is a detailed narrative that describes the operational environment of an organization.
Such a description requires great precision and detail. If the description of operations is incorrect or
inomplete, the business rules derived from it will not reflect the real world data environment
accurately, thus leading to poorly defined data models, which lead to poor database designs. In turn,
poor database designs lead to poor applications, thus setting the stage for poor decision making –
which may ultimately lead to the demise of the organization.
Note especially that business rules help to create and enforce actions within that organization’s
environment. Business rules must be rendered in writing and updated to reflect any change in the
organization’s operational environment.
Properly written business rules are used to define entities, attributes, relationships, and constraints.
Because these components form the basis for a database design, the careful derivation and
definition of business rules is crucial to good database design.
3. How do you translate business rules into data model components?
As a general rule, a noun in a business rule will translate into an entity in the model, and a verb
(active or passive) associating nouns will translate into a relationship among the entities. For
example, the business rule “a customer may generate many invoices” contains two nouns (customer
and invoice) and a verb (“generate”) that associates them.
Chapter 2 Data Models
16
4. What does each of the following acronyms represent, and how is each one related to the birth
of the network data model?
a. CODASYL
Conference on Data Systems Languages. This group created a COBOL standard; furthermore,
CODASYL created the network model specifications.
b. SPARC
Standards Planning and Requirements Committee. This committee augmented the database
standards in 1975.
c. ANSI
American National Standards Institute. Adopted the CODASYL database specifications as a
standard database model.
d. DBTG
Database Task Group. This group defined an environment to facilitate database creation and data
manipulation.
5. What three languages were adopted by the DBTG to standardize the basic network data
model, and why was such standardization important to users and designers?
The three languages were:
1. The DDL (schema) constitutes the Data Definition Language for the database schema. The
DDL’s use enabled the database administrator to define the database schema, i.e., its over-all
blueprint.
2. The DDL (subschema) allows the definition of the specific database components that will
be used by each application.
3. The DML is the Data Manipulation Language that allows us to manipulate the database
contents.
Standardization is important to users and designers because it allows them to shift from one
commercial application to another with little trouble when they operate at the logical level.
Chapter 2 Data Models
17
6. Describe the basic features of the relational data model and discuss their importance to the end
user and the designer.
A relational database is a single data repository that provides both structural and data independence
while maintaining conceptual simplicity.
The relational database model is perceived by the user to be a collection of tables in which data are
stored. Each table resembles a matrix composed of row and columns. Tables are related to each other
by sharing a common value in one of their columns.
The relational model represents a breakthrough for users and designers because it lets them operate
in a simpler conceptual environment. End users find it easier to visualize their data as a collection of
data organized as a matrix. Designers find it easier to deal with conceptual data representation,
freeing them from the complexities associated with physical data representation.
7. Explain how the entity relationship (ER) model helped produce a more structured relational
database design environment.
An entity relationship model, also known as an ERM, helps identify the database’s main entities and
their relationships. Because the ERM components are graphically represented, their role is more
easily understood. Using the ER diagram, it’s easy to map the ERM to the relational database
model’s tables and attributes. This mapping process uses a series of well-defined steps to generate all
the required database structures. (This structures mapping approach is augmented by a process
known as normalization, which is covered in detail in Chapter 5, “Normalization of Database
Tables.”)
8. Use the scenario described by “A customer can make many payments, but each payment is
made by only one customer” as the basis for an entity relationship diagram (ERD)
representation.
This scenario yields the ERDs shown in Figure Q2.8. (Note the use of the PowerPoint Crow’s Foot
template. We will start using the Visio Professional–generated Crow’s Foot ERDs in Chapter 3, but
you can, of course, continue to use the template if you do not have access to Visio Professional.)
