ENTITY-RELATIONAL MODELLING
CS220 - DATABASE SYSTEMS
Slides adopted from
Dr. Shams Qazi
CONCEPTUAL DATABASE DESIGN
Conceptual database design involves modeling the collected
information at a high-level of abstraction without using a
particular data model or DBMS.
Since conceptual database design occurs independently from a
particular DBMS or data model, we need high-level modeling
languages to perform conceptual design.
The entity-relationship (ER) model was originally proposed by
Peter Chen in 1976 for conceptual design. We will perform ER
modeling using Unified Modeling Language (UML) syntax.
ENTITY-RELATIONSHIP MODELLING
Entity-relationship modeling is a top-down approach to database design that
models the data as entities, attributes, and relationships.
The ER model refines entities and relationships by including properties of
entities and relationships called attributes, and by defining constraints on
entities, relationships, and attributes.
The ER model conveys knowledge at a high-level (conceptual level) which
is suitable for interaction with technical and non- technical users.
Since the ER model is data model independent, it can later be converted into
the desired logical model (e.g. relational model).
EXAMPLE RELATION INSTANCES
Emp Relation WorksOn Relation
eno ename bdate title salary supereno dno eno pno resp hours
E1 J. Doe 01-05-75 EE 30000 E2 null E1 P1 Manager 12
E2 M. Smith 06-04-66 SA 50000 E5 D3 E2 P1 Analyst 24
E3 A. Lee 07-05-66 ME 40000 E7 D2 E2 P2 Analyst 6
E4 J. Miller 09-01-50 PR 20000 E6 D3 E3 P3 Consultant 10
E5 B. Casey 12-25-71 SA 50000 E8 D3 E3 P4 Engineer 48
E6 L. Chu 11-30-65 EE 30000 E7 D2 E4 P2 Programmer 18
E7 R. Davis 09-08-77 ME 40000 E8 D1 E5 P2 Manager 24
E8 J. Jones 10-11-72 SA 50000 null D1 E6 P4 Manager 48
E7 P3 Engineer 36
Proj Relation Dept Relation
pno p nam e b ud get dno dno dname mgreno
P1 Instruments 150000 D1 D1 Management E8
P2 DB Develop 135000 D2 D2 Consulting E7
P3 Budget 250000 D3 D3 Accounting E5
P4 Maintenance 310000 D2 D4 Development null
P5 CAD/CAM 500000 D2
ER MODELEXAMPLE IN UML NOTATION
Supervises
0..1
Supervisor Manages
0..* Employee Department
0..1 0..*
Supervisee number {PK} Has number {PK}
name name
0..* 0..1
address
0..1
state Has
city
street 0..*
title
salary WorksOn Project
0..* 0..* number {PK}
name budget
location [1..3]
/totalEmp
responsibility
hours
ENTITY TYPES
An entity type is a group of objects with the same properties which are
identified as having a n independent existence.
▶ A n entity type is the basic c o n c e p t of the ER model a n d represents a group of
real-world objects that have properties.
▶ Note that an entity type does not always have to b e a physical real-world
object such as a person or department, it c a n b e an abstract c on c e pt such as a
project or job.
▶ An entity instance is a particular example or occurrence of an entity type.
▶For example, a n entity type is Employee. An entity instance is 'E1 - John Doe’.
▶ An entity set is a set of entity instances.
REPRESENTING ENTITY TYPES
Entity types are represented by rectangles with the name of the entity
type in the rectangle.
Examples:
Project Department
An entity type name is normally a singular noun.
That is, use Person instead of People, Project instead of Projects, etc.
The first letter of each word in the entity name is capitalized.
ENTITIES QUESTION
Question: How many of the following statements are true?
1) Entity types are represented using a rectangle box.
2) An entity is always a physical object.
3) An entity type is named using a plural noun.
4) Employee number is an entity.
A) 0 B) 1 C) 2 D) 3 E) 4
RELATIONSHIP TYPES
A relationship type is a set of associations among entity types.
Each relationship type has a name that describes its function.
A relationship instance is a particular occurrence of a
relationship type that relates entity instances.
For example, WorksOn is a relationship type. A
relationship instance is
that 'E1' works on project 'P1' or (E1,P1).
A relationship set is a set of relationship instances.
There can be more than one relationship between two entity types.
VISUALIZING RELATIONSHIPS
Employee WorksOn Project
E1 r1 P1
E2 r2
r3 P2
E3
r4
E4
r5 P3
E5
r6
E6 P4
r7
E7 r8
E8 P5
r9
Note: This is an example of a many-to-many relationship. A project can have more than one
employee, and an employee can work on more than one project.
REPRESENTING RELATIONSHIP
TYPES
The relationship type is represented as a labeled edge between the
two entity types. The label is applied only in one
direction, so an arrow indicates the correct way to read it.
Employee WorksOn Project
A relationship type name is normally a verb or verb phrase.
The first letter of each word in the name is capitalized.
Do not put arrows on either end of the line.
RELATIONSHIP DEGREE
The degree of a relationship type is the number of entity
types participating in the relationship.
For example, WorksOn is a relationship type of degree two as the
two participating entity types are Employee and Project.
Note: This is not the same as degree of a relation which was the number of
attributes in a relation.
Relationships of degree two are binary, of degree three are
ternary, and of degree four are quaternary.
Relationships of arbitrary degree N are called n-ary.
Use a diamond to represent relationships of degree higher than two.
TERNARY RELATIONSHIP TYPE
EXAMPLE
Supplier Project
Provides
Part
A project may require a part from multiple different
RECURSIVE RELATIONSHIPS
A recursive relationship is a relationship type where the same entity type
participates more than once in different roles.
Forexample, an employee has a supervisor. The supervisor is also an
employee. Each role has a role name.
Supervises
Example:
Supervisor
Employee
Supervisee
Note that the degree of a recursive relationship is two as the same entity
type participates twice in the relationship.
It is possible for an entity type to be in a relationship more than twice.
RELATIONSHIP QUESTION
Question: How many of the following statements are true?
1) Relationships are represented using a directed edge (with
arrows).
2) A relationship is typically named using a verb.
3) It is not possible to have a relationship of degree 1.
4) The degree of a relationship is the number of attributes it has.
5) A diamond is used to represent a relationship of degree larger than
2.
A) 0 B) 1 C) 2 D) 3 E) 4
ATTRIBUTES
An attribute is a property of an entity or a relationship type.
For example, entity type Employee has attributes name, salary,
title, etc.
Some rules:
By convention, attribute names begin with a lower case letter.
Each attribute has a domain which is the set of allowable
values for the attribute.
Different attributes may share the same domain, but a single
attribute may have only one domain.
SIMPLE AND COMPLEX ATTRIBUTES
An attribute is a simple attribute if it contains a single
component with an independent existence.
For example, salary is a simple attribute.
Simple attributes are often called atomic attributes.
An attribute is a composite attribute if it consists of multiple
components each with an independent existence.
For example, address is a complex attribute because it consists
of street, city, and state components (subattributes).
Question: Is the name attribute of Employee simple or
complex?
SINGLE AND MULTI-VALUED
ATTRIBUTES
An attribute is a single-valued attribute if it consists of a
single value for each entity instance.
For example, salary is a single-valued attribute.
An attribute is a multi-valued attribute if it may have multiple
values for a single entity instance.
For example, a telephone number attribute for a person may be
multivalued as people may have different phone numbers (home
phone number, cell phone number, etc.)
A derived attribute is an attribute whose value is calculated
from other attributes but is not physically stored.
The calculation may involve attributes within the entity type of
the derived attribute and attributes in other entity types.
ATTRIBUTE QUESTION
Question: How many of the following statements are true?
1) Attributes are properties of either entities or relationships.
2) An attribute may be multi-valued.
3) A composite attribute contains two or more components.
4) Each attribute has a domain representing its data type.
5) A derived attribute is physically stored in the database.
A) 0 B) 1 C) 2 D) 3 E) 4
KEYS
A candidate key is a minimal set of attributes that uniquely identifies each
instance of an entity type.
For example, the number attribute uniquely identifies an Employee and is a
candidate key for the Employee entity type.
A primary key is a candidate key that is selected to identify each instance of
an entity type.
The primary key is chosen from a set of candidate keys. For
instance, an employee may also have SSN as an attribute. The primary key may
be either SSN or number as both are candidate keys.
A composite key is a key that consists of two or more attributes.
For example, a course is uniquely identified only by the department
code (COSC) and the course number within the department (304).
KEY QUESTION
Question: How many of the following statements are true?
1) It is possible to have two candidate keys with different
numbers of attributes.
2) A composite key has more than 1 attribute.
3) The computer picks the primary key used in the design.
4) A relationship has a primary key.
5) An attribute has a primary key.
A) 0 B) 1 C) 2 D) 3 E)
4
