Modeling Tools for System

Analyst
E-R Diagram

1/21/2020 G.R Yadav 1

Outline:

• What Entity relationship diagrams (ERD) are.

• What Entities in an ERD are?
• What Attributes in an ERD are?
• What Relationships in an ERD are?
• How to start an ERD .

1/21/2020 G.R Yadav 2

 The Entity-Relationship Model
• An entity relationship model, also called an entity-
relationship (ER) diagram, is a graphical
representation of entities and their relationships to
each other, typically used in computing in regard
to the organization of data within databases or
information systems.
• An entity relationship diagram (ERD) shows the
relationships of entity sets stored in a database.
• An entity in this context is a component of data.
• In other words, ER diagrams illustrate the logical
structure of databases

1/21/2020 G.R Yadav 3

 The E-R Model Contd..
• An entity relationship model, also called an entity-
relationship (ER) diagram, is a graphical
representation of entities and their relationships to
each other, typically used in computing in regard to
the organization of data within databases or
information systems.
• An entity is a piece of data-an object or concept
about which data is stored.

1/21/2020 G.R Yadav 4

 The E-R Model Contd..
A systems analyst uses entity relationship data
model (E-R model) as a conceptual data model.
A conceptual data model is a detailed model that
captures the overall structure of organizational
data while being independent of any database
management system or other implementation
consideration.
And an E-R model is a detailed, logical
representation of the data for an organization or
for a business area.

1/21/2020 G.R Yadav 5

The Entity-Relationship Model Cont..
The E-R model is expressed in terms of entities in
the business environment, the relationships or
associations among those entities, and the
attributes or properties of both the entities and
their relationships.
An E-R model is normally expressed as an entity
relationship diagram (E-R diagram), which is a
graphical representation of an E-R model.
 It has three basic concepts: entities, attributes,
and relationships

1/21/2020 G.R Yadav 6

 The E-R Model Contd..
• Entity-Relationship model is a type of database
model based on the notion of real world entities
and relationship among them. We can map real
world scenario onto ER database model.
• ER Model creates a set of entities with their
attributes, a set of constraints and relation among
them.
• ER Model is best used for the conceptual design
of database.
• ER Model can be represented as follows :

1/21/2020 G.R Yadav 7

The E-R Model Cont…

1/21/2020 G.R Yadav 8

E-R Diagram
• Entity
Entity in DBMS can be a real-world object with an
existence, For example, in a School database, the
entities can be Teachers, Students, Courses, etc.

1/21/2020 G.R Yadav 9

E-R Diagram
• Attributes
• Entities has attributes, which can be considered as
properties describing it, for example, for
Teachers entity, the attributes are Teacher_Name,
Teacher_Address, Teacher_Subject, etc. The
attribute value gets stored in the database.

1/21/2020 G.R Yadav 10

E-R Diagram
• Weak Entity
• The weak entity in DBMS does not have a primary key
and are dependent on the parent entity. It mainly
depends on other entities, for example, dependents of
a professor.

1/21/2020 G.R Yadav 11

E-R Diagram
• Strong Entity
• The strong entity has a primary key. It has weak entities
that are dependent on strong entity. Its existence is not
dependent on any other entity.
• For example, Professor is a strong entity:

1/21/2020 G.R Yadav 12

E-R Diagram
• Primary Key
• Every table has one Primary key and cannot have null
values. A primary key can be StudentID, SSN,
AccountNumber, etc.

1/21/2020 G.R Yadav 13

E-R Diagram
• Multivalued Attribute
• An attribute that has multiple values for a single entity
at a time is called a Multivalued Attribute.
• For example, technical skills of a student that can be
programming, web development, etc.

1/21/2020 G.R Yadav 14

E-R Diagram
• Composite Attribute
• If an attribute has two or more other attributes, then it is
called a Composite Attribute.
• For example, Student Name can be divided as Student
First Name, Student Middle Name, and Student Last
Name.

