Unit-2

❖ ER Modeling
❖ Entity type
❖ Entity set
❖ Attribute
❖ Key relationship
❖ Relation types
❖ Roles and structural constraints
❖ Enhenced ER concepts
❖ Sub class
❖ Super class
❖ Inheritance
❖ Specialisation
❖ Generalisation
ER Model
The Entity Relational Model is a model for identifying entities to be represented in the database and
representation
of how those entities are related. The ER data model specifies enterprise schema that represents the overall
logical structure of a database graphically.
ER models are used to model real-world objects like a person, a car, or a company and the relation between
these real-world objects. In short, the ER Diagram is the structural format of the database.
ER Diagrams In DBMS
▪ ER diagrams are used to represent the E-R model in a database, which makes them easy to be converted into
relations (tables).
▪ ER diagrams require no technical knowledge and no hardware support.
▪ These diagrams are very easy to understand and easy to create even for a naive user.
Symbols Used in ER Model
•Rectangles: Rectangles represent Entities in the ER Model.
•Ellipses: Ellipses represent Attributes in the ER Model.
•Diamond: Diamonds represent Relationships among Entities.
•Lines: Lines represent attributes to entities and entity sets with other relationship types.
•Double Ellipse: Double Ellipses represent Multi-Valued Attributes.
•Double Rectangle: Double Rectangle represents a Weak Entity.
Components of ER Diagram
ER Model consists of Entities, Attributes, and Relationships among Entities in a Database System.
Entity
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.
Entity Set: An Entity is an object of Entity Type and a set of all entities is called an entity set. For Example, E1 is
an entity having Entity Type Student and the set of all students is called Entity Set. In ER diagram, Entity Type is
represented as:
Strong Entity
A Strong Entity is a type of entity that has a key Attribute. Strong Entity does not depend on other Entity in the
Schema. It has a primary key, that helps in identifying it uniquely, and it is represented by a rectangle. These are
called Strong Entity Types.
2. Weak Entity
An Entity type has a key attribute that uniquely identifies each entity in the entity set. But some entity type exists for
which key attributes can’t be defined. These are called Weak Entity types.
For Example, A company may store the information of dependents (Parents, Children, Spouse) of an Employee.
But the dependents don’t have existed without the employee. So Dependent will be a Weak Entity Type and
Employee will be Identifying Entity type for Dependent, which means it is Strong Entity Type.
A weak entity type is represented by a Double Rectangle. The participation of weak entity types is always total. The
relationship between the weak entity type and its identifying strong entity type is called identifying relationship and
it is represented by a double diamond.

Attributes
Attributes are the properties that define the entity type. For example, Roll_No, Name, DOB, Age, Address, and
Mobile_No are the attributes that define entity type Student. In ER diagram, the attribute is represented by an
oval.

1. Key Attribute
The attribute which uniquely identifies each entity in the entity set is called the key attribute. For example,
Roll_No will be unique for each student. In ER diagram, the key attribute is represented by an oval with
underlying lines.

Composite Attribute
An attribute composed of many other attributes is called a composite attribute. For example, the Address
attribute of the student Entity type consists of Street, City, State, and Country. In ER diagram, the composite
attribute is represented by an oval comprising of ovals.
 Multivalued Attribute
An attribute consisting of more than one value for a given entity. For example, Phone_No (can be more than one for
a given student). In ER diagram, a multivalued attribute is represented by a double oval.

Derived Attribute
An attribute that can be derived from other attributes of the entity type is known as a derived attribute. e.g.; Age (can
be derived from DOB). In ER diagram, the derived attribute is represented by a 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, the relationship type is represented by a
diamond and connecting the entities with lines.
A set of relationships of the same type is known as a relationship set. The following relationship set depicts
S1 as enrolled in C2, S2 as enrolled in C1, and S3 as registered in C3.

Degree of a Relationship Set

The number of different entity sets participating in a relationship set is called the degree of a relationship
set.
1. Unary Relationship: When there is only ONE entity set participating in a relation, the relationship is called
a unary relationship. For example, one person is married to only one person.
 2. Binary Relationship: When there are TWO entities set participating in a relationship, the relationship is
