ENTITY RELATIONSHIP MODELING

PRESENTATION
 In relation to a database, an entity is a single
person, place, or thing about which data can be
stored. In data modelling (a first step in the
creation of a database), an entity is some unit of
data that can be classified and have stated
relationships to other entities. An entity is an object
in the real world with an independent existence and
can be differentiated from other objects.
 An entity might be an object with physical
existence. E.g. a lecturer, a student or a car.
An object with conceptual existence. E.g. a
course, a job, a position.
An Entity Set is a collection of entities of
an entity type at a point of time.
An Entity Type defines a collection of
similar entities.
In ER diagrams, an entity type is represented
by a name in a box
Types of Entities
Independent entities,
also referred to as Kernels, are the backbone of
the database. It is what other tables are based
on. Kernels have the following characteristics:
 they are the ‘building blocks’ of a database

 the primary key may be simple or composite

 the primary key is not a foreign key

 they do not depend on another entity for their

existence. Example: Customer table,

Employee table, Product table.
Dependent Entities
also referred to as derived, they depend on
other tables for their meaning. Dependent
entities are used to connect two kernels
together. They are said to be existent
dependent on two or more tables. Many to
many relationships become associative tables
with at least two foreign keys. They may
contain other attributes. The foreign key
identifies each associated table.
DOMAIN
Is the set of possible values for a given attribute.
In data management and database analysis, a
data domain refers to all the values which a data
element may contain. The rule for determining
the domain boundary may be as simple as a data
type with an enumerated list of values.

.
EXISTENCE DEPENDENCY
An entity is said to be existence-dependent if it can exist
in the database only when it is associated with another
related entity occurrence. In implementation terms, an
entity is existence-dependent if it has a mandatory
foreign key—that is, a foreign key attribute that cannot
be null. For example, if an employee wants to claim one
or more dependents for tax-withholding purposes, the
relationship “EMPLOYEE claims DEPENDENT” would be
appropriate. In that case, the DEPENDENT entity is clearly
existence-dependent on the EMPLOYEE entity because it
is impossible for the dependent to exist apart from the
EMPLOYEE in the database.
ATTRIBUTES
 An attribute is a characteristic. In a database management
system (DBMS), an attribute refers to a database
component, such as a table. It also may refer to a database
field. Attributes describe the instances in the row of a
database.
Each entity is described by a set of attributes. E.g.
Employee = (Name, Address, Age, Salary).
Each attribute has a name, associated with an entity and is
associated with a domain of legal values. However the
information about attribute domain is not presented on the
ER diagram.
In the diagram, each attribute is represented by an oval
with a name inside and are connected to the entity
rectangle with a line
DIAGRAM
Types Of Attributes
SIMPLE ATTRIBUTES
are attributes that are drawn from the atomic value
domains
e.g. Name = {John} ; Age = {23} , also called Single
valued.
Single-Valued Attributes
A single-valued attribute is an attribute that can
have only a single value. For example, a person
can have only one Social Security number, and a
manufactured part can have only one serial
number. Keep in mind that a single-valued attribute
is not necessarily a simple attribute. For instance, a
part’s serial number, such as SE-08-02-189935, is
single-valued, but it is a composite attribute
because it can be subdivided into the region in
which the part was produced (SE), the plant within
that region (08), the shift within the plant (02), and
the part number (189935).
Composite attributes:
Attributes that consist of a hierarchy of attributes
e.g. Address may consists of Number, Street and Suburb → Address =
{59 + ‘Meek Street’ + ‘Kingsford’}. Composite attributes can be
divided into smaller subparts. These subparts represent the basic
attributes with independent meanings of their own. For example, Name
attributes can be divided it into sub-parts like Firstname, Middlename,
and Lastname.
Composite diagram
Derived attributes:
Attributes contain values that are calculated from other attributes
e.g. Age can be derived from attribute Date Of Birth. In this situation,
Date Of Birth might be called Stored Attribute. A derived attribute is
an attribute whose value is calculated (derived) from other attributes.
The derived attribute need not be physically stored within the
database; instead, it can be derived by using an algorithm.
Multivalued attributes:
Attributes that have a set of values for each entity
e.g. Degrees of a person: ‘ BSc’ , ‘MIT’, ‘PhD’
Keys

 An important constraint on the entities is

