Transcript Data Modelling – ERD`s

Data Modelling – ERD
Entity Relationship Diagram’s
Entity Relationship Diagrams
and how to create them.
1
What is an Entity Relationship
Diagram?

Drawing technique used to analyse the
logical structure of an organisations
information (e.g. database).

An entity is an object or concept about
which the system needs to hold
information eg ..............................................

Each entity must be involved in at least
one relationship, and may be involved in
more than one relationship.
2
Relationships



What information will be kept in the system?
The entities and their attributes.
Eg of attributes .............................................
For example, how do we represent a
relationship between a book copy and a
borrower in a library?
Do we wish to say:
• “A book copy may be borrowed by only one
borrower” or
• “A book copy may be borrowed by one or more
borrowers” ?
3
Entity Relationship Diagram’s

Pairs of entities are linked.

All entities in the system relate directly or
indirectly with all the others.

How many relationships may exist
between linked entities?

This consideration is called multiplicity or
cardinality.
4
Types of Relationships
There are three ways that entities can be
linked together:

One-to-one

One-to-many

Many-to-many
5
One-to-One Relationship

One occurrence of an entity ‘A’ is related
to one and only one occurrence of the
entity ‘B’ and

One occurrence of an entity ‘B’ is related
to one and only one occurrence of the
entity ‘A’
6
One-to-One continued

There are two link phrases used on the relationship.

Use the phrase ‘one and only one’ rather than
‘one’ to emphasise the cardinality existing at the
both ends of this relationship.

Consider joining the two entities.

We might have one entity called School that would
include all the attributes of the Head Teacher.

This would have the advantage of reducing the
number of entities.

But it might make the model produced less clear.
7
One-to-Many Relationship

One occurrence of an entity ‘A’ is linked
to one or more occurrences of another
entity ‘B’ and

One occurrence of entity ‘B’ is linked to
one and only one occurrence of entity ‘A’
8
Many-to-Many Relationships

One occurrence of an entity ‘A’ is linked
to one or more occurrences of another
entity ‘B’ and

One occurrence of entity ‘B’ is linked to
one or more occurrences of entity ‘A’
9
Deciding on Relationships
Which pairs of entities have a direct
relationship?
 Looking out for verbs – ‘A stockholder of
the company owns shares in that company’
 Sometimes we might not want to include
a direct relationship between two entities
because an indirect relationship already
exists - ‘Customer buys Products’
 We may already have - ‘Customer places
Order’ and ‘Order lists Products’

10
The Problem with Many-to-Many
11
The Problem with Many-to-Many

Project Code is included in the engineer
table to provide a link between the two.

What happens if an engineer works on
two projects at the same time? We
would need more than one value in the
Project Code? (engineer ID51 works on
both project W45 and Y65).

Solution: form another table which links
the two tables together.
12
Solving Many-to-Many
 Any
engineer may be linked via an
assignment table to a number of
projects.
 Any
project may have more than one
engineer working on it.
 We
need a link entity.
 Sometimes
the name of this link
entity is difficult to work out.
13
The Solution

We now have 2 one-to-many relationships
instead of 1 many-to-many relationship.
14
Mandatory and Optional Relationships
In this case we interpret the relationship as:

A School employs one or more Teachers.

A Teacher is employed at one and only one
School.
15
Mandatory and Optional Relationships

Suppose some teachers are employed in
more than one school - they may move
around within an area and visit a number
of schools.

It is possible, that a teacher is not
currently employed - that particular
teacher would not participate in this
relationship.
16
Mandatory and Optional Syntax
A [name of entity] may / must ‘link phrase’
(one and only one) / (one or more) [name of entity]
A School must employ many Teachers.
A Teacher may be employed in one School.
17
Referential Integrity

We insist on the mandatory part of the
relationship between Order and
Customer.

We do not want an Order created which
is not related to a particular Customer.

This is known as referential integrity.

All databases support this structure.
18
Referential Integrity

When a user enters a new order it must
relate to an existing customer.

We cannot delete a customer for which
there are existing orders.

We can enter a customer without
requiring an order to be related to that
customer.
19
Attributes and Primary Keys
You will assigned data types to attributes.
 You must created primary keys for tables.
 You can then link tables together.

20
Foreign Keys





An Order may not be produced which is not
related to an existing customer. Therefore, every
time we enter a new order we must identify an
existing Customer ID, so Customer ID would be a
field on the Order table.
This is called a Foreign key as Customer ID is a
primary key on the Customer table.
Foreign keys are identified by an asterisk *
If we relaxed this condition and did not insist on
referential integrity we would be allowed to enter
no-value for this foreign key.
Wherever possible we will endeavour to use
referential integrity.
21
Entity Attribute Relationship Model






Select the entities.
Identify relationships between entities
(including decisions about mandatory and
optional relationships).
Resolve any many-to-many relationships.
List the entities with their attributes.
Identify suitable primary keys.
Place the required foreign keys in the
detail entities.
22
Example – Steps 1 to 3
1.
2.
3.
Select the entities.
Populate the entities with attributes.
Identify suitable primary keys.
23
Example – Steps 4 to 6
4. Identify
relationships between entities.
5. Resolve any many-to-many relationships.
6. Place the required foreign keys in the detail entities.
24
Things to Note
 Course ID is a foreign key on Course Offering table
and Course ID is used as part of the primary key.
 Employee Number is used as a foreign key on the
Booking table.
 Other foreign keys need to be included.
 It is now possible to answer questions such as:
 Who made this Booking?
 Which Course Offering is this booking for?
 Which Courses is a Staff Member booked to study?
25