Entity Relationship Diagrams
Entity Relationship Diagrams
Taslima Ferdaus Shuva
Sr. Lecturer, CSE
Entity
•
“An
entity
is a business object that represents
a group, or category of data.”
•
Do we know a similar concept?
1) Stephens, R.K. and Plew. R.R., 2001.
Database Design
. SAMS, Indianapolis , IN.
Attribute
•
“An
attribute
is a sub-group of information
within an entity.”
•
Do we know a similar concept?
1) Stephens, R.K. and Plew. R.R., 2001.
Database Design
. SAMS, Indianapolis , IN.
Entity Relationship Models
•
Ratio from entity to entity: Cardinality
•
Many-to-Many Relationships
|m..m/n…n/*..*/<-|….|->
•
One-to-Many Relationships
|1..m/1…n/1..*/|..|->
•
One-to-One Relationships
|1..1
Overview of Database Design
•
Requirements Analysis:
Understand what data will be stored in
the database, and the operations it will be subject to.
•
Conceptual Design
:
(
ER Model
is used at this stage.)
–
What are the
entities
and
relationships
in the enterprise?
–
What information about these entities and relationships
should we store in the database?
–
What are the
integrity constraints
or
business rules
that
hold?
–
A database `schema’ in the ER Model can be represented
pictorially (
ER diagrams
).
–
Can map an ER diagram into a relational schema.
•
Logical Design
:
Convert the conceptual database design into the
data model underlying the DBMS chosen for the application.
•
ERD
Schema
Database!!!!!
ER Model Basics
•
Entity
:
Real-world object distinguishable from
other objects. An entity is described using a set
of
attributes
.
•
Entity Set
:
A collection of entities of the same
kind. E.g., all employees.
–
All entities in an entity set have the same set of
attributes.
–
Each entity set has a
key(a set of attributes uniquely
identifying an entity)
.
–
Each attribute has a
domain
.
Entity
•
An entity can be a person, place, event, or object
that is relevant to a given system. For example, a
school system may include students, teachers,
major courses, subjects, fees, and other items.
Entities are represented in ER diagrams by a
rectangle and named using singular nouns.
•
Weak Entity
•
A weak entity is an entity that depends on the
existence of another entity. In more technical
terms it can defined as an entity that cannot be
identified by its own attributes
Child
Employee
De
pe
nd
en
cy
Attribute
•
An attribute is a property, trait, or characteristic of an
entity, relationship, or another attribute. For example,
the attribute Inventory Item Name is an attribute of
the entity Inventory Item. An entity can have as many
attributes as necessary. Meanwhile, attributes can also
have their own specific attributes. For example, the
attribute “customer address” can have the attributes
number, street, city, and state. These are called
composite attributes. Note that some top level ER
diagrams do not show attributes for the sake of
simplicity. In those that do, however, attributes are
represented by oval shapes.
Classification
•
Multivalued Attribute
•
If an attribute can have more than one value it is called
an multivalued attribute. It is important to note that
this is different to an attribute having its own
attributes. For example a teacher entity can have
multiple subject values. E.g. : Skill
•
Example of a multivalued attribute
•
Derived Attribute
•
An attribute based on another attribute. This is found
rarely in ER diagrams. For example for a circle the area
can be derived from the radius. E.g: Age
•
Derived Attribute in ER diagrams
Student
ID
DoB
Address
Skill
Age
Street
House
Zip
Classification
•
Simple Attribute:
•
Combined of only one attribute. E.g. ID
•
Complex Attribute:
•
Combined of more than one attribute
–
Example: Address
WEAK Entity & WEAK Relationship
•
For converting ER to Relational Schema
,
Weak entity will accept the primary key of strong
entity.
We need not to convert the weak relationship into
table.
(Contd.)
•
Relationship
:
Association among two or more entities. E.g., Peter works in Pharmacy
department.
•
Schema
•
Employee
(
ssn
, name, DoB)
•
Department
(
did
, dname, budget)
•
Works_in(
since,
ssn
,
did
)
D
o
B
d
n
a
m
e
b
u
d
g
e
t
d
i
d
s
i
n
c
e
n
a
m
e
W
o
r
k
s
_
I
n
D
e
p
a
r
t
m
e
n
t
s
E
m
p
l
o
y
e
e
s
s
s
n
R
e
p
o
r
t
s
_
T
o
l
o
t
n
a
m
e
E
m
p
l
o
y
e
e
s
s
u
b
o
r
-
d
i
n
a
t
e
s
u
p
e
r
-
v
i
s
o
r
s
s
n
Key Constraints (Cardinality)
•
.
M
a
n
y
-
t
o
-
M
a
n
y
1
-
t
o
-
1
1
-
t
o
M
a
n
y
M
a
n
y
-
t
o
-
1
Constraints are
IMPORTANT
because they must be
ENFORCED
when
IMPLEMENTING
the database
•
Consider Works_In (in previous slide): An employee
can work in many departments; a dept can have
many employees.
•
In contrast, each dept has at most one manager,
according to the
key constraint
on Manages
Specialization & Generalization
•
Generalization
•
As mentioned above, the process of generalizing
entities, where the generalized entities contain
the properties of all the generalized entities, is
called generalization. In generalization, a number
of entities are brought together into one
generalized entity based on their similar
characteristics. For example, pigeon, house
sparrow, crow and dove can all be generalized as
Birds.
Specialization
•
Specialization is the opposite of
generalization. In specialization, a group of
entities is divided into sub-groups based on
their characteristics. Take a group ‘Person’ for
example. A person has name, date of birth,
gender, etc. These properties are common in
all persons, human beings. But in a company,
persons can be identified as employee,
employer, customer, or vendor, based on what
role they play in the company.
Understand entities, attributes, relationships, and database design stages. Learn about conceptual and logical designs, ER diagrams, and conversion to relational schemas. Explore ER models, schema, and basic concepts for effective database design.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Entity Relationship Diagrams Taslima Ferdaus Shuva Sr. Lecturer, CSE
Entity An entity is a business object that represents a group, or category of data. Do we know a similar concept? 1) Stephens, R.K. and Plew. R.R., 2001. Database Design. SAMS, Indianapolis , IN.
Attribute An attribute is a sub-group of information within an entity. Do we know a similar concept? 1) Stephens, R.K. and Plew. R.R., 2001. Database Design. SAMS, Indianapolis , IN.
Entity Relationship Models Ratio from entity to entity: Cardinality Many-to-Many Relationships |m..m/n n/*..*/<-| .|-> One-to-Many Relationships |1..m/1 n/1..*/|..|-> One-to-One Relationships |1..1
Overview of Database Design Requirements Analysis: Understand what data will be stored in the database, and the operations it will be subject to. Conceptual Design: (ER Model is used at this stage.) What are the entities and relationships in the enterprise? What information about these entities and relationships should we store in the database? What are the integrity constraints or business rules that hold? A database `schema in the ER Model can be represented pictorially (ER diagrams). Can map an ER diagram into a relational schema. Logical Design: Convert the conceptual database design into the data model underlying the DBMS chosen for the application.
ERD Schema Database!!!!!
name ER Model Basics ssn DoB Employees Entity: Real-world object distinguishable from other objects. An entity is described using a set of attributes. Entity Set: A collection of entities of the same kind. E.g., all employees. All entities in an entity set have the same set of attributes. Each entity set has a key(a set of attributes uniquely identifying an entity). Each attribute has a domain.
Entity An entity can be a person, place, event, or object that is relevant to a given system. For example, a school system may include students, teachers, major courses, subjects, fees, and other items. Entities are represented in ER diagrams by a rectangle and named using singular nouns. Weak Entity A weak entity is an entity that depends on the existence of another entity. In more technical terms it can defined as an entity that cannot be identified by its own attributes
Child De pe nd en cy Employee
Attribute An attribute is a property, trait, or characteristic of an entity, relationship, or another attribute. For example, the attribute Inventory Item Name is an attribute of the entity Inventory Item. An entity can have as many attributes as necessary. Meanwhile, attributes can also have their own specific attributes. For example, the attribute customer address can have the attributes number, street, city, and state. These are called composite attributes. Note that some top level ER diagrams do not show attributes for the sake of simplicity. In those that do, however, attributes are represented by oval shapes.
Classification Multivalued Attribute If an attribute can have more than one value it is called an multivalued attribute. It is important to note that this is different to an attribute having its own attributes. For example a teacher entity can have multiple subject values. E.g. : Skill Example of a multivalued attribute Derived Attribute An attribute based on another attribute. This is found rarely in ER diagrams. For example for a circle the area can be derived from the radius. E.g: Age Derived Attribute in ER diagrams
DoB ID Age Student Address Skill Street House Zip
Classification Simple Attribute: Combined of only one attribute. E.g. ID Complex Attribute: Combined of more than one attribute Example: Address
WEAK Entity & WEAK Relationship For converting ER to Relational Schema, Weak entity will accept the primary key of strong entity. We need not to convert the weak relationship into table.
name (Contd.) ssn lot Employees since name dname super- visor ssn budget DoB did subor- dinate Reports_To Works_In Employees Departments Relationship: Association among two or more entities. E.g., Peter works in Pharmacy department. Schema Employee(ssn, name, DoB) Department(did, dname, budget) Works_in(since, ssn, did )
Key Constraints (Cardinality) since name dname . ssn lot did budget Employees Manages Departments 1-to-1 1-to Many Many-to-1 Many-to-Many Constraints are IMPORTANT because they must be ENFORCED when IMPLEMENTING the database
Consider Works_In (in previous slide): An employee can work in many departments; a dept can have many employees. In contrast, each dept has at most one manager, according to the key constraint on Manages
Specialization & Generalization Generalization As mentioned above, the process of generalizing entities, where the generalized entities contain the properties of all the generalized entities, is called generalization. In generalization, a number of entities are brought together into one generalized entity based on their similar characteristics. For example, pigeon, house sparrow, crow and dove can all be generalized as Birds.
Specialization Specialization is the opposite of generalization. In specialization, a group of entities is divided into sub-groups based on their characteristics. Take a group Person for example. A person has name, date of birth, gender, etc. These properties are common in all persons, human beings. But in a company, persons can be identified as employee, employer, customer, or vendor, based on what role they play in the company.
