Entity Relationship Diagrams ERDs

1
Entity Relationship Diagrams (ERDs)

• Dr Jon Tepper
• Room N330

2
Learning Objectives

• Define entity relationship models and describe
  its benefits
• Recognise and understand the basic concepts and
  constructs of an entity relationship diagram
  (ERD)
• Distinguish between unary, binary, and ternary
  relationships, and give an example of each

3
Learning Objectives

• Learn how to identify entities from a simple
  narrative
• Learn how to identify entity relationships using
  an Entity Relationship Grid
• Draw ERD diagrams

4
Entity Relationship Diagrams (ERDs)

• A data modelling technique for documenting the
  data requirements of system under investigation
• Diagrammatically represents the business
  relationships amongst organisational data
• Aids communication across a variety of users and
  stakeholders

5
ERD Components Entities

• Entity
• An entity is a business object or thing we want
  to store and process information about.
• Entities can be grouped into one of 5 classes
  persons, places, objects, events or concepts
• Entity attributes contain the specific
  information required for each entity
• Nouns refer to entities
• Entity Occurrences
• Each set of values for the attributes represents
  an occurrence of an entity (a single record/row
  in database table)

6
Student Activity 1

• In groups of two or three
• Give two examples of each of the 5 different
  types of entity (persons, places, objects, events
  and concepts). Each example should relate to
  either your role with the company or something
  within your workplace

7
Attributes

• We need to identify what specific pieces of data
  we want to store about each instance of a given
  entity
• An attribute is a descriptive property or
  characteristic of an entity
• e.g. For entity STUDENT we have such attributes
  as NAME, ADDRESS, DATE OF BIRTH etc
• Synonyms element, property or field
• A compound attribute is actually one that
  consists of other attributes
• e.g. for entity STUDENT, a students NAME is
  actually a compound attribute that consists of
  FIRST NAME, SURNAME and MIDDLE INITIAL
• Synonyms composite attribute, concatenated
  attribute and data structure

8
Keys as Identifiers

• An entity has many instances, perhaps 1,000s or
  even 1,000,000s!
• A key is an attribute, or group of attributes
  that assumes a unique value for each entity
  instance
• e.g. Identify each instance of EMPLOYEE by the
  EMPLOYEE NUMBER attribute
• A group of attributes that uniquely identify an
  instance of an entity an compound key
• Synonyms concatenated key and composite key
• For each TAPE entity instance in a video store
  might be uniquely identified by the concatenation
  of TITLE NUMBER plus COPY NUMBER
• We need both pieces of data to identify a
  specific tape e.g. copy 3 of The Minority Report

9
Multiple Keys

• Frequently, an entity may have more than one key
• e.g. The entity STUDENT may be uniquely
  identified by NATIONAL INSURANCE NUMBER or
  university assigned STUDENT NUMBER, EMAIL ADDRESS
  or LIBRARY NUMBER
• Each of these attributes is called a candidate
  key
• A candidate key is a candidate to become a
  primary key of instances of an entity
• Could be a single attribute or a composite key
• A primary key is that candidate key that will
  most commonly be used to uniquely identify a
  single entity instance - default value is always
  NOT NULL

10
Representations of an Entity with Attributes
Module Enrolment(Student Number, Module Code,
Grade Point, Result Code)
11
Student Activity 2

• In groups of two or three
• Write down a table definition of some entity
  (make up attributes) showing the primary key
  try and relate it to any aspect of your work

12
ERD Components Relationships

• Depicts business relationship between entities
  (different from business to business)
• Relationship types
• one-to-many (1n or n1)
• one-to-one (11)
• many-to-many (mn)
• Link entities
• Optionality
• Exclusive relationships
• Multiple relationships

• Number of entity instances participating in a
  relationship is referred to as the cardinality of
  the relationship
• Number of entities participating in the
  relationship is referred to the degree of
  relationship

13
one-to-many (1n or n1)

• One instance of an entity is associated with many
  instances of another entity
• Crows feet at one end of relationship line
  depicts direction of 1n relationship
• n0 or more (business specific)

