Conceptual Data Modeling Chapter 9

1
Conceptual Data ModelingChapter 9

• The Entity-Relationship Model/Diagram

2
Agenda

• Conceptual Data Modelingwhat and why?
• Terminology
• How to
• Lots of examples

3
Learning Objectives

• After this chapter, you should be able to
• Define key data-modeling terms (entity type,
  attribute, multivalued attribute, relationship,
  degree, cardinality, associative entity, etc.)
• Draw an E-R diagram to represent common scenarios
• Explain the role of conceptual data modeling in
  the overall analysis and design of an information
  system
• Distinguish between types of relationships
  (unary, binary, ternary, etc.)
• Relate data modeling the process and logic
  modeling as different views of describing an
  information system

4
For Starters
1. What do we mean by conceptual data modeling?
2. Why is it important?
3. How is it different than other views of the
business system such as process models (DFDs)?
5
Basic E-R Notation
6
Figure 9-3 Sample Conceptual Data Model
7
E-R Model Constructs

• Entity - person, place, object, event, concept
• Entity Type versus Entity Instance
• Entity Type versus System Input, Output, or User
• Consider the diagram on next page
• Treasurer is the person entering data.
• Gives-to and Summarizes are business
  activities, not relationships between entities.

8
Example of inappropriate entities
(a) System user (Treasurer) and output (Expense
Report) shown as entities
9
Corrected E-R model with only the necessary
entities
10
E-R Model Constructs

• Strong versus Weak Entity Type
• Independent versus dependent entity.
• Identifying owner.
• Identifying relationship.
• Weak entity identifier is its partial identifier
  combined with that of its owner.

11
Example of a weak entity
12
E-R Model Constructs

• Attribute - property or characteristic of an
  entity type
• Simple versus Composite Attribute
• Single-Valued versus Multivalued Attribute
• Stored versus Derived Attributes

13
A composite attribute
14
Entity with a multivalued attribute (Skill) and
derived attribute (Years_Employed)
15
E-R Model Constructs

• Identifier or Key - An attribute (or combination
  of attributes) that uniquely identifies
  individual instances of an entity type.
• Simple Key versus Composite Key
• Candidate Key

16
Simple and composite key attributes
Here Simple key attribute
17
And now Composite key attribute
18
Relationships

• Relationship Type versus Instance
• Degree of a Relationship - number of entity types
  that participate in it.
• Unary (or Recursive) Relationship

19
Relationship types and instances
(a) Relationship type (Completes)
20
as opposed to relationship instances (here)
21
Relationships

• Types
• Unary Relationship (previously mentioned)
• Binary Relationship
• Ternary Relationship
• Attributes of Relationships
• Many-to-Many
• Associative Entities (Gerunds)
• All relationships involved are many
• Result has independent meaning
• Gerund has one or more non-key attributes

22
Unary Relationship Example
23
Binary Relationship Examples
24
(c ) Ternary relationships
25
A relationship with an attribute
as an associative entity
26
Relationships

• Cardinality Constraints - the number of instances
  of one entity that can or must be associated with
  each instance of another entity
• Minimum Cardinality
• If zero, then optional.
• Maximum Cardinality
• Mandatory One - when min max both 1.

27
Introducing cardinality constraints
(a) Basic relationship
28
(b) Relationship with cardinality constraints
29
Examples of cardinality constraints
(a) Mandatory cardinalities
30
(b) One optional, one mandatory cardinality
31
Cardinality constraints in a ternary relationship
32
Other Special Case Types of Relationships

• Modeling Time-Dependent Data
• Time Stamps
• Multiple Relationship - more than one
  relationship between the same entity types

33
Simple example of time stamping
34
(b) Why two separate relationships here?
35
Supertypes and Subtypes

• Subtype a subrouping of the entities in an
  entity type that shares common attributes or
  relationships distinct from other subtypes
• Supertype a generic entity type that has a
  relationship with one or more subtype

36
Figure 9-19 Example of supertypes/subtypes
37
Problems/Exercises

• PEs from Chapter 9
• 4
• 5
• 8
• 16

38
PE 4

• A software training program is divided into
  training modules, and each module is described by
  module name and the approximate practice time.
  Each module sometimes has prerequisite modules.
  Model this situation of training program with an
  E-R diagram.

39
PE 5

• Each semester, each student must be assigned an
  advisor who counsels students about degree
  requirements and helps students register for
  classes. Students must register for classes with
  the help of an advisor, but if their assigned
  advisor is not available, they may register with
  any advisor. We must keep track of students,
  their assigned advisor, and with whom the student
  registered for the current term. Represent this
  situation of students and advisors with an E-R
  diagram.
• What if some students are also advisors? Hint
  use supertypes/subtypes.

40
PE 8

• How many PROJECTs can an employee work on?
• What is the degree of the Includes relationship?
• Are there any associative entities on this
  diagram?
• How else could the attribute Skill be modeled?
• Is it possible to attach any attributes to the
  Includes relationship?
• Could TASK be modeled as an associative entity?

41
Figure 9-23
Alternate Representation
42
PE 16 Draw ERD