1/21/2020 G.R Yadav 15

E-R Diagram
• Derived Attribute
• As the name suggests, the derived attribute is an
attribute whose value can be calculated from another
attribute.
• For example, Student Age can be derived from Date-of-
birth of a student.

1/21/2020 G.R Yadav 16

E-R Diagram
• ER Diagram Example
• Here’s an ER Diagram for Hospital:
• It has three entities: Patient, Doctor and Tests.
• Age is a derived attribute for Patient Entity
• Name in the Tests entity is a Primary Key
• ID in the Doctor entity is a Primary Key
• ID in the Patient entity is a Primary Key

1/21/2020 G.R Yadav 17

Sample ER Diagram for Hospital

1/21/2020 G.R Yadav 18

 The Entity-Relationship Model Cont..
• Entity - An entity in ER Model is a real world being, which
has some properties called attributes.
• Every attribute is defined by its corresponding set of values,
called domain.
• For example, Consider a school database. Here, a student
is an entity. Student has various attributes like name, id,
age and class etc.
• Relationship - The logical association among entities is
called relationship.
• Relationships are mapped with entities in various ways.
Mapping cardinalities define the number of associations
between two entities.
1/21/2020 G.R Yadav 19
ERM and ERD
• Entity-Relationship Data Model (ERM) is a
detailed, logical representation of the data for an
organization or for a business area.
• Expressed in terms of:
• Entities
• Attributes
• Relationships
• Entity-Relationship Diagram (ERD) is a
graphical representation of a Entity-Relationship
Model.

1/21/2020 G.R Yadav 20

ERD

• The purpose of an ERD is to capture the richest

possible understanding of the meaning of data
necessary for an information system or
organization.

• ERDs are made from Entities, Attributes, and

Relations.

1/21/2020 G.R Yadav 21

University Entity-Relationship Diagram

1/21/2020 G.R Yadav 22

Entity
• What is an Entity?
• Has its own identity that distinguishes it from
other entities.
• Examples:
• Person: PROFESSOR, STUDENT
• Place: STORE, UNIVERSITY
• Object: MACHINE, BUILDING
• Event: SALE, REGISTRATION
• Concept: ACCOUNT, COURSE

1/21/2020 G.R Yadav 23

 Entity
(Entity Type vs. Entity Instances)
• Entity Type is a collection of entities that share
common properties or characteristics.

STUDENT CLASS PROFESSOR

• Entity Instance is a single occurrence of an entity type.

STUDENT

• Entities should always be placed in a rectangle!

1/21/2020 G.R Yadav 24
 Entity Types
(Naming Guidelines)
Entity type name should be:
• A singular noun and in capital letters.
• Descriptive and specific to the organization.
• Concise.
• Named for the result of the event, not the
activity or process of the event.

1/21/2020 G.R Yadav 25

University Entity-Relationship Diagram

Entity

1/21/2020 G.R Yadav 26

 Entity Types
(Defining Guidelines)
• An Entity type definition should:
• Include a statement of what the unique characteristics are
for each instance.
• Make clear what entity instances are included and not
included.
• Include a description of when an instance of the entity type
is created and deleted.
• Specify when an instance might change into an instance of
another entity type.
• Specify what history is to be kept about entity instances.

1/21/2020 G.R Yadav 27

 Attributes
• Each Entity has a set of Attributes
• Attribute is a property or characteristic of an
entity that is of interest to the organization.
• Example:
• STUDENT: Student_ID, Student_Name,
Phone_Number, Major

1/21/2020 G.R Yadav 28

Attributes

Student

Student_ID
Student_Address
Student_Phone

1/21/2020 G.R Yadav 29

 Attributes
(Naming Guidelines)
• An attribute name:
• Should be a noun and capitalize the first letter of
each word. (Example: Student_ID.)
• Should be unique.
• Should follow a standard format. (Example:
Student_GPA, not GPA_of_Student.)
• Similar attributes of different entity types should use
similar but distinguished names.
• Example: Faculty_Residence_City_Name and
Student_Residence_City_Name

