There are three types of relationships that can exist between two entities.
An entity-relationship (ER) diagram can be created based on these three types, which are listed below:
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)
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.
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 relationship
A child has exactly one biological father; a father can have many biological children.
M:N relationship
A 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.
Types of Constraints
Limit the number of possible combinations of entities that may participate in a relationship set. There are two types of constraints:
cardinality ratio and
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
An employee can work in at most one department, and a department can have at most one employee.
2) One To Many
An employee can work in many departments (>=0), but a department can have at most one employee.
3) 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)
An employee can work in many departments (>=0), and a department can have several employees
The next lesson defines one-to-one relationships.