RelationalDBDesignRelationalDBDesign





ER Diagrams   «Prev  Next»
Lesson 3Types of Relationships
ObjectiveDescribe the three types of Relationships

Three Types of Relationships in an ERD Diagram

There are three types of relationships that can exist between two entities.
An entity-relationship (ER) diagram is created based on these three types, which are listed below:
  1. one-to-one relationship: In relational database design, a one-to-one (1:1) relationship exists when zero or one instance of entity A can be associated with zero or one instance of entity B, and zero or one instance of entity B can be associated with zero or one instance of entity A. (abbreviated 1:1)
  2. one-to-many relationship: (abbreviated 1:N) In relational database design, a one-to-many (1:N) relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity B; but for one instance of entity B, there exists zero or one instance of entity A.
  3. many-to-many relationship: In relational database design, a many-to-many (M:N) relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity B; and for one instance of entity B, there exists zero, one, or many instances of entity A. (abbreviated M:N)
Following are simple examples of each:
1:1 relationship In a traditional American marriage, a man can be married to only one woman; a woman can be married to only one man.
1:N relationshipA child has exactly one biological father; a father can have many biological children.
M:N relationshipA student can enroll in many classes; a class can have many enrolled students.

In the business world, one-to-one relationships are few and far between. One-to-many and many-to-many relationships, on the other hand, are common. However, as will be explained later, many-to-many relationships are not permitted in a relational database and must be converted into one-to-many relationships. Relational databases are comprised almost entirely of tables in one-to-many relationships.



Big Data Modeling

Types of Constraints

Limit the number of possible combinations of entities that may participate in a relationship set. There are two types of constraints:
  1. cardinality ratio and
  2. participation constraints.
Very useful concept in describing binary relationship types. For binary relationships, the cardinality ratio must be one of the following types:

1) One To One

one-many
An employee can work in at most one department, and a department can have at most one employee.

2) One To Many

one-many
An employee can work in many departments (>=0), but a department can have at most one employee.

3) Many To One

many-to-one
An employee can work in at most one department (<=1), and a department can have several employees.

4) Many To Many (default)

many-to-many
An employee can work in many departments (>=0), and a department can have several employees

The next lesson defines one-to-one relationships.