Introduction of ER Model

 Difficulty Level : Easy

 Last Updated : 20 Aug, 2019
ER Model is used to model the logical view of the system from data
perspective which consists of these components:
Entity, Entity Type, Entity Set –
An Entity may be an object with a physical existence – a particular
person, car, house, or employee – or it may be an object with a
conceptual existence – a company, a job, or a university course.
An Entity is an object of Entity Type and set of all entities is called as
entity set. e.g.; E1 is an entity having Entity Type Student and set of
all students is called Entity Set. In ER diagram, Entity Type is
represented as:

Attribute(s):
Attributes are the properties which define the entity type. For
example, Roll_No, Name, DOB, Age, Address, Mobile_No are the
attributes which defines entity type Student. In ER diagram,
attribute is represented by an oval.
1. Key Attribute –
The attribute which uniquely identifies each entity in the
entity set is called key attribute.For example, Roll_No will be
unique for each student. In ER diagram, key attribute is
represented by an oval with underlying lines.

2. Composite Attribute –
An attribute composed of many other attribute is called
as composite attribute. For example, Address attribute of
student Entity type consists of Street, City, State, and
Country. In ER diagram, composite attribute is represented
by an oval comprising of ovals.

3. Multivalued Attribute –
An attribute consisting more than one value for a given
entity. For example, Phone_No (can be more than one for a
given student). In ER diagram, multivalued attribute is
represented by double oval.

4. Derived Attribute –
An attribute which can be derived from other
attributes of the entity type is known as derived attribute.
e.g.; Age (can be derived from DOB). In ER diagram, derived
attribute is represented by dashed oval.

The complete entity type Student with

its attributes can be represented as:
Relationship Type and Relationship Set:
A relationship type represents the association between entity
types. For example,‘Enrolled in’ is a relationship type that exists
between entity type Student and Course. In ER diagram, relationship
type is represented by a diamond and connecting the entities with
lines.

A set of relationships of same type is known as relationship set. The

following relationship set depicts S1 is enrolled in C2, S2 is enrolled
in C1 and S3 is enrolled in C3.

Degree of a relationship set:

The number of different entity sets participating in a
relationship set is called as degree of a relationship set.
 1. Unary Relationship –
When there is only ONE entity set participating in a
relation, the relationship is called as unary relationship. For
example, one person is married to only one person.

2. Binary Relationship –
When there are TWO entities set participating in a
relation, the relationship is called as binary relationship.For
example, Student is enrolled in Course.

3. n-ary Relationship –
When there are n entities set participating in a relation, the
relationship is called as n-ary relationship.

Cardinality:
The number of times an entity of an entity set participates in
a relationship set is known as cardinality. Cardinality can be of
different types:
1. One to one – When each entity in each entity set can take
part only once in the relationship, the cardinality is one
to one. Let us assume that a male can marry to one female
and a female can marry to one male. So the relationship will
be one to one.

Using Sets, it can be represented as:

2. Many to one – When entities in one entity set can take
part only once in the relationship set and entities in
other entity set can take part more than once in the
relationship set, cardinality is many to one. Let us assume
that a student can take only one course but one course can
be taken by many students. So the cardinality will be n to 1.
It means that for one course there can be n students but for
one student, there will be only one course.

Using Sets, it can be represented as:

In this case, each student is taking only 1 course but 1

course has been taken by many students.
3. Many to many – When entities in all entity sets can take
part more than once in the relationship cardinality is
many to many. Let us assume that a student can take more
than one course and one course can be taken by many
students. So the relationship will be many to many.
 Using sets, it can be represented as:

In this example, student S1 is enrolled in C1 and C3 and

Course C3 is enrolled by S1, S3 and S4. So it is many to
many relationships.
Participation Constraint:
Participation Constraint is applied on the entity participating in the
relationship set.
1. Total Participation – Each entity in the entity set must
participate in the relationship. If each student must enroll
in a course, the participation of student will be total. Total
participation is shown by double line in ER diagram.
2. Partial Participation – The entity in the entity set may or
may NOT participate in the relationship. If some courses
are not enrolled by any of the student, the participation of
course will be partial.
The diagram depicts the ‘Enrolled in’ relationship set with
Student Entity set having total participation and Course
Entity set having partial participation.

Using set, it can be represented as,

 Every student in Student Entity set is participating in
