The data we need to create tblSectionsInstructors already exist in tblSections. To make the new junction table, we can copy tblSections, give it a new name, and adjust its fields accordingly. Once the table is created, we will modify the relationships to include the new table.
Another way of creating a copy of a table is to simply copy it from the Navigation pane, then paste. This will prompt us to give the new table a name. Let's create a copy of tblSections named "tblSectionsInstructors".
Step1. To copy tblSections, in the Navigation pane,
press: Control key+c, Control key+v,
type: tblSectionsInstructors, press: Enter
Modifying the New Table
Before we go on, let's define the two fields in tblSectionsInstructors as a primary key and remove unnecessary fields. This will ensure that we only assign an instructor to a particular section once. We also need to set the data type for SectionKey to be number instead of autonumber. This way we can build a relationship with the autonumber key field in tblSectionsMultiInst.
This section has less guidance than the previous sections. If you would like a walk-through, watch this videoabout Modifying the New Table.
Step1. In Design view, set the data type for SectionKey in tblSectionsInstructors to number.
NOTE: We don't want SectionKey to be an AutoNumber field anymore, but in order to match the data type of the AutoNumber SectionKey field in tblSectionsMultiInst, the data type needs to be a Long Integer Number. The number data type defaults to long integer in this case.
Step2. In Design view, delete all fields except SectionKey and InstructorID.
NOTE: When we copied tblSections, we also copied its indexes. Since we're deleting several fields, we will also be deleting those indexes. This is the desired outcome since the fields aren't required in the final table anyway.
In order to create a one-to-many relationship between tblSectionsInstructors and tblSectionsMultiInst, we must first remove the indexes we defined in tblSections. Since we copied the table to create tblSectionsMultiInst and tblSectionsInstructors, we also copied the indexes. That's fine for tblSectionsMultiInst since we want it to be as close to tblSections as possible, but it's not OK for tblSectionsInstructors since we want to be able to assign the same instructor to multiple sections.
The first step in removing indexes is to temporarily unassign the primary key field.
Step1. To remove the primary key from tblSectionsInstructors,
Click the SectionKey field, Click
Step2. To open the Indexes dialog box, on the Ribbon,
Step3. To delete the table's indexes, select every row, press:
Step4. Close the Indexes dialog box.
Step5. Create a composite key of SectionKey and InstructorID.
Step6. Save and close tblSectionsInstructors.
Now that we have created our new table, it's time to change how our database relationships are configured to include the new table. We want to relate tblSections to tblSectionsInstructors via SectionKey and tblFaculty to tblSectionsInstructors via InstructorID/EmployeeID. Lastly, we want to remove the relationship between tblSections and tblFaculty. First, let's build the relationship between tblSections and tblSectionsInstructors and between tblFaculty and tblSectionsInstructors.
This section has less guidance than the previous sections. If you would like a walk-through, watch this videoabout Modifying Relationships.
Step1. Add tblSectionsMultiInst and tblSectionsInstructors to the Database Relationships window.
Step2. Edit the database relationships based on the following table:
NOTE: Notice how tblFaculty and tblCourses are still related to both tblSections and the new tables we added. We will fix that shortly.
Step3. To hide tblSections,
Right-Click the title bar for tblSections,
Click Hide Table
Step4. If desired, Reorder the tables in the Relationships window to make the relationships easier to read.
Step5. Save and close the Relationships window.
Deleting a Field
Before the multiple instructor functionality is complete, we need to remove the InstructorID field from tblSectionsMultiInst as it is no longer needed and causing a normalization problem. Specifically, we are storing the InstructorID for a section in two separate tables: tblSectionsMultiInst and tblSectionsInstructors. Let's delete the InstructorID field from tblSectionsMultiInst now since we will rely on the InstructorID in tblSectionsInstructors from now on.
Step1. Open tblSectionsMultiInst in Design view.
Step2. To delete the InstructorID field,
press: Delete key
Step3. To continue deleting InstructorID,
Step4. To continue deleting InstructorID,
Step5. Save and close tblSectionsMultiInst.