Entity Relationship Diagrams
Download
Report
Transcript Entity Relationship Diagrams
Entity Relationship Diagrams
Mr.Prasad Sawant
MIT PUNE
Software Analysis & Design
Universe
of Discourse
REQUIREMENTS
COLLECTION
AND ANALYSIS
FUNCTIONAL
ANALYSIS
APPLICATION
PROGRAM
DESIGN
Description of requirements of users
data modelling, process modelling
Data modelling is expressed using a
high level model such as ENTITYRELATIONSHIP (ER)
The ER Model represented pictorially
(ER diagrams)
ER Model contains detailed
descriptions of:
What are the entities and
relationships in the enterprise?
What information about these
entities and relationships should we
store in the database?
What are the integrity constraints or
business rules that hold?
Data modelling vs Process
modelling
Process modelling (i.e. DFD) shows
data stores, how, where, when data
are used or changed in an System
Data modelling (i.e ER) shows the
definition, structure, & relationship
within the data
Conceptual Data Modeling and
the E-R Diagram
Goal
Capture as much of the meaning of the data as
possible
A better design that is scalable and easier to
maintain
Introduction to EntityRelationship (E-R) Modeling
Notation uses three main constructs
Data entities
Attributes
Relationships
Entity-Relationship (E-R) Diagram
A detailed, logical representation of the
entities, associations and data elements
for an organization or business
Entity-Relationship (E-R) Modeling
Key Terms
Entity
A person, place, object, event or concept in the
user environment about which the organization
wishes to maintain data
Represented by a rectangle in E-R diagrams
Entity Type
A collection of entities that share common
properties or characteristics
Attribute
A named property or characteristic of an entity
that is of interest to an organization
Entity-Relationship (E-R) Modeling
Key Terms
Candidate keys and identifiers
Each entity type must have an attribute
or set of attributes that distinguishes one
instance from other instances of the same
type
Candidate key
Attribute (or combination of attributes) that
uniquely identifies each instance of an entity
type
Entity-Relationship (E-R) Modeling
Key Terms
Identifier
A candidate key that has been selected as the
unique identifying characteristic for an entity
type
Selection rules for an identifier
1. Choose a candidate key that will not change its
value
2. Choose a candidate key that will never be null
3. Avoid using intelligent keys
4. Consider substituting single value surrogate
keys for large composite keys
Notation Guide
ENTITY TYPE
WEAK ENTITY TYPE
RELATIONSHIP TYPE
IDENTIFYING
RELATIONSHIP TYPE
… Notation Guide
ATTRIBUTE
KEY ATTRIBUTE
_____
MULTIVALUED ATTRIBUTE
DERIVED ATTRIBUTE
...
COMPOSITE ATTRIBUTE
ER Diagram Basics
Entity
sname
Store
Locations
Relationship
manager
qty
Keeps
Attributes
pname
Product
price
descrip
Entity Sets
A collection of similar entities (e.g. all employees)
All entities in an entity set have the same set of
attributes
Each attribute has a domain
Can map entity set to a relation easily
EMPLOYEES
SSN
NAME
SAL
321-23-3241
Kim
23,000
645-56-7895
Jones
45,000
Entity Type
Defines set of entities that have
the same attributes (e.g.
EMPLOYEE)
Each Entity Type is described
by its NAME and attributes
The Entity Type describes the
“Schema” or “Intension” for a
set of entities
Collection of all entities of a
particular entity type at a given
point in time is called the
“Entity Set” or “Extension” of
an Entity Type
Entity Type and Entity Set are
customarily referred to by the
same name
name
sal
SSN
EMPLOYEE
Notation
Attributes
Key Attributes
Attribute Types
Notation
Key Attributes: Identifier
Key (or uniqueness)
constraints are applied to
entity types
Key attribute’s values are
distinct for each individual
entity in the entity set
A key attribute has its name
underlined inside the oval
Key must hold for every
possible extension of the
entity type
Multiple keys are possible
SSN
EMPLOYEE
Null Valued Attributes
A particular entity may not have an applicable
value for an attribute
– Home-Phone: Not known if it exists
– Height: Not known at present time
Type of Null Values
– Not Applicable
– Unknown
– Missing
Composite Vs. Simple Attributes
Composite attributes can be divided into
smaller parts which represent simple
attributes with independent meaning
Simple Attribute: Aircraft-Type
Complex Attribute: Aircraft-Location
which is comprised of :
Aircraft-Latitude
Notation
Aircraft-Longitude
Aircraft-Altitude
… There is no formal concept of “composite
attribute” in the relational model
Single Vs. Multivalued Attributes
Simple attributes can either be single-valued
or multi-valued
Single-valued: Gender = F
Notation
Multivalued: Degree = {BSc, MInfTech}
Notation
… An “attribute” in the relational model is always
single valued - Values are atomic!
Derived Vs. Stored Attributes
Some attribute values can be derived from
related attribute values:
Notation
Age ® Date - B-day
Y-Sal ® 12 * M-Sal
Age
M-sal
B-days
Y-sal
EMPLOYEE
Derived Vs. Stored Attributes
Some attribute
values can be
derived from
attributed values of
related entities
total-value ® sum
(qty * price)
Order
Total-Value
qty
Item
price
Representing Attributes
Parenthesis ( ) for composite attributes
Brackets { } for multi-valued attributes
Assume a person can have more than one residence
and each residence can have multiple telephones
{AddressPhone
({ Phone ( AreaCode,PhoneNum ) },
Address (StreetAddresss (Number, Street, AptNo),
City,State,PostalCode) ) }
Entity-Relationship (E-R) Modeling
Key Terms
Relationship
An association between the instances of
one or more entity types that is of
interest to the organization
Association indicates that an event has
occurred or that there is a natural link
between entity types
Relationships are always labeled with
verb phrases
Cardinality
The number of instances of entity B that
can be associated with each instance of
entity A
Minimum Cardinality
The minimum number of instances of entity B
that may be associated with each instance of
entity A
This is also called “modality”.
Maximum Cardinality
The maximum number of instances of entity B
that may be associated with each instance of
entity A
Naming and Defining
Relationships
Relationship name is a verb phrase
Avoid vague names
Guidelines for defining relationships
Definition explains what action is being taken
and why it is important
Give examples to clarify the action
Optional participation should be explained
Explain reasons for any explicit maximum
cardinality
Naming and Defining
Relationships
Guidelines for defining relationships
Explain any restrictions on participation in
the relationship
Explain extent of the history that is kept
in the relationship
Explain whether an entity instance
involved in a relationship instance can
transfer participation to another
relationship instance
10.25
Relationships
Relationship Types and Sets
Relationship Degree
Entity Roles and Recursive Relationships
Relationship Constraints
Attributes of Relationship Types
Relationship Types and Sets
A Relationship is an association among two or
more entities (e.g John works in Pharmacy
department)
A Relationship Type defines the relationship,
and a Relationship Set represents a set of
relationship instances
A Relationship Type thus defines the structure
of the Relationship Set
Relationship Type and corresponding Set are customarily
referred to by the same name
Relationship Degree
The degree of a relationship type is
the number of participating entity
types
– 2 entities: Binary Relationship
3 entities: Ternary Relationship
n entities: N-ary Relationship
– Same entity type could participate in
multiple relationship types
Supplier
Supply
Ternary
Part
Project
Departments
Works_In
Binary
Employees
Multiple
Assigned_to
Entity Roles
Each entity type that
participates in a relationship
type plays a particular role
in the relationship type
Departments
employer
Works_In
The role name signifies the
role that a participating
entity from the entity type
plays in each relationship
instance, i.e. it explains what
the relationship means
worker
Employees
Role
Names
Recursive Relationships
Same entity type can participate more than once in
the same relationship type under different “roles”
Such relationships are called
“Recursive Relationships”
Employees
Recursive
Relationship
Supervisor
Subordinate
Supervision
Relationship Constraints
What are Relationship Constraints ?
Constraints on relationships are determined
by the UoD, which these relationships are
describing
Constraints on the relationship type limit the
possible combination of entities that may
participate in the corresponding relationship
set
Kinds of Constraints
What kind of constraints can be defined in the
ER Model?
Cardinality Constraints
Participation Constraints
Together called “Structural Constraints”
Constraints are represented by
specific notation in the ER diagram
Possible Cardinality Ratios
The “Cardinality Ratio” for a
binary relationship specifies
the number of relationship
instances that an entity can
participate in
– Works-In is a binary relationship
– Participating entities are
DEPARTMENT : EMPLOYEE
– One department can have
Many employees Cardinality Ratio is 1 : N
Departments
Works_In
Employees
Possible Cardinality Ratios
1–to-1 (1 : 1)
– Both entities can
participate in only one
relationship instance
1-to-Many, Many-to-1
(1 : N, N : 1)
– One entity can
participate in many
relationship instances
Many-to-Many (N: M)
– Both entities can participate in
many relationship instance
.
.
.
.
.
.
1-to-1
.
.
.
.
.
.
1- to - Many
.
.
.
.
.
.
.
.
.
.
.
Many - to 1
.
.
.
Many-to-Many
.
Example Cardinality Constraints
How many Employees can work in a Department?
One employee can work in only one department
How many Employees can be employed by a Department?
One department can employ many employees
How many managers can a department have?
One department can have only one manager
How many departments can an employee manage?
One employee can have manage only one department
Representing Cardinality
N
Works_In
1
Employees
Departments
1
Manages
1
One employee can work in only one department
One department can employ many employees
One department can have only one manager
One employee can manage only one department
Existence Dependency
Existence dependency indicates
whether the existence of an
entity depends on its
relationship to another entity via
the relationship type
– Every employee must work for
a department - EMPLOYEE is
existentially dependent on
DEPARTMENT via the Works In
relationship type
Departments
Works_In
Employees
Kinds of participating constraints
TOTAL Participation (Existence Dependency)
Constraint : Every employee must work for a
department
PARTIAL Participation
Constraint : Not every employee is a
manager
Representing Participation
N
Works_In
1
Employees
Departments
1
Manages
1
Every employee must work for a
department
Every department must have a manager
Every department must have employees
Not every employee is a manager