Transcript Chapter 3: Entity-Relationship Modeling

Data Modeling and Database
Design
Chapter 3:
Entity-Relationship Modeling
Application of ER Modeling Grammar to
the Conceptual Modeling Process
The ER model for conceptual modeling serves two
major purposes
– As a communication/presentation device used by
an analyst to interact with the end-user community
(i.e., the Presentation Layer ER Model/Schema)
– As a design tool at the highest level of abstraction
to convey a deeper level of understanding to the
database designer (i.e., the Design-Specific ER
Model/Schema)
Chapter 3 – Entity-Relationship Modeling
2
The ER Model
The ER model includes:
• An ER diagram (ERD) portraying entity types,
attributes for each entity type, and relationships
among entity types
• Semantic integrity constraints that reflect the
business rules about data not captured in the ER
diagram.
Chapter 3 – Entity-Relationship Modeling
3
The ER Model (continued)
Chapter 3 – Entity-Relationship Modeling
4
Notation
Figure 3.2 Summary of Presentation Layer ER diagram notation
Chapter 3 – Entity-Relationship Modeling
5
Notation (continued)
E1
E1
E1
m
R
n
R
R
E2
E2
E2
Cardinality Ratio of m:n
Chen's Notation
Cardinality Ratio of m:n
Crow's Foot Notation
E1 Optionally Related To E2
(Partial Participation of E1 in R)
E2 Mandatorily Related to E1
(Total Participation of E2 in R)
[Existence Dependency of E2 in R]
Inclusive Arc
Noninclusive Arc
Exclusive Arc
Figure 3.2 Summary of Presentation Layer ER diagram notation (continued)
Chapter 3 – Entity-Relationship Modeling
6
Presentation Layer ER Model
(An End-User Communication/Presentation
Device)
• Not a Science !
• Art? Engineering? - Artistic engineering or
Engineered art !
• Heuristic (Intuitive), iterative process
• Recursive incremental refinement
• Target Audience: End-user community
• Technology-independent
Chapter 3 – Entity-Relationship Modeling
7
The Synthesis Approach to Generate an
Initial Set of Entity Types and Attributes
• List all discernable data elements and treat them as
attributes
• Group these attributes based on apparent
commonalities
• Designate each cluster of attributes as an entity type
• Review leftover data elements and investigate the
possibility that some of them serve as links among
the entity types previously identified
• Designate these links as relationship types
Chapter 3 – Entity-Relationship Modeling
8
The Analysis Approach to Generate an
Initial Set of Entity Types and Attributes
• Begin by searching for things that can be labeled by
singular nouns and call these things entity types
• Gather properties that appear to belong to individual
entity types and label them as attributes of a
particular entity type
• Be sensitive throughout the process to the
identification of relationships among the various entity
types
Chapter 3 – Entity-Relationship Modeling
9
Bearcat, Incorporated
Initial Set of Entity Types
• Read through the vignette on pages 75-77
• Identify entity types
– Plant
- Company ?
– Project
- Manager ?
– Employee
- Supervisor ?
– Dependent
– Credit Union Account
– Hobby
Chapter 3 – Entity-Relationship Modeling
10
Bearcat, Incorporated
Revised Set of Entity Types
• Read through the vignette on pages 75-77
• Identify entity types
– Plant
- Company x
– Project
- Manager x
– Employee
- Supervisor x
– Dependent
– Credit Union Account
– Hobby
Chapter 3 – Entity-Relationship Modeling
11
Development of
Presentation Layer ER Model
• Divide story into meaningful “chunks” and identify
relationship(s) among entity types
• Version 0 of ER diagram results: a punctuated
equilibrium
• Go back and read the story (strike out story lines
already captured if need be) and refine entity types
and relationship types of the ER diagram based on
additional facts discerned
• Gather business rules that can’t be captured in the
ERD into a list of Semantic Integrity Constraints, a
supplement to the ERD
• Repeat last two steps (as many times as necessary)
until all aspects of the story (i.e., the business rules)
have been accounted for in the ER model
Chapter 3 – Entity-Relationship Modeling
12
Development of Presentation Layer ER Model
(continued)
Box 1
Bearcat Incorporated is a manufacturing company that has several plants in the
northeastern part of the United States. These plants are responsible for leading
different projects that the company might undertake, depending on a plants’ function.
A certain plant might even be associated with several projects but a project is always
under the control of just one plant. Some plants do not undertake any projects at all.
If a plant is closed down, the projects undertaken by that plant cannot be canceled.
The project assignments from a closed plant must be temporarily removed in order to
allow the project to be transferred to another plant.
PLANT
1
Undertaken_by
n
PROJECT
Note: The relationship shown models the italicized text
Chapter 3 – Entity-Relationship Modeling
13
Development of Presentation Layer ER Model
(continued)
Box 2
Employees work in these plants and each employee works in only one plant. A plant
could employ many employees but must have at least 100 employees. A plant with
employees cannot be closed down. Every plant is managed by an employee who
works in the same plant; but every employee is not a plant manager nor can an
employee manage more than one plant. Company policy dictates that every plant
must have a manager. Therefore, an employee currently managing a plant cannot be
deleted from the database. If a plant is closed down, the employee no longer manages
the plant but becomes an employee of another plant. Some employees are assigned to
work on projects and in some cases might even be assigned to work on several
projects simultaneously. For a project to exist, it must have at least one employee
assigned to it. Projects might need several employees depending on their size and
scope. As long as an employee is assigned to a project, his or her record cannot be
removed from the database. However, once a project ends it is removed from the
system and all assignments of employees to that project must be removed.
n
1
Works_in
EMPLOYEE
PLANT
1
Managed_by
1
1
Undertaken_by
n
m
n
Assigned
Chapter 3 – Entity-Relationship Modeling
PROJECT
14
Development of Presentation Layer ER Model
(continued)
Box 3
Some of the employees also supervise other employees but all employees need not be
supervised – the employees that are supervised, are supervised by just one employee.
An employee can be a supervisor of several employees, but no more than 20. The
Human Resources Department uses a default employee number to replace a supervisor
who leaves the company. It is not possible for an employee to be his or her own
supervisor.
EMPLOYEE
Supervisor
1
Supervisee
Supervised_by
20
Note: The relationship shown models the italicized text
Chapter 3 – Entity-Relationship Modeling
15
Development of Presentation Layer ER Model
(continued)
Box 4 Storyline
Some employees may have several dependents. Bearcat Incorporated does not allow
both husband and wife to be an employee of the company. Also, a dependent can only be
a dependent of one employee at any time. Bearcat Incorporated offers credit union
facilities as a service to its employees and to their dependents. An employee is not
required to become a member of Bearcat Credit Union (BCU). However, most
employees and some of their dependents have accounts in BCU. Some BCU accounts
are individual accounts and others are joint accounts between an employee and his or
her dependent(s). Every BCU account must belong to at least an employee or a
dependent. Each joint account must involve no more than one employee and no more
than one of his or her dependents. If an employee leaves the company, all dependents
and BCU accounts of the employee must be removed. In addition, as long as a
dependent has a BCU account, deletion of the dependent is not permitted.
Chapter 3 – Entity-Relationship Modeling
16
Development of Presentation Layer ER Model
(continued)
Box 4
EMPLOYEE
1
Supervised_by
20
1
Dependent_of
1
n
Held_by_E
m
DEPENDENT
1
Held_by_D
n
BCU_ACCOUNT
Chapter 3 – Entity-Relationship Modeling
17
Development of Presentation Layer ER Model
(continued)
Business rule: Joint-account between an employee and a dependent is not permitted
Box 5
EMPLOYEE
1
Dependent_of
1
20
Supervised_by
n
1
Held_by_E
DEPENDENT
1
m
Held_by_D
n
BCU_ACCOUNT
Note: The exclusive arc indicates that the relationship types Held_by_E and Held_by_D are mutually
exclusive; i.e., Held_by_E and Held_by_D are manditorily exclusive.
Chapter 3 – Entity-Relationship Modeling
18
Development of Presentation Layer ER Model
(continued)
Business rule: All BCU accounts are joint-accounts between an employee and a dependent
Box 6
EMPLOYEE
1
Dependent_of
1
Supervised_by
20
n
DEPENDENT
1
1
Held_by_E
Held_by_D
m
n
BCU_ACCOUNT
Note: The inclusive arc indicates that the relationship types Held_by_E and Held_by_D are mutually
inclusive; i.e., Held_by_E and Held_by_D are mandatorily inclusive.
Chapter 3 – Entity-Relationship Modeling
19
Development of Presentation Layer ER Model
(continued)
Business rule: Joint-account between an employee and a dependent is permitted
Box 7
EMPLOYEE
1
Dependent_of
1
Supervised_by
20
n
DEPENDENT
1
1
Held_by_E
Held_by_D
m
n
BCU_ACCOUNT
Note: The noninclusive arc indicates that the relationship types Held_by_E and Held_by_D are
mutually noninclusive; i.e., Held_by_E and Held_by_D are optionally inclusive.
Chapter 3 – Entity-Relationship Modeling
20
Development of Presentation Layer ER Model
(continued)
Box 8
Bearcat Incorporated sponsors recreational opportunities for the dependents of
employees in order to nurture the hobbies of the dependents. Some dependents need
not have a hobby, however, it is possible that some dependents may have several
hobbies. Because some hobbies are not as popular as others, every hobby need not
have participants. If a dependent is no longer in the database, all records of the
participation of that dependent in hobbies should not exist in the database either.
Finally, as long as at least one dependent participates in a hobby, that hobby should
continue to exist.
EMPLOYEE
1
Dependent_of
n
DEPENDENT
m
n
Participates
HOBBY
Note: The relationship shown models the italicized text
Chapter 3 – Entity-Relationship Modeling
21
Presentation Layer ER Diagram
Pl_name
Name_tag
Lname
Pnumber
Salary
Minit
n
Address
Fname
1
Works_in
employer
Name
worker
Emp#
Gender
Building
No_of_employees
EMPLOYEE
PLANT
Mgr_start_dt
Budget
Date_hired
manager
Responsible
managed by
No_of_dependents
1
Managed_by
1
1
Supervisee
Undertaken_by
Assignee
Hours
Holder of
n
Supervisor
Controlled
Plocation
1
20
n
Supervised_by
Having
m
n
Assigned
1
Assignment
PROJECT
Pr_name
1
Pnumber
Dependent_of
Held_by_E
Dname
n
Related_how
m
Depends_on
Dependent
--------
DEPENDENT
Belongs to
Participant
Birthdate
Gender
Account Holder
Annual_cost
m
1
Participates
Held_by_D
n
n
Hb_name
Hrs_per_wk
Usage
Account of
Gi_activity
HOBBY
Account_id
BCU_ACCOUNT
BANK
ACCOUNT
Acct_type
Io_activity
Account#
Balance
Figure 3.3 Presentation Layer ER diagram for Bearcat, Incorporated
Chapter 3 – Entity-Relationship Modeling
22
Presentation Layer
Semantic Integrity Constraints
Semantic integrity constraints are grouped into the
following categories:
–
–
–
–
Attribute level business rules
Entity level business rules
Business rules governing entity deletion
Miscellaneous business rules
Chapter 3 – Entity-Relationship Modeling
23
Presentation Layer
Semantic Integrity Constraints (continued)
Table 3.1 Semantic integrity constraints for the Presentation Layer ER Model
Attribute-Level Business Rules
1.
2.
3.
4.
5.
6.
7.
Each plant has a plant number that ranges from 10 to 20.
The gender of each employee or dependent is either male or female.
Project numbers range from 1 to 40.
Project locations are confined to the cities of Bellaire, Blue Ash, Mason, Stafford, and Sugarland.
Account types are coded as C – checking account, S – savings account, I – investment account.
A hobby can be either I – indoor activity or O – outdoor activity.
A hobby can be a G – group activity or I – individual activity.
Entity-Level Business Rules
1.
2.
3.
4.
5.
A mother or daughter dependent must be a female, a father or son dependent must be a male, and a spouse
dependent can be either male or female.
An employee cannot be his or her own supervisor.
A dependent may have a joint account only with an employee of Bearcat Incorporated to whom he or she is
related.
Either plant number or plant name must be present.
Every plant is managed by an employee who works in the same plant.
Business Rules Governing Entity Deletion
1.
2.
3.
A plant with employees cannot be closed down.
If an employee is deleted, all BCU accounts of that particular employee must be deleted.
*
If a plant is closed down, the projects undertaken by that plant cannot be canceled. The project assignments
from a closed plant must be temporarily removed in order to allow the project to be transferred to another plant.
4. The Human Resources Department uses a designated default employee number to replace a supervisor who
leaves the company.
5. An employee currently managing a plant cannot be deleted from the database.
6. If a plant is closed down, the employee no longer manages the plant but becomes an employee of another plant.
7. **If an employee leaves the company, all dependents and BCU accounts of the employee must be removed.
8. **As long as a dependent has a BCU account, the deletion of that dependent is prohibited.
9. As long as an employee is assigned to a project his or her record cannot be removed from the database.
10. If a project is deleted, all assignments of employees to that project must be deleted.
11. If a dependent is deleted, all records of the participation of that dependent in hobbies must be deleted.
12. A hobby with at least one dependent participating in it cannot be deleted.
*
Honoring this rule entails relaxation of the requirement at the beginning of Section 3.1 that a project is always under control of
a plant.
**
Rule 7 cannot be honored for dependents who have bank accounts because Rule 8 prohibits deletion of such dependents. This
is resolved in favor of Rule 7 by letting the bank account be deleted when a dependent is deleted. Additional discussion of this
conflict resolution appears in Section 3.2.3.
Miscellaneous Business Rules
1.
2.
3.
Each plant has at least three buildings.
Each plant must have at least 100 employees.
The salary of an employee cannot exceed the salary of the employee’s supervisor.
Chapter 3 – Entity-Relationship Modeling
24
Design-Specific ER Model
(Technology-Independent Design Tool)
• Not a Science !
• Art? Engineering?
Not an Art anymore; more of an Engineering
• Systematic process
• Target Audience: Database Designer/Administrator
– Coarse granularity
– Fine granularity
Chapter 3 – Entity-Relationship Modeling
25
Coarse-granular Design-Specific ER Model
• Tasks
– Collect additional attribute characteristics relevant to
design/implementation (e.g., data type, size) and
prepare an updated list of Semantic Integrity
Constraints
– Introduce a technically more precise notation, viz.,
(min, max) notation, for expressing the structural
constraints of a relationship type
– Map deletion rules to the ER diagram
Chapter 3 – Entity-Relationship Modeling
26
Design-Specific ER Model
(Coarse Granularity)
Components of a Design-specific ER Model (Coarse
Granularity) comprise:
– Collection of a few characteristics for attributes (i.e.,
data type and size)
– Using the technically more precise (min, max) notation
for the specification of relationships
– Mapping deletion rules to the ER diagram
See Figures 3.4 – 3.8 in the textbook
Chapter 3 – Entity-Relationship Modeling
27
Various Structural Constraint Notations
EMPLOYEE
1
Held_by_E
m
BCU_ACCOUNT
(a)
'Look Across' (Chen's) Notation for a Binary Relationship
EMPLOYEE
BCU_ACCOUNT
Held_by_E
Note: the bar adjacent to EMPLOYEE and the crow’s foot adjacent to BCU_ACCOUNT
convey the cardinality ratio of 1:m.
(b)
'Look across' (Crow's foot variant) notation for a binary relationship
EMPLOYEE
(0,m)
Held_by_E
(0,1)
BCU_ACCOUNT
(c)
'Look here' (min, max) notation for a binary relationship
Figure 3.4 Introduction of (min, max) notation for a binary
relationship
Chapter 3 – Entity-Relationship Modeling
28
Differentiating Chen’s Notation From the (min, max) Notation
Fname
Pl_name
Name_tag
Lname
Pnumber
Salary
Minit
Address
Works_in
(1, 1)
Name
Building
(100, n)
Emp#
Gender
EMPLOYEE
No_of_employees
Mgr_start_dt
PLANT
Budget
Date_hired
(0,1)
(1,1)
(0,n)
No_of_dependents
Managed_by
(0, 20)
Undertaken_by
(0, 1)
(0, n)
(0,m)
Hours
(0, n)
(1,1)
Plocation
Supervised_by
(1, m)
Assigned
PROJECT
Pr_name
Pnumber
Dependent_of
Held_by_E
Related_how
Dname
(1, 1)
Transformed from the Chen’s (look across)
notation to [min, max] (look here) notation
Dependent
----- --DEPENDENT
(0,1)
(0, n)
Birthdate
Gender
(0, n)
Annual_cost
Participates
Held_by_D
Hrs_per_wk
Hb_name
(0,m)
(0,1)
Gi_activity
HOBBY
Account_id
BCU_ACCOUNT
Acct_type
Io_activity
Account#
Balance
Figure 3.5 Course-granular Design-Specific ER diagram for Bearcat, Incorporated - Stage 1
Chapter 3 – Entity-Relationship Modeling
29
Deletion Constraints
Restrict Rule (R): If a parent entity in a relationship is
deleted and if all child entities related to this parent in
this relationship should not be deleted, then the deletion
of the parent should be disallowed
Cascade Rule (C): If a parent entity in a relationship is
deleted and if all child entities related to this parent in
this relationship should also be deleted, the cascade
rule applies
Note: Conventionally, when a deletion constraint is not
specified, the restrict rule is implied by default
Chapter 3 – Entity-Relationship Modeling
30
Deletion Constraints (continued)
Set Null Rule (N): If a parent entity in a relationship is
deleted and if all child entities related to this parent in
this relationship should be retained but no longer
referenced to this parent, the ‘set null’ rule applies
Set Default Rule (D): If a parent entity in a relationship
is deleted and if all child entities related to this parent
in this relationship should be retained but no longer
referenced to this parent but should be referenced to
a predefined default parent, the ‘set default’ rule
applies
Chapter 3 – Entity-Relationship Modeling
31
The Four Deletion Rules
Deletion Rule Examples
FACULTY
FACULTY
R
(0,n)
(0,n)
Diss_chair
Diss_chair
(1,1)
(1,1)
C
STUDENT
The Restrict Rule
When a parent entity in a relationship is deleted, if all child
entities related to this parent in this relationship should not be
deleted, then the deletion of the parent should be restricted.
FACULTY
STUDENT
The Cascade Rule
When a parent entity in a relationship is deleted, if all child
entities related to this parent should be deleted, then the
cascade rule applies.
FACULTY
(0,n)
(0,n)
Diss_chair
Diss_chair
(1,1) (0,1)
N
STUDENT
The ‘Set Null’ Rule
When a parent entity in a relationship is deleted, if all
child entities related to this parent in this relationship
should be retained but no longer referenced to this
parent, the “set null” rule applies.
Chapter 3 – Entity-Relationship Modeling
(1,1)
D
STUDENT
The ‘Set Default’ Rule
When a parent entity in a relationship is deleted, if all child entities
related to this parent in this relationship should be retained but no
longer referenced to this parent, but should be referenced to a
predefined default parent, the “set default” rule applies.
32
Representing Deletion Constraints Using Deletion Rules
Fname
Pl_name
Name_tag
Lname
Pnumber
Salary
Minit
Address
Works_in
(1, 1)
Name
Building
(100, n)
R
Emp#
Gender
EMPLOYEE
Date_hired
R
PLANT
Budget
(0,1)
R
D
No_of_employees
Mgr_start_dt
N
(1,1)
(0,n)
No_of_dependents
Managed_by
(0, 20)
Undertaken_by
(0, 1)
(0, n)
(0,m)
Hours
(0, n)
(1,1)
Plocation
N
Supervised_by
(1, m)
Assigned
PROJECT
Pr_name
C
Pnumber
Dependent_of
1. deletion rule on undertaken_by conflicts with the
participation constraint of PROJECT
Held_by_E
Related_how
Dname
(1, 1)
2. Contradicting deletion rules on DEPENDENT
C
Dependent
----- ---
DEPENDENT
R
(0,1)
(1, n)
Birthdate
Gender
(0, n)
Annual_cost
C
Participates
Held_by_D
Hrs_per_wk
Hb_name
(0,m)
(0,1)
R
Gi_activity
C
Balance
Account_id
HOBBY
BCU_ACCOUNT
Acct_type
Io_activity
Account#
Figure 3.7 Course-granular Design-Specific ER diagram for Bearcat, Incorporated - Stage 2
{deletion constraints added}
Chapter 3 – Entity-Relationship Modeling
33
Representing Deletion Constraints Using Deletion Rules (continued)
Fname
Pl_name
Name_tag
Lname
Pnumber
Salary
Minit
Address
Works_in
(1, 1)
Name
Building
(100, n)
R
Emp#
Gender
EMPLOYEE
Date_hired
R
PLANT
Budget
(0,1)
R
D
No_of_employees
Mgr_start_dt
N
(1,1)
(0,n)
No_of_dependents
Managed_by
(0, 20)
Undertaken_by
(0, 1)
(0, n)
(0,m)
Hours
(0, n)
(0,1)
Plocation
N
Supervised_by
(1, m)
Assigned
PROJECT
Pr_name
C
Pnumber
Dependent_of
Held_by_E
Related_how
Dname
(1, 1)
Conflicting deletion rules and constraints realigned
C
Dependent
----- ---
DEPENDENT
(0, n)
(0,1)
Birthdate
Gender
(0, n)
Annual_cost
C
Participates
Held_by_D
Hrs_per_wk
Hb_name
(0,m)
(0,1)
R
C
C
Gi_activity
HOBBY
Balance
Account_id
BCU_ACCOUNT
Acct_type
Io_activity
Account#
Figure 3.8 Course-granular Design-Specific ER diagram for Bearcat, Incorporated - Final
Chapter 3 – Entity-Relationship Modeling
34
Fine-granular Design-Specific ER Model
• Tasks
– Map the additional attribute characteristics to the ER
diagram
– Decompose ER constructs preparatory to logical
model mapping
• Decomposition of multi-valued attribute
• Decomposition of m:n cardinality constraint (ratio)
– Generate an updated list of Semantic Integrity
Constraints
Chapter 3 – Entity-Relationship Modeling
35
Design-Specific ER Model
(Fine Granularity)
Components of the Fine-granular Design-Specific ER
Model comprise:
– Mapping the attribute characteristics into the ER
diagram (i.e., A-Alphabetic, N-Numeric, XAlphanumeric, Dt-Data type)
– Decomposing constructs that cannot directly be
mapped to a logical schema (i.e., multi-valued
attributes and n:m relationship types)
See Figures 3.9 – 3.11 and Table 3.3 in Textbook
Chapter 3 – Entity-Relationship Modeling
36
Replacing a Multi-valued Attribute with a
Single-valued Attribute
Pnumber
Building
Plname
PLANT
Building
Pnumber
Plname
Or
Pl_id
Budget
PLANT
Building
Pl_id
Pnumber
Budget
PLANT
Figure 3.9 (a) Transformation of a multi-valued attribute to a single-valued attribute
Two design variations
Chapter 3 – Entity-Relationship Modeling
37
An Illustration Using Sample Data
Chapter 3 – Entity-Relationship Modeling
38
Replacing a Multi-valued Attribute With a
Weak Entity Type
Budget
Pnumber
Building
Plname
PLANT
Budget
Pnumber
Building
Plname
PLANT
(1,n)
Houses
(1,1)
BUILDING
Figure 3.9 (b) Mapping a multi-valued attribute to a weak entity type
A structural alternative to Figure 3.9(a)
Chapter 3 – Entity-Relationship Modeling
39
An Illustration Using Sample Data
Chapter 3 – Entity-Relationship Modeling
40
Decomposing an m:n Relationship Type
(by Creating a Gerund Entity Type)
(1,n)
EMPLOYEE
Assigned
(1,m)
PROJECT
(a)
An m:n relationship between EMPLOYEE and PROJECT
EMPLOYEE
(1,n)
Uses
(1,1)
ASSIGNMENT
(1,1)
(1,m)
Belongs to
PROJECT
(b)
Decomposition of Assigned relationship type to eliminate m:n cardinality ratio
Figure 3.10 Resolution of m:n cardinality constraint (ratio)
Chapter 3 – Entity-Relationship Modeling
41
The Fine-granular Design-Specific ER Diagram For Bearcat, Inc.
[A,1]
[A,5]
Emp_a
Suffix
[A,1]
Minit
[N,1]
Name_tag
[A,20]
Lname
[A,20]
Fname
[N,5]
[A,1]
Emp_n
Prefix
[A,30]
Pl_name
[N,6]
Salary
[X,50]
Address
[N,2]
Pnumber
Works_in
(1, 1)
Name
(100, n)
R
Emp#
EMPLOYEE
[A,1]
Gender
D
R
[Dt,8]
Mgr_start_dt
R
N
[N,7]
Budget
[N,3]
No_of_employees
PLANT
(0,1)
R
R
(1,1)
(0,n)
[Dt,8]
Date_hired
Managed_by
[N,2]
No_of_dependents
(3, n)
(0,
(1, n)
(0, n)
(0, 20)
Undertaken_by
(0, 1)
(0,m)
[N,2]
Pnumber
(0,1)
Uses
Houses
N
Supervised_by
PROJECT
[A,20]
Pr_name
(1,1)
(1, m)
Dependent_of
[A,15]
Plocation
(1, 1)
Held_by_E
[A,12]
Related_how
Belongs_to
(1, 1)
[A,15]
Dname
BUILDING
C
Dependent
----- ---
[N,3]
Hours
(1, 1)
C
DEPENDENT
(0,1)
[A,20]
Building
-----
ASSIGNMENT
(0, n)
[Dt,8]
Birthdate
[A,1]
[X,6]
Acct_type Account#
Held
_by_
[A,1]
Gender
(0, n)
[N,6]
Annual_cost
D
Includes_D
Account_id
(1, 1)
C
(0,1)
PARTICIPATION
C
C
[A,20]
Hb_name
BCU_ACCOUNT
(1, 1)
HOBBY
[N,2.1]
Hrs_per_wk
R
[N,8.2]
Balance
(o,
m)
(1,m)
Io_activity
[A,1]
Includes_H
Gi_activity
[A,1]
Figure 3.11 Fine-granular Design-Specific ER diagram for Bearcat, Incorporated
Chapter 3 – Entity-Relationship Modeling
42
Summary
Presentation Layer ER Model
• Diagram + Integrity Constraints
Coarse-Granular ER Model
• Collection of a few characteristics for attributes (i.e., data type and
size)
• Using the technically more precise (min, max) notation for the
specification of relationships
• Mapping deletion rules to the ER diagram
Chapter 3 – Entity-Relationship Modeling
43
Summary (continued)
Fine-Granular ER Model
• Mapping the attribute characteristics into the ER diagram
(i.e., A-Alphabetic, N-Numeric, X-Alphanumeric, Dt-Data
type)
• Decomposing constructs that cannot directly be mapped to
a logical schema (i.e., multi-valued attributes and n:m
relationship types)
Chapter 3 – Entity-Relationship Modeling
44