This lesson requires Students.fp7.

When you related the "Schools" and "Students" tables in exercise #1, you established a one-to-many relationship between those two tables. In more complex databases, a many-to-many relationship may exist. For example, each student can potentially enroll in many seminars and each seminar can potentially have many students. Whenever you have a many-to-many relationship between two tables, you cannot link those tables together directly. You will need to create an intermediate table between the two to act as a "bridge". This "bridge" table only needs to include only the information necessary to record a single event. For example, if a student registers for a seminar, we only need to record the ID of the student and the ID of the seminar that the student has requested.

Creating an Intermediate Table:

1. Choose File -> New-> Database and click on the Tables tab.

2. Type "Registrations" in the "Table Name" field and click "Create".

3. Now select the Fields tab and enter Student Number in the Field Name box, select Number for the field type and click Create. Repeat this process and create another number field named Seminar Number.

4. We will now need to define the relationships between the Student and Registrations tables and the Seminars and Registrations tables.

5. Choose the Relationships tab.

6. Click on the "Student Number" field in the Registrations table and drag the field over on top of the Student Number field in the Students table. Now double click on the relationship line between the tables and check "Allow creation of records in this table wia this relationship" and "Delete related records in this table via this relationship" in the lower left-hand corner of the dialog box and click OK.

7. Now select the field "Seminar Number" in the Registrations table and drag it over on top of Seminar Number in the Seminars table.

 

COE Techweb

COE Home

UO Home