1/21/2020 G.R Yadav 30

 Attributes
(Defining Guidelines)
• An attribute definition should:
• State what the attribute is and why it is
important.
• Make clear what is and isn’t included in the
attribute's value.
• Define any aliases.
• Indicate if the attribute is required or not.
• Indicate any relationships with other
attributes.

1/21/2020 G.R Yadav 31

 Attributes
• Candidate Key = is an attribute that uniquely
identifies each instance of an entity type.
• Identifier = A candidate key that has been
selected as the unique, identifying
characteristic of an entity type. (Should be
underlined).
• Other types of Attributes: multivalued, required,
optional, composite, and derived.

1/21/2020 G.R Yadav 32

 Criteria for Selecting Attributes
• Choose a candidate key that will not change its
value.
• Choose a candidate key that has valid values
and not be null.
• Avoid using codes, such as a 2 digit warehouse
location.

1/21/2020 G.R Yadav 33

 Relationships
• Relationships are associations between one or more entity
types.
• Are the “glue” that holds together components of an E-R
model.
• The degree of a relationship = is the number of entity types
that participate in a relationship.
• There are 3 common relationships:
1. Unary (degree one)
2. binary (degree two)
3. Ternary (degree three)

1/21/2020 G.R Yadav 34

University Entity-Relationship Diagram

Relationship

1/21/2020 G.R Yadav 35

 Relationships
(Naming Guidelines)

• A relationship name should:

Be a verb phrase, such as Is_assigned_to.
Avoid vague names, such as “Has”.

1/21/2020 G.R Yadav 36

 Relationships
(Naming Guidelines)
• A relationship definition should Explain:
What action is being taken and why it is important.
If there is any optional participation.
The history that is kept in the relationship.
What any restrictions on participation in the
relationship.
For example: An EMPLOYEE may only be able to
participate in two PROJECTS.

1/21/2020 G.R Yadav 37

Unary Relationship

• Relationship between the instances of one entity type.

Is_married_to Manages

EMPLOYEE
PERSON
One-to-one One-to-many

1/21/2020 G.R Yadav 38

 Binary Relationship
• Relationship between the instances of two entity type.

Is_assigned Contains

PARKING
EMPLOYEE PRODUCT
One-to-One SPACE One-to-Many PRODUCTS
LINE
Can also have many to many!

1/21/2020 G.R Yadav 39

Ternary Relationship
• A simultaneous relationship among instances of
three entity types.

PART

Supplies

VENDOR WAREHOUSE

1/21/2020 G.R Yadav 40

Relationship Cardinalities
• Mandatory Cardinalities = The entity must participate in
another entity.
• Optional Cardinalities = The entity has a the option to
participate in another entity.

1/21/2020 G.R Yadav 41

Starting an ERD
1. Define the Entities.
2. Define the Relationships.
3. Add attributes to the relationships.
4. Add cardinality to the relationships.
5. Don’t forget to use proper naming
conventions and symbol representation.

1/21/2020 G.R Yadav 42

 Guidelines for Drawing ERDs
• Lay out the diagram with minimal line crossing.
• Place subject entity types on the top of the diagram.
• Place plural entity types below a single entity type in a one-
to-many relationship.
• Place entity types participating in one-to-one and many-to-
many relationships alongside each other.
• Group closely related entity types when possible. Try to
keep the length of relationship lines as short as possible.
Also try to minimize the number of changes of direction in a
single line.
• Show the most relevant relationship name. One name
must always be shown.

1/21/2020 G.R Yadav 43

 Sample E-R Library Management System

1/21/2020 G.R Yadav 44

Sample Conceptual Data Model diagram

1/21/2020 G.R Yadav 45

1/21/2020 G.R Yadav 46
Questions or Comments over ERD?

1/21/2020 G.R Yadav 47