Remember
me?
RegisterForgot Your Password?

Concepts of Entity Relationship Diagram( ER diagram)

Basic Symbols of ER Diagram

Entity Relationship Model:

An Entity – Relationship model (ER model) is an abstract way to describe a database. It is a visual representation of different data using conventions that describe how these data are related to each other.

There are three basic elements in ER models:

  • Entities are the “things” about which we seek information.
  • Attributes are the data we collect about the entities.
  • Relationships provide the structure needed to draw information from multiple entities.

Symbols used in E-R Diagram:

  • Entity – rectangle
  • Attribute -oval
  • Relationship – diamond
  • Link - line

                                                                                             

Entities and Attributes

Entity Type:It is a set of similar objects or a category of entities that are well defined

  • A rectangle represents an entity set
  • Ex: studentscourses
  • We often just say “entity” and mean “entity type”

Attribute:It describes one aspect of an entity type; usually [and best when] single valued and indivisible (atomic)

  • Represented by oval on E-R diagram
  • Ex: name, maximum enrollment
Types of Attribute:

Simple and Composite Attribute

Simple attribute that consist of a single atomic value.A simple attribute cannot be subdivided. For example the attributes age, sex etc are simple attributes.

A composite attribute is an attribute that can be further subdivided. For example the attribute ADDRESS can be subdivided into street, city, state, and zip code. 

Simple Attribute: Attribute that consist of a single atomic value.

Example: Salary, age etc

Composite Attribute  : Attribute value not atomic.
Example :   Address  :  ‘House_no:City:State
Name      :  ‘First Name: Middle Name: Last Name’ 

Single Valued and Multi Valued attribute

A single valued attribute can have only a single value. For example a person can have only one ‘date of birth’, ‘age’ etc. That is a single valued attributes can have only single value. But it can be simple or composite attribute.That is ‘date of birth’ is a composite attribute , ‘age’ is a simple attribute. But both are single valued attributes.

Multivalued attributes can have multiple values. For instance a person may have multiple phone numbers,multiple degrees etc.Multivalued attributes are shown by a double line connecting to the entity in the ER diagram.

Single Valued Attribute: Attribute that hold a single value
Example1: Age
Exampe2: City
Example3:Customer id

Multi Valued Attribute: Attribute that hold multiple values.
Example1: A customer can have multiple phone numbers, email id’s etc
Example2: A person may have several college degrees

Stored and Derived Attributes

The value for the derived attribute is derived from the stored attribute. For example ‘Date of birth’ of a person is a stored attribute. The value for the attribute ‘AGE’ can be derived by subtracting the ‘Date of Birth’(DOB) from the current date. Stored attribute supplies a value to the related attribute.

Stored Attribute: An attribute that supplies a value to the related attribute.
Example: Date of Birth

Derived Attribute: An attribute that’s value is derived from a stored attribute.
Example : age, and it’s value is derived from the stored attribute Date of Birth.

Keys

Super key: An attribute or set of attributes that uniquely identifies an entity–there can be many of these

Composite key:A key requiring more than one attribute

Candidate key: a superkey such that no proper subset of its attributes is also a superkey (minimal superkey – has no unnecessary attributes)

Primary key: The candidate key chosen to be used for identifying entities and accessing records.  Unless otherwise noted “key” means “primary key”

Alternate key: A candidate key not used for primary key

Secondary key: Attribute or set of attributes commonly used for accessing records, but not necessarily unique

Foreign key: An attribute that is the primary key of another table and is used to establish a relationship with that table where it appears as an attribute also.

Graphical Representation in E-R diagram

Rectangle – Entity

Ellipses – Attribute (underlined attributes are [part of] the primary key)

Double ellipses – multi-valued attribute

Dashed ellipses– derived attribute, e.g. age is derivable from birthdate and current date.

Relationships

Relationship: connects two or more entities into an association/relationship

  • “John” majors in “Computer Science”

Relationship Type: set of similar relationships

  • Student (entity type) is related to Department (entity type) by MajorsIn (relationship type).

relationship diagram in ER

Relationship Types may also have attributes in the E-R model.  When they are mapped to the relational model, the attributes become part of the relation. Represented by a diamond on E-R diagram.

Cardinality of Relationships

Cardinality is the number of entity instances to which another entity set can map under the relationship. This does not reflect a requirement that an entity has to participate in a relationship. Participation is another concept.

One-to-one: X-Y is 1:1 when each entity in X is associated with at most one entity in Y, and each entity in Y is associated with at most one entity in X.

One-to-many: X-Y is 1:M when each entity in X can be associated with many entities in Y, but each entity in Y is associated with at most one entity in X.

Many-to-many: X:Y is M:M if each entity in X can be associated with many entities in Y, and each entity in Y is associated with many entities in X (“many” =>one or more and sometimes zero)

 

Relationship Participation Constraintsparticipation ER example

Total participation

  • Every member of entity set must participate in the relationship
  • Represented by double line from entity rectangle to relationship diamond
  • E.g., A Class entity cannot exist unless related to a Faculty member entity in this example, not necessarily at Juniata.
  • You can set this double line in Dia
  • In a relational model we will use the references clause.

Key constraint

  • If every entity participates in exactly one relationship, both a total participation and a key constraint hold
  • E.g., if a class is taught by only one faculty member.

Partial participation

  • Not every entity instance must participate
  • Represented by single line from entity rectangle to relationship diamond
  • E.g., A Textbook entity can exist without being related to a Class or vice versa.

Strong and Weak Entities

Strong Entity Vs Weak Entity
An entity set that does not have sufficient attributes to form a primary key is termed as a weak entity set. An entity set that has a primary key is termed as strong entity set.

A weak entity is existence dependent. That is the existence of a weak entity depends on the existence of  a identifying entity set. The discriminator (or partial key) is used to identify other attributes of a weak entity set.The primary key of a weak entity set is formed by primary key of identifying entity set and the discriminator of weak entity set. The existence of a weak entity is indicated by a double rectangle in the ER diagram. We underline the discriminator of a weak entity set with a dashed line in the ER diagram.

ER Diagram Example