Conceptual Database Design - Entity Relationship(ER) Modeling

Database Design Techniques

  1. ER Modeling (Top down Approach)

  2. Normalization (Bottom Up approach)

Le's see ER Modeling in detail.

What is ER Modeling?

A graphical technique for understanding and organizing the data independent of the actual database implementation

We need to be familiar with the following terms to go further.

Entity

Any thing that has an independent existence and about which we collect data. It is also known as entity type.

In ER modeling, notation for entity is given below.

ER Modeling - notation for Entity

Entity instance

Entity instance is a particular member of the entity type.

Example for entity instance : A particular employee

Regular Entity

An entity which has its own key attribute is a regular entity.

Example for regular entity : Employee.

Weak entity

An entity which depends on other entity for its existence and doesn't have any key attribute of its own is a weak entity.

Example for a weak entity : In a parent/child relationship, a parent is considered as a strong entity and the child is a weak entity.

In ER modeling, notation for weak entity is given below.

In ER modeling, notation for weak entity

Attributes

Properties/characteristics which describe entities are called attributes.

In ER modeling, notation for attribute is given below.

Domain of Attributes

The set of possible values that an attribute can take is called the domain of the attribute. For example, the attribute day may take any value from the set {Monday, Tuesday ... Friday}. Hence this set can be termed as the domain of the attribute day.

Key attribute

The attribute (or combination of attributes) which is unique for every entity instance is called key attribute.
E.g the employee_id of an employee, pan_card_number of a person etc.If the key attribute consists of two or more attributes in combination, it is called a composite key.

In ER modeling, notation for key attribute is given below.

ER modeling -  notation for key attribute

Simple attribute

If an attribute cannot be divided into simpler components, it is a simple attribute.

Example for simple attribute : employee_id of an employee.

Composite attribute

If an attribute can be split into components, it is called a composite attribute.

Example for composite attribute : Name of the employee which can be split into First_name, Middle_name, and Last_name.

Single valued Attributes

If an attribute can take only a single value for each entity instance, it is a single valued attribute.

example for single valued attribute : age of a student. It can take only one value for a particular student.

Multi-valued Attributes

If an attribute can take more than one value for each entity instance, it is a multi-valued attribute. Multi-valued

example for multi valued attribute : telephone number of an employee, a particular employee may have multiple telephone numbers.

In ER modeling, notation for multi-valued attribute is given below.

Stored Attribute

An attribute which need to be stored permanently is a stored attribute

Example for stored attribute : name of a student

Derived Attribute

An attribute which can be calculated or derived based on other attributes is a derived attribute.

Example for derived attribute : age of employee which can be calculated from date of birth and current date.

In ER modeling, notation for derived attribute is given below.

ER modeling -  notation for derived attribute

Relationships

Associations between entities are called relationships

Example : An employee works for an organization. Here "works for" is a relation between the entities employee and organization.

In ER modeling, notation for relationship is given below.

However in ER Modeling, To connect a weak Entity with others, you should use a weak relationship notation as given below

Degree of a Relationship

Degree of a relationship is the number of entity types involved. The n-ary relationship is the general form for degree n. Special cases are unary, binary, and ternary ,where the degree is 1, 2, and 3, respectively.

Example for unary relationship : An employee ia a manager of another employee

Example for binary relationship : An employee works-for department.

Example for ternary relationship : customer purchase item from a shop keeper

Cardinality of a Relationship

Relationship cardinalities specify how many of each entity type is allowed. Relationships can have four possible connectivities as given below.

The minimum and maximum values of this connectivity is called the cardinality of the relationship

Example for Cardinality – One-to-One (1:1)

Employee is assigned with a parking space.

Example for Cardinality – One-To-One (1:1)

One employee is assigned with only one parking space and one parking space is assigned to only one employee. Hence it is a 1:1 relationship and cardinality is One-To-One (1:1)

In ER modeling, this can be mentioned using notations as given below

 ER Notations - Employee is assigned with a parking space

 

Example for Cardinality – One-to-Many (1:N)

Organization has employees

Example for Cardinality – One to Many (1:m)

One organization can have many employees , but one employee works in only one organization. Hence it is a 1:N relationship and cardinality is One-To-Many (1:N)

In ER modeling, this can be mentioned using notations as given below

 ER Notations - One organization can have many employees

 

Example for Cardinality – Many-to-One (M :1)

It is the reverse of the One to Many relationship. employee works in organization

Example for Cardinality – Many-to-One (M :1)

One employee works in only one organization But one organization can have many employees. Hence it is a M:1 relationship and cardinality is Many-to-One (M :1)

In ER modeling, this can be mentioned using notations as given below.

Cardinality – Many-to-Many (M:N)

Students enrolls for courses

Cardinality – Many-to-Many (M:N)

One student can enroll for many courses and one course can be enrolled by many students. Hence it is a M:N relationship and cardinality is Many-to-Many (M:N)

In ER modeling, this can be mentioned using notations as given below

ER Notation - Students enrolls for courses

Relationship Participation

1. Total

In total participation, every entity instance will be connected through the relationship to another instance of the other participating entity types

2. Partial

Example for relationship participation

Consider the relationship - Employee is head of the department.

Here all employees will not be the head of the department. Only one employee will be the head of the department. In other words, only few instances of employee entity participate in the  above relationship. So employee entity’s participation is partial in the said relationship.

However each department will be headed by some employee. So department  entity’s participation is total in the said relationship.

 

Advantages and Disadvantages of ER Modeling ( Merits and Demerits of ER Modeling )

Advantages

  1. ER Modeling is simple and easily understandable. It is represented in business users language and it can be understood by non-technical specialist.

  2. Intuitive and helps in Physical Database creation.

  3. Can be generalized and specialized based on needs.

  4. Can help in database design.

  5. Gives a higher level description of the system.

Disadvantages

  1. Physical design derived from E-R Model may have some amount of ambiguities or inconsistency.

  2. Sometime diagrams may lead to misinterpretations


Comments(7)


sandhu 18 Nov 2014 3:56 PM
not given about normalization...
Like (0)| Dislike (0)| Reply| Flag

Nitin Nayyar 22 Aug 2014 3:07 AM
Any idea about entity Set?How it is different from Entity Type.

Like (0)| Dislike (0)| Reply| Flag

basil 15 Jul 2014 12:42 PM
The explanation was very good



thanks 
Like (0)| Dislike (0)| Reply| Flag

varun 10 Apr 2014 11:11 AM
very well explained
Like (0)| Dislike (0)| Reply| Flag

Pallavi 13 Jun 2013 12:34 PM
what is that "Physical design derived from E-R Model may have some amount of ambiguities or inconsistency" is provided?

could any one please elaborate on this
Like (0)| Dislike (0)| Reply| Flag

Jay 13 Jun 2013 10:23 PM

Database designed based on ER model may have some amount of ambiguities  or inconsistency and redundancy. To resolve this, we do  some amount of refinement and this  process is called as Normalization.

Like (0)| Dislike (0)| Reply| Flag

sindhu 13 Apr 2013 10:43 AM
Information what
Like (0)| Dislike (0)| Reply| Flag

 
Add a new comment ...


Name   Email

Please answer the simple math question given below

8 + 3 =


close

Sign in