the key. The key is an attribute or a group
of attributes whose values can be used to
uniquely identify an individual entity in an
entity set.
 Candidate key

 a simple or composite key that is unique and

minimal ,unique – no two rows in a table may have the
same value at any time. The minimal set of attributes that
uniquely identifies each occurrence key of an entity type.
 A candidate key is the minimal number of attributes, whose
value(s) uniquely identify each entity occurrence. For
example, the branch number (branchNo) attribute is the
candidate key for the Branch entity type, and has a distinct
value for each branch entity occurrence. The candidate key
must hold values that are unique for every occurrence of an
entity type. This implies that a candidate key cannot
contain a null (see Section 3.2). For example, each branch
has a unique branch number (for example, B003), and there
will never be more than one branch with the same branch
number
 PRIMARY KEYS
A primary key, also called a primary keyword or unique identifier, is
a key in a relational database that is unique for each record. It is
unique such as a driver license number, telephone number
(including area code), or vehicle identification number (VIN). A
relational database must always have one and only one primary
key.

A candidate key is selected by the designer to uniquely identify

tuples in a table. It must not be null.
A key is chosen by the database designer to be used as an
identifying mechanism for the whole entity set. This is referred to
as the primary key. This key is indicated by underlining the
attribute in the ER model.
For example Employee(EID, First Name, Last Name, SIN, Address,
Phone, BirthDate, Salary, DepartmentID) – EID is the Primary key.
COMPOSITE PRIMARY KEYS
A composite key, in the context of relational databases, is a
combination of two or more columns in a table that can be used to
uniquely identify each row in the table. Uniqueness is only
guaranteed when the columns are combined; when taken individually
the columns do not guarantee uniqueness. Composed of more than
one attribute

For example
First Name and Last Name – assuming there is no one else in the
company with the same name, Last Name and Department ID –
assuming two people with the same last name don’t work in the
same department.
A composite key can have two or more attributes, but it must be
minimal.
Secondary key

 an attribute used strictly for retrieval purposes (can be

composite), for example: Phone number, Last Name and
Phone number, etc.
 all other candidate keys not chosen as the primary key
 An attribute in one table that references the primary key of
another table OR it can be null.
 Both foreign and primary keys must be of the same data type
 For example: Employee(EID, First Name, Last Name, SIN,
Address, Phone, BirthDate, Salary, DepartmentID) –
DepartmentID is the Foreign key.
MULTIPLICITY
The number (or range) of possible occurrences of an entity type that
may relate to a single occurrence of an associated entity type
through a particular relationship.
Multiplicity constrains the way that entities are related. It is a
representation of the policies (or business rules) established by the
user or enterprise. Ensuring that all appropriate constraints are
identified and represented is an important part of modelling an
enterprise. As we mentioned earlier, the most common degree for
relationships is binary. Binary relationships are generally referred to
as being one-to-one (1:1), one-to-many (1:*), or many-to-many (*:*).
RELATIONSHIP TYPE
 The concept of relationship strength is based on how the
primary key of a related entity is defined. To implement a
relationship, the primary key of one entity appears as a
foreign key in the related entity. For example, the 1:M
relationship between VENDOR and PRODUCT in Chapter
3, Figure 3.3, is implemented by using the VEND_CODE
primary key in VENDOR as a foreign key in PRODUCT.
There are times when the foreign key also is a primary
key component in the related entity. For example, in
Figure 4.5, the CAR entity primary key (CAR_VIN) appears
as both a primary key component and a foreign key in the
CAR_COLOR entity. In this section, you will learn how
various relationship strength decisions affect primary key
arrangement in database design.
Weak entity
An entity type that is existence-dependent on some other entity
type. A weak entity type is dependent on the existence of another
entity type. Each entity occurrence cannot be uniquely identified
using only the attributes associated with that entity type. For
example, note that there is no primary key for the Preference entity.
This means that we cannot identify each occurrence of the Preference
entity type using only the attributes of this entity. We can only
uniquely identify each preference through the relationship that a
preference has with a client who is uniquely identifiable using the
primary key for the Client entity type, namely clientNo. In this
example, the Preference entity is described as having existence
dependency for the Client entity, which is referred to as being the
owner entity. Weak entity types are sometimes referred to as child,
dependent, or subordinate entities and strong entity types as parent,
owner, or dominant entities.
ACTIONS
 Which are represented by diamond shapes,
show how two entities share information in
the database. In some cases, entities can be
self-linked. For example, nurses can
supervise other nurses.
Cardinality
 specifies how many instances of an entity
relate to one instance of another entity.
Ordinality is also closely linked to
cardinality. While cardinality specifies the
occurrences of a relationship, ordinality
describes the relationship as either
mandatory or optional. In other words,
cardinality specifies the maximum number
of relationships and ordinality specifies the
absolute minimum number of relationships.
ONE TO MANY
 One department has many employees
1:M relationship Digram

birthday
name
SALARY

name

EMPLOYEE
worksin department office
address

ID IDdep
1:1 Relationship
One entity can be related to only one other
entity, and vice versa. For example, one
department chair—a professor—can chair
only one department, and one department
can have only one department chair. The
entities PROFESSOR and DEPARTMENT thus
exhibit a 1:1 relationship
PROFFESSOR CHAIR DEPARTMENT
S
M:N relationships

Consider a rather typical college environment

in which each STUDENT can take many
CLASSES, and each CLASS can contain many
STUDENTs.

STUDENT Has CLASS

 An entity-relationship diagram (ERD) is a
graphical representation of an information
system that shows the relationship between
people, objects, places, concepts or events
within that system. An ERD is a data modeling
technique that can help define business
processes and can be used as the foundation
for a relational database.
 The components of an ERD are the entities,
which are objects or concepts that can have
data stored about them, the relationship
between those entities, and the cardinality,
which defines that relationship in terms of
numbers, attributes and the connecting lines.
Relationship Participation

Relationship participation is either optional

or mandatory.
 Optional participation means that one

entity occurrence does not require a

corresponding entity occurrence.
 Mandatory participation means that one

entity occurrence does require a

corresponding entity occurrence.
Relationship Degree
A relationship degree indicates the number of
entities or participants associated with a
relationship.
 Unary Relationships – One entity Also known as

a recursive relationship. One employee

manages another employee, one person is
married to another person, etc.
 Binary Relationships – Two entities This is the

most common relationship and most higher

order relationships are normally decomposed
into appropriate equivalent binary relationships.
 Ternary and Higher Order Relationships – Three

entities or more
RECURSIVE
RELATIONSHIPS
A relationship type where the same entity type participates more
relationship than once in different roles.
 Consider a recursive relationship called Supervises, which represents

an association of staff with a Supervisor where the Supervisor is also a

member of staff. In other words, the Staff entity type participates twice
in the Supervises relationship; the first participation as a Supervisor,
and the second participation as a member of staff who is supervised
(Supervisee). Recursive relationships are sometimes called unary
relationships. Relationships may be given role names to indicate the
purpose that each participating entity type plays in a relationship. Role
names can be important for recursive relationships to determine the
function of each participant. The use of role names to describe the
Supervises recursive relationship. The first participation of the Staff
entity type in the Supervises relationship is given the role name
‘Supervisor’ and the second participation is given the role name
‘Supervisee’.
DIAGRAM

supervises
Role
name

superviso
r

Role supervis staff

name ee
 Role names may also be used when two entities are
associated through more than one relationship. For
example, the Staff and Branch entity types are
associated through two distinct relationships called
Manages and Has. As shown in Figure 11.10, the use of
role names clarifies the purpose of each relationship. For
example, in the case of Staff Manages Branch, a member
of staff (Staff entity) given the role name ‘Manager’
manages a branch (Branch entity) given the role name
‘Branch Office’. Similarly, for Branch Has Staff, a branch,
given the role name ‘Branch Office’ has staff given the
role name ‘Member of Staff’. Role names are usually not
required if the function of the participating entities in a
relationship is unambiguous.
THANK

YOU

ALL