Thursday, 2 October 2014

What is an ER Diagram and how to draw it?

ER Diagram or Entity Relationship Diagram is a tool used to graphically represent the relational model of a database schema. It is a very important and useful tool that can be used in the Database Design phase of software development process to represent entities, relations, attributes, multiplicity, participation and constraints identified.  Various icons and notations are used to represent entities, relationships, attributes, multiplicity, participation and constraints are discussed below.

Common shapes/icon used in ER Diagrams

Strong EntityA rectangle icon is used to represent a strong entity. A strong entity is a standalone entity with a primary key.
Weak EntityA rectangle icon with double lined border is used to represent a weak entity. A weak entity depends on another entity and it will not have a primary key.

Strong Relation
A diamond shape is used to represent the relationship between two or more entities. Relationship between strong entities is considered as strong and a single lined diamond shape is used for its notation.
Weak RelationA diamond shape with double lined border is used to represent a relationship  to a weak entity. Any relationship with a weak entity is considered as weak.
Single Valued AttributeAs the name implies a single valued attribute can have only a single value for an instance of the entity.

Key AttributeAn attribute which is identified as Primary Key

Multi Valued AttributeA multi valued attribute can have more than one value for an instance of the entity.

Derived AttributeDerived attributes are derived from existing attributes. These attributes can be calculated based on the values of existing attribute or attributes.

Composite AttributesComposite attributes represents a composite data structure. That means it is composite value with more than one child attributes.

Multiplicity / Cardinality

Multiplicity / Cardinality refers to the number of instances of an entity which will be participating in a relationship.  For example, if there is a relationship R between entity A and B we can measure the degree of links between A and B. That is the number/count of Bs related to one A and the number of As related to one B. One A may be related to one or many Bs and vice versa.

For a relation multiplicity may be one of the following:

one -  to - one  (1 - 1)
one -  to -  many  (1 - N)
many -  to  - one  (N – 1)
many -  to  - many  (N – N)


one - to - one

one - to - many

many - to one

many - to - many


Another factor that has to be considered while drawing ER Diagram is the participation of entities in a relationship. That is either the participation from an entity to a relation is full or partial. If an entity is fully participating in a relation, the line which connects the entity and the relation will be having double strokes.


Full participation from entity B in relation R, i.e. all Bs will be participating in relation R .

Full participation from entity A in relation R, i.e. all As will be participating in relation R .

Partial participation from both entity A and B in relation R, i.e. both  A and B is not  fully participating in relation R .

A complete example

Let us consider the scenario of a company where employees are working in various departments. Each employee has a designation and he will be associated to a department.

Here we can identify the following entities, relations and attributes.


Employee – woks in - Department
Employee – has a - Designation

Employee – empid, name, address, phone, email, doj, service
Department – deptid, name of department
Designation – desigid, title

ER- Diagram

Structure of Tables created from the above ER Diagram


Field NameData TypeSizeConstraints
deptidnumeric3PRIMARY KEY
namevarchar30UNIQUE, NOT NULL


Field NameData TypeSizeConstraints
desigtidnumeric5PRIMARY KEY
titlevarchar30UNIQUE, NOT NULL


Field NameData TypeSizeConstraints
empptid numeric 9 PRIMARY KEY
name varchar 30 UNIQUE, NOT NULL
houseno varchar 20 NULL
city varchar 30 NULL
state varchar 30 NULL
doj date 8 NOT NULL
phone varchar 15 NULL
email varchar 30 NULL
deptid numeric 3 FOREIGN KEY (Department), NOT NULL
desigid numeric 5 FOREIGN KEY (Designation), NOT NULL

SQL Query to create tables

CREATE TABLE Department (
     deptid  numeric (3) PRIMARY KEY,
     name    varchar (30) UNIQUE NOT NULL

CREATE TABLE Designation (
     desigid  numeric (5) PRIMARY KEY,
     title    varchar (30) UNIQUE NOT NULL

      empptid numeric (9) PRIMARY KEY,
      name varchar (30) UNIQUE  NOT NULL,
      houseno varchar (20),
      city varchar (30),
      state varchar (30),
      doj date (8) NOT NULL,
      phone varchar (15),
      email varchar (30),
      deptid numeric (3) REFERENCES Department (deptid ),
      desigid numeric (5) REFERENCES Designation (desigid )

No comments:

Post a Comment