called a binary relationship. For example, a Student is enrolled in a Course.

3. n-ary Relationship: When there are n entities set participating in a relation, the relationship is called an 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 one female and a female can marry one male. So the
relationship will be one-to-one.
the total number of tables that can be used in this is 2.
Using Sets, it can be represented as:

2. One-to-Many: In one-to-many mapping as well where each entity can be related to more than one
relationship and the total number of tables that can be used in this is 2. Let us assume that one surgeon
deparment can accomodate many doctors. So the Cardinality will be 1 to M. It means one deparment has
many Doctors.
total number of tables that can used is 3.
Using sets, one-to-many cardinality can be represented as:

3. Many-to-One: When entities in one entity set can take part only once in the relationship set and entities in
other entity sets 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.
The total number of tables that can be used in this is 3.
Using Sets, it can be represented as:

4. 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.
the total number of tables that can be used in this is 3.
Using Sets, it can be represented as:

Participation Constraint
Participation Constraint is applied to 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 students will be total. Total participation is shown by a double line in
the 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 students, the participation in the 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,

How to Draw ER Diagram?

•The very first step is Identifying all the Entities, and place them in a Rectangle, and labeling them accordingly.
•The next step is to identify the relationship between them and pace them accordingly using the Diamond, and
make sure that, Relationships are not connected to each other.
•Attach attributes to the entities properly.
•Remove redundant entities and relationships.
•Add proper colors to highlight the data present in the database.
Structural Constraints
Structural Constraints are also called Structural properties of a database management system (DBMS). The name
constraints refer to the fact that such limitations must be imposed on the data, for the DBMS system to be
consistent with the requirements.
There are two things when it comes to structural constraints-
i)Cardinality ratio
ii)Participation constraints
i)Cardinality ratio:Cardinality ratio is maximim no. of relationship an entity is participating.
ii)Participation constraints:Minimum cardinality i.e minimum no. of relationship an entity is participating

The Structural constraints are represented by Min-Max notation. This is a pair of numbers(m, n) that appear
on the connecting line between the entities and their relationships. The minimum number of times an entity
can appear in a relation is represented by m whereas, the maximum time it is available is denoted by n. If m is
0 it signifies that the entity is participating in the relation partially, whereas, if m is either greater than or
equal to 1, it denotes total participation of the entity.
 Enhanced ER Model
Today 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 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 represent the 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 relationships found in real entities.
However, this kind of relationship was added later as an enhanced extension to the classical ER
model. Specialized classes are often called subclass while a generalized class is called a superclass,
probably inspired by object-oriented programming. A sub-class is best understood by “IS-A
analysis”. The following statements hopefully make some sense to your mind “Technician IS-A
Employee”, and “Laptop IS-A Computer”.
.An entity is a specialized type/class of another entity. For example, a Technician is a special
Employee in a university system Faculty is a special class of Employees. We call this phenomenon
generalization/specialization. In the example here Employee is a generalized entity class while the
Technician and Faculty are specialized classes of Employee.
Example:
This example instance of “sub-class” relationships. Here we have four sets of employees: Secretary,
Technician, and Engineer. The employee is a super-class of the rest three sets of individual sub-class is a
subset of Employee set.

•An entity belonging to a sub-class is related to some

