Our next task is to add the ability to assign multiple instructors to a single section. We are going to be making changes not only to the structure of a table, but also to the keys on which tables are related. This is a substantial structural change, so before we modify the existing tables, we will first build the new functionality. This allows us to build the new functionality, then check all of our existing database objects to ensure that they will work with the new structure before completely switching from the old to the new. If we break something, we can simply delete the tables involved with the new functionality and try again. In a sense, the existing tblSections will serve as a backup in case things go wrong.
If you are unsure about the scale of the changes you need to make, it is best to create new functionality and data structures before modifying your existing data structures. This ensures that you will still be able to continue business as usual while the new capabilities are introduced to your database.
Let's create the copy of tblSections that we will use to build the ability to have multiple instructors per section.
Step1. To select tblSections, in the Navigation pane,
Click tblSections
Step 2. To copy tblSections, on the keyboard, press:
Control key + C
Step 3. To paste tblSections and give it a new name, on the keyboard, press:
Control key + V, type: tblSectionsMultiInst Enter key
By giving this copy of tblSections the name "tblSectionsMultiInst", we will be building our new functionality using the newly created table. This essentially makes tblSections the backup of our data. By creating a copy and building new functionality on that copy, if things don't work out, we just delete the copied table and try again.
Since we are fundamentally changing the data structure in our database, we want to make all of our changes first, then test all of the objects in the database to be sure that they still work before making the change permanent. So, with that in mind, let's make the tblSectionsInstructors table while maintaining the data relationships that already exist between tblSections and tblFaculty. This will allow us to go back and check all of the queries that we've written to ensure that they will still work when we are finished.