5. RELATIONSHIPS AND THE RELATIONSHIPS TOOL
5.2.3: 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:
|
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 |
|
deptId |
department |
manager |
phone |
|
1 |
Marketing |
2 |
(204) 999-4444 |
|
2 |
Human Resources |
3 |
(204) 999-3333 |
|
3 |
Sales |
4 |
(204) 999-2222 |
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.
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.
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.
- 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.
- 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:
Exercises 4-6 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.
4) Create a query to list for each department, the name of the department and the name of its manager.
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.
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.
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. Hint: begin the query by dragging the Employee table onto the relationships diagram twice, and then drag empId to supervisor.
