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 Entity	A rectangle icon is used to represent a strong entity. A strong entity is a standalone entity with a primary key.
	Weak Entity	A 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 Relation	A 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 Attribute	As the name implies a single valued attribute can have only a single value for an instance of the entity.
	Key Attribute	An attribute which is identified as Primary Key
	Multi Valued Attribute	A multi valued attribute can have more than one value for an instance of the entity.
	Derived Attribute	Derived attributes are derived from existing attributes. These attributes can be calculated based on the values of existing attribute or attributes.
	Composite Attributes	Composite 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)

Notation


one - to - one


one - to - many

many - to one

many - to - many



Participation

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.

Notation

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.

Entities
Department
Designation
Employee

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

Attributes
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

Department

Field Name	Data Type	Size	Constraints
deptid	numeric	3	PRIMARY KEY
name	varchar	30	UNIQUE, NOT NULL

Designation

Field Name	Data Type	Size	Constraints
desigtid	numeric	5	PRIMARY KEY
title	varchar	30	UNIQUE, NOT NULL

Employee

Field Name	Data Type	Size	Constraints
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
);

CREATE TABLE Employee (
      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 )
);