Gestió de Bases de Dades

UD 2 - Entity / Relationship Model

1r CFGS Administració de sistemes informàtics i xarxes
Teacher: Bartomeu Vives
BLOCK 1 - DATABASE ENGINEERING
LESSON 1 - Introduction to databases
LESSON 2 - Logical Design: Entity-Relationship Model
LESSON 3 - Logical to Physical Design: Relational Model
BLOCK 2 - DATABASE MANAGEMENT
LESSON 4 - Physical Design: Data Definition Language
LESSON 5 - Implementation Stage: Data Management Language
BLOCK 3 - DATABASE MAINTENANCE
LESSON 6 - Implementation & Maintenance Stage: Scripting
LESSON 7 - Maintenance Stage: Redesigning the DB Model
LESSON 8 - Maintenance Stage: Triggers and Other Database Objects
LESSON 9 - Data Security Management
UD2 - Objectives
After completing this lesson, you should be able to:
◉ Place E/R Model in context of Modeling Data
◉ Identify
○ On one hand Entities, their attributes
○ On the other hand Relationships between entities
from a case of study
◉ Diagram all of them
UD2 - E/R Model
1. Introduction to E/R Diagram
a. Introduction
b. What a ERD provides ?
2. Components of a E/R Diagram
a. Entities
b. Attributes
c. Relationships
d. Domains
UD2 - Introduction to E/R Diagram - Introduction
UD2 - Introduction to E/R Diagram
ERD provides:
◉ A simple understanding way to explain how data is organized.
◉ A picture that "users" and computer scientist can easily understand.
◉ A clear and precise way to show the data required for an organization.
◉ A way to easily develop and refine the data model.
◉ A clear picture of the scope of the project.
◉ A framework for integrating multiple applications, development
projects, and purchased application packages.

◉ Therefore it is called Universe of Discourse at the most real approach

that the data engineer makes about the real world surrounding the data
analysis that intends to model.
UD2 - Introduction to E/R Diagram
First of all, it is highly relevant to get a clear and complete vision and a good
knowledge of the system (and the organization or enterprise ).

Lack of experience in diagraming is often a problem to perform this task.

UD2 - Introduction to E/R Diagram
The diagram for a logical data model is called an Entity/Relationship Diagram
(ERD) which is a high level (and semantic) data model to translate the
meanings and interactions of real-world of an enterprise into a conceptual
scheme:
◉ ERD is the most used system to represent a Relational Data Model.

◉ Represents a collection of basic objects (entities) we got from the

perception of a real world (Universe of Discourse), their attributes which
give us a completely description of each one and lastly the relationships
among these objects. All together are expressed graphically by an ERD.
UD2 - Components of a E/R Diagram
UD2 - Components of a E/R Diagram - Entities
An entity is ...
◉ Data that must be tracked.
◉ A "container" for people, animals, things that you can list ...

◉ An Instance (or item) of an Entity represents one element from the real
world we want to show.
UD2 - Components of a E/R Diagram - Entities
An entity is represented by …
◉ … rectangles, the entity name is written inside the rectangle (in capital
letters).
◉ Usually can MUST be named as a noun (singular or plural, but the same
criteria for the ERD).
UD2 - Components of a E/R Diagram - Entities
An Entity can represent a set of instances that are of interest to a particular
business.
Suppose this typical case of study: an ERD for a
Secondary school's website.
UD2 - Components of a E/R Diagram - Entities
Entities are a compound of items I can put together because of their
relationship between them. Below some examples ...
◉ Related to People:
○ Students, Teachers, Staff, …
○ Players, Referees, Managers
◉ Related to Places:
○ Banks, Secondary Schools, Stadiums, Departments, Areas, Zones
○ Countries, States, Provinces, Cities or Villages, Streets, Postal code
zones
◉ Related to Intangible items:
○ Customers, Suppliers, Invoices, Payments, Charges, Services,
Prices, Categories of something (Type of …),
UD2 - Components of a E/R Diagram - Attributes
Attributes are ...
◉ Describe an entity and are the specific information that must be known.
◉ The name of the attribute should not include the name of the entity
because attributes describe the entity.
◉ MUST be named as a singular noun.
UD2 - Components of a E/R Diagram - Attributes
Key Attributes are …
◉ ... defined to identify easily any instance (item) in an entity.

◉ Instances of an Entity can be identified by one or more attributes.