relationship but there exists a course C4 which is not taking
part in the relationship.
Weak Entity Type and Identifying Relationship:
As discussed before, an entity type has a key attribute which
uniquely identifies each entity in the entity set. But there
exists some entity type for which key attribute can’t be
defined. These are called Weak Entity type.
For example, A company may store the information of dependants
(Parents, Children, Spouse) of an Employee. But the dependents
don’t have existence without the employee. So Dependent will be
weak entity type and Employee will be Identifying Entity type for
Dependant.
A weak entity type is represented by a double rectangle. The
participation of weak entity type is always total. The relationship
between weak entity type and its identifying strong entity type is
called identifying relationship and it is represented by double
diamond.

Quiz on ER-MODEL

Enhanced ER Model
 Difficulty Level : Medium
 Last Updated : 27 Nov, 2020
Prerequisite – Introduction of ER Model
Todays time the complexity of the data is increasing so it becomes
more and more difficult to use the traditional ER model for database
modeling. To reduce this complexity of modeling we have to make
improvements or enhancements were made to the existing ER
model to make it able to handle the complex application in a better
way.
Enhanced entity-relationship diagrams are advanced database
diagrams very similar to regular ER diagrams which represents
requirements and complexities of complex databases.
It is a diagrammatic technique for displaying the Sub Class and
Super Class; Specialization and Generalization; Union or Category;
Aggregation etc.
Generalization and Specialization –
These are very common relationship found in real entities. However
this kind of relationships was added later as enhanced extension to
classical ER model. Specialized class are often called
as subclass while generalized class are called superclass,
probably inspired by object oriented programming. A sub-class is
best understood by “IS-A analysis”. Following statements
hopefully makes some sense to your mind “Technician IS-A
Employee”, “Laptop IS-A Computer”.
An entity is specialized type/class of other entity. For example,
Technician is special Employee in a university system Faculty is
special class of Employee. We call this phenomenon as
generalization/specialization. In the example here Employee is
generalized entity class while Technician and Faculty are specialized
class of Employee.
Example – This example instance of “sub-class” relationships.
Here we have four sets employee: Secretary, Technician, and
Engineer. Employee is super-class of rest three set of individual sub-
class is subset of Employee set.
 An entity belonging to a sub-class is related with some
super-class entity. For instance emp no 1001 is a secretary,
and his typing speed is 68. Emp no 1009 is engineer (sub-
class) and her trade is “Electrical”, so forth.
 Sub-class entity “inherits” all attributes of super-class; for
example employee 1001 will have attributes eno, name,
salary, and typing speed.
Enhanced ER model of above example –

Constraints – There are two types of constraints on “Sub-class”

relationship.
1. Total or Partial – A sub-classing relationship is total if
every super-class entity is to be associated with some sub-
class entity, otherwise partial. Sub-class “job type based
employee category” is partial sub-classing – not necessary
every employee is one of (secretary, engineer, and
technician), i.e. union of these three types is proper subset
of all employees. Whereas other sub-classing “Salaried
Employee AND Hourly Employee” is total; union of entities
from sub-classes is equal to total employee set, i.e. every
employee necessarily has to be one of them.
2. Overlapped or Disjoint – If an entity from super-set can
be related (can occur) in multiple sub-class sets, then it is
overlapped sub-classing, otherwise disjoint. Both the
 examples: job-type based and salaries/hourly employee sub-
classing are disjoint.
Note – These constraints are independent of each other: can be
“overlapped and total or partial” or “disjoint and total or partial”.
Also sub-classing has transitive property.
Multiple Inheritance (sub-class of multiple super classes) –
An entity can be sub-class of multiple entity types; such entities are
sub-class of multiple entities and have multiple super-classes;
Teaching Assistant can subclass of Employee and Student both. A
faculty in a university system can be sub-class of Employee and
Alumnus both. In multiple inheritance, attributes of sub-class is
union of attributes of all super-classes.
Union –
 Set of Libray Members is UNION of Faculty, Student, and
Staff. A union relationship indicates either of type; for
example: a library member is either Faculty or Staff or
Student.
 Below are two examples shows how UNION can be depicted
in ERD – Vehicle Owner is UNION of PERSON and Company,
and RTO Registered Vehicle is UNION of Car and Truck.

You might see some confusion in Sub-class and UNION; consider

