Teorey J., Lightstone S., Nadeau T. Database Modeling and Design: Logical Design

Подождите немного. Документ загружается.

b. If a conflict in choice between two or more potential

entity clusters cannot be resolved (e.g., between two

constraint groupings at the same level of prece-

dence), leave these entity clusters ungrouped within

their functional area. If that functional area remains

cluttered with unresolved choices, define functional

subareas in which to group unresolved entities,

entity clusters, and their relationships.

3. Form higher-level entity clusters. Apply the grouping

operations recursively to any combination of elemen-

tary entities and entity clusters to form new levels of

entity clusters (higher-level objects). Resolve conflicts

using the same set of priority rules given in Step 2. Con-

tinue the grouping operations until all the entity

representations fit on a single page without undue com-

plexity. The root entity cluster is then defined.

4. Validate the cluster diagram. Check for consistency of

the interfaces (relationships) between objects at each

level of the diagram. Verify the meaning of each level

with the end users.

The result of one round of clustering is shown in

Figure 4.10,

where each of

the clusters is shown at level 2.

Summary

Conceptual data modeling, using either the ER or UML

approach, is particularly useful in the early steps of the data-

base life cycle, which involve requirements analysis and log-

ical design. These two steps are often done simultaneously,

particularly when requirements are determined from

interviews with end users and modeled in terms of data-to-

data relationships and process-to-data relationships. The

conceptual data modeling step (ER approach) involves the

classification of entities and attributes first, then identifica-

tion of generalization hierarchies and other abstractions,

and finally the definition of all relationships among entities.

Relationships may be binary (the most common), ternary,

and higher-level n-ary. Data modeling of individual

requirements typically involves creating a different view for

each end user’s requirements. Then the designer must inte-

grate those views into a global schema so that the entire

Chapter 4 REQUIREMENTS ANALYSIS AND CONCEPTUAL DATA MODELING 81

database is pictured as an integrated whole. This helps to

eliminate needless redundancy—such elimination is partic-

ularly important in logical design. Controlled redundancy

can be created later, at the physical design level, to enhance

database performance.

Finally, an entity cluster is a grouping of entities and

their corresponding relationships into a higher-level

abstract object. Clustering promotes the simplicity that is

vital for fast end user comprehension. In Chapter 5 we take

the global schema produced from the conceptual data

modeling and view integration steps and transform it into

Department

has

is-

managed-by

Skill

Project

Location

Project

skill-used

assigned-to

Employee

is-

married-to

manages

Technician

is-

headed-by

Division

Project

management

cluster

Manager

cluster

Secretary

cluster

Engineer

cluster

2.3 2.4 2.5

2.2

2.1

Division/

Department

Cluster

111

Figure 4.10 Clustering

results.

82 Chapter 4 REQUIREMENTS ANALYSIS AND CONCEPTUAL DATA MODELING

SQL tables. The SQL format is the end product of logical

design, which is still independent of any particular data-

base management system.

Tips and Insights for Database

Professionals

Tip 1. Clearly state the database requirements before

doing any ER/UML (conceptual) modeling. Describe

what goes into the database (requirements coverage),

what comes out of the database (queries), and flexibility

for future possible usage.

Tip 2. Best order of ER modeling—entities first, then

relationships, then attributes for entities, and finally

attributes for relationships when appropriate. You

can iterate on relationships and attributes.

Tip 3. Identify binary relationships first whenever pos-

sible. Only use ternary relationships as a last resort.

Avoid modeling n-ary relationships (n greater than 2),

whenever possible, by using equivalent binary relation-

ships. If you can’t avoid this, follow the strict rules of

functional dependencies to model appropriately.

Tip 4. Keep the conceptual model simple. Too much

detail wastes time and is harder to convey to your client.

Tip 5. Interact often with the end user (client), if pos-

sible, to make sure all assumptions you make are also

true for the client’s view of the database.

Tip 6. Entity clustering is optional. Only consider

it when the ER diagram is massive and there is a

need to increase the level of abstraction to more

clearlyconveythebasicconcepts (relationships) in

the database.

Literature Summary

Conceptual data modeling is defined in Tsichritzis

and Lochovsky (1982), Brodie, Mylopoulos, and Schmidt

(1984), Nijssen and Halpin (1989), and Batini, Ceri, and

Navathe (1992). Discussion of the requirements data

Chapter 4 REQUIREMENTS ANALYSIS AND CONCEPTUAL DATA MODELING 83

collection process can be found in Martin (1982), Teorey

and Fry (1982), and Yao (1985). View integration has prog-

ressed from a representation tool (Smith and Smith, 1977)

to heuristic algorithms (Batini, Lenzerini, and Navathe,

1986; Elmasri and Navathe, 2010). These algorithms are

typically interactive, allowing the database designer to

make decisions based on suggested alternative integration

actions. A variety of entity clustering models have been

defined that provide a useful foundation for the clustering

technique shown here (Feldman and Miller, 1986; Dittrich,

Gotthard, and Lockemann, 1986; Teorey et al., 1989).

84 Chapter 4 REQUIREMENTS ANALYSIS AND CONCEPTUAL DATA MODELING

TRANSFORMING THE

CONCEPTUAL DATA MODEL

TO SQL

CHAPTER OUTLINE

Transformation Rules and SQL Constructs 86

Binary Relationships 87

Binary Recursive Relationships 92

Ternary and n-ary Relationships 92

Generalization and Aggregation 95

