Lecture Outcomes

1
Lecture Outcomes

• By the end of this lecture the successful
  student will be able to
• Define ERDs, and explain the need for their use
  in during systems analysis.
• Identify the basic component of an ERD.
• Explain the concepts and components of ERDs.
• Decompose a many-to-many relationship.
• Give examples of existence conditions.

2
Entity Relationship Diagramming

• We are still in the Systems Analysis stage. The
  purpose of
• this stage is to model some part of the real
  world.
• We need to model what happens (i.e. what
  processes are
• involved in the system, what data is stored and
  processed,
• what relationships exist between data items,
  etc.). We are not
• interested in how things are done.
• The models we create therefore, are Conceptual
  Models of
• the system (i.e. describe the concept of the
  system,
• independent of implementation).

3
Entity Relationship Diagramming

• DFDs provide us with a conceptual process model
  of the
• system. They gave some consideration to the data
  (stores and
• flows). But, to determine the file structures
  needed to support
• a system, we have to examine data more closely
  and model it
• better.
• To do this we can use a top-down modelling
  technique called
• the Entity-Relationship Diagram (E-R Diagram).

4
Entity Relationship Diagramming

• When we model the data in a system, we are really
  modelling
• the database structure of the system. E-R
  diagrams help us to
• focus on three elements of data that must be
  incorporated into
• the final database Entity types, Relationship
  types, and
• Existence Conditions.
• The E-R diagram provides us with a conceptual
  model of the
• data in a system. It tells us what data should
  be stored and
• what relationships exist between items of data.
• It does not tell us how these things can be
  implemented.

5
Entity Relationship Diagramming

• A system database will be made up of files which
  are all linked to each other in some way. These
  files will have constraints or conditions on to
  how records in the database files are inserted
  and deleted.
• The E-R diagram allows us to represent
• entity types which equate to database files.
• relationship types which equate to the links
  between the database files.
• existence conditions which equate to the
  conditions or constraints imposed on the database
  files.

6
Basic Concepts

• E-R diagrams use four basic components to model
  data, namely,
• Entities
• Attributes (these are not shown on E-R diagrams,
  but you need to be aware of what they are)
• Relationships
• Existence conditions

7
Entities
An actual, real thing or person about which data
might be stored is referred to as an entity.

• An entity can be uniquely identified.
• Organisations collect and store data about
  entities
• if a bank stores data about you - you are an
  entity
• if a business stores a piece of paper called an
  invoice - the invoice is an entity
• a library stores data about a particular book -
  the book is an entity

8
Entity Types

• An organisation will not store data about just
  one entity. They will
• store data about collections of entities, which
  bear the same relation
• to the organisation.

These collections of data about entities are
called ENTITY TYPES.
A bank will store data about all the people
(entities) who do business with them and will
refer to them generically as CUSTOMER, for
example. This makes CUSTOMER an entity type with
characteristics or attributes shared by all the
people who do business with the bank.
9
Attributes

• These are the characteristics shared by all
  entities of a given
• entity type.

An attribute is a data element associated with an
entity.

• The entity CUSTOMER is likely to have attributes
  such as
• customer_name
• customer_address, etc.

10
Relationships

• Entities are associated with each other via
  relationships.
• A relationship is a named association between two
  or more entity
• types
• the relationship SCORED between the entity types
  STUDENT and MARK.
• the relationship CITIZEN-OF between the entity
  types PERSON and COUNTRY.

11
Degree of Relationships

• We can have different types or degree of
  relationships,
• one-to-one (11)
• one-to-many (1N)
• many-to-many (NM)

12
One-to-One Relationships

• The relationship HEAD-OF between entity types
  MANAGER
• and DEPARTMENT is a one-to-one relationship.
• This means that a department has at most one head
  and that a
• manager is head of at most one department.

13
One-to-Many Relationships

• The relationship SUPERVISES between the entity
  types
• MANAGER and EMPLOYEE is a one-to-many
  relationship.
• This means that a manager may supervise any
  number of
• different employees but a given employee is
  supervised by at
• most one manager (i.e. an employee will have only
  one
• supervisor at a given time).

14
Many-to-Many Relationships

• The relationship ASSIGNED-TO between the entity
  types
• EMPLOYEE and PROJECT is a many-to-many
  relationship.
• This means that an employee may be assigned to
  many
• different projects and each project may have many
  employees
• assigned to it.

15
Existence Conditions

• The choice of existence conditions are based on
  real-world facts.
• An existence condition allows us to record
• Whether an occurrence of the relationship type
  must exist for all occurrences of an entity type
  e.g. if a STUDENTS record will not be entered on
  the database until s/he has enrolled on a COURSE
  gt for every occurrence of the entity type
  STUDENT the relationship 'enrols on' must exist
  in both directions i.e. its existence is
  MANDATORY.

16
Existence Conditions

• An existence condition allows us to record
• Whether an occurrence of the relationship type
  might be optional for at least some of the
  occurrences of the entity type e.g. if the
  university maintained COURSE records but not
  necessarily STUDENT records e.g. during summer
  months before students arrived or if the
  university maintained STUDENT details before they
  were assigned to a COURSE or maintained details
  about a brand new course, then the existence
  conditions would be OPTIONAL.

17
Notation
A plain rectangle is used to represent the
entity type.
INVOICE
A labelled line to represent the relationship.
We represent the degrees of relationships as
follows
1 1
1 N
N M
18
Examples
One-to-Many ONE course has enrolled on it ONE or
MORE students. ONE student is enrolled on ONE
course.
head_of
MANAGER
DEPARTMENT
One-to-One ONE manager is head of ONE
department. ONE department has ONE head who is a
manager.
19
Handling Many-to-Many Relationships

• Relational databases cannot support this
  relationship type, so they
• are not represented on E-R diagrams.
• They do not occur often, but when they do, they
  must be removed
• and replaced with two one-to many relationships.
• This is done by introducing an extra entity type
  into the diagram.
• This extra entity type may be created by the
  analyst solely for the
• purpose of eliminating the many-to-many
  relationship. Or it may
• be some real entity type which exists, and which
  will provide the
• needed link.

20
Many-to-Many Relationship Example

• Suppose analysis, into a business which buys
  parts from suppliers,
• has shown that
• any one supplier might supply more than one kind
  of part.
• any one kind of part might be bought in from a
  number of different suppliers.

This relationship is shown as
21
Many-to-Many Relationship Example

• We must remove this many-to-many relationship
  type if we
• are going to implement it in a database. We can
  do this in the
• following way,

We must find or invent the entity type X.
22
Many-to-Many Relationship Example

• Thinking of an appropriate name for entity X
  can be
• difficult.
• In such cases it is acceptable to form it from
  the names of the
• original two entities.

23
Guidelines for Drawing an E-R Diagram

• Select likely entities.
• Select an identifier for each of the entities.
• Identify relationships between the selected
  entities.
• Sketch an E-R Diagram, adding the degree of each
  relationship.
• Decompose any NM relationships.

24
Summary

• You should now be able to
• Define ERDs, and explain the need for their use
  in during systems analysis.
• Identify the basic component of an ERD.
• Explain the concepts and components of ERDs.
• Decompose a many-to-many relationship.
• Give examples of existence conditions.