5.3: Relationships

Two tables can be related through one-to-one, one-to-many, or many-to-many relationships. If you open the Relationships Tool for the University database you will see the following diagram showing two tables and one relationship:

Figure \(\PageIndex{1}\): Relationship: department offers courses.

There are two labels on the line which inform us the relationship is one-to-many for which there are two rules that are in place:

• for each department there will be zero or more courses for that department, and,
• each course is for exactly one department.

To create a relationship in MS Access you must

• open the Relationships Tool
• add the pertinent tables to the diagram if they are not there already
• click, hold, and drag a field (normally this is the PK) of one table to the related field (to become a FK) in the other table.

You will be asked whether or not Referential Integrity is to be enforced. As a general rule-of-thumb, you should select Yes – there must be some exceptional circumstance that makes you select No.

Once relationships are established using the Relationships Tool they are used by MS Access when you create queries – the relationships are used as the default for table joins.

One-to-Many

If you drag the PK field of one table to the other table, and if the FK does not have unique values, then you are creating a one-to-many relationship. MS Access will know and will display that fact for you. For each row in the referenced table there can be several related rows in the other table; that is, for a PK value there can be many rows in the other table with that value stored in the FK.

Example

Department and Course are related through the deptCode field. You can go through the exercise of creating the relationship between these two tables, but first you must remove the current relationship:

delete the existing relationships line (click the line, press delete, and follow through with the dialog to delete the relationship).

Now, click and drag the deptCode field in Department and drop it on top of the deptCode field in Course. On releasing the mouse MS Access will present the following dialogue box:

Figure \(\PageIndex{2}\): Defining a one-to-many relationship.

At this point MS Access is requesting the user to confirm the proper fields are being related, and for the user to make a choice regarding Referential Integrity and on some ‘Cascade’ options – we do not discuss cascading in these notes. You should choose Enforce Referential Integrity in almost all cases as this helps reduce the chance of corrupting data. For the above, when the user clicks Create, MS Access shows the relationships line with 1 on the one side and an infinity symbol on the many side of the relationship:

Figure \(\PageIndex{3}\): One-to-many relationship: department offers courses.

One-to-One

If you drag a PK field of one table to another table, and if the FK has unique values (a unique index exists for it) then you are creating a one-to-one relationship. For each row in the first table there can be at most one related row in the other table; a row in the referenced table has a PK value that equals the FK value in at most one row of the referencing table.

Many-to-Many

If you create a relationship in MS access where both fields you associated (via the click, hold, and drag sequence) do not have unique values (i.e. neither have unique indexes) then MS Access creates an ‘indeterminant’ relationship. In this situation a row in one table, A, may be related to multiple rows in the other table, B, and where a row in table B may be related to multiple rows in the table A.

This is not done very often and corresponds to a many-to-many relationship. Most database designers would avoid this in their database designs. If a database designer is faced with two tables, A and B, that are related via a many-to-many relationship, the designer would likely introduce a third table, say C, where A and C will be related via a one-to-many relationship and similarly, B and C will be related via a one-to-many relationship.

Later in these notes we discuss database design. We will see how many-to-many relationships can be decomposed into two one-to-many relationships.

Exercises

For these exercises use the Company database which does not have any relationships defined. The first few rows of Employee and Department are:

Employee
empId	firstName	lastName	supervisor	dept
1	Tanya	Dickson
2	Heidi	Herring	1	1
3	Hiroko	Hawkins	1	2
4	Emmanuel	Watkins	1	3
5	Oliver	Holt	2	1
6	Raphael	Delaney	3	2
7	Basia	Franks	2	1
8	Bruno	Pena	2	1

Department
deptId	department	manager	phone
1	Marketing	2	[phone]
2	Human Resources	3	[phone]
3	Sales	4	[phone]

Exercise \(\PageIndex{1}\)

Consider the Employee and Department tables. Note the Employee table has a field dept which indicates the department where the employee works. The relationship can be stated:

• Each department has zero or more employees, and,
• Each employee works in at most one department.

Create a one-to-many “works in” relationship between Employee and Department.

Exercise \(\PageIndex{2}\)

The Department table has a field manager which indicates the employee who is the head of the department. The relationship is stated:

• each department has one employee who manages that department, and,
• an employee may manage at most one department.

There is a unique index defined for the manager field and so you can create a one-to-one relationship “has manager” between Department and Employee.

If you do this exercise after exercise 1 has been completed, then you need to read the dialog boxes carefully when you create this second relationship between these tables. You must respond No to the following dialogue window.

Note how MS Access represents two relationships between two tables.

Exercise \(\PageIndex{3}\)

Consider the empId and the supervisor fields of Employee. Most employees report to someone – someone who is their supervisor. Only employee 1 does not report to anyone else. The supervises relationship can be stated:

an employee may supervise many other employees, and,

an employee reports to at most one other employee.

1. Create the supervises relationship.

   If you are doing this exercise after exercise 2 then your relationship diagram has 2 copies of the Employee table. If you are not doing this after exercise 2, then you must add Employee to the diagram twice so there are 2 copies of Employee on the diagram. Drag the PK, empId, from one copy of Employee to the supervisor field of the other copy. Note how MS Access draws this diagram.

2. The supervisor field is an implementation of a hierarchical reporting structure for our company. Use a piece of paper and draw the reporting structure for the company (for the data given at the start of these exercises). We have started this exercise showing the reporting structure for the first 4 employees:

The following exercises depend on the relationships diagram from the above exercises. When developing a query you will see that MS Access will include relationships when you include tables in the relationships area of a query. Do consider them closely to ensure they are the relationships and joins you need.

Exercise \(\PageIndex{4}\)

Create a query to list for each department, the name of the department and the name of its manager.

Exercise \(\PageIndex{5}\)

Create a query to list for each department, the name of the department and the names of its employees (the people who work in the department). Sequence your results by department name.

Exercise \(\PageIndex{6}\)

Create a query to list for each department, the name of the department head and the names of the department’s employees. Your query must list on each row of the result set the department name, the head’s last name, and the last name of each employee. Sequence your results by department name, and within department by employee last name.

Exercise \(\PageIndex{7}\)

Create a query that lists each supervisor and the employees he/she is supervising. Your query must list, on each row of the result set, the last name of the supervisor and the last name of the supervised employee. Sequence the results by supervisor and within supervisor by employee.