Getting started with Entity Relationship Diagram(ERD) for conceptual representation of Database Schema

The Entity Relationship diagrams are used to represent the Entity Relationship Model(ER Model). Data models provide the abstraction in Data Base Design. The Entity Relationship diagrams also called as ER Diagrams are what database designers usually do when designing database. This is beast practice to make ER Diagrams during database implementation since it provides conceptual overview of data and it’s relationships and describes entities, attributes and relationships.

Entity Relationship diagrams are tool to represent visually the ER Model. ER Model was first created by Peter Chen in 1971 to be used for Relational Database Design and network. Where ER Model is data model diagram which provides a high level conceptual view of the database. Entity Relational model works around real world entities and relationship among those entities.

Entity Relationship Diagram

Entity relationship diagrams help you to represent the ER Model. ER diagrams are useful to visualize the relationship among the entity set stored in the database, as a result we can better communicate the logical structure of the database. It helps you to identify entities, it’s attributes and relationship among entities.

Logical structure provided by ER Diagrams helps you build database schema quickly, Where ER Diagrams can be translated into relational tables. There are software’s that can take ER Diagrams as input and produce the database schema for you. Well, it might not be perfect for whom looking for fine grained schema. But, server the need and database can be build quickly.

Why ER Diagrams?

  • ER Diagrams give you the visual layout of the database
  • Standard method to draw/diagram entities, attributes and relationships
  • Works as communication tool to present logical structure of the database.
  • Gives detailed visual outlook about relationship and attributes of entities (Entity set/Database table)
  • ER Diagrams help you describe Entities, Attributes and Relationships
  • ER Diagrams help you identify relationship among entity sets in database
  • ER Diagrams are visual tool to better communicate database
  • ERD can be translated into relational tables
  • It is possible to build database quickly in no time with reference of ER Diagrams
  • Third party software’s can be used to generate database schema from ER diagrams.

Basic Building Blocks of ER Diagrams

Following are basic building blocks which are used by ER diagrams to represent the logical structure of a Database visually

  • Entity
  • Attribute
  • Relationship

Entity

The Entity can be any person, place or thing of real world which can be either living or non-living that can be tangible or intangible, anything that can be represented in the database. In simple words any thing that we want to keep track of in database.

There are different kind of entities. Which are

  • Domain Entity
  • Linking Entity
  • Lookup Entity
  • Strong Entity
  • Weak Entity
  • Composite or Associate Entity

A group or set of entities that share similar attributes is called Entity Set . Entity Set will be the table or relation in the physical database.

Attribute

Every entity has it’s own set of properties called attributes. Entities are defined by their attributes. For example, if you take a dog as entity. It’s properties/attributes are color, breed and height etc.

Domain is the range of attributes that can be assigned to attributes. For example age can not be negative, username can be alpha numeric but not just number etc.

There are different types of attributes

Simple Attribute
Simple attributes are atomic. They can’t be divided any further. Example: Phone number, Social security Number
Composite Attribute
Composite attributes are attributes which are made up of two or more attributes. For example Person Full can have first_name, middle_name, last_name
Derived Attribute
These are type of attribute that do not exist in the physical database. Derived attributes are derived from other attributes in the database. For example: age is derived from DOB instead of storing it
Single Value Attribute
Attribute which hold only single value. Example: social_security_number, DOB
Multi Value Attribute
Attribute that may hold more that one value. Example: mobile_number, address etc.

Relationship

Relationship describes the association among two or more entities. Example: Bob works at Software Inc, Alice manages team. Where, works and manages are called relationships.

A group or set of relationships of similar kind is called relationship set. Attributes of relationship are called “Descriptive Attributes“.

Recursive Relationship

If Same entity is involved in a relationship itself is called recursive relationship .

Degree of Relationship

The degree of relationship is defined by the number of participating entity sets in the relationship

Unary
Degree 1 : Only one Entity set participation a relationship.
Binary
Degree 2 : Two Entity sets participation a relationship.
Ternary
Degree 3 : Three Entity sets participation in a relationship
N-ary
Degree: More than three entity sets participation in a relationship

Relationship Cardinality and Ordinality

Cardinality is the degree to which different entities or entity sets are related or associated. It defines number of entities in on entity set that can be associated(related) with number of entities in other set at most .

Ordinality is the minimum number of entities in one entity set that can be associated with other entity set.

Different types of Cardinality (relationships):

Let’s take two entity sets X and Y for the demonstration of types of cardinality

