Teorey J., Lightstone S., Nadeau T. Database Modeling and Design: Logical Design
Подождите немного. Документ загружается.
7
AN EXAMPLE OF LOGICAL
DATABASE DESIGN
CHAPTER OUTLINE
Requirements Specification 131
Design Problems 132
Logical Design 133
Summary 137
Tips and Insights for Database Professionals 137
The following example illustrates how to
proceed through
the requirements analysis and logical design steps of the
database life cycle, in a practical way, for a relationa l database.
Requirements Specification
The management of a large retail store would like a
database to keep track of sales activities. The requirements
analysis for this database led to the six entities and their
unique identifiers shown in Table 7.1.
The following assertions describe the data relationships:
• Each customer has one
job title, but different customers
may have the same job title.
• Each customer may place many orders, but only one
customer may place a particular order.
• Each department has many salespeople, but each sales-
person must work in only one department.
• Each department has many items for sale, but each item
is sold in only one department (“item” means item type,
like IBM PC).
• For each order, items ordered in different departments
must involve different salespeople, but all items ordered
131
within one department must be handled by exactly one
salesperson. In other words, for each order, each item
has exactly one salesperson; and for each order, each
department has exactly one salesperson.
For physical design (access methods, etc.) it is necessary
to determine what kind of processing needs to be done on
the data—that is, what are the queries and updates needed
to satisfy the user requirements, and what are their
frequencies? In addition, the requirements analysis should
determine if there will be substantial database growth (i.e.,
volumetrics); what time frame that growth will take place
over; and whether the frequency and type of queries and
updates will change, as well. Decay as well as growth
should be estimated, as each will have a significant effect
on the later stages of database design.
Design Problems
1. Using the information given and, in particular, the five
assertions, derive a conceptual data model and a set of
functional dependencies (FDs) that represent all the
known data relationships.
2. Transform the conceptual data model into a set of can-
didate SQL tables. List the tables, their primary keys,
and other attributes.
3. Find the minimum set of normalized (BCNF) tables that
are functionally equivalent to the candidate tables.
Table 7.1 Requirements Analysis Results
Entity Entity Key
Key Length (max)
in characters
Number of
Occurrences
Customer cust-no 6 80,000
Job job-no 24 80
Order order-no 9 200,000
Salesperson sales-id 20 150
Department dept-no 2 10
Item item-no 6 5000
132 Chapter 7 AN EXAMPLE OF LOGICAL DATABASE DESIGN
Logical Design
Our first step is to develop a conceptual data model
diagram and a set of FDs to correspond to each of the
assertions given. Figure 7.1 presents the diagram for
the entity–relationship (ER) model and Figure 7.2 shows
the equivalent diagram for the Unified Modeling Language
(UML). Normally, the conceptual data model is developed
without knowing all the FDs, but in this example the non-
key attributes are omitted so that the entire database
can be represented with only a few statements and FDs.
The results of this analysis, relative to each of the
assertions given, are shown in Table 7.2.
The candidate tables needed to represent the semantics
of this problem can be derived easily from the constructs
for entities and relationships. Primary keys and foreign
keys are explicitly defined.
Customer
1
1
1
order-
dept-sales
order-
item-sales
Department
hires
contains
Item
1
Salesperson
1
1
1
N
N
N
N
Order
places
N
N
N
has
Job
Figure 7.1 Conceptual data
model diagram for the ER
model.
Chapter 7 AN EXAMPLE OF LOGICAL DATABASE DESIGN 133
Customer
Job
has
places
Order
Department
order-
item-
sales
Item
contains
hires
order-dept-sales
Salesperson
1
1
1
1
1
1
1
*
*
*
*
*
*
*
Figure 7.2 Conceptual data
model diagram for UML.
Table 7.2 Results of the Analysis of the
Conceptual Data Model
ER Construct FDs
Customer(many): Job(one) cust-no -> job-title
Order(many): Customer(one) order-no -> cust-no
Salesperson(many): Department(one) sales-id -> dept-no
Item(many): Department(one) item-no -> dept-no
Order(many): Item(many): Salesperson(one) order-no, item-no -> sales-id
Order(many): Department(many): Salesperson(one) order-no, dept-no -> sales-id
134 Chapter 7 AN EXAMPLE OF LOGICAL DATABASE DESIGN
create table customer
(cust_no char(6),
job_title varchar(256),
primary key (cust_no),
foreign key (job_title) references job
on delete set null on update cascade);
create table job
(job_no char(6),
job_title varchar(256),
primary key (job_no));
create table order
(order_no char(9),
cust_no char(6) not null,
primary key (order_no),
foreign key (cust_no) references customer
on delete set null on update cascade);
create table salesperson
(sales_id char(10)
sales_name varchar(256),
dept_no char(2),
primary key (sales_id),
foreign key (dept_no) references department
on delete set null on update cascade);
create table department
(dept_no char(2),
dept_name varchar(256),
manager_name varchar(256),
primary key (dept_no));
create table item
(item_no char(6),
dept_no char(2),
primary key (item_no),
foreign key (dept_no) references department
on delete set null on update cascade);
create table order_item_sales
(order_no char(9),
item_no char(6),
sales_id varchar(256) not null,
Chapter 7 AN EXAMPLE OF LOGICAL DATABASE DESIGN 135
primary key (order_no, item_no),
foreign key (order_no) references order
on delete cascade on update cascade,
foreign key (item_no) references item
on delete cascade on update cascade,
foreign key (sales_id) references salesperson
on delete cascade on update cascade);
create table order_dept_sales
(order_no char(9),
dept_no char(2),
sales_id varchar(256) not null,
primary key (order_no, dept_no),
foreign key (order_no) references order
on delete cascade on update cascade,
foreign key (dept_no) references department
on delete cascade on update cascade,
foreign key (sales_id) references salesperson
on delete cascade on update cascade);
Note that it is often better to put foreign key definitions
in separate (alter) statements. This prevents the possibility
of getting circular definitions with ver y large schemas.
This process of decomposition and reduction of tables
moves us closer to a minimum set of normalized (BCNF)
tables, as shown in Table 7.3.
The reductions shown in this section have decreased
storage space and update costs
and have maintained the
normalization of BCNF (and thus 3NF). On the other hand,
Table 7.3 Decomposition and Reduction of Tables
Table Primary Key Likely Nonkeys
customer cust_no job_title, cust_name, cust_address
order order_no cust_no, item_no, date_of_purchase, price
salesperson sales_id dept_no, sales_name, phone_no
item item_no dept_no, color, model_no
order_item_sales order_no, item_no sales_id
order_dept_sales order_no, dept_no sales_id
136 Chapter 7 AN EXAMPLE OF LOGICAL DATABASE DESIGN
however, we have potentially higher retrieval cost—for
example, given the transaction “list all job_titles”—and
have increased the potential for loss of integrity because
we have eliminated simple tables with only key attributes.
Resolution of these trade-offs depends on your priorities
for your database.
The details of indexing are covered in the companion
book Physical Database Design (Lightstone et al., 2007).
However, during the logical design phase of defining SQL
tables, it makes sense to start considering where to create
indexes. At a minimum, all primary keys and all foreign
keys should be indexed. Indexes are relatively easy to
implement and store, and make a significant difference in
reducing the access time to stored data.
Summary
In this chapter we developed a global conceptual
schema and a set of SQL tables for a relational database,
given the requirements specification for a retail store data-
base. The example illustrates the database life cycle steps
of conceptual data modeling, global schema design, trans-
formation to SQL tables, and normalization of those tables.
It summarizes the techniques presented in Chapters 1–6.
Tips and Insights for Database
Professionals
Tip 1. Separate the logical and physical design steps to
satisfy different objectives.
Tip 2. Tune a database periodically after initial imple-
mentation is completed.
Chapter 7 AN EXAMPLE OF LOGICAL DATABASE DESIGN 137
8
OBJECT-RELATIONAL DESIGN
CHAPTER OUTLINE
Object Orientation 140
Classes and Instances 141
Inheritance 141
Identity 141
Encapsulation 142
Abstraction 142
Object-Oriented Databases 143
The Impedance Mismatch 143
Object-Relational Mapping 144
Persistent Programming Languages 146
Features of Object-Oriented Database Systems 149
Object-Relational Databases 153
User-Defined Functions and Abstract Data Types 153
An Evaluation of Object-Relational Systems 154
Design Considerations 155
Summary 158
Tips and Insights for Database Professionals 159
Literature Summary 159
Object orientation is a standard feature of many modern
programming
languages and softwar
e systems. This notion
has also been incorporated into data management sys-
tems. In this chapter we will study the interplay of object
orientation and databases. Programming languages in
which application software is written play a large role in
this interplay, and will be discussed. This will also lead
naturally into a discussion of how to store XML data in a
relational database (see Chapter 9).
139
This chapter begins with an overview describing
object orientation. The following two sections continue
with a discussion of object-oriented and object-relational
databases.
Object Orientation
The world is modeled as a collection of objects that
interact with one another. Correspondingly, software is
also designed as a collection of interacting objects. A soft-
ware object is a logical unit: a bundle of data and pro-
cedures that belong together. Frequently, a software
object represents a real-world object.
Figure 8.1 shows a real-world object and its representa-
tion as a collection of software objects. Observe that many
components of the real-world object have been pulled out
and represented as objects themselves, which indeed they
are. All edges in this figure represent inclusion links. What
unit of information comprises a software object is a design
decision. In this example, the designer has chosen to
tire
chassis seats doors
tire
body
engine
car
(a)
(b)
hp
fuel
no-cyl
mfr
shape
tire
tire
Figure 8.1 (a) A real-world object and (b) its representation as a
collection of software objects.
140 Chapter 8 OBJECT-RELATIONAL DESIGN
represent each tire as a separate object, but grouped all four
doors together as a single object. Objects have attributes,
which are shown only for the engine object in the figure.
There are several notions central to most object-
oriented systems. N
ote that there is no single agreed upon
hard definition of an object-oriented system or database;
rather, there is a list of properties, most of which one
would expect an object-oriented system to have. We briefly
describe some central notions below.
Classes and Instances
Many objects are similar. Similar objects are grouped
together into a class. Individual objects in the class are
called instances of the class. From a programming perspec-
tive, data structures and methods are associated with the
class, and are part of the class definition. From a database
perspective, one can think of each relation as a class, and
each tuple (or record) in the relation as an instance of
the relation class.
Inheritance
Often, there are related classes that share some properties
but not all. For example, a vehicle may be a car, truck, or
motorcycle, each of which has some unique properties (for
example, the number of axles is a variable that matters for
trucks, is unnecessary for cars (which always have two),
and is meaningless for motorcycles). Yet, all vehicles share
many common properties, such as owner, model year, brand
name, registration number, etc. In such situations, inheri-
tance is useful, as we have already seen with the generali-
zation hierarchy in the context of entity-relationship (ER)
design in Chapter 2. Inheritance is a central concept in
object-oriented systems.
Identity
A crucial property of object-oriented systems is the
notion of object identity. Over time, attributes of an object
may change value; however, its identity remains the same.
Think of a person—over their lifetime there are likely to be
several changes of address, phone number, and so on;
there may even be changes in name; however, we know
Chapter 8 OBJECT-RELATIONAL DESIGN 141