Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Building Relationships Between Tables
The final step in building our database is to create the necessary relationships between our tables. Because each asset must be assigned to a user that exists in the HR table, we will create a relationship between those two tables. In addition, because a username and password can only be assigned to an employee with a record in the HR database, we will create a relationship between those two tables as well.
Exercise 5 Building relationships between tables
-
With the kc_corporation_0493 database open, choose Tools, Relationships from the menu bar.
-
In the Show Table dialog box, shown in Figure 7, click on each of the four tables and click the Add button.
-
Click the Close button on the Show Table dialog box. You should now see the four tables within the Relationships window. Expand each of the boxes so that you can see all the field names. To do this, click on the bottom border of the box and drag the mouse down. The windows should now look like Figure 8.
-
Click the EmployeeID field in the tbIS box and drag it onto the EmployeeID field in the tbHR box.
-
In the Edit Relationships dialog box, shown in Figure 9, check the Enforce Referential Integrity check box and then check the Cascade Update Related Fields and Cascade Delete Related Records. Click the Create button.
-
Click and drag the EmployeeID field in the tbUserID box onto the EmployeeID field in the tbHR box.
-
Select all three check boxes again and click the Create button. As shown in Figure 10, you should now have two relationships between three of your tables.
-
Close the Relationships window and save the layout when prompted.
)
Figure
7 The Show Table dialog box allows you to specify which tables you
would like to create relationships for.
)
Figure
8 The Relationships window before adding the relationships.
)
Figure
9 The Edit Relationships dialog box allows you to enforce referential
integrity.
By creating this relationship, we are creating what is called a one-to-many relationship. This means that the one record in the HR database (the employee) can have many records in the IS database (for example, a phone, a computer, or a pager). In addition, by applying referential integrity to the relationship, we ensure that when an employee leaves the company and is deleted from the HR database, his records in the IS database are deleted as well. This avoids having orphaned records in the IS database.
)
Figure
10 The relationships have been created.
Congratulations! You have just created the back-end database for our upcoming intranet project. Before we break out Dreamweaver and start laying down code, there are a few more issues that we need to cover regarding Microsoft Access.