Entity-Relationship Diagram Flashcards
is a design tool
is a graphical representation of the database system
provides a high-level conceptual data model
supports the user’s perception of the data
is DBMS and hardware independent
had many variants
is composed of entities, attributes, and relationships
Entity Relationship (ER) modelling
An entity is any object in the system that we want to model and store information about
Individual objects are called entities
Groups of the same type of objects are called entity types or entity sets
Entities are represented by rectangles (either with round or square corners)
Entities
All the data relating to an entity is held in its attributes.
An attribute is a property of an entity.
Each attribute can have any value from its domain.
Each entity within an entity type:
o May have any number of attributes.o Can have different attribute values than that in any other entity.
o Have the same number of attributes.
Attributes can be
simple or composite
single-valued or multi-valued
Attributes can be shown on ER models
They appear inside ovals and are attached to their entity.
Note that entity types can have a large number of attributes… If all are shown then the diagrams would
be confusing. Only show an attribute if it adds information to the ER diagram, or clarifies a point.
Attribute
is a data item that allows us to uniquely identify individual occurrences or an entity type.
A candidate key is an attribute or set of attributes that uniquely identifies individual occurrences or an
entity type.
An entity type may have one or more possible candidate keys, the one which is selected is known as the
primary key.
A composite key is a candidate key that consists of two or more attributes
The name of each primary key attribute is underlined.
Keys
A relationship type is a meaningful association between entity types
A relationship is an association of entities where the association includes one entity from each
participating entity type.
Relationship types are represented on the ER diagram by a series of lines.
As always, there are many notations in use today…
In the original Chen notation, the relationship is placed inside a diamond, e.g. managers manage
employees:
Figure: Chens notation for relationships
For this module, we will use an alternative notation, the Crow’s Foot Notation where the relationship is
a label on the line. The meaning is identical
Relationships
The number of participating entities in a relationship is known as the degree of the relationship.
If there are two entity types involved it is a binary relationship type
Figure: Binary Relationships
If there are three entity types involved it is a ternary relationship type
Figure: Ternary relationship
It is possible to have a n-ary relationship (e.g. quaternary or unary).
Unary relationships are also known as a recursive relationship.
Figure: Recursive relationship
It is a relationship where the same entity participates more than once in different roles.
In the example above we are saying that employees are managed by employees.
If we wanted more information about who manages whom, we could introduce a second entity type
called manager.
Degree of a Relationship
When ternary relationships occurs in an ER model they should always be removed before finishing the model.
Sometimes the relationships can be replaced by a series of binary relationships that link pairs of the original
ternary relationship.
Figure: A ternary relationship example
This can result in the loss of some information - It is no longer clear which sales assistant sold a customer
a particular product.
Try replacing the ternary relationship with an entity type and a set of binary relationships.
Relationships are usually verbs, so name the new entity type by the relationship verb rewritten as a noun.
The relationship sells can become the entity type sale.
Figure: Replacing a ternary relationship
So a sales assistant can be linked to a specific customer and both of them to the sale of a particular
product.
This process also works for higher order relationships.
Replacing ternary relationships
Relationships are rarely one-to-one
For example, a manager usually manages more than one employee
This is described by the cardinality of the relationship, for which there are four possible categories.
One to one (1:1) relationship
One to many (1:m) relationship
Many to one (m:1) relationship
Many to many (m:n) relationship
On an ER diagram, if the end of a relationship is straight, it represents 1, while a “crow’s foot” end
represents many.
A one to one relationship - a man can only marry one woman, and a woman can only marry one man, so
it is a one to one (1:1) relationship
Figure: One to One relationship example
A one to many relationship - one manager manages many employees, but each employee only has one
manager, so it is a one to many (1:n) relationship
Figure: One to Many relationship example
A many to one relationship - many students study one course. They do not study more than one course,
so it is a many to one (m:1) relationship
Figure: Many to One relationship example
A many to many relationship - One lecturer teaches many students and a student is taught by many
lecturers, so it is a many to many (m:n) relationship
Figure: Many to Many relationship example
Cardinality
A relationship can be optional or mandatory.
If the relationship is mandatory
an entity at one end of the relationship must be related to an entity at the other end.
The optionality can be different at each end of the relationship
For example, a student must be on a course. This is mandatory. To the relationship student studies
course' is mandatory.
But a course can exist before any students have enrolled. Thus the relationship course is_studied_by
student’ is optional.
To show optionality, put a circle or 0' at the optional end’ of the relationship.
As the optional relationship is course is_studied_by student', and the optional part of this is the
student, then the O’ goes at the student end of the relationship connection.
Figure : Optionality example
It is important to know the optionality because you must ensure that whenever you create a new entity
it has the required mandatory links.
Optionality
Sometimes it is useful to try out various examples of entities from an ER model. One reason for this is to confirm
the correct cardinality and optionality of a relationship. We use an entity set diagram' to show entity examples
graphically. Consider the example of course is_studied_by student’.
Figure : Entity set example
Confirming Correctness
Figure : Entity set confirming errors
Use the diagram to show all possible relationship scenarios.
Go back to the requirements specification and check to see if they are allowed.
If not, then put a cross through the forbidden relationships
This allows you to show the cardinality and optionality of the relationship
Entity Sets
To check we have the correct parameters (sometimes also known as the degree) of a relationship, ask two
questions:
1. One course is studied by how many students? Answer = zero or more'.
o This gives us the degree at the student’ end.
o The answer zero or more' needs to be split into two parts.
o The more’ part means that the cardinality is many'.
o The zero’ part means that the relationship is optional'.
o If the answer was one or more’, then the relationship would be mandatory'.
2. One student studies how many courses? Answer = One’
o This gives us the degree at the course' end of the relationship.
o The answer one’ means that the cardinality of this relationship is 1, and is mandatory'
o If the answer had been zero or one’, then the cardinality of the relationship would have been 1,
and be `optional’.
Deriving the relationship parameters
Some ER diagrams end up with a relationship loop.
check to see if it is possible to break the loop without losing info
Given three entities A, B, C, where there are relations A-B, B-C, and C-A, check if it is possible to navigate
between A and C via B. If it is possible, then A-C was a redundant relationship.
Always check carefully for ways to simplify your ER diagram. It makes it easier to read the remaining
information.
A many to many relationship in an ER model is not necessarily incorrect. They can be replaced using an
intermediate entity. This should only be done where:
the m:n relationship hides an entity
the resulting ER diagram is easier to understand.
Redundant relationships
Consider the case of a car hire company. Customers hire cars, one customer hires many card and a car is hired
by many customers.
Figure : Many to Many example
The many to many relationship can be broken down to reveal a hire' entity, which contains an attribute date of
hire’.
Figure : Splitting the Many to Many example
Splitting n:m Relationships - Example
Chen’s Notation