One to One Relationship
Only one entity from entity set X can be associated with at most one and only one entity of entity set Y and vice versa.
One to Many Relationship
Only one entity from entity set X can be associated with multiple entities of entity set Y. But, an entity from entity set Y can be associated with at most one entity of entity set X
Many to One Relationship
One or more entities from entity set X can be associated with at most one entity of entity set Y. But, an entity from entity set Y can be associated with more than one entity of entity set X
Many to Many Relationship
One entity from entity set X can be associated with multiple entities of entity set Y and vice versa
Relationship cardinality types
Relationship Cardinality Types

There are different notation to represent cardinality in ER Diagrams. One of popular widely used notation is so called “Crow’s Foot Notation’. Which is used to represent relationship cardinality

Relationship Cardinality
Relationship Cardinality, Crow’s foot notation

Entity Relationship Diagram Components, Symbols and Notions

There are multiple elements that make up the entity relationship diagram. ER diagrams are similar to flowcharts. This section describes different elements and their symbols and they are used in ER diagrams

Following are elements of ER diagrams

Entity

The entity is any kind of real world object, concept or thing that is either real or virtual which can be either tangible or intangible. It is anything that is to be represented in a database to track.

This is also call strong entity, where each entity will have a primary key which is used to uniquely identify any entity in the entity set.

Few examples of entities: place, car, person, student, order, product, sale, account

Group of entities are called entity set. it is represented by rectangle with it’s name inside in ER Diagrams.

Weak Entity

A weak entity is a kind of entity which doesn’t have key attribute. It depends on other entity which has primary key to uniquely identify itself, for this weak entities need participation.

Weak entity is represented by double rectangle.

For example: Consider rental property as an entity, where property type can be a weak entity.

Associative Entity

Associative entities relate multiple kind of entities and also hold the attributes regarding the relationship

Associative entity is represented by diamond inside rectangle.

Attribute

Attributes are properties of entity that define the entity and it’s characteristics.

For example: If you take student as an entity. Attributes are Name, Age, Roll.No, Class, College etc.

Attributes are represented by ellipses.

Key Attribute

The key attribute is the attribute that uniquely identifies the each entity in a entity set.

For example: Roll.No can be a key attribute where it identifies each student in the class(entity set) uniquely.

Key attributes are denoted same a attribute but with underline on name.

Composite Attribute

Composite attribute are attributes which are made of two or more simple attribute.

Composite attribute are represented by ellipses comprised of ellipses.

Derived Attribute

Derived attributes are attributes that do not really exist in the physical database. But, these attributes are derived from other attributes of entity.

For example: age is a derived attribute which don’t really need a place in physical database since it can be derived from DOB.

Derived attributes are represented by dashed ovals.

Single Value Attribute

Single value attributes are simple attributes that contain only one value. These attributes are represented usual ellipsis

For example: roll_no, social_security_number, driving license number

Multivalued Attribute

It is the attribute that may hold more than one value. Examples of multivalued attributes are “phone number”, “email” and “address”.

Multivalued attributes are represented by double ellipses.

Relationship

Association among entity sets(entities) is called relationship. A relationship describes how two or more entity sets are related.

Relationship is represented by Diamond symbol.

Examples of relationships are Manager “manages” team, Account “has” cash, Owner “owns” property

There is different degrees of relations ship and cardinality which defines how many number entities in different entity sets are associated via relation ship. Which is explained here .

Weak Relationship

A relationship which represents an association between weak entity and it’s owner is called weal realtionship

A weak relationship is represented by double diamond symbol.

Partial Participation

A connection is represented by single solid line, if it is not necessary that all entities are involved in the relationship it is called partial participation.

Total Participation

If each entity is involved in a relationship, it is said to be total participation, where it is represented by double solid lines.

Steps to follow to create Entity Relationship Diagram

ER Diagrams are created and used as a visual tool for conceptual representation of database. It is used during initial stage of database design, it is helpful as a communication tool, to tell a story about database schema. You may create ER Diagrams in ad hoc manner. But, to make your life easier and get better representation of database follow steps explained below.

Steps to create Entity Relationship diagrams

  • Understand the Requirement
  • Identify Different Entities
  • Identify Relationships
  • Identify Cardinality
  • Identify Attributes
  • Put all together, make ER Diagram

Entity Relationship Diagram Example

#TODO

Software to create ER Diagrams

There are several tools and applications are available out there to make ER Diagrams. Here are some of those helpful tools. Some of the following applications are browser based web apps

  • Dia
  • Microsoft Visio
  • Lucidchart
  • Cacoo
  • visual-paradigm

Summary

  • ER diagrams are used as visual tools to communicate database design
  • ER diagrams present the logical structure of a database visually
  • ER Diagrams can be used to generate database schema using third party softwares
  • ER Diagrams can be used by developers and database designers to communicate better about database design
  • Since ER Diagrams hold less information about platform specific things physical database, it can be used by non-technical or management people as well