Ch5: ER Diagrams - Part 2

1
Ch5 ER Diagrams - Part 2

• Much of the material presented in these slides
  was developed by Dr. Ramon Lawrence at the
  University of Iowa

2
Topics

• Min. and Max. Cardinality
• Understanding Relationships
• M-way relationships
• Equivalence between M-N and 1-M relationships
• Identification dependency
• Generalization Hierarchies
• Modeling Guidelines

3
Crows Foot Cardinality Notation
4
Classification of Cardinalities

• Maximum cardinality based
• 1-1
• A maximum cardinality of 1 is a functional
  relationship
• 1-M
• a functional relationship in one direction
• M-N

5
One-to-One (Functional) Relationship
Relationship explanation A department may have
only one manager. A manager (employee) may manage
only one department.
6
One-to-Many (Functional) Relationship
Relationship explanation A project may be
associated with at most one department. A
department may have multiple projects.
7
Many-to-Many Relationship
8
Classification of Cardinalities

• Minimum cardinality based
• Mandatory existence dependent
• Optional

9
Existence Dependency

• Participation determines whether all or only some
  entity instances participate in a relationship.
• Participation can either be optional or
  mandatory.
• If an entity's participation in a relationship is
  mandatory (also called total participation), then
  the entity's existence depends on the
  relationship.
• Called an existence dependency.

10
One-to-Many Mandatory Participation (Existence
Dependent) Relationship
Relationship explanation A project must be
associated with one department. A department may
have zero or more projects.
11
Many-to-Many Mandatory Participation (Existence
Dependent) Relationship
12
Summary of Cardinalities
13
Understanding Relationships

• M-way relationships
• Equivalence between M-N and 1-M relationships
• Identification dependency

14
Cardinality of Non-Binary Relationships

• The cardinality in a complex relationship of an
  entity type is the number of possible occurrences
  of that entity-type in the n-ary relationship
  when the other (n-1) values are fixed.
• Example A supplier may provide zero or more
  parts to a project. A project may have zero or
  more suppliers, and each supplier may provide to
  the project zero or more parts.

15
Challenges with Cardinality of Non-Binary
Relationships

• The minimum cardinality for N-ary relationships
  (i.e., participation) is ambiguous. Example
• Constraint A project has at least 1 supplier per
  part.
• Initial idea Cardinality 1.. beside Supplier
  should force there to be a Supplier for each
  Part/Project combination.
• Problem Two different interpretations of
  Part/Project combination results in unforeseen
  consequences
• Actual tuple All entities must always
  participate (as have actual tuples) so minimum
  values for all entities would be 1.
• Potential tuple 1 beside Supplier implies always
  a Supplier for every Part/Project combination.
  Not true in practice.
• Bottom line We will avoid problem of specifying
  participation constraints for N-ary
  relationships. One way to avoid it is convert a
  relationship into an entity with N binary
  relationships.

16
Associative Entity Types for M-way Relationships
17
Practice Question

• Consider the university database developed
  before. Write cardinalities into the ER diagram
  given that
• A department must offer at least 2 courses and no
  more than 20 courses. Courses are offered by only
  one department.
• A course may have multiple sections, but always
  has at least one section.
• A student may enroll for courses (but does not
  have to).
• A professor may be in multiple departments (at
  least 1), and a department must have at least 3
  professors.
• A section is taught by at least one professor,
  but may be taught by more than one. A professor
  does not have to teach.
• A student may only enroll in a course (and in a
  single section) once. (Not keeping track of
  history of student enrollments.)

18
Strong and Weak Entity Types

• A strong entity type is an entity type whose
  existence is not dependent on another entity
  type.
• A strong entity type always has a primary key of
  its own attributes that uniquely identifies its
  instances.
• A weak entity type is an entity type whose
  existence is dependent on another entity type
  i.e., it is Identification Dependent
• A weak entity type does not have a set of its own
  attributes that uniquely identifies its
  instances.
• A common example of strong and weak entity types
  are employees and their dependents
• An employee is a strong entity because it has an
  employee number to identify its instances.
• A dependent (child) is a weak entity because the
  database does not store a key for each child, but
  rather they are identified by the parent's
  employee number and their name.

19
Identification Dependency in Crows Foot Notation
Partial Key
20
Superclasses and Subclasses

• Java code
• public class SavingsAccount extends BankAccount
• UML class diagram

21
When to use Generalization Hierarchies?

• It is important to emphasize that most database
  projects do not need the object-oriented modeling
  features of Generalization Hierarchies.
• Remember the goal of conceptual modeling is to
  produce a model that is simple and easy to
  understand.
• Do not introduce complicated subclass/superclass
  relationships if they are not needed.
• Only use Generalization Hierarchies constructs if
  they offer a significant advantage over regular
  ER modeling.
• Generalization Hierarchies are especially useful
  when the domain being modeled is object-oriented
  in nature and the use of inheritance reduces the
  complexity of the design.
• Most business databases have minimal
  object-oriented data.

22
When to use Generalization Hierarchies?Using
Attribute Inheritance

• Note that the title attribute indicates what job
  the employee does at the company. Consider if
  each job title had its own unique information
  that we would want to record such as
• EE, PR - programming language used (lang), DB
  used (db)