14
one-to-many (1n or n1)
15
Representation of Entity Relationships Tables
Foreign Keys
Student(Student Number, Student Name, Course
Code)
Course(Course Code, Course Title)
A foreign key is a primary key of one entity that
is contributed to (copied in) another entity to
identify instances of a relationship . A foreign
key (always in a child entity) always matches the
primary key (in a parent entity)
16
one-to-one (11)

• One instance of an entity is associated with one
  instance of another entity
• Rare

17
one-to-one (11)
18
Student Activity 3

• In groups of two or three
• Identify two examples of a 11 entity
  relationship and a 1n relationship again try
  and relate it to any aspect of your work

19
many-to-many (mn)

• Many instances of an entity are associated with
  many instances of another entity
• Very common when first analysing business data
• Such relationships ALWAYS imply that an entity is
  missing

20
mn Relationships Require Link Entities

• Scenario for TASK A
• A doctors practice has many doctors and a doctor
  can have appointments with a number of patients.
  A patient could have an appointment with more
  than one doctor.
• What is missing in the ERD? Hint one patient
  sees one doctor at any given time via?

21
mn Relationships Require Link Entities

• Appointment was missing!
• The two entity data groups become masters of the
  link data group
• Link (Appointment) data groups contain common
  entity data that establishes the relationship

22
mn Relationships Require Link Entities
23
Rules for Link Entities

• A many-to-many relationship MUST always be
  resolved by creating a new link entity
• The link entity is joined to each original entity
  by a one-to-many relationship with the many
  (crows feet) end at the link entity
• The half of the relationship nearest the link
  entity is ALWAYS mandatory/compulsory

24
Rules for Link Entities

• The other half of the relationship has the same
  optionality as in the original many-to-many
  relationship
• If it is difficult to name a link entity between
  entity x and entity y the just name it x/y Link
• Name both ends of the relationship

25
Student Activity 4

• In groups of two or three
• How would you resolve an mn relationship between
  EMPLOYEE and PROJECT?
• Identify another example of an mn entity
  relationship and propose a solution

26
Optionality

• Some relationships are mandatory and some are
  optional
• Rules that govern optionality are specfic to the
  business (based on business rules and policies)
• Minimum and maximum cardinality must be specified
  see last weeks notes
• For example
• e.g. A course may have one or more students
  enrolled on it but a student must be enrolled on
  a course.

27
Optionality
28
Optionality and Link Entities
29
Student Activity 5

• In groups of two or three
• Decide on a likely cardinality and membership
  class for each of the following
• Entity Types Relationship
• a) House, person ownership
• b) Sales-area, customer has assigned
• c) Employee, skill has

30
Exclusive Relationships

• An instance of an entity x can be associated with
  one or more instances of an entity y or one or
  more instances of an entity z but NOT both
  (simultaneously)

31
Recursive or Unary Relationships

• An instance of an entity is related to other
  instance(s) of the same type of entity
• A manager is a type of employee (and who manages
  other employees)

32
Multiple Relationships

• We have looked at single binary relationships
  (one relationship between 2 entities) and a unary
  relationship
• Lets now consider
• Multiple binary relationships
• Simultaneous relationships across three entities
  (ternary relationships)

33
Multiple Binary Relationships
34
Ternary Relationships
Alternative notation for Relation similar to
link entity
35
Worked Example

• Scenario
• A loan or reservation always begins with a
  borrower who may decide to either loan a DVD, or
  if it is unavailable, to make a reservation.
  Each DVD title in stock is separately categorised
  according to its subject matter and the starring
  actor(s)

36
Worked Example

• Identify entities
• A loan or reservation always begins with a
  borrower who may decide to either loan a DVD, or
  if it is unavailable, to make a reservation.
  Each DVD title in stock is separately categorised
  according to its subject matter and the starring
  actor(s)

37
Worked Example

• Identify direct entity relationships

• Must ignore indirect relationships
• Borrower and DVD entities are linked by the
  direct relationships
• Borrower/Loan and Loan/DVD

38
Worked Example
39
Summary

• Define entity relationship models and describe
  its benefits
• Recognise and understand the basic concepts and
  constructs of an entity relationship diagram
  (ERD)
• Distinguish between unary, binary, and ternary
  relationships, and give an example of each

40
Summary

• Learn how to identify entities from a simple
  narrative
• Learn how to identify entity relationships using
  an Entity Relationship Grid
• Draw ERD diagrams