LECTURE WEEK FOUR (4)
Entity relationship (ER) Modelling
- Cardinality (1:1, 1, N)
- Participation constraints (Mandatory/Optional)
- Designing ER diagrams for complex scenarios
The ER model defines the conceptual view of a database. It works around real-world entities and
the associations among them. At view level, the ER model is considered a good option for
designing databases.
In the view of databases, cardinality refers to the uniqueness of data values that are contained in
a column. High cardinality is nothing, but the column contains a large percentage of totally
unique values. Low cardinality is nothing but the column which has a lot of “repeats” in its data
range.
Cardinality between the tables can be of type one-to-one, many-to-one or many-to-many.
Mapping Cardinality
It is expressed as the number of entities to which another entity can be associated via a
relationship set.
For binary relationship set there are entity set A and B then the mapping
cardinality can be one of the following −
● One-to-one
● One-to-many
● Many-to-one
● Many-to-many
One-to-one relationship
One entity of A is associated with one entity of B.
Example
Given below is an example of the one-to-one relationship in the mapping cardinality. Here, one
department has one head of the department (HOD).
One-to-many relationship
An entity set A is associated with any number of entities in B with a possibility of zero and entity
in B is associated with at most one entity in A
Example
Given below is an example of the one-to-many relationship in the mapping cardinality. Here, one
department has many faculties.
Many-to-one relationship
An entity set A is associated with at most one entity in B and an entity set in B can be associated
with any number of entities in A with a possibility of zero.
Example
Given below is an example of the many-to-one relationship in the mapping cardinality. Here,
many faculties work in one department.
Many-to-many relationship
Many entities of A are associated with many entities of B.
An entity in A is associated with many entities of B and an entity in B is associated with many
entities of A.
Many to many=many to one + one to many
Example
Given below is an example of the many-to-many relationship in the mapping cardinality. Here,
many employees work on many projects.
Mandatory and optional Participation
Mandatory and optional participation are observed in E-R models between 2 different entities.
Mandatory Participation
In the mandatory participation, for every instance of entity A, there must exist an instance of
entity B and vice versa.
An example of Mandatory participation would be relationship between mother and child. The
child entity would exist only if there were a mother and similarly a mother would exist only if
there were a child.
Optional participation
In optional participation, it is not necessary for all the instances of the entity to participate in a
relationship. It may be that the number of instances participating for a particular entity may even
be zero.
Optional participation is good for depicting relationships that are not compulsory or may be
temporary i.e. relationships that may change over a period of time.
An example of this is Entity A is married to Entity B. Not all instances partake in this
relationship and the relationship may even change over time.
Designing ER diagrams for complex scenarios
For more examples of Designing ER diagrams for complex scenarios, students can visit the
website below.
[Link]