Relationship Type Constraints

Relationship Type Constraints
The constraints of relationship types limit the possible combination of entities that participate in the relationship set. Two main types of constraints is mapping cardinality and participation
Mapping Cardinality
Mapping Cardinality describes the maximum number of entities that a given entity can be associated with via a relationship. In this section, we consider only the cardinality constraint for the binary relationship. The possible cardinality for binary relationship types are : One - to- One (1-1) , One – to – Many (1 – N) and Many – to – Many (M-N).

* One to one relationship: Given two entity sets A and B, there is a one-to-one relationship between A and B if each entity in the set A is associated with at most one entity in the set B and vice versa each entity in the set B is associated with at most one entity in the set A.


Figure 15: One-to-one relationship example
Example: An employee if is a manager then can manage one department and a department can have only one manager.


Figure 16: One-to-one relationship notation

* One to many relationship: There is a one to many relationship sets associates two entities sets A and B if each entity in A is associated with several entities in B however, each entity in B is associated with at most one entity in A.


Figure 17: One-to-many relationship example
Example: There are many employees work in a department however, an employee can work for only one department.


Figure 18: One-to-many relationship notation

* Many to many relationship : There is a many to many relationship sets associates two entities sets A and B if each entity in A is associated with several entities in B however, each entity in B is associated with several entities in A.


Figure 19: Many-to-many relationship example
Example: An employee can join in several projects and a project can have several employees


Figure 20: Many-to-many relationship notation
Participation
The participation constraints specifiy whether the existence of an entity depends on its being related to another entity via the relationship type.
Participation in relationship set R by entity set A may be

* Total : It means every entity a in A participates in at least 1 relationship in R
* Partial: It means only some a in A participate in relationships in R

Example Every project have at least 1 employee joined in it.
Not every employee in the company join in a project


Figure 21: Participation in relationship notation
Attributes of Relationship Types
The relationship types can have attributes like entity types. For 1-1 and 1-N relationship types, attributes of the relationship can be migrated to one of the participating entity types. However, for M-N relationship types, attributes must be specified as the relationship attribute.
Example: The StartDate attribute records the date on which an employee joins in a project


Figure 22: Attribute in relationship example
Weak Entity Type
Weak entity types are those whose existence depends on the existence of other entity type. Entities belongs to weak entity type are identified by being related to specific entities from another entity type which is called strong entity types.
The weak entity type does not have key attributes of their own. The keys of this type os partially or totally derived from strong entity types.
In ER diagram, weak entity and strong/weak relationship are denoted by double box/diamond
Example: Entity type DEPENDENT related to EMPLOYEE. This entity type is used to keep track of the dependents of each employees via 1-N relationship. DEPENDENT entity has Name, Birthdate and Relationship. Two dependents of two distinct employees might have the same {Name, Birthdate, Relationship} values but they are totally different. They are identified as distinct only after consider the association with particular employee entities to which the dependents relates. So the key of the DEPENDENT might be {EID, Name} in which EID is the attribute from the strong entity type EMPLOYEE.


Figure 23: Weak entity type notation
Entity Relationship Diagram for COMPANY database
Modeling Entities:


Figure 24: Entities of COMPANY database


Figure 25: Entities of COMPANY database
Modeling Relationships


Figure 26: Relationships of COMPANY database