8.5: Relationships

Last updated
Save as PDF

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}}\)

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.

*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)

A 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.

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

Ternary Relationships

A 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

What two concepts are ER modelling based on?
The database in Figure \(\PageIndex{5}\) is composed of two tables. Use this figure to answer questions 2.1 to 2.5.

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?
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
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.

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).
  
  Figure \(\PageIndex{7}\): Customer and BookOrders tables for question 5, by A. Watt.
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?
Looking at the student table in Figure \(\PageIndex{8}\), list all the possible candidate keys. Why did you select these?

Figure \(\PageIndex{8}\): Student table for question 6, by A. Watt.

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

Also see Appendix B: Sample ERD Exercises