We've already worked with one junction table today, tblSections. We will be building another junction table to solve another many-to-many problem. Specifically, we want to be able to assign multiple instructors to a section of a course. Each assigned instructor will have a specific role within the course. For example, we might have a lead instructor, lab instructor, student coordinator, lecturer, etc. This would mean that one faculty member could teach many sections, but one section could have many faculty teaching it. In other words, it would be a many-to-many relationship, so we will need to create a junction table to make it work.
Creating the Sections & Instructors Table
Before we start any work in Access, let's plan out how this table will work. We need to be able to draw a bridge between tblFaculty and tblSections to allow multiple instructors, so we need to consider how to represent this new data within our database.
Currently, we have this relationship:
In order to bridge the two tables and allow multiple records, there are several things we need to consider. First, how will our table keys work? Right now, we have a composite key in tblSections. Can we trust our data entry specialists to be able to correctly carry over the appropriate fields? Can we simplify the relationship?
The table keys should be reworked in these tables. Specifically, we shouldn't trust data entry personnel to be able to reliably enter two key values when we can use an autonumber to make this process easier and less error-prone. This will also reduce redundancy in our database.
Second, if we need to change the key to make this work, can we maintain the integrity of the data in tblSections after changing it? If so, what sort of constraints need to be made on the data to ensure that a section number is only used once per semester?
By creating an auto number primary key, we will lose the built-in guarantee that a section number can only be used once a semester. We will need to change some table properties to retain this restriction. We will explore those properties as we create our junction table.
The third thing to consider is what data needs to remain in tblSections and which pieces can be removed. For example, do we need to maintain InstructorID in tblSections, or can we leave it in our junction table?
Since the relationship will be made between tblSections and the junction table, tblSectionsInstructors, on an autonumber field, we can store the InstructorID information in just tblSectionsInstructors and remove it from tblSections. This helps us keep our data normalized by eliminating the redundant data.
All those things considered and solutions implemented, we would like to end up with a structure similar to this:
Let's start by tackling our first consideration, the key fields in tblSections.
Understanding Composite Keys
Composite keys can sometimes cause problems when expanding a database's functionality. Specifically, in our case, we would have to carry the whole key from tblSections (SectionNumber and SemesterYear) into tblSectionsInstructors. Having to maintain two fields for a relationship can introduce an opportunity for error. Simplifying this into an autonumber field (SectionKey in the previous screen shot) will allow us to reduce the chance for error and redundancy in our data, making it easier to normalize our data.
Converting the Composite Key to an AutoNumber
There are a couple of things we need to do to convert to a different key. The first is to create the new key field. Let's do that now.
Step1. Open tblSections in Design view.
Step2. To add a row above SectionNumber, on the Design tab of the Ribbon,
Step3. To add the SectionKey field, in the row that was just added, type:
SectionKey Tab key
Step4. To change the field to autonumber, type:
a Tab key
Step5. To make SectionKey the primary key for tblSections, on the Ribbon,
Step6. To save the table, press:
Control key + S
Creating Unique Constraints
The second thing we need to do is maintain the primary key-like functionality of the SemesterYear and SectionNumber combination. This is accomplished by establishing a unique constraint. A unique constraint is a way for us to mark a field or a combination of fields to be unique throughout the table. Unique constraints are a type of index. Indexes are a table property that allow us to require the data to conform to a particular condition. We've already been working with one other type of index, the primary key, but you can create indexes for non-key fields. There are also unique and required constraints.
Unique constraints are usually applied to candidate keys. A candidate key is a field or combination of fields that could serve as the primary key in a table. Identifying candidate keys is an important step in database design because candidate keys will likely need to have unique constraints applied to them so they function like primary keys. In our example, the combination of SectionNumber and SemesterYear serves as a candidate key. We will apply a unique constraint to make sure they maintain that candidate status.
Step1. To open the Indexes for tblSections, on the Ribbon,
NOTE: CourseCode and InstructorID already have associated Indexes. Access created these indexes when those fields were set to be the foreign key of a relationship.
Step2. To name the index, in the empty row below PrimaryKey, type:
SectNumSemYr Tab key
Step3. To add SectionNumber as a field, type:
SectionN Tab key Tab key Tab key
Step4. To add SemesterYear to the index, type:
Seme Tab key Tab key
NOTE: You can also make these selections using the drop down list for field name.
Step5. To select the index, in the Index Name field,
Step6. To change the Unique property to Yes, at the bottom, in the Index Properties section,
7. Close the Indexes: tblSections dialog box.
Step8. Save and close tblSections.