| Lesson 3 | Types of Relationships |
| Objective | Describe the three types of Relationships |
Three Types of Relationships in ERD Diagram
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)
1:n Relationship Explained
In database design, a "1:N (one-to-many) relationship" describes a situation where a single record in one table (the "one" side) can be associated with multiple records in another table (the "many" side). This type of relationship is common in relational databases and is typically implemented using foreign keys.
Here’s an example:
In this scenario:
This creates a 1:N relationship because:
The foreign key in the "Orders" table ensures that the relationship between the two tables is maintained, linking many orders back to a single customer.
Here’s an example:
- Table A (One side): "Customers"
- Table B (Many side): "Orders"
In this scenario:
- One customer can place multiple orders, but each order is placed by only one customer.
- The "Customers" table has a primary key (e.g., `CustomerID`).
- The "Orders" table has a foreign key (e.g., `CustomerID`) that references the primary key of the "Customers" table.
This creates a 1:N relationship because:
- A single row in the "Customers" table can relate to multiple rows in the "Orders" table (a customer can have many orders).
- A row in the "Orders" table can relate to only one row in the "Customers" table (each order belongs to one customer).
The foreign key in the "Orders" table ensures that the relationship between the two tables is maintained, linking many orders back to a single customer.
Relational Database Design
Following are simple examples of each:
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.
| 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.
- One To One:
An employee can work in at most one department, and a department can have at most one employee. - One To Many:
An employee can work in many departments (>=0), but a department can have at most one employee. - Many To One:
An employee can work in at most one department (<=1), and a department can have several employees. - Many To Many (default):
An employee can work in many departments (>=0), and a department can have several employees
Three relationship types implemented in Microsoft Access.
The following page contains three diagrams describing the 3 relationship types implemented in Microsoft Access.
Three Relationships in MS Access.
Three Relationships in MS Access.
Question: Within the context of data modeling, does the notation 1..* have the same meaning as 1:N for the mappings of tables?
In data modeling, different notations are often used to represent the relationships between entities (such as tables in a relational database). The notations "1..*" and "1:N" do, in fact, express the same type of relationship, albeit in different notational systems.
While the notation differs, both "1..*" and "1:N" represent a one-to-many relationship between two entities. In the context of relational databases, this often translates to a primary key to foreign key relationship between two tables, where one row in the first table can correspond to multiple rows in the second table. However, one subtle difference lies in the way they handle the lower limit of cardinality. In "1:N", it is usually assumed that the relationship can also be optional on the 'N' side, i.e., there may be instances of the first entity that are not related to any instance of the second entity. But "1.." explicitly states that at least one instance on the '' side must be associated. It's important to interpret and apply these notations within the specific conventions of the modeling methodology you are using.
The next lesson defines one-to-one relationships.
- "1..*" Notation: This is commonly used in UML (Unified Modeling Language) diagrams to denote a relationship. The "1.." signifies that one instance of an entity can be associated with zero or more instances of another entity. This notation allows you to specify both minimum and maximum cardinality. In this case, the minimum is one (at least one instance must exist) and the maximum is unspecified (denoted by ''), indicating any number of instances.
- "1:N" Notation: This notation, commonly used in ER (Entity-Relationship) diagrams, also signifies a relationship between two entities. The "1:N" means that for one instance of an entity, there can be zero, one, or more related instances of another entity. In other words, it denotes a one-to-many relationship.
While the notation differs, both "1..*" and "1:N" represent a one-to-many relationship between two entities. In the context of relational databases, this often translates to a primary key to foreign key relationship between two tables, where one row in the first table can correspond to multiple rows in the second table. However, one subtle difference lies in the way they handle the lower limit of cardinality. In "1:N", it is usually assumed that the relationship can also be optional on the 'N' side, i.e., there may be instances of the first entity that are not related to any instance of the second entity. But "1.." explicitly states that at least one instance on the '' side must be associated. It's important to interpret and apply these notations within the specific conventions of the modeling methodology you are using.
The next lesson defines one-to-one relationships.
[1]
binary relationship:In an Entity-Relationship Diagram (ERD), binary relationship types represent the associations between two entity types. These relationships can be classified further based on their cardinality, indicating the number of instances of one entity that can be related to a single instance of another entity, commonly represented as one-to-one, one-to-many, or many-to-many relationships.