In many cases, a many-to-many relationship can be condensed into a more efficient structure to improve the speed of data retrieval. After all, less tables will need to be joined to get the desired information. Using the relationship between a course and a student as an example, Figure 4.7 shows how a many-to-many relationship can be collapsed into a more compact structure.

Figure 4.7  Collapsing a many-to-many relationship into a more compact structure.

A student takes many courses, and each course has many students. This is a classical many-to-many relationship and requires us to define a junction table between the base entities to establish the foreign keys necessary to join them together. Note that the GRADE  is the junction table, and it contains the following contents: course_nbr, the primary key for the COURSE table; student_nbr, the primary key for the STUDENT table; and grade, which is a no-key attribute for both foreign keys. Next, consider the following: In what context does a grade have meaning? Simply stating that The grade is A in CS-101 is insufficient, and stating Joe earned an A makes no sense, either. Only when both the student number and the course number are associated does the grade column have meaning. Stating Joe earned an A in CS-101 makes sense.