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