Skip to main content
Engineering LibreTexts

8.5: Relationships

  • Page ID
    92182
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    Relationships are the glue that holds the tables together. They are used to connect related information between tables.

    Relationship strength is based on how the primary key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary key of the related entity does not contain a primary key component of the parent entity. Company database examples include:

    • Customer(CustID, CustName)
    • Order(OrderID, CustID, Date)

    A strong, or identifying, relationship exists when the primary key of the related entity contains the primary key component of the parent entity. Examples include:

    • Course(CrsCode, DeptCode, Description)
    • Class(CrsCode, Section, ClassTime…)

    Types of Relationships

    Below are descriptions of the various types of relationships.

    One to many (1:M) relationship

    A one to many (1:M) relationship should be the norm in any relational database design and is found in all relational database environments. For example, one department has many employees. Figure \(\PageIndex{1}\) shows the relationship of one of these employees to the department.

    A light blue diamond in the middle connected on either side to a blue rectangle. The rectangle on the left says EMPLOYEE and is connected with a line to five yellow ovals with the words Birthdate, Name, Address, Salary, EID. The diamond is also connected to a blue rectangle on its right with the word DEPARTMENT and that is connected with lines to three yellow ovals with the words Name, Office, DeptID.
    Figure \(\PageIndex{1}\): Example of a one to many relationship.

    One to one (1:1) relationship

    A one to one (1:1) relationship is the relationship of one entity to only one other entity, and vice versa. It should be rare in any relational database design. In fact, it could indicate that two entities actually belong in the same table.

    An example from the COMPANY database is one employee is associated with one spouse, and one spouse is associated with one employee.

    Many to many (M:N) relationships

    For a many to many relationship, consider the following points:

    • It cannot be implemented as such in the relational model.
    • It can be changed into two 1:M relationships.
    • It can be implemented by breaking up to produce a set of 1:M relationships.
    • It involves the implementation of a composite entity.
    • Creates two or more 1:M relationships.
    • The composite entity table must contain at least the primary keys of the original tables.
    • The linking table contains multiple occurrences of the foreign key values.
    • Additional attributes may be assigned as needed.
    • It can avoid problems inherent in an M:N relationship by creating a composite entity or bridge entity. For example, an employee can work on many projects OR a project can have many employees working on it, depending on the business rules. Or, a student can have many classes and a class can hold many students.

    Figure \(\PageIndex{2}\) shows another another aspect of the M:N relationship where an employee has different start dates for different projects. Therefore, we need a JOIN table that contains the EID, Code and StartDate.

    image
    Figure \(\PageIndex{2}\): Example where employee has different start dates for different projects.

    Example of mapping an M:N binary relationship type

    • For each M:N binary relationship, identify two relations.
    • A and B represent two entity types participating in R.
    • Create a new relation S to represent R.
    • S needs to contain the PKs of A and B. These together can be the PK in the S table OR these together with another simple attribute in the new table R can be the PK. 
    • The combination of the primary keys (A and B) will make the primary key of S.

    Unary relationship (recursive)

    unary relationship, also called recursive, is one in which a relationship exists between occurrences of the same entity set. In this relationship, the primary and foreign keys are the same, but they represent two entities with different roles. See Figure \(\PageIndex{3}\)an example.

    For some entities in a unary relationship, a separate column can be created that refers to the primary key of the same entity set.

    image
    Figure \(\PageIndex{3}\): Example of a unary relationship.

    Ternary Relationships

    ternary relationship is a relationship type that involves many to many relationships between three tables. 

    Refer to Figure \(\PageIndex{4}\) for an example of mapping a ternary relationship type. Note n-ary means multiple tables in a relationship. (Remember, N = many.)

    • For each n-ary (> 2) relationship, create a new relation to represent the relationship.
    • The primary key of the new relation is a combination of the primary keys of the participating entities that hold the N (many) side.
    • In most cases of an n-ary relationship, all the participating entities hold a many side.
    Ternary-Mapping-Relationships-300x197
    Figure \(\PageIndex{4}\): Example of a ternary relationship.

     

     

    Key Terms

    alternate key: all candidate keys not chosen as the primary key

    candidate key: a simple or composite key that is unique (no two rows in a table may have the same value) and minimal (every column is necessary)

    characteristic entities: entities that provide more information about another table

    composite attributes: attributes that consist of a hierarchy of attributes

    composite key: composed of two or more attributes, but it must be minimal

    dependent entities: these entities depend on other tables for their meaning

    derived attributes: attributes that contain values calculated from other attributes

    derived entities: see dependent entities

    EID: employee identification (ID)

    entity: a thing or object in the real world with an independent existence that can be differentiated from other objects

    entity relationship (ER) data model: also called an ER schema, are represented by ER diagrams. These are well suited to data modelling for use with databases.

    entity relationship schema: see entity relationship data model

    entity set:a collection of entities of an entity type at a point of time

    entity type: a collection of similar entities

    foreign key (FK): an attribute in a table that references the primary key in another table OR it can be null

    independent entity: as the building blocks of a database, these entities are what other tables are based on

    kernel: see independent entity

    key: an attribute or group of attributes whose values can be used to uniquely identify an individual entity in an entity set

    multivalued attributes: attributes that have a set of values for each entity

    n-ary: multiple tables in a relationship

    null: a special symbol, independent of data type, which means either unknown or inapplicable; it does not mean zero or blank

    recursive relationship: see unary relationship

    relationships: the associations or interactions between entities; used to connect related information between tables

    relationship strength:  based on how the primary key of a related entity is defined

    secondary key an attribute used strictly for retrieval purposes 

    simple attributes: drawn from the atomic value domains

    SIN: social insurance number

    single-valued attributes: see simple attributes

    stored attribute: saved physically to the database

    ternary relationship: a relationship type that involves many to many relationships between three tables.

    unary relationship: one in which a relationship exists between occurrences of the same entity set.

     

     

    Exercises

     

    1. What two concepts are ER modelling based on?
    2. The database in Figure \(\PageIndex{5}\) is composed of two tables. Use this figure to answer questions 2.1 to 2.5.
      Ch8-Exercises -Fig8-1
      Figure \(\PageIndex{5}\): Director and Play tables for question 2, by A. Watt.
      1. Identify the primary key for each table.
      2. Identify the foreign key in the PLAY table.
      3. Identify the candidate keys in both tables.
      4. Draw the ER model.
      5. Does the PLAY table exhibit referential integrity? Why or why not?
    3. Define the following terms (you may need to use the Internet for some of these):
      schema
      host language
      data sublanguage
      data definition language
      unary relation
      foreign key
      virtual relation
      connectivity
      composite key
      linking table
    4. The RRE Trucking Company database includes the three tables as shown in Figure \(\PageIndex{6}\). Use Figure 8.5.6 to answer questions 4.1 to 4.5.
      Ch8-Exercises -Fig8-2
      Figure \(\PageIndex{6}\): Truck, Base and Type tables for question 4, by A. Watt.
      1. Identify the primary and foreign key(s) for each table.
      2. Does the TRUCK table exhibit entity and referential integrity? Why or why not? Explain your answer.
      3. What kind of relationship exists between the TRUCK and BASE tables?
      4. How many entities does the TRUCK table contain ?
      5. Identify the TRUCK table candidate key(s).
        Ch8-Exercises -Fig8-3
        Figure \(\PageIndex{7}\): Customer and BookOrders tables for question 5, by A. Watt.
    5. Suppose you are using the database in Figure \(\PageIndex{7}\), composed of the two tables. Use Figure \(\PageIndex{7}\) to answer questions 5.1 to 5.6.
      1. Identify the primary key in each table.
      2. Identify the foreign key in the BookOrders table.
      3. Are there any candidate keys in either table?
      4. Draw the ER model.
      5. Does the BookOrders table exhibit referential integrity? Why or why not?
      6. Do the tables contain redundant data? If so which table(s) and what is the redundant data?
    6. Looking at the student table in Figure \(\PageIndex{8}\), list all the possible candidate keys. Why did you select these?
      Ch8-Exercises - No6 Student
      Figure \(\PageIndex{8}\): Student table for question 6, by A. Watt.

       

      Ch8-Exercises -No7 School Database
      Figure \(\PageIndex{9}\): ERD of school database for questions 7-10, by A. Watt.

      Use the ERD of a school database in Figure \(\PageIndex{9}\) to answer questions 7 to 10.

    7. Identity all the kernels and dependent and characteristic entities in the ERD.
    8. Which of the tables contribute to weak relationships? Strong relationships?
    9. Looking at each of the tables in the school database in Figure \(\PageIndex{9}\), which attribute could have a NULL value?  Why?
    10. Which of the tables were created as a result of many to many relationships?

    Also see Appendix B: Sample ERD Exercises


    8.5: Relationships is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?