ER Diagrams   «Prev  Next»

Lesson 7One-to-many relationships, part 2
Objective Identify one-to-many relationships.

Identify One-to-many Relationships

Because one-to-one relationships are rare, and because all many-to-many relationships must be converted into one-to-many relationships (you will find out why later on in this module), most databases are comprised entirely of one-to-many relationships. A typical business rule for a 1:N relationship might be stated as follows:
Each employee is assigned to one department; each department has many employees.

The two entities represented in the example above are EMPLOYEE and DEPARTMENT.
The EMPLOYEE:DEPARTMENT relationship is 1:1; for DEPARTMENT:EMPLOYEE the relationship is 1:N.
The “N” in 1:N represents either a value greater than one or an unlimited value.
One-to-many relationships are fairly easy to identify. Consider the examples illustrated in the following Slide Show:

  1. Customer may place many orders (1:N)
  2. and each order comes from a single customer.
  3. Every project is assigned to a project manager
  4. But a project manager may be assigned to no more than three projects (1:N)
  5. A vendor supplies many products (1:N)
  6. but each product comes from a single vendor.

Identifying Relationships
A many-to-one (N:1) relationship is a mirror image of a one-to-many (1:N) relationship. They are treated identically.
Once a 1:N relationship has been identified, it is a snap to create a link between two entities: the entity (table) on the “many” side of the relationship receives the key attribute (primary key) from the entity (table) on the “one” side of the relationship. The graphic below illustrates the link between the EMPLOYEE and DEPARTMENT entities:

Link between EMPLOYEE and DEPARTMENT entities.
The entity on the 'many' side of the relationship receives the key attribute (primary key) from the entity on the 'one' side of the relationship.

The next lesson defines many-to-many relationships.

One To Many Relationships - Exercise

Before moving on to the next lesson, click the Exercise link below to check your understanding of one-to-many relationships.
One To Many Relationships - Exercise

SEMrush Tool Suite Plan, Write and Analyze Content