Create Relationship between Student and StudentCourse Tables – Introduction to Databases
2.9.6.4 Create Relationship between Student and StudentCourse Tables
The relationship between the STUDENT and the STUDENTCOURSE Tables is one-to-many, the STUDENT _ ID in the STUDENT Table is a primary key and the STUDENT _ ID in the STUDENTCOURSE Table is a foreign key.
FIGURE 2.32 The finished foreign key for the COURSE Table (Copyrighted by Oracle and used with permission).
Perform the following operations to set up this relationship between the STUDENT and the
STUDENTCOURSE Tables:
1) Click on the STUDENTCOURSE Table under our customer account, CSE _ DEPT, to open it.
2) In the open Table, select the Constraints tab on the top and click on the Edit icon to open the Edit Table wizard.
3) In the opened Edit Table wizard, click on the Constraints item on the left pane, click on the green + icon and then select the New Foreign Key Constraint item from the popup menu.
4) Change the name of this foreign key, which is located at the second line under the Name column in the Constraints list, to STUDENTCOURSE _ STUDENT _ FK.
5) Select the STUDENT Table from the Table box, STUDENT _ PK from the Constraints box and Cascade from the On Delete box. In this way, these two Tables have a cas-cading deletion relationship for this column when a delete action is performed later. Your completed wizard is shown in Figure 2.33.
6) Click on the OK button to set up this foreign key relationship.
Finally, let’s handle the relationship between the COURSE and STUDENTCOURSE Tables.
2.9.6.5 Create Relationship between Course and StudentCourse Tables
The relationship between the COURSE and the STUDENTCOURSE Tables is one-to-many, the COURSE _ ID in the COURSE Table is a primary key and the COURSE _ ID in the STUDENTCOURSE Table is a foreign key.
FIGURE 2.33 The finished foreign key for STUDENTCOURSE Table (Copyrighted by Oracle and used with permission).
Perform the following operations to set up this relationship between the COURSE and the
STUDENTCOURSE Tables:
- Click on the STUDENTCOURSE Table under our customer database account, CSE _ DEPT, to open it.
- In the open Table, select the Constraints tab on the top and click on the Edit icon to open the Edit Table wizard.
- In the opened Edit Table wizard, click on the Constraints item on the left pane, click on the green + icon and then select the New Foreign Key Constraint item from the popup menu.
- Change the name of this foreign key, which is located at the second line under the Name column in the Constraints list, to STUDENTCOURSE _ COURSE _ FK.
- Select the COURSE Table from the Table box, COURSE _ PK from the Constraints box and Cascade from the On Delete box. In this way, these two Tables have a cas-cading deletion relationship for this column when a delete action is performed later. Your completed wizard is shown in Figure 2.34.
- Click on the OK button to set up this foreign key relationship.
At this point, we complete setting up the relationships among our five data Tables. Go to the File|Save All item to save this foreign key setup. Now, if you open the Model tab from the LOGIN Table, you can see that two foreign keys, LOGIN _ FACULTY _ FK and LOGIN _ STUDENT _ FK, have been set up, as shown in Figure 2.35.
Next let’s discuss how to store an image in the related column in the Oracle database.
FIGURE 2.34 The finished Tables and Columns dialog (Copyrighted by Oracle and used with permission).
FIGURE 2.35 Relationships among LOGIN, FACULTY and STUDENT Tables (Copyrighted by Oracle and used with permission).