example in above figure Vehicle is super-class of CAR and Truck; this
is very much the correct example of subclass as well but here use it
different we are saying RTO Registered vehicle is UNION of Car and
Vehicle, they do not inherit any attribute of Vehicle, attributes of car
and truck are altogether independent set, where is in sub-classing
situation car and truck would be inheriting the attribute of vehicle
class.

Minimization of ER Diagrams
 Difficulty Level : Medium
 Last Updated : 13 May, 2020

Entity Relationship (ER) Diagram is diagrammatic representation

of data in databases, it shows how data is related.
Note: This article for those who already know what is ER diagram
and how to draw ER diagram.
1) When there is One to Many cardinality in ER diagram.
For example, a student can be enrolled only in one course, but a
course can be enrolled by many students

For Student(SID, Name), SID is the primary key. For Course ( CID,
C_name ), CID is the primary key
Student Course
(SID Name) ( CID C_name )
 -------------- -----------------
1 A c1 Z
2 B c2 Y
3 C c3 X
4 D

Enroll
(SID CID)
----------
1 C1
2 C1
3 c3
4 C2
Now the question is, what should be the primary key for Enroll SID
or CID or combined. We can’t have CID as primary key as you
can see in enroll for the same CID we have multiples SID. (SID ,
CID) can distinguish table uniquely, but it is not minimum. So SID
is the primary key for the relation enroll.

For above ER diagram, we considered three tables in database

Student
Enroll
Course
But we can combine Student and Enroll table renamed as
Student_enroll.
Student_Enroll
( SID Name CID )
---------------------
1 A c1
2 B c1
3 C c3
4 D c2
Student and enroll tables are merged now .
So require minimum two DBMS tables for Student_enroll and
Course.
Note: In One to Many relationship we can have minimum two
tables.

2. When there is Many to Many cardinality in ER Diagram.

Let us consider above example with the change that now student
can also enroll more than 1 course.

Student Course
( SID Name) ( CID C_name )
-------------- -----------------
1 A c1 Z
2 B c2 Y
 3 C c3 X
4 D

Enroll
( SID CID )
----------
1 C1
1 C2
2 C1
2 C2
3 c3
4 C2
Now, same question what is the primary key of Enroll relation, if
we carefully analyse the Enroll primary key for Enroll
table is ( SID , CID ).
But in this case we can’t merge Enroll table with any one of
Student and Course. If we try to merge Enroll with any one of the
Student and Course it will create redundant data.
Note: Minimum three tables are required in Many to Many
relationship.

3. One to One Relationship

There are two possibilities
A) If we have One to One relationship and we have total
participation at at-least one end.
For example, consider the below ER diagram.
A1 and B1 are primary keys of E1 and E2 respectively.
In the above Diagram we have total participation at E1 end.

Only a single table is required in this case having primary key of

E1 as its primary key.
Since E1 is in total participation, each entry in E1 is related to
only one entry in E2, but not all entries in E2 are related to an
entry in E1.
The primary key of E1 should be allowed as the primary key of the
reduced table, since if the primary key of E2 is used, it might
have null values for many of its entries in the reduced table.
Note: Only 1 table required.

B) One to One relationship with no total participation.

A1 and B1 are primary keys of E1 and E2 respectively.
Primary key of R can be A1 or B1, but we can’t still combine all
the three table into one. if we do, so some entries in combined
table may have NULL entries. So idea of merging all three table
into one is not good.
But we can merge R into E1 or E2. So minimum 2 tables are
required

Generalization, Specialization and

Aggregation in ER Model
 Difficulty Level : Medium
 Last Updated : 20 Aug, 2019
Prerequisite – Introduction of ER Model
Generalization, Specialization and Aggregation in ER model are used
for data abstraction in which abstraction mechanism is used to hide
details of a set of objects.
Generalization –
Generalization is the process of extracting common properties from
a set of entities and create a generalized entity from it. It is a
bottom-up approach in which two or more entities can be
generalized to a higher level entity if they have some attributes in
common. For Example, STUDENT and FACULTY can be generalized
to a higher level entity called PERSON as shown in Figure 1. In this
case, common attributes like P_NAME, P_ADD become part of higher
entity (PERSON) and specialized attributes like S_FEE become part of
specialized entity (STUDENT).