• SA, ME - MBA? (MBA), bonus

23
Using Attribute Inheritance

• We could represent all these attributes in a
  single relation

Note the wasted space as attributes that do not
apply to a particular subclass are NULL.
24
Using Attribute Inheritance

• A better solution would be to make two subclasses
  of Employee called Developer and Manager

25
Specialization Example
26
Using Attribute Inheritance The Result
27
Generalization and Specialization

• Subclasses and superclasses are created by using
  either generalization or specialization.
• Specialization is the process of creating more
  specialized subclasses of an existing superclass.
• Top-down process Start with a general class and
  then subdivide it into more specialized classes.
• The specialized classes may contain their own
  attributes. Attributes common to all subclasses
  remain in the superclass.
• Generalization is the process of creating a more
  general superclass from existing subclasses.
• Bottom-up process Start with specialized classes
  and try to determine a general class that
  contains the attributes common to all of them.

28
Generalization Example
29
Constraints on Generalization and Specialization

• There are two types of constraints associated
  with generalization and specialization
• Completeness constraint - determines if every
  member in a superclass must participate as a
  member of one of its subclasses.
• It may be optional for a superclass member to be
  a member of one of its subclasses, or it may be
  mandatory that a superclass member be a member of
  one of its subclasses.
• Manditory membership in one of the subclasses is
  denoted by a C (for completeness)
• Disjoint constraint - determines if a member of a
  superclass can be a member of one or more than
  one of its subclasses.
• If a superclass object may be a member of only
  one of its subclasses this is denoted by a D
  (subclasses are disjoint).

30
Constraints Example
Disjoint constraint (cannot be both a developer
and a manager)
Completeness constraint (must be developer or
manager)
D,C
31
Constraints Question
Note What is the completeness and the disjoint
constraints for superclass Employee (with
subclasses Manager and Supervisor) given these
instances?
32
Problems with ER Models

• When modeling a Universe of Discourse using ER
  models, there are several challenges that you
  have.
• The first, basic challenge is knowing when to
  model a concept as an entity, a relationship, or
  an attribute.
• In general
• Entities are nouns.
• You should be able to identify a set of key
  attributes for an entity.
• Attributes are properties and may be nouns or
  adjectives.
• Use an attribute if it relates to one entity and
  does not have its own key.
• Use an entity if the concept may be shared by
  entities and has a key.
• Relationships should generally be binary.
• Note that non-binary relationships can be modeled
  as an entity instead.

33
Modeling Traps

• There are several different "modeling traps"
  (called connection traps) that you can fall into
  when designing your ER model.
• Two connection traps that we will look at are
• Fan traps
• Chasm traps

34
Fan Trap

• A fan trap is when a model represents a
  relationship between entity types, but the
  pathway between certain entity instances is
  ambiguous.
• Often occurs when two or more one-to-many
  relationships fan out (come from) the same entity
  type.
• Example A department has multiple employees, a
  department has multiple projects, and each
  project has multiple employees.

Now answer the question, which projects does
employee E3 work on?
35
Fan Trap Example
Which projects does employee E3 work on?
36
Chasm Traps

• A chasm trap occurs when a model suggests that a
  relationship between entity types should be
  present, but the relationship does not actually
  exist. (missing relationship)
• May occur when there is a path of optional
  relationships between entities.
• Example A department has multiple employees, a
  department has multiple projects, and each
  project has multiple employees.

37
Chasm Trap Example
Which department is employee E8 in? What are the
employees of department D4?
38
Good Design Practices

• When designing ER models, there are several
  things that you should consider
• Avoid redundancy - do not store the same fact
  more than once in the model.
• Do not use an entity when you can use an
  attribute instead.
• Limit the use of weak entity sets.

39
Good Design Practices Avoiding Redundancy Example
40
Good Design Practices Entity versus Attribute
Example

• An entity should only be used if one of these
  conditions is true
• The entity set should contain at least one
  non-key attribute.
• It is the many side in a many-to-one
  relationship.
• Example Projects have a department. A department
  only has a number.

41
Good Design Practices Weak Entity Sets

• Avoid the use of weak entity sets in modeling.
  Although at first glance there are very few
  natural keys based on the properties of objects
  (name, age, etc.), there are many good human-made
  keys that can be used (SSN, student, etc.)
• Whenever possible use a global, human-made key
  instead of a weak entity. Note that sometimes a
  weak entity is required if no such global key
  exists.
• For example, a database storing the students of
  multiple universities could not use a single
  student as a key as it is unlikely that
  universities could agree on a global
  numberingsystem. Rather, student becomes a weak
  entity with partial key student and identifying
  entity the university the student attends.

42
Simplifications

• Sometimes it is necessary to simplify some of the
  relationships
• Crows foot database design tool only supports
  binary relationships.
• Two common simplifications
• Many-to-many relationship Two one-to-many
  relationships
• Higher order relationships binary
  relationships

43
Many-to-Many Relationship Simplification

• A many-to-many relationship can be converted into
  one entity with two 1N relationships between the
  new entity and the original entities
  participating in the relationship.

Original
Simplified
44
Simplifying Higher Degree Relationships

• A non-binary relationship can be converted into a
  weak entity with identifying relationships to the
  original entity types.