Transcript Entity Relationship Diagrams Rizwan Rehman, CCS , DU Software Analysis & Design Universe of Discourse REQUIREMENTS COLLECTION AND ANALYSIS FUNCTIONAL ANALYSIS APPLICATION PROGRAM DESIGN  Description of requirements of users  data modelling, process.

Entity Relationship Diagrams
Rizwan Rehman, CCS , DU
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 can be 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?
Conceptual Data Modeling and
the E-R Diagram
 Goal
 Capture as much of the meaning of the data as
possible
 If you know the rules of normalization,
referential integrity, foreign keys, etc., this is
good but not as important now. It is much more
important to get the organizational data model
correct, i.e. to understand the actual data
requirements for the organization.
 Result
 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
10.4
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
10.5
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
… Notation Guide
R
E1
E2
N
1
E1
R
E2
(min,max)
R
E2
 TOTAL PARTICIPATION OF E2
IN R
 CARDINALITY RATIO 1:N FOR
E1:E2 IN R
 STRUCTURAL CONSTRAINT
(min, max) ON PARTICIPATION
OF E IN R (Alternative
Notation)
… Notation Guide
E1
 E1 IS A SUBCLASS OF E2
E2
 E1 and E2 ARE
SUBCLASSES OF E3
E1
E3
E2
 Overlapping specialization
o
 Disjoint specialization
d
ER Diagram Basics
Entity
sname
Store
Locations
Relationship
manager
qty
Keeps
Attributes
pname
Product
price
descrip
Entity
Real-world object distinguishable from other objects
(e.g a student, car, job, subject, building ...)
 An entity is described using a set of attributes
 The same entity may have different prominence in
different UoDs
– In the Company database, an employee’s car is of
lesser importance
– In the Department of Transportation’s registration
database, cars may be the most important concept
– In both cases, cars will be represented as entities;
but with different levels of detail
Entity Sets
A collection of similar entities (e.g. all employees)
 All entities in an entity set have the same set of
attributes
 Each entity set has a key
 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
Notation
 Key Attributes
 Value Sets of Attributes
 Null Valued Attributes
 Attribute Types
– Composite Vs. Simple Attributes
– Single-valued Vs. Multi-valued Attributes
– Derived Vs. Stored Attributes
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
– Tertiary-Degree: Not applicable for a person
with no university education
– 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
 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) ) }
Examples
 Identify a few entity types, instances,
attributes and candidate keys for:
 Case at DFD course
To be continued ....
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.29
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
In database design, a compound key is a key that consists of 2 or
more attributes that uniquely identify an entity occurrence. Each
attribute that makes up the compound key is a simple key in its
own right.
This is often confused with a composite key whereby even though
this is also a key that consists of 2 or more attributes that uniquely
identify an entity occurrence, at least one attribute that makes up
the composite key is not a simple key in its own right.
An example might be an entity that represents the modules each
student is attending at University. The entity has a studentId and a
moduleCode as its primary key. Each of the attributes that make up
the primary key are simple keys because each represents a unique
reference when identifying a student in one instance and a module
in the other.
.
In contrast, using the same example, imagine we identified a
student by their firstName + lastName. In our table representing
students on modules our primary key would now be firstName +
lastName + moduleCode. Because firstName + lastName represent
a unique reference to a student, it is not a simple key, it is a
combination of attributes used to uniquely identify a student.
Therefore the primary key for this entity is a composite key.
No restriction is applied to the attributes regarding their (initial)
ownership within the data model. This means that any one, none,
or all, of the multiple attributes within the compound key can be
foreign keys. Indeed, a foreign key may itself be a compound key.
Compound keys almost always originate from attributive or
associative entities (tables) within the model, but this is not an
absolute