Specialization –
In specialization, an entity is divided into sub-entities based on their
characteristics. It is a top-down approach where higher level entity is
specialized into two or more lower level entities. For Example,
EMPLOYEE entity in an Employee management system can be
specialized into DEVELOPER, TESTER etc. as shown in Figure 2. In
this case, common attributes like E_NAME, E_SAL etc. become part
of higher entity (EMPLOYEE) and specialized attributes like TES_TYPE
become part of specialized entity (TESTER).

Aggregation –
An ER diagram is not capable of representing relationship between
an entity and a relationship which may be required in some
scenarios. In those cases, a relationship with its corresponding
entities is aggregated into a higher level entity. For Example,
Employee working for a project may require some machinery. So,
REQUIRE relationship is needed between relationship WORKS_FOR
and entity MACHINERY. Using aggregation, WORKS_FOR relationship
with its entities EMPLOYEE and PROJECT is aggregated into single
entity and relationship REQUIRE is created between aggregated
entity and MACHINERY.

Recursive Relationships in ER diagrams

 Difficulty Level : Medium
 Last Updated : 20 Aug, 2019
Prerequisite – ER Model
A relationship between two entities of similar entity type is called
a recursive relationship. Here the same entity type participates
more than once in a relationship type with a different role for each
instance. In other words, a relationship has always been between
occurrences in two different entities. However, it is possible for the
same entity to participate in the relationship. This is termed
a recursive relationship.

Example –
Let us suppose that we have an employee table. A manager
supervises a subordinate. Every employee can have a supervisor
except the CEO and there can be at most one boss for each
employee. One employee may be the boss of more than one
employee. Let’s suppose that REPORTS_TO is a recursive
relationship on the Employee entity type where each Employee
plays two roles
1. Supervisor
2. Subordinate

Supervisor and Subordinate are called “Role Names”. Here the

degree of the REPORTS_TO relationship is 1 i.e. a unary relationship.
 The minimum cardinality of Supervisor entity is ZERO since
the lowest level employee may not be a manager for
anyone.
 The maximum cardinality of Supervisor entity is N since an
employee can manage many employees.
 Similarly the Subordinate entity has a minimum cardinality
of ZERO to account for the case where CEO can never be a
subordinate.
 It maximum cardinality is ONE since a subordinate
employee can have at most one supervisor.
Note – Here none of the participants have a total participation since
both minimum cardinalities are Zero. Hence, the relationships are
connected by a single line instead of a double line in the ER
diagram.
To implement a recursive relationship, a foreign key of the
employee’s manager number would be held in each employee
record. A Sample table would look something like this:-
Emp_entity( Emp_no,Emp_Fname, Emp_Lname, Emp_DOB,
Emp_NI_Number, Manager_no);

Manager no - (this is the employee no of the employee's

manager)

Impedance Mismatch in DBMS

 Difficulty Level : Easy
 Last Updated : 09 Oct, 2019

Impedance mismatch is the term used to refer to the problems

that occurs due to differences between the database model and
the programming language model. The practical relational model
has 3 components these are:
1. Attributes and their data types
2. Tuples
3. Tables
Problems:
Following problems may occur due to the impedance mismatch:
1. The first problem that may occur is that is data type
mismatch means the programming language attribute
data type may differ from the attribute data type in the
data model.

Hence it is quite necessary to have a binding for each

host programming language that specifies for each
attribute type the compatible programming language
types. It is necessary to have different data types, for
example, we have different data types available in
different programming languages such as data types in C
are different from Java and both differ from SQL data
types.
2. The second problem that may occur is because the results
of most queries are sets or multisets of tuples and each
tuple is formed of a sequence of attribute values. In the
program, it is necessary to access the individual data
 values within individual tuples for printing or processing.

Hence there is a need for binding to map the query result

data structure which is a table to an appropriate data
structure in the programming language. A mechanism is
needed to loop over the tuples in a query result in order
to access a single tuple at a time and to extract individual
values from the tuple.
The extracted values are typically copied to appropriate
program variables for further processing by the program.
A cursor or iterator is a variable which is used for looping
over the tuples in a query result. Individual values within
each tuple are extracted into different or unique program
variables of the appropriate datatype.
Impedance mismatch is less of a problem when a special
database programming language is designed that uses the same
data model and data type as a database model for example
Oracles’sPL/SQL.
Example – UGC NET CS 2016 Aug – III | Question 7