ER MODEL in DBMS

The ER model is used to define the conceptual view of database. The work of the ER Model revolves around real-world entities and the associations between these objects. At view level, the ER model is considered a good option for designing databases.

The basic concepts of an ER Model can be distinctly subdivided into three major categories, namely:

  • Entities
  • Attributes
  • Relationship

Entities:

An entity can defined as a real-world object, irrespective of whether it is animate or inanimate, which is easy to identify. For example, in a company database, employees, dealers, orders, and products can be considered as entities. Each of these entities have some specific attributes or properties that provide them their own unique identity.

A collection of similar types of entities put together is known as an entity set. An entity set may contain entities with attribute sharing similar values. For example, an Employee set may contain all the employees of a company. Entity sets need not be disjoint.

Attributes:

As mentioned earlier, each entity has a specific set of properties which gives it a unique identity. Entities are represented based on these properties, called attributes. All attributes have values. For example, a teacher entity may have name, subject, and age as attributes.

There exists a domain or range of values that can be assigned to attributes. For example, an employee ID cannot be an alphabetical value. It has to be numeric.

Types of Attributes:

  • Simple attribute − Simple attributes can be described as atomic values, which cannot be divided any further. For example, an employee’s secret pin number is an atomic value of 4 digits.
  • Composite attribute − Composite attributes are made of multiple simple attributes. For example, a teacher’s complete name may be a combination of first_name and last_name.
  • Derived attribute − Derived attributes are the attributes which are not a part of the physical database, but their values are derived from other attributes present in the database. For example, average_salary in a department should not be saved directly in the database, instead it can be derived.
  • Single-value attribute − Single-value attributes contain single value.
  • Multi-value attribute − Multi-value attributes may contain multiple values. For example, a person can have more than one phone number, email_address, etc.

Entity-Set and Keys:

Key can be defined as an attribute or collection of attributes which uniquely identifies an entity in an entity set.

For example, the roll_number of a student makes him/her identifiable among students.

  • Super Key − A set of attributes (one or more) that collectively identifies an entity in an entity set.
  • Candidate Key − A minimal super key is called a candidate key. An entity set may have more than one candidate key.
  • Primary Key − A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set.

Relationship:

The association shared by multiple entities is called a relationship. For example, an employee works_at a department, a teacher teaches a course. Here, Works_at and Teaches are called relationships.

Relationship Set:

A set of relationships having similar characteristics is termed as a relationship set. Like entities, a relationship too can have attributes. These attributes are called descriptive attributes.

Degree of Relationship

The number of participating entities in a relationship defines the degree of the relationship.

  • Binary = degree 2
  • Ternary = degree 3
  • n-ary = degree

Mapping Cardinalities

Cardinality is used to define the number of entities in one particular entity set, which can be associated with the number of entities of other set via relationship set.

One-to-one – One entity from entity set A can be associated with at most one entity of entity set B and vice versa.

One-to-many – One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity.

Many-to-many – One entity from A can be associated with more than one entity from B and vice versa.