Multiple

Relationships 99

Weak Entities 100

Transformation Steps 101

Entity Transformation 103

Many-to-Many Binary Relationship Transformation 105

Ternary Relationship Transformation 106

Example of ER-to-SQL Transformation 106

Summary 107

Tips and Insights for Database Professionals 108

Literature Summary 108

This chapter focuses on the database life cycle step that

of particular inter

est when designing relational

databases: transformation of the conceptual data model

to candidate tables and their definition in SQL (Step II.c).

There is a natural evolution from the entity–relationship

(ER) and Unified Modeling Language (UML) data models

to a relational schema. The evolution is so natural, in fact,

that it supports the contention that conceptual data

modeling is an effective early step in relational database

development. This contention has been proven to some

extent by the widespread commercialization and use of

software design tools that support not only conceptual

data modeling but also the automatic conversion of these

models to vendor-specific SQL table definitions and integ-

rity constraints.

Transformation Rules and SQL Constructs

Let’s first look at the ER and UML modeling constructs

in detail to see how the rules about transforming the con-

ceptual data model to SQL tables are defined and applied.

Our example is drawn from the company personnel and

project conceptual schemas illustrated in Figure 4.3 (see

Chapter 4).

The basic transformations can be described in terms of

the three types of tables they produce:

• SQL table with the same information content as the orig-

inal entity from which it is derived. This transformation

always occurs for entities with binary relationships

(associations) that are many-to-many, one-to-many on

the “one” (parent) side, or one-to-one on either side

(see Figures 5.1 and 5.2);

entities with binar

y recursive

relationships that are many-to-many (see Figures 5.3

and 5.4); and entities with any ternary or higher-degree

relationship (see Figures 5.5 and 5.6), or a generalization

hierarchy (see Figures 5.7 and 5.8).

• SQL table with the embedded foreign key of the parent

entity. This transformation

always occurs for entities with

binary relationships that are one-to-many for the entity

on the “many” (child) side (see Figures 5.1 and 5.2),

for

one-to-one relationships

for one of the entities (see

Figures 5.1 and 5.2), and for each entity with a binary

recursive relationship that is one-to-one or one-to-many

(see Figures 5.3 and 5.4). This is one of the two most

common ways design tools handle relationships, by

prompting the user to define a foreign key in the child

table that matches a primary key in the parent table.

• SQL

table derived from a relationship, containing the

foreign keys

of all the entities in the relationship. This

transformation always occurs for relationships that are

86 Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL

binary and many-to-many (see Figures 5.1f and 5.2f),

relationships that are binary recursive and many-to-

many, (see Figures 5.3c and 5.4c), and all relationships

that are of ternary or higher degree (see Figures 5.5 and

5.6). This is the other most common way design

tools handle relationships in the ER and UML models.

A many-to-many relationship can only be defined in

terms of a table that contains foreign keys that match

the primary keys of the two associated entities. This new

table may also contain attributes of the original relation-

ship—for example, a relationship “enrolled-in” between

two entities Student and Course might have the attributes

term and grade, which are associated with a particular

enrollment of a student in a particular course.

The following rules apply to handling SQL null values in

these

transformations:

• Nulls are allowed in an SQL table for foreign keys of

associated (referenced) optional entities.

• Nulls are not allowed in an SQL table for foreign keys of

associated (referenced) mandatory entities.

• Nulls are not allowed for any key in an SQL table

derived from a many-to-many relationship because only

complete row entries are meaningful in the table.

Figures 5.1 through 5.8 sho

w how

SQL-created table

statements can be derived from each type of ER or UML

model construct. Note that in each SQL table definition,

the term primary key represents the key of the table that

is to be used for indexing and searching for data.

Binary Relationships

A one-to-one binary relationship between two entities is

illustrated in Figure 5.1(a)–(c). Note that

the UML-equiva-

lent binary association is given in Figure 5.2(a)–(c).

When both entities are mandatory (Figure 5.1a), each

entity becomes a

table, and the key of either entity can

appear in the other entity’s table as a foreign key. One of

the entities in an optional relationship (see Department

in Figure 5.1b) should contain the foreign key of the other

entity in its transformed table. Employee, the other entity

in Figure 5.1(b), could also contain a foreign key (dept_no)

with nulls allowed, but this would require more storage

Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL 87

space because of the much greater number of Employee

entity instances than Department instances. When both

entities are optional (Figure 5.1c), either entity can contain

the embedded foreign key of the other entity, with nulls

allowed in the foreign keys.

Figure 5.1 ER model: one-to-one binary relationship between two entities:

(a) one-to-one, both entities mandatory, (b) one-to-one, one entity optional, one mandatory,

88 Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL

(d)

(e)

(f)

Figure 5.1, cont’d. (d) one-to-many, both entities mandatory, (e) one-to-many, one entity mandatory, one

optional, and (f) many-to-many, both entities optional.

Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL 89

The one-to-many relationship can be shown as either

mandatory or optional on the “many” side, without affect-

ing the transformation. On the “one” side it may be either

mandatory (Figure 5.1d)oroptional(Figure 5.1e). In all

cases the foreign key must appear on the “many” side,

Figure 5.2 UML: one-to-one binary relationship between two entities: (a) one-to-one, both entities

mandatory, (b) one-to-one, one entity optional, one mandatory, (c) one-to-one, both entities optional,

90 Chapter 5 TRANSFORMING THE CONCEPTUAL DATA MODEL TO SQL