Modeling Data in the Organization
Modeling Relationships
Data Modeling Part I
© 2013 Pearson Education
1
Modern Database Management
11th Edition, International Edition
Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi
Chapter 2
© 2013 Pearson Education
Different modeling software tools may have different notation for the same constructs.
2
Chapter 2
© 2013 Pearson Education
Notation style 1— Using Erassistant
Chapter 2
© 2013 Pearson Education
3
Notation style 2— lecture (textbook )
4
Chapter 2
© 2013 Pearson Education
One quick introduction to ERassistant
5
Chapter 2
© 2013 Pearson Education
Modeling Relationships
Relationship Types vs. Relationship Instances
The relationship type is modeled as lines between entity types…the instance is between specific entity instances
Relationships can have attributes
These describe features pertaining to the association between the entities in the relationship
Two entities can have more than one type of relationship between them (multiple relationships)
Associative Entity–combination of relationship and entity
6
Chapter 2
© 2013 Pearson Education
7
Figure 2-10 Relationship types and instances
a) Relationship type (Completes)
b) Relationship instances
7
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
17
Degree of Relationships
Degree of a relationship is the number of entity types that participate in it
Unary Relationship
Binary Relationship
Ternary Relationship
8
Chapter 2
© 2013 Pearson Education
16
9
Degree of relationships – from Figure 2-2
Entities of two different types related to each other
Entities of three different types related to each other
One entity related to another of the same entity type
9
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
8
10
Figure 2-12 Examples of relationships of different degrees
a) Unary relationships
10
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
22
Another example of Self-Referencing Relationships
Chapter 2
© 2013 Pearson Education
11
12
Figure 2-12 Examples of relationships of different degrees (cont.)
b) Binary relationships
12
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
22
13
Figure 2-12 Examples of relationships of different degrees (cont.)
c) Ternary relationship
Note: a relationship can have attributes of its own
13
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
22
Cardinality of Relationships
One-to-One
Each entity in the relationship will have exactly one related entity
One-to-Many
An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity
Many-to-Many
Entities on both sides of the relationship can have many related entities on the other side
14
Chapter 2
© 2013 Pearson Education
Cardinality Constraints
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
If one or more, then mandatory
Maximum Cardinality
The maximum number
15
Chapter 2
© 2013 Pearson Education
29
16
Figure 2-17 Examples of cardinality constraints
a) Mandatory cardinalities
A patient must have recorded at least one history, and can have many
A patient history is recorded for one and only one patient
16
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
1
17
Figure 2-17 Examples of cardinality constraints (cont.)
b) One optional, one mandatory
An employee can be assigned to any number of projects, or may not be assigned to any at all
A project must be assigned to at least one employee, and may be assigned to many
17
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
1
18
Figure 2-17 Examples of cardinality constraints (cont.)
c) Optional cardinalities
A person is married to at most one other person, or may not be married at all
18
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
1
Comparison to Oracle Notation
Oracle Relational Model Diagram
Model Diagram drawn using ERAssistant
Chapter 2
© 2013 Pearson Education
19
20
Entities can be related to one another in more than one way
Figure 2-21 Examples of multiple relationships
a) Employees and departments
20
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
40
21
Figure 2-21 Examples of multiple relationships (cont.)
b) Professors and courses (fixed lower limit constraint)
Here, min cardinality constraint is 2. At least two professors must be qualified to teach each course. Each professor must be qualified to teach at least one course.
21
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
40
22
Figure 2-15a and 2-15b Multivalued attributes can be represented as relationships
simple
composite
22
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
Strong vs. Weak Entities, and
Identifying Relationships
Strong entity
exists independently of other types of entities
has its own unique identifier
identifier underlined with single line
Weak entity
dependent on a strong entity (identifying owner)…cannot exist on its own
does not have a unique identifier (only a partial identifier)
entity box and partial identifier have double lines
Identifying relationship
links strong entities to weak entities
23
Chapter 2
© 2013 Pearson Education
24
Strong entity
Weak entity
Figure 2-5 Example of a weak identity and its identifying relationship
Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
Chapter 2
© 2013 Pearson Education
Another example with Erassistant
Chapter 2
© 2013 Pearson Education
25
M-N Relationship Equivalency Rule
Replace M-N relationship
Associative entity type
Two identifying 1-M relationships
M-N relationship versus associative entity type
Largely preference
Associative entity type is more flexible in some situations
Chapter 2
© 2013 Pearson Education
26
M-N Relationships with Attributes
Chapter 2
© 2013 Pearson Education
27
M-N Relationships with Attributes (II)
Chapter 2
© 2013 Pearson Education
28
Associative Entities
An entity–has attributes
A relationship–links entities together
When should a relationship with attributes instead be an associative entity?
All relationships for the associative entity should be many
The associative entity could have meaning independent of the other entities
The associative entity preferably has a unique identifier, and should also have other attributes
The associative entity may participate in other relationships other than the entities of the associated relationship
Ternary relationships should be converted to associative entities
29
Chapter 2
© 2013 Pearson Education
30
Figure 2-11a A binary relationship with an attribute
Here, the date completed attribute pertains specifically to the employee’s completion of a course…it is an attribute of the relationship.
30
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
20
31
Figure 2-11b An associative entity (CERTIFICATE)
Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right.
Note that the many-to-many cardinality between entities in Figure 2-11a has been replaced by two one-to-many relationships with the associative entity.
31
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
21
Another example
Chapter 2
© 2013 Pearson Education
33
Cardinality constraints and associate entity
in a ternary relationship
33
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
36
Another example
Chapter 2
© 2013 Pearson Education
34
35
Figure 2-22
Data model for Pine Valley Furniture Company in Microsoft Visio notation
Different modeling software tools may have different notation for the same constructs.
35
Chapter 2
© 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
36
Copyright © 2013 Pearson Education
Chapter 2
© 2013 Pearson Education
Entity Type
symbol
Relationship
symbol
Primary Key
Attributes
Relationship
name
Entity Type
name
CourseNo
CrsDesc
CrsUnits
Course
OfferNo
OffLocation
OffTime
Offering
Has
Entity name
Name
Name
OfferNo
OffLocation
Offering
Registers
StdNo
StdName
Student
OfferNo
OffLocation
Offering
EnrGrade
Enrollment
RegistersGrants
Entity name
Name
Name
Supervises
b) course prerequisites
a) manager-subordinate
CourseNo
CrsDesc
Course
PrereqTo
FacNo
FacName
Faculty
FacNo
FacName
Faculty
a) manager-subordinate
Supervises
b) course prerequisites
CourseNo
CrsDesc
Course
PrereqTo
CourseNo
CrsDesc
CrsUnits
Course
OfferNo
OffLocation
OffTime
Offering
Has
ERD (Crow’s Foot)
BldgID
BldgName
BldgLocation
Building
RoomNo
RoomCapacity
Room
Contains
Identification Dependency Symbols:
Ÿ
Solid relationship line for identifying
relationships
Ÿ
Diagonal lines in the corners denote
weak entities.
Entity name
Name
Name
OfferNo
OffLocation
OffTime
Offering
EnrollsIn
EnrGrade
attribute of relationship
StdNo
StdName
Student
StdNo
StdName
Student
OfferNo
OffLocation
OffTime
Offering
EnrollsIn
EnrGrade
attribute of relationship
AuthNo
AuthName
Author
ISBN
Title
Book
AuthOrder
b)
Writes
relationship
Writes
Entity name�
�
Name�
�
�
Name�
SuppNo
SuppName�
Supplier�
Provides�
Qty�
a) Provides relationship�
PartNo
PartName�
Part�
PartNo
PartName
Part
SuppNo
SuppName
Supplier
Qty
Provides
a) Provides relationship
Entity name
Name
Name
OfferNo
OffLocation
Offering
Registers
Entity name
Name
Name
OfferNo
OffLocation
OffTime
Offering
EnrollsIn
EnrGrade
StdNo
StdName
Student
StdNoStdNameStudentOfferNoOffLocationOffTimeOfferingEnrollsInEnrGrade
PartNo
PartName
Part
SuppNo
SuppName
Supplier
Associative
entity type
ProjNo
ProjName
Project
Uses
Part-Uses
Supp-Uses
Proj-Uses
/docProps/thumbnail.jpeg