super-class entity. For instance emp, no 1001 is a
secretary, and his typing speed is 68. Emp no 1009 is an
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.
constraints – There are two types of constraints on the “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 a proper subset of all employees. Whereas other
sub-classing “Salaried Employee AND Hourly Employee” is total; the union of entities from sub-
classes is equal to the total employee set, i.e. every employee necessarily has to be one of them.
Overlapped or Disjoint – If an entity from a 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.
Multiple Inheritance (sub-class of multiple superclasses) –
An entity can be a 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 a subclass of Employee and Alumnus. In
multiple inheritances, attributes of sub-class are the union of attributes of all super-classes.
Union –
•Set of Library Members is UNION of Faculty, Student, and Staff. A union relationship indicates
either type; for example, a library member is either Faculty or Staff or Student.
•Below are two examples that show 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 an example in above figure Vehicle is
super-class of CAR and Truck; this is very much the correct example of the subclass as well but here use it
differently 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.
An Enhanced Entity-Relationship (EER) model is an extension of the original Entity-Relationship (ER) model
that includes additional concepts and features to support more complex data modeling requirements. The EER
model includes all the elements of the ER model and adds new constructs, such as subtypes and supertypes,
generalization and specialization, and inheritance.
Here are some of the key features of the EER model:
•Subtypes and Supertypes: The EER model allows for the creation of subtypes and supertypes. A supertype is
a generalization of one or more subtypes, while a subtype is a specialization of a supertype. For example, a
vehicle could be a supertype, while car, truck, and motorcycle could be subtypes.
•Generalization and Specialization: Generalization is the process of identifying common attributes and
relationships between entities and creating a supertype based on these common features. Specialization is
the process of identifying unique attributes and relationships between entities and creating subtypes based
on these unique features.
•Inheritance: Inheritance is a mechanism that allows subtypes to inherit attributes and relationships from their
supertype. This means that any attribute or relationship defined for a supertype is automatically inherited by
all its subtypes.
•Constraints: The EER model allows for the specification of constraints that must be satisfied by entities and
relationships. Examples of constraints include cardinality constraints, which specify the number of
relationships that can exist between entities, and participation constraints, which specify whether an entity is
required to participate in a relationship.
•Overall, the EER model provides a powerful and flexible way to model complex data relationships, making it
a popular choice for database design. An Enhanced Entity-Relationship (EER) model is an extension of the
traditional Entity-Relationship (ER) model that includes additional features to represent complex relationships
between entities more accurately. Some of the main features of the EER model are:
•Subclasses and Superclasses: EER model allows for the creation of a hierarchical structure of entities
where a superclass can have one or more subclasses. Each subclass inherits attributes and relationships
from its superclass, and it can also have its unique attributes and relationships.
•Specialization and Generalization: EER model uses the concepts of specialization and generalization to
create a hierarchy of entities. Specialization is the process of defining subclasses from a superclass,
while generalization is the process of defining a superclass from two or more subclasses.
•Attribute Inheritance: EER model allows attributes to be inherited from a superclass to its subclasses.
This means that attributes defined in the superclass are automatically inherited by all its subclasses.
•Union Types: EER model allows for the creation of a union type, which is a combination of two or more
entity types. The union type can have attributes and relationships that are common to all the entity
types that make up the union.
•Aggregation: EER model allows for the creation of an aggregate entity that represents a group of
entities as a single entity. The aggregate entity has its unique attributes and relationships.
•Multi-valued Attributes: EER model allows an attribute to have multiple values for a single entity
instance. For example, an entity representing a person may have multiple phone numbers.
•Relationships with Attributes: EER model allows relationships between entities to have attributes.
These attributes can describe the nature of the relationship or provide additional information about the
relationship.
Generalization
Generalization is the process of extracting common properties from a set of entities and creating 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, and P_ADD become part of a higher entity (PERSON), and specialized attributes like S_FEE
become part of a specialized entity (STUDENT).

Generalization is also called as ‘ Bottom-up approach”.

Specialization
In specialization, an entity is divided into sub-entities based on its characteristics. It is a top-down approach
where the higher-level entity is specialized into two or more lower-level entities. For Example, an 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 a higher entity (EMPLOYEE),
and specialized attributes like TES_TYPE become part of a specialized entity (TESTER).
Specialization is also called as ” Top-Down approch”.
Aggregation
An ER diagram is not capable of representing the 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. Aggregation is an abstraction through which we can represent
relationships as higher-level entity sets.
For Example, an Employee working on a project may require some machinery. So, REQUIRE relationship is
needed between the relationship WORKS_FOR and entity MACHINERY. Using aggregation, WORKS_FOR
relationship with its entities EMPLOYEE and PROJECT is aggregated into a single entity and relationship
REQUIRE is created between the aggregated entity and MACHINERY.