Building a Junction Table

Now that we've reviewed the three types of relationships, let's discuss how the courses information is related to the rest of our data. As we noted earlier, when designing a database, you need to take into account the business rules of the organization you are designing for. Let's take a closer look at what business rules are.

Understanding Business Rules

Business rules are rules of operation specific to a particular business or organizational environment. Understanding business rules will often have an effect on how tables are designed and how relationships between tables are created. Because business rules are often taken for granted, it is important to make sure that as many business rules as possible are discovered and represented in the database design.

For example, one business rule used by many schools deals with calculating student grade-point averages, or GPA. By this rule, an A is worth 4 points, a B worth 3, and so on. If we were also tracking grade information in our database, we would need to represent that rule within our data in some way.

For the database we're working on, we will assume these business rules for courses, sections, faculty, and departments:

• Faculty ID numbers are 10 digits long
• Course codes are unique across the university
• Department IDs are four letters long
• A course belongs to one department
• A course must have a title and description when created
• Large courses are offered in many sections
• A faculty member can teach many sections
• Section numbers are recycled from semester to semester
• A section can be taught by multiple instructors
• Each section of a course is taught in its own classroom

We will need to keep these rules (as well as some others described later in the workshop) in mind as we work with our data today.

Creating Many-to-Many Relationships

Many-to-many relationships are the most common place to find non-normalized database tables. Keeping the principles of normalization in mind, let's synthesize a solution that will allow us to represent the following business rules:

• Large courses are offered in many sections
• A section can be taught by multiple instructors
• Section numbers are recycled from semester to semester
• Each section of a course is taught in its own classroom

Let's take a detailed look at a couple of solutions.

Brainstorming Possible Solutions

The process for creating a new table for an existing database is similar to the process for creating a database from scratch. The only difference is the scope. When designing a database, you need to determine what entities need to be present to provide the required information. Similarly, when designing a table, you need to determine what fields need to be present to adequately describe the entity you are trying to represent.

Here are some things to keep in mind when creating a table:

• Determine all fields necessary to track the required information
• Be sure the fields that have been proposed keep the database normalized
• Determine the table's primary key and/or foreign keys that need to be present to form relationships with the existing tables

Since we are representing the information related to a section of a course, we need to track at least the course code, section number, and, since section numbers can be recycled each semester, semester/year. We need to know what course the section belongs to and in which semester it is being taught, since section numbers are re-used each semester. These three fields are the only ones that we absolutely need in order to make this junction table.

Usually, when we can conceptualize a many-to-many relationship in our data, there is other information that needs to be present as well. This is why we create junction tables rather than trying to create only a many-to-many relationship. In our case, we need to be able to keep track of who is teaching the section and where and when it's being taught. Here are the fields that we need in order to meet our needs:

NOTE: If you would like to include other fields, do so after we import data into this table.

Creating tblSections

Now that we have it all planned, let's build a table to hold section data.

This section has less guidance than the previous sections. If you would like a walk-through, watch the video Creating tblSections. 

Step1. Create a table called "tblSections" with the following fields and properties:

NOTE: This might be easier to accomplish in Design view.

Step2. Set the primary key to be the SectionNumber and SemesterYear fields.

NOTE: To set two primary keys, select both fields first.

Step3. Remove the default key field (ID).

Step4. Save the table as "tblSections" and close.

Adding Relationships for tblSections

Now that tblSections has been created, let's build its relationships with the rest of our data.

This section has less guidance than the previous sections. If you would like a walk-through, watch the video Adding Relationships for tblSections.

Step1. Create the following relationships:

Your relationships should look like this when finished:

NOTE: You may have to move your tables around in the Relationships window to have them mirror the screen shot. The important piece is the relationship lines. As long as yours go to and from the same fields as in the image, your relationships will be correct.

Step2. Save and close the Relationships window.

Importing Section Data Into tblSections

Before we explore some more complex aspects of relationships, let's import some data into tblSections from an Excel spreadsheet already created for us. This will allow us to explore some relationship properties that become available to us when writing queries.

Step1. To begin importing data to tblSections, on the Ribbon,

Click the External Data tab, Click 

You see the Get External Data dialog box:

Let's select our spreadsheet.

Step2. To browse to the file to import,

Click

You see the Open dialog box.

We need to specify the name and location of the file to open.

Setting the Location for Opening Your File

When the dialog box opens, it lists a default location from where the file will be opened. All of our exercise files are contained in the epclass folder, located on the desktop. We'll want to change our location to this folder.

We will start at the desktop, since our exercise file folder, epclass, is located there.

Step1. To move to the desktop,

Click

The current location is now set to the desktop. All of our exercise files are contained in the epclass folder, located on the desktop.

Step2. To open the epclass folder,

Double-Click

The epclass folder contents are now visible.

We will need to open the Access_Structure subfolder of the epclass folder that contains all of the necessary documents.

Step3. To open the correct folder,

Double-Click the Access_Structure folder

Let's select the spreadsheet.

Step4. To select the spreadsheet,

Double-Click SectionsData.xslx

We want to append this data to the end of tblSections.

Step5. To append the data,

Click the "Append a Copy of the Records to the table:" radio button

Now we choose the table from the drop down list.

Step6. To select tblSections, in the drop down list,

Click, Click tblSections

We can now finish the import.

Step7. To complete the import,

Click, Click, Click

Each record in the imported data has a course code and instructor ID that exist in their respective primary tables. We can draw this conclusion because the data import did not cause any errors and the relationships between tblSections and tblCourses and tblFaculty has referential integrity turned on.

We now have a table full of section information. Let's view and verify the data.

Step8. To view the data in tblSections, in the Navigation pane,

Double-Click tblSections

You see that we have a table full of information about various sections.

Step9. Close tblSections.

Before we go on, let's look at the data in the rest of the tables in our database.

Step10. Review the contents of tblFaculty & tblCourses.

You can view the related data in each record's subdatasheet.

Step11. To view a record's subdatasheet, next to the record,

Click [+]

The related records appear in a subdatasheet. Let's close the subdatasheet and the tables before we continue.

Step12. To close the subdatasheet, to the left of the record,

Click [-]

The subdatasheet is closed.

Step13. Close tblFaculty & tblCourses.