Structural Constraints of Relationships in ER Model
Prerequisite – ER Model To understand Structural Constraints, we must take a look at Cardinality Ratios and Participation Constraints. Cardinality Ratios of relationships : The entities are denoted by rectangle and relationships by diamond. There are numbers (represented by M and N) written above the lines which connect relationships and entities. These are called cardinality ratios. These represent the maximum number of entities that can be associated with each other through relationship, R. Types of Cardinality : There can be 4 types of cardinality –
- One-to-one (1:1) – When one entity in each entity set takes part at most once in the relationship, the cardinality is one-to-one.
- One-to-many (1: N) – If entities in the first entity set take part in the relationship set at most once and entities in the second entity set take part many times (at least twice), the cardinality is said to be one-to-many.
- Many-to-one (N:1) – If entities in the first entity set take part in the relationship set many times (at least twice), while entities in the second entity set take part at most once, the cardinality is said to be many-to-one.
- Many-to-many (N: N) – The cardinality is said to be many to many if entities in both the entity sets take part many times (at least twice) in the relationship set.
Participation Constraints : Participation Constraints tell us that the participation in a relationship can either be total or partial. When each entity in an entity set participates in a relation, it is called Total Participation. However, when all entities in the given entity set do not participate in a relation, it is called Partial Participation. Structural Constraints : Structural Constraints are also called Structural properties of a database management system (DBMS). Cardinality Ratios and Participation Constraints taken together are called Structural Constraints. 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. 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. Note – Number of times an entity participates in a relationship is same as the number appearance of the entity in the tuples.