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
- Mapping Cardinalities
- Existence Dependencies
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:
- One-to-one (1:1)
- One-to-many (1:M)
- Many-to-one (M:1)
- 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
- 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
- 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
- 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
- One professor can teach more than one subjects
- One subject can be taught by more than one faculty
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.
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
She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists.