Business Database Management Systems

1
Business Database Management Systems

• Class 7
• Conceptual Modeling III
• DSC 544/444 ?Fall 2006

2
Reminders

• Test 1. Wednesday, Oct. 25. Weight 25
• Conceptual modeling concepts
• Understanding/drawing E-R diagram notation
• Translating from conceptual to tables
• we will continue to discuss these through next
  Monday. After the midterm, we will discuss
  normalization and queries (emphasized on Test 2)
• Upcoming Presentations
• Today NewEgg.com
• October 18 CostCo.com
• October 23 AllMusic.com
• October 30 Expedia.com

3
Agenda

• Complete exercise from last time
• 4 conceptual modeling topics
• Recursive relationships
• N-ary relationships
• Generalization hierarchies
• The Fan Trap problems
• Team presentation

4
Exercise from last time

• Step 1 draw an E-R model, based on this earlier
  example, but eliminate the remaining MN
  relationship
• Step 2 create table structures that reflect your
  new E-R diagram

Goes on
To
M
Member
SkiArea
Trip
1
M
M
5
Recursive Relationships

• Sometimes entity instances of the same entity are
  related. We call this a recursive relationship.
• Technically, a recursive relationship is
  categorized as a unary rather than binary
  relationship, because only 1 entity is involved.
• However, we can think of a recursive relationship
  as one of our familiar binary relationships (11,
  1M, or MN), where the two entities being
  related are identical copies of each other.
• Examples
• Member carpools with another member
• Member mentors another member

6
Visualizing Recursive Relationships

• We visualize this concept by drawing a
  relationship diamond that connects with two lines
  connecting to a single entity.
• Because these relationships are potentially
  confusing, we indicate relationship roles and
  use arrowheads to denote the corresponding
  relationship direction.
• Example assume that we wish to keep track of
  which head referees our other referees learned
  from.

Trained
head
Referee
This shows that eachstudent referee wouldbe
trained by a singlemaster referee.
1
M
student
7
Agenda

• Complete exercise from last time
• 4 conceptual modeling topics
• Recursive relationships
• N-ary relationships
• Generalization hierarchies
• The Fan Trap problems
• Team presentation

8
Binary vs. Ternary Relationships

• The relationships we have discussed so far are
  called relationships with binary degree.
• A binary relationship reflects a simultaneous
  connection between two entity instances
• A ternary relationship implies a simultaneous
  connection between three entity instances.
• More generally, an n-ary relationship implies a
  simultaneous connection between n entity
  instances (with n greater than 3)

9
Visualizing an n-ary Relationship

• Ternary
• Use the standard relationship (diamond) symbol,
  but extend 3 lines from the diamond rather than
  two.
• N-ary
• Use the standard relationship (diamond) symbol,
  but extend n (in this case, four) lines from the
  diamond rather than two.

Game
Team
Referee
Game
Team
Referee
Note relationship connectivitylabels are
typically omitted when the degree is higher than
binary
Field
10
Modeling an n-ary Relationship as an Entity

• As we saw with Many-to-Many binary relationships,
  in our conceptual model we can express a complex
  relationship by defining a new (associative)
  entity
• Example rather than think of a game as a
  relationship that occurs between teams, a
  referee, and a field, we can conceptualize a game
  as a distinct entity. This conceptualization
  implies the following E-R diagram
• The two approaches will ultimately imply the same
  set of relational tables, so both are correct.

has
assigned
M
Team
Referee
Game
1
M
2..2
M
M
for
This notation is referred to as cardinality
information, which gives specific detail
regarding the min/max number of instances being
related.
1
Field
11
Agenda

• Complete exercise from last time
• 4 conceptual modeling topics
• Recursive relationships
• N-ary relationships
• Generalization hierarchies
• The Fan Trap problems
• Team presentation

12
Generalization Hierarchies

• You will sometimes want to store information
  about things that are fundamentally the same type
  of thing, but with distinguishing attributes.
• Example skis, boots, and bindings are all
  Equipment things, but they also have
  distinguishing characteristics.
• We could simply create the following Equipment
  entity

Brand
EID
Equipment
Model
Date Acquired
Ski Length
Binding Min Weight
Ski Tip Width
Binding Max Weight
Ski Waist Width
Boot Gender
Ski Tail Width
Boot Size
13
Generalization Hierarchies

• What is wrong with that approach?
• Another poor approach would be to simply create
  entirely separate entities for skis, boots, and
  bindings
• This approach would eliminate blanks, but causes
  unnecessary relationship duplication

Skis
rents
rents
Boots
Member
rents
Bindings
14
Generalization Hierarchies

• The preferred approach create a general entity
  called Equipment and additional specialized
  entities for each subtype
• The general entity (here, Equipment) is called
  the superclass
• Each specialized subtype is called a subclass
• All of these entities share the same identifier!

Brand
EID
Equipment
Model
Date Acquired
Length
Tip Width
Gender
Min Weight
Binding
Boot
Ski
Waist Width
Size
Max Weight
Tail Width
15
Two Basic Types of Generalization Hierarchies

• Disjoint (non-overlapping) hierarchy an entity
  instance can appear at most in 1 subclass.
• Overlapping hierarchy an entity instance can
  appear in 2 or more subclasses.
• Is this a disjoint or overlapping generalization
  hierarchy?

Equipment
Disjoint
Binding
Boot
Ski
16
Generalization Hierarchies

• Relationships sometimes apply to specific
  subclass entities
• Example to add additional detail and usefulness
  to our information architecture for the ski club,
  we could keep track of which pair of skis each
  binding is mounted to.

People dont bother showingthe 1/M
symbolshere. Why arent they necessary?
Equipment
Binding
Boot
Ski
1
1
mounted
17
Agenda

• Complete exercise from last time
• 4 conceptual modeling topics
• Recursive relationships
• N-ary relationships
• Generalization hierarchies
• The Fan Trap problems
• Team presentation

18
The Fan Trap Problems

• Suppose there are multiple divisions in a
  company, each divided into departments
• Every employee works for a division (and is
  assigned to a particular department in that
  division)
• What's wrong with the diagram below?

dividedinto
worksfor
1
M
1
M
Department
Employee
Division
19
Understanding this Fan Trap Problem

• Can we determine which department an employee is
  assigned to?

20
The Reverse Fan Trap

• Suppose a company has multiple divisions
• Every employee is employed by a division, and
  assigned to a particular department (in that
  division)
• What's wrong with this diagram?

worksfor
employs
M
1
M
1
Department
Division
Employee
21
Understanding the Reverse Fan Trap

• Does it make sense to allow two employees in the
  same department to be assigned to different
  divisions

How can we resolve the fan traps?
22
Avoiding the Fan Traps

• Keep the fans expanding in one direction

partof
worksfor
1
M
M
1
Division
Employee
Department


23
Summary

• In this set of notes we investigated three new
  modeling constructs recursive relationships,
  n-ary relationships, and generalization
  hierarchies
• In essence, these are are applications of what we
  already knew.
• A recursive relationship is nothing more than a
  binary relational (11, 1M, or MN) between an
  entity and itself
• N-ary relationships can be modeled by creating an
  associative entity, just as we did previously
  with binary MN relationships
• Generalization hierarchies are implemented as a
  set of entities with 11 relationships and the
  same identifier
• Finally, be careful of the fan traps that can
  result from 1M relationships!