SQL Mapping Carnality Explained with Examples

Overview of SQL Mapping Cardinality

SQL Cardinality refers to the uniqueness in the data value of a particular column, the higher the uniqueness the cardinality will be higher in percentage, and Low cardinality means the column contains repeat values in a column data

An E-R enterprise schema defines certain constraints to which the contents of a database must conform; there are two most important types of mapping constraints in the database management system

1. Mapping Cardinalities
2. Existence Dependencies

 

Mapping Cardinalities

SQL mapping cardinalities define the relationship between the entities or tables of one relationship set with the entities or tables of another relationship sets, mapping cardinalities are most useful in describing binary relationship sets, although they can contribute to the description of relationship sets that involve more than two entity sets

For binary relationship set R on an entity set A and B, there are four possible mapping cardinalities. These are as follows:

1. One-to-one (1:1)
2. One-to-many (1:M)
3. Many-to-one (M:1)
4. Many-to-many (M: M)

 

One-to-one mapping (1:1)

In one-to-one mapping, an entity in entity set E1 is associated with at most one entity of entity set E2, and an entity in E2 is associated with at most one entity in E1

Here each entity of the entity set participates only once in the relationship

 

Example of One-to-one mapping

Example 1: If student and course are 2 entity sets, then we can define a one to one relationship, one student can be enrolled in at most one course and one course can enroll only one student

Here,

• One student can enroll in at most one course.
• One course can be enrolled by at most one student

 

One-to-many mapping (1:M)

In a one-to-many mapping, an entity in entity set E1 is associated with more than one entity of entity set E2, and an entity in E2 is associated with at most one entity in E1

Here entities in one entity set can take participation any number of times in a relationship set and entities in another entity set can take participation only once in a relationship set

 

Example of one-to-many mapping

Example 2: If student and course are 2 entity sets, then we can define a one-to-many relationship, one student can be enrolled in more than one course and one course can enroll only one student

Here,

• One student can enroll in any number (zero or more) of courses.
• One course can be enrolled by at most one student.

 

Many-to-one mapping (M:1)

In many-to-one mapping, an entity in entity set E1 is associated with at most one entity of entity set E2, and an entity in E2 is associated with any number of entities in E1

Here more than one instance of an entity is associated with a single instance of another entity then it is called many to one relationship

 

Example of many-to-one mapping

Example 3: Students can take admitted to one college, whereas a college can give admission to any number of students

Here,

• One student can take admission in at most one college
• College can give admission to any number of students

 

Many-to-many mapping (M:M)

In many-to-one mapping, an entity in entity set E1 is associated with any number of entities of an entity set E2, and an entity in E2 is associated with any number of entities in E1

Many-to-many relationships are not ideal, the data would be duplicated

The appropriate mapping cardinality for a particular relationship set obviously depends on the real-world situation that the relationship set is modeling

 

Example of many-to-many mapping

Example 4: a profession can teach more than one subject and one subject can also be taught by more than one professor

Here,

• One professor can teach more than one subjects
• One subject can be taught by more than one faculty

 

Existence Dependencies

Existence dependencies are another important class of SQL mapping constraints in the database management system

If the existence of entity E1 depends on the existence of entity E2, the E1 is said to be existence dependent as E2. So, operationally if entity E2 is deleted then entity E1 will also be deleted

In this scenario Entity, E2 is said to be a Dominant Entity and E1 is said to be a Subordinate entity.

 

Example of existence dependencies

Example 5: Suppose there are two entity sets bill and bill-Payment, an entity in bill-Payment is existence dependent on an entity in the bill since the entity bill-payment does not exist without the existence of the bill Entity Set.

If a bill entity is deleted, all its corresponding payment entities from the Invoice-Payment entity set are deleted, but the vice-versa is not true

 

Total Participation in SQL Mapping

The participation of an Entity Set E1 in the relationship set R is said to be total if each attribute of entity E1 participates in at least one relationship in R.

 

Partial Participation in SQL Mapping

If only some entities of E participate in R, then the participation of entity set E in R is said to be Partial. Total participation is closely related to existence dependency.

 

Summary

In this article on SQL Mapping Cardinality, we have covered an overview of SQL mapping cardinality with two types of mapping constraints of SQL one is mapping cardinality and another is existence dependency, also explained four different types of  SQL mapping cardinalities with examples and diagrams, existence dependencies constraint is also explained with examples. We have concluded the article by explaining two ways of participation of entities in a relationship, total participation, and partial participation

 

References

SQL  constraints

 

Read More

SQL create and manage relationships