UD2 - Components of a E/R Diagram - Attributes
Multivalued attributes are ...
There may be instances where an attribute has a set of values for a
specific entity.

Examples:
◉ address: consisting of street name, number, postal code, city ...
◉ surnames: 1st surname, 2nd surname
◉ movie-produced-by: co-produced by several produced films.
UD2 - Components of a E/R Diagram - Attributes
Derived (or calculated) attributes are ...
The value for this type of attribute can be derived from the values of other
related attributes or entities.

Example:
◉ suppose that a student entity has an attribute named "age", which
indicates the student’s age. If the student entity also has an attribute
"date-of-birth", we can calculate age from date-of-birth and the current
date. Thus, age is a derived attribute. Somehow, take into account that
"date-of-birth" can provide more information than "age".
UD2 - Components of a E/R Diagram - Attributes
Repetitive attributes are ...
A limited number of attributes representing

Examples:
◉ phone-number: anybody may have zero, one or several phone
numbers: Telephone1, Telephone2, Telephone3, ...
◉ movie-produced-by: co-produced by several produced films:
Producer1, Producer2, Producer3, …
◉ A list of skills for a Person: Skill1, Skill2, Skill3, ...
UD2 - Components of a E/R Diagram - Attributes
Multivalued, Derived and Repetitive attributes must ...
… not be present at the end of Designing phase, hence should be cleared up
from Physical Design.

This question will be treated later.

UD2 - Components of a E/R Diagram - Attributes
Optional attributes are ...
… used when:
◉ the value of an attribute for a certain entity is unknown,
◉ does not have any applicable value for the attribute,
◉ Otherwise, they are mandatory.

Examples:
◉ phone-number: unknown value at this moment
◉ video-rent-data: null value when video is saled
UD2 - Components of a E/R Diagram - Attributes
Attributes are represented by ...
… an ellipse in lower letters and a line (solid for mandatory attribute, and
broken for non) which link attributes to entity.

◉ Key attributes are represented by underline.

◉ Multivalued and Repetitive attributes are represented by double
ellipses.
◉ Derived attributes are represented by dashed ellipses.
◉ Optional attributes are shown with a broken line linked to its entity.
UD2 - Components of a E/R Diagram - Attributes
UD2 - Components of a E/R Diagram - Attributes
UD2 - Components of a E/R Diagram - Attributes
UD2 - Components of a E/R Diagram - Attributes
Attributes are also represented by ...
Attributes can be shown within the entity box.

◉ Attributes are either:

○ Key Attribute, indicated by a #
○ Mandatory, indicated by *
○ Optional, indicated by o
○ Multivalued, Derived and Repetitive attributes do not have a
specific representation
UD2 - Components of a E/R Diagram - Attributes
UD2 - Components of a E/R Diagram - Relationships
Relationships are ...
… associations, links or correspondences between instances of entities
related in some way in the real world.

Must be translate as "interrelació" (cat) or "interrelación" (cast).

UD2 - Components of a E/R Diagram - Relationships
Relationships have ...
◉ Name: which provides an action to the relationship, therefore
relationships are written as verbs.
◉ Grade: number of entities related in a relationship.
○ Reflexive: 1 entity is related on a relationship,
○ Binary: most commonly used, 2 entities are related on a relationship
○ Ternary: 3 entities are related
and may have ...
◉ Attribute/s: particularize the way in which a relationship between the
instances are related.
UD2 - Components of a E/R Diagram - Relationships
Relationships also have ...
◉ Cardinality: is the number of occurrences in one entity which are
associated (or linked) to the number of occurrences in another. There are
three degrees of relationship, known as:
○ one-to-one (1 : 1)
○ one-to-many (1 : M)
○ many-to-many (M : N)
UD2 - Components of a E/R Diagram - Relationships
UD2 - Components of a E/R Diagram - Relationships
UD2 - Components of a E/R Diagram - Relationships
Relationships also have ...
◉ Mandatory / Optionality: a cardinality in a relationship may also be
optional or mandatory. Either end of the relationship can include zero
occurrences as an option or at least one as mandatory.

An employee can be
A company can have
related to a Company or to
Employees related or have
be related to no one
no one.
Company.
Common usage for a 1 : N
Uncommon usage for a 1 :
relationship.
N relationship.
UD2 - Components of a E/R Diagram - Relationships
Strong and weak entities are ...
◉ Strong entity: is the one whose existence does not depend on the
existence of any other entity in a schema. A strong entity always has the
primary key in the set of attributes that describes the strong entity.

