Entity Relationship Diagrams

1
Entity Relationship Diagrams

• Mr.Prasad Sawant
• MIT PUNE

2
Software Analysis Design

• Description of requirements of users ? data
  modelling, process modelling
• Data modelling is expressed using a high level
  model such as ENTITY-RELATIONSHIP (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?

3
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

4
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

5
Introduction to Entity-Relationship (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

6
Entity-Relationship (E-R) ModelingKey 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

7
Entity-Relationship (E-R) ModelingKey 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

8
Entity-Relationship (E-R) ModelingKey Terms

• Identifier
• A candidate key that has been selected as the
  unique identifying characteristic for an entity
  type
• Selection rules for an identifier
• Choose a candidate key that will not change its
  value
• Choose a candidate key that will never be null
• Avoid using intelligent keys
• Consider substituting single value surrogate keys
  for large composite keys

9
Notation Guide

• ENTITY TYPE
• WEAK ENTITY TYPE
• RELATIONSHIP TYPE
• IDENTIFYING RELATIONSHIP TYPE

10
Notation Guide

• ATTRIBUTE
• KEY ATTRIBUTE
• MULTIVALUED ATTRIBUTE
• DERIVED ATTRIBUTE
• COMPOSITE ATTRIBUTE

_____
. . .
11
ER Diagram Basics
Relationship
Attributes
12
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
13
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

14
Attributes

• Key Attributes
• Attribute Types

Notation
15
Key Attributes Identifier

• Key (or uniqueness) constraints are applied to
  entity types
• Key attributes 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
16
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

17
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
• Aircraft-Longitude
• Aircraft-Altitude

Notation
There is no formal concept of
composite attribute in the relational model
18
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!

19
Derived Vs. Stored Attributes

• Some attribute values can be derived from
• related attribute values
• Age Date - B-day
• Y-Sal 12 M-Sal

Notation
M-sal
Age
B-days
Y-sal
EMPLOYEE
20
Derived Vs. Stored Attributes

• Some attribute values can be derived from
  attributed values of related entities
• total-value sum (qty price)

21
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) )

22
Entity-Relationship (E-R) ModelingKey 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

23
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

24
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

25
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
26
Relationships

• Relationship Types and Sets
• Relationship Degree
• Entity Roles and Recursive Relationships
• Relationship Constraints
• Attributes of Relationship Types

27
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

28
Relationship Degree
Departments

• 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

Binary
Multiple
Employees
Supplier
Project
Ternary
Part
29
Entity Roles

• Each entity type that
• participates in a relationship
• type plays a particular role
• in the relationship type
• 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

30
Recursive Relationships

• Same entity type can participate more than once
  in the same relationship type under different
  roles
• Such relationships are called
• Recursive Relationships

31
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

32
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
33
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

34
Possible Cardinality Ratios

• 1to-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 - Many
35
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
36
Representing Cardinality
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
37
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

38
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

39
Representing Participation
Every employee must work for a department Every
department must have a manager Every department
must have employees Not every employee is a
manager