Relationship Constraints in DBMS –
There are three Types of Relationship Constraints-
- Structural Constraints
- Participation Constraints
- Cardinality Ratio
- Overlap Constraints
- Covering Constraints
Structural Constraints are applicable for binary relationships and
Overlap and Covering Constraints are applicable for EERD(Extended ER Diagrams).
Participation (or) Optionality Constraints-
Participation concerns with the involvement of entities in a relationship. It specifies whether the existence of an entity depends on another entity. There are two types of Participation Constraints –
- Total/Mandatory Participation
- Partial/Optional Participation
Notations of Different Types of Participation In ER Diagram –
Total/Mandatory Participation or Existence Dependency-
Participation is said to be total if every entity in E participates in atleast one relationship in R (or) every entity in entity set must depend on another entity. For example, every department will have a startup date as a department was created on that startup date (SINCE ATTRIBUTE of MANAGE relationship). and that department is being handled from that date through a Manager. So, the participation of DEPARTMENT entity in the “MANAGE” relationship type is total.
Total Participation is also known as Existence Dependency.
In ER Diagram, it is represented as a Double Line, connecting the participating entity to the relationship.
Participation is said to be partial if only some entities in E participate in relationships in R (or) some entities in entity set are depend on some another entities in entity set. For example, It is not necessary that all employees manage some department Because all employees may not be the Manager . So the participation of “EMPLOYEE” entity in the “MANAGES” relationship type is partial.
Cardinality expresses the number of entities to which another entity can be associated via a relationship set (or)
It specifies the number of relationship instances that an entity can participate in a relation set.
There are 4 types of Cardinality Ratios :
- One-to-One Cardinality (1:1)
- One-to-Many Cardinality (1:m)
- Many-to-One Cardinality (m:1)
- Many-to-Many Cardinality (m:n)
Notations of Different Types of Cardinality In ER Diagram –
One-to-One Cardinality (1:1) –
An entity in set A is associated with atmost one entity in B, and an entity in B is associated with atmost one entity in A. This type of cardinality is referred as one to one Cardinality.
For example, an Employee as a Manager manage only one Department and the vice versa is also true as a department have only one Manager
Create Table MANAGE ( SSN varchar(10), DNO varchar(15), Primary key(SSN), Foreign Key(SSN) ........., Foreign Key(DNO) ........., );
Candidate Keys of "MANAGE" relationship - SSN,DNO
One to Many Cardinality (1:M) –
An entity in A is associated with any number (0 or more) with an entity B, but a entity in B, however can be associated with atmost one entity in A. For example, An employee as a Manager can manage more than one Department.
Create Table MANAGE ( SSN varchar(10), DNO varchar(15), SINCE date, Primary key(DNO), Foreign Key(SSN) ........., Foreign Key(DNO) ........., );
Candidate Keys of "MANAGE" relationship - DNO
Many to One Cardinality (M:1) –
An entity in A is associated with atmost one entity in B. An entity in B, however, can be associated with any number (0 or more) of entities in A. For example, An Employee can work only for one Department, But each Department can have 0 or more employees.
Create Table WORKS_ON ( SSN varchar(10), DNO varchar(15), SINCE date, Primary key(SSN), Foreign Key(SSN) ........., Foreign Key(DNO) ........., );
Candidate Keys of "WORKS_ON" relationship - SSN
Many to Many Cardinality (M:N) –
An entity in A is associated with any number (0 or more) of entities in B, and an entity in B is associated with any number( 0 or more) of entities in A. For example, An Employee can works on several Projects and a Project may have several Employees.
Create Table WORKS_ON ( SSN varchar(10), DNO varchar(15), SINCE date, Primary key(SSN,DNO), Foreign Key(SSN) ........., Foreign Key(DNO) ........., );
Candidate Keys of "WORKS_ON" relationship - SSN PID
Some Points regarding Cardinality and Participation –
1. Minimum Cardinality = 0, for partial participation and equal to 1 for total participation. 2. Maximum Cardinality = n, if one entity occurrence relates to multiple entity occurrences. 3. Minimum Cardinality give partial or total participation. 4. Maximum Cardinality give the maximum number of entities that is related to.
Relation Between Cardinality and Joins
Cardinality means it is a relation between tables using joins which tells that how many rows of one table will match with rows in other tables when these tables are joined.
In other words Relationships between the tables define Cardinality while explaining how each table links to the other.
Consider the two tables Consumer(custID) and Bill(BID, FK_custID). Each consumer pays the electricity bill every month. So, a customer will be associated with 12 bills after one year. It means there is one to many relationship between consumer and the bills he paid. In this case, the cardinality is defined from primary key of the Customer table(PK_Custid) to the foreign key (FK_Custid) of the Bill table.
The number of same rows in both the tables depends upon which join is used.
|Types of Relationships in ER Diagram||Entity and its Types|
Incoming search terms:
- structural constraints in dbms
- mapping constraints
- mapping constraints in dbms
- structural constraints in database
- structural constraints
- structural constraints definition in dbms
- mapping in dbms with example
- roles and structural constraints in dbms
- explain structural constraints of bank database
- mapping constraint in dbms