◉ Weak entity: is the one whose existence that depends on a strong entity
for its existence. The primary key of a weak entity is a composite key
formed from the primary key of the strong entity and partial key of the
weak entity.
UD2 - Components of a E/R Diagram - Relationships
Relationships also have ...
◉ Transferable: related to relationships 1 : N and strong / weak entities.

→ Strong entity

→ Weak entity
UD2 - Components of a E/R Diagram - Relationships
Relationships are represented by ...
There are many representations of an ERD. Below are some examples:
◉ Standard notation
◉ Depending on the author:
○ Chen notation,
○ Bachman notation,
○ Martin notation,
○ Barker notation.
 UD2 - Components of a E/R Diagram - Relationships
Standard notation
UD2 - Components of a E/R Diagram - Relationships
Chen notation
UD2 - Components of a E/R Diagram - Relationships
Bachman notation
UD2 - Components of a E/R Diagram - Relationships
Martin notation
UD2 - Components of a E/R Diagram - Relationships
The notation we will use (Barker Notation) ...
UD2 - Components of a E/R Diagram - Domains
Domains are ...
… a set of values which an attribute can derive from. Each simple attribute is
associated with a domain, which specifies its valid values.

A domain can be defined from differents ways:

◉ Extension: defined from an assertion
◉ Intention: defined from the possible values it can take

Examples:
◉ NAMES: a character string of up to 30 chars.
◉ DESCRIPTIONS: a character string of up to 100 chars.
◉ PHONE-NUMBERS: a character string of up to 9 numbers.
◉ GENDERS: a character string of 1 char: M → male, F → female.
UD2 - Components of a E/R Diagram - Domains
Domains are represented by ...
It is not used to represent the relationship between domains and their
attributes.

◉ In any case, it can be represented by a table.

◉ Domain names are plural and shown in uppercase.
 UD2 - Components of a E/R Diagram - Domains

DOMAIN ATTRIBUTES

NAMES name, first-name, last-name

DESCRIPTIONS full-name, subject-name, country-description,

department-description, ...

... ...
Recursos de la UD

● ACT2.6 - ERD of an Academy

Additional resources ● ACT2.7 - ERD of a Hospital
● Oracle Data Modeler - User's guide, Learn more ... ● ACT2.8 - ERD of an Academy
○ Tutorial: Del modelo lógico al modelo Físico ● ACT2.9 - ERD of Film Festivals
● SQL Workbench - EER Diagram Editor ● ACT2.10 - Reverse ERD
● Enterprise Architect - Enterprise Architect ● ACT2.11 - Practice test UD2
● MS PROJECT ● ACT2.12 - ERD of a Chess Championship
● ACT2.13 - ERD of Energy Treatment
Assignments ● ACT2.14 - ERD of The Management of a
Tournament
● ACT2.1 - ERD Kick off
● ACT2.2 - ERD - Entities, their Attributes and Relationships
● ACT2.3 - ERD - Multiple Relationships
● ACT2.4 - ERD of a Library
● ACT2.5 - ERD of a Geography Location
Bibliografia
◉ Smartdraw; Entity Relationship Diagram;
○ Cardinalidades ternarias
Sou lliure de:
Compartir — copiar i redistribuir el material en qualsevol mitjà i format
Adaptar — remesclar, transformar i crear a partir del material

El llicenciador no pot revocar aquestes llibertats, sempre que seguiu els termes de la llicència.

Amb els termes següents:

Reconeixement — Heu de reconèixer l'autoria de manera apropiada, proporcionar un enllaç a la llicència i

indicar si heu fet algun canvi. Podeu fer-ho de qualsevol manera raonable, però no d'una manera que
suggereixi que el llicenciador us dóna suport o patrocina l'ús que en feu.
NoComercial — No podeu utilitzar el material per a finalitats comercials.
CompartirIgual — Si remescleu, transformeu o creeu a partir del material, heu de difondre les vostres
creacions amb la mateixa llicència que l'obra original.

No hi ha cap restricció addicional — No podeu aplicar termes legals ni mesures tecnològiques que
restringeixin legalment a altres de fer qualsevol cosa que la llicència permet

https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode