Transcript ( Chpt 8 )

Chapter 8:
Object-Relational Modeling
Object-Oriented Systems Analysis and
Design
Joey F. George, Dinesh Batra, Joseph S.
Valacich, Jeffrey A. Hoffer
© Prentice Hall, 2007
8-1
Chapter Objectives
– Understand the relational model.
– Normalize data to the third normal form.
– Understand the object-oriented
extensions to the relational model.
– Realize the role of object relations in
systems analysis.
– Translate a conceptual data model into
object relations.
– Integrate object relations obtained from
different use cases.
Chapter 8
© Prentice Hall, 2007
8-2
Chapter 8
© Prentice Hall, 2007
8-3
Object-relational data modeling converts a conceptual
data model to a logical data model based on relational
and object-oriented technology.
Chapter 8
© Prentice Hall, 2007
8-4
Purposes of Object-Relational
Modeling
 Produce
database structures
 Create entity classes
 Enhance and finalize the attributes
in the data model
Chapter 8
© Prentice Hall, 2007
8-5
What Is a Relational Data Model?

Based on the concept of relations (tables of data)

Relationships established by matching primary
and foreign keys

Relational DBMSs (RDBMS) are the most
commonly used in industry today.

Many DBMS vendors have begun adding objectoriented features, creating an object-relational
model.
Chapter 8
© Prentice Hall, 2007
8-6
What Is a Relation?

A named, two-dimensional table with the
following properties:
– All cells contain are atomic (single-value) data.
– Entries in columns are from the same set of
values (domain range).
– Each row is unique (i.e. has a nonempty
primary key).
– Column order does not matter.
– Row order does not matter.
This is called First Normal Form (1NF)
Chapter 8
© Prentice Hall, 2007
8-7
Example relation: note uniqueness of rows
guaranteed by Emp_ID. Primary keys are underlined
Chapter 8
© Prentice Hall, 2007
8-8
What Is Normalization?

The process of converting complex data
structures into well-structured relations

Well-structured relation – a relation that
contains a minimum amount of
redundancies and allows rows to be
inserted, modified, and deleted without
introducing errors or inconsistencies
Chapter 8
© Prentice Hall, 2007
8-9
Consequences of Relations that
Are Not Well Structured

Insertion anomaly – adding new rows forces user
to create duplicate data

Update anomaly – changes in one row force
changes in other rows because of duplication

Deletion anomaly – deleting rows may cause a
loss of data that would be needed for other future
rows
Data integrity requires well-structured relations.
Chapter 8
© Prentice Hall, 2007
8-10
The Normal Forms
NF – all relations are in 1NF
 2nd NF – relations with no partial-key
functional dependencies
 3rd NF – relations with no transitive
functional dependencies
 Higher normal forms: 4th, Boyce Codd, 5th
– mainly theoretical, not needed for most
OOSAD problems
 1st
Main goal is to achieve 3NF for all relations.
Chapter 8
© Prentice Hall, 2007
8-11
What Is a Functional
Dependency?

The functional dependency of attribute B on
attribute A is represented by an arrow A  B, and
implies that every valid value of attribute A
uniquely determines the value of attribute B.

Determinant – the attribute on the left side of the
arrow

All primary keys are determinants
Chapter 8
© Prentice Hall, 2007
8-12
Second Normal Form (2NF)

1NF, plus no partial key functional dependencies

If the primary key is a composite key (composed
of more than one attribute) it is possible for an
attribute to be functionally dependent on only part
of the key

Avoid partial dependencies for 2NF
Chapter 8
© Prentice Hall, 2007
8-13
This table has a composite key (Emp_ID and Course)
Functional dependencies:
Emp_ID  Name, Dept, Salary
Emp_ID, Course  Date_Completed
Name, Dept, and Salary all have partial key
dependencies, causing duplication of data.
Chapter 8
© Prentice Hall, 2007
8-14
Solution:
Break the relation
into two separate
relations.
1:N relationship
linked by Emp_ID
No partial key
dependencies
Chapter 8
Well structured
© Prentice Hall, 2007
8-15
Third Normal Form (3NF)

2NF, plus no transitive functional
dependencies

Given three attributes in a relation A, B, C,
if A  B and B  C, this forms a transitive
functional dependency

Avoid transitive dependencies for 3NF
Chapter 8
© Prentice Hall, 2007
8-16
Here, Customer_ID  Salesperson, and Salesperson
 Region, cause a transitive dependency
Chapter 8
© Prentice Hall, 2007
8-17
Solution:
Break the relation
into two separate
relations.
1:N relationship
linked by SalesPerson
No transitive
dependencies
Well structured
Chapter 8
© Prentice Hall, 2007
8-18
Primary and Foreign Keys

Primary key – one or more attributes that together
form a unique identifier for rows in the relation

Foreign key – an attribute that appears as a nonprimary key attribute or as part of a primary key in
one relation and as a primary key attribute in another
relation

Relationship – rows in one relation are matched with
related rows in another relation through foreign keys
Chapter 8
© Prentice Hall, 2007
8-19
Referential Integrity

A rule that states that any foreign key value
(on the relation of the many side) MUST
match a primary key value in the relation of
the one side

No foreign key can contain a value that is
not present in a primary key in the other
relation
Chapter 8
© Prentice Hall, 2007
8-20
Object-Oriented Extensions to
Relational Modeling
– Generalization
– Multivalued attributes (OK to violate
–
–
–
–
–
atomicity requirement of 1NF)
Aggregation
Object identifiers
Pointers
Object-relational Data Model
Behaviors
Richer set of data types
Chapter 8
© Prentice Hall, 2007
8-21
Translating Conceptual Data
Model to Object-Relational Model
 Translate
classes
 Translate relationships
 Normalize object relations
 Merge object relations
Chapter 8
© Prentice Hall, 2007
8-22
Relational approach, forces
atomic attributes
Comparison of techniques
for translating
multivalued attributes
Object-relational approach, with
multivalued attribute
Chapter 8
© Prentice Hall, 2007
8-23
When constructing 1:N relationships, the foreign key
is added as an attribute to the relation on the N side.
Chapter 8
© Prentice Hall, 2007
8-24
Associative
class and
M:N
relationship
Chapter 8
© Prentice Hall, 2007
8-25
Points for Figure 8.11

Associative class is translated into a relation
whose primary key is a composite of the
primary keys of the other two classes.

M:N relationship between Work and Skill is
implemented as an extra relation UseSkills,
whose primary key is composed of the
primary keys of Work and Skill.
Chapter 8
© Prentice Hall, 2007
8-26
Unary 1:N relationship is
represented by a foreign
key managerID, which
matches with the empId
primary key of the same
relation.
Chapter 8
© Prentice Hall, 2007
8-27
Unary M:N relationship
is represented by a
separate relation
Prerequisite, whose
primary key is composed
of two primary key
values from the Course
relation.
Chapter 8
© Prentice Hall, 2007
8-28
Other Relationships

Aggregation and Composition – use same
rules that apply to association. Composition
is 1:N, aggregation is M:N

Generalization – no uniform standard
exists. Can make use of stereotypes to
annotate generalizations
Chapter 8
© Prentice Hall, 2007
8-29
Merging Object Relationships

Similar or redundant object relations can be
merged into one
 Example:
merge
Chapter 8
© Prentice Hall, 2007
8-30
Problems with Merging

Synonyms – two relations with different attribute
names referring to same meaning
– Solution – decide on a single standard name

Homonyms – two relations with same attribute names
referring to different meanings
– Solution – create new attribute for second meaning

Dependencies between non-key attributes after merge
– Solution – normalize the merged relation
Chapter 8
© Prentice Hall, 2007
8-31