WEBVTT Kind: captions Language: en 00:00:07.660 --> 00:00:10.090 This lesson is all about relationships. 00:00:10.090 --> 00:00:13.700 The tables that we created are only good if they are linked. 00:00:13.700 --> 00:00:21.540 We said that a customer comes to our store and finds a book and then makes a purchase. 00:00:21.540 --> 00:00:27.810 We have our tables that show us what information we plan to gather and store in each table. 00:00:27.810 --> 00:00:31.930 Now we just need a way to create a relationship between these tables. 00:00:31.930 --> 00:00:33.830 Here's the database we're currently working on. 00:00:33.830 --> 00:00:37.620 Now, we're going to notice that none of our tables are open. 00:00:37.620 --> 00:00:42.800 That's because all of the tables must be closed in order to create relationships. 00:00:42.800 --> 00:00:48.969 Let's start creating relationships by clicking on the Database Tools tab and Relationships 00:00:48.969 --> 00:00:51.969 in the Show/Hide group. 00:00:51.969 --> 00:00:54.370 The Show Table dialog box appears. 00:00:54.370 --> 00:00:59.219 And, from here you can choose which tables you want to relate. 00:00:59.219 --> 00:01:10.320 Let's go ahead and add our tables by clicking on each one and then the Add button. 00:01:10.320 --> 00:01:12.470 Now click Close. 00:01:12.470 --> 00:01:18.220 This table should be a flashback to our tables and fields diagram. 00:01:18.220 --> 00:01:22.050 I'm going to move the tables around a bit to put them in the same order as the ones 00:01:22.050 --> 00:01:23.610 in our diagram. 00:01:23.610 --> 00:01:34.360 You can do this by simply left clicking on a table and dragging it to a new location. 00:01:34.360 --> 00:01:39.700 You'll notice that the first field under each one of these tables has a little key icon. 00:01:39.700 --> 00:01:41.610 This is the primary key field. 00:01:41.610 --> 00:01:48.950 The primary key field is the first field in each table of our database which we previously 00:01:48.950 --> 00:01:54.590 said was the unique identifier of records in that particular table. 00:01:54.590 --> 00:02:01.709 For example, we could have two customers with the same name, Mary Jones, but because they 00:02:01.709 --> 00:02:07.950 are being identified with their Customer ID number, the database always knows which customer 00:02:07.950 --> 00:02:08.950 is which. 00:02:08.950 --> 00:02:15.050 That is why Customer ID is the primary key -- it ensures us that each Mary Jones is tracked 00:02:15.050 --> 00:02:19.200 separately. 00:02:19.200 --> 00:02:24.780 So in the Customers table the primary key is customer ID, in the Orders table the primary 00:02:24.780 --> 00:02:30.340 key is Order ID, and in the Books table it's Book ID. 00:02:30.340 --> 00:02:36.830 If you look closely at the Orders table you'll notice that customer ID and book ID are listed 00:02:36.830 --> 00:02:38.840 but you don't see a key beside them. 00:02:38.840 --> 00:02:43.700 Now, that's because they are considered Foreign Keys which just means that these fields are 00:02:43.700 --> 00:02:48.090 the Primary keys in other tables. 00:02:48.090 --> 00:02:49.410 That's enough to make your head spin! 00:02:49.410 --> 00:02:53.040 So, stay with me. 00:02:53.040 --> 00:02:57.700 So we've got our tables in the order we want them in and now it's time to create the relationships 00:02:57.700 --> 00:02:59.510 we've been talking about. 00:02:59.510 --> 00:03:04.850 We could use edit relationships from the Ribbon but it's much, much easier to just drag and 00:03:04.850 --> 00:03:07.010 drop from one table to the other. 00:03:07.010 --> 00:03:12.730 It's a good thing we called our ID's Customer ID, Book ID, and Order ID earlier. 00:03:12.730 --> 00:03:16.150 This just makes it easier to see the fields we want to join. 00:03:16.150 --> 00:03:20.800 If we didn't name them this way we would just have several fields named ID. 00:03:20.800 --> 00:03:24.980 Now, I'll start by joining my Customer IDs. 00:03:24.980 --> 00:03:31.630 So, I'll drag the primary key Customer ID from the Customers table to the foreign key 00:03:31.630 --> 00:03:37.140 Customer ID in the Orders table. 00:03:37.140 --> 00:03:40.480 The Edit Relationships dialog box appears. 00:03:40.480 --> 00:03:45.820 On the left you see a header that says Table and on the Right a header that says Related 00:03:45.820 --> 00:03:46.970 Table. 00:03:46.970 --> 00:03:53.650 In this case the Table is Customers and the Related Table is Orders. 00:03:53.650 --> 00:03:58.310 In this area is where we can edit our relationships if needed. 00:03:58.310 --> 00:04:01.069 Below we have other options. 00:04:01.069 --> 00:04:05.680 The only one that is important is Enforce Referential Integrity. 00:04:05.680 --> 00:04:11.200 By checking off Referential Integrity we assure that there is never an order in our Orders 00:04:11.200 --> 00:04:15.660 table for a customer that doesn't exist in our Customer's table. 00:04:15.660 --> 00:04:20.880 And, likewise we would never want to have an order for a book that doesn't appear in 00:04:20.880 --> 00:04:22.120 our Book's table. 00:04:22.120 --> 00:04:26.940 So, clicking Enforce Referential Integrity forces Access to check for these types of 00:04:26.940 --> 00:04:28.680 things. 00:04:28.680 --> 00:04:34.180 Click Create to establish the relationship. 00:04:34.180 --> 00:04:40.810 By Enforcing Referential Integrity you establish a one-to-many relationship between the tables. 00:04:40.810 --> 00:04:44.090 This means that one customer may place many orders. 00:04:44.090 --> 00:04:52.240 So, in the customer table the ID shows up one time but in the Orders table, we expect 00:04:52.240 --> 00:04:57.190 the customer may show up many times because they may order from us again and again and 00:04:57.190 --> 00:05:00.160 again. 00:05:00.160 --> 00:05:03.560 I'm going to set up my other relationship. 00:05:03.560 --> 00:05:06.630 This one is from Book ID to Book ID. 00:05:06.630 --> 00:05:14.660 Again, I'll check Enforce Referential Integrity to so I can establish the one to many relationship. 00:05:14.660 --> 00:05:21.540 So now we can see that we have one book ID for each book but there can be many Book IDs 00:05:21.540 --> 00:05:22.540 in an order. 00:05:22.540 --> 00:05:24.300 I hope you got that. 00:05:24.300 --> 00:05:28.889 Everything looks good but if I needed to go back and edit my relationships I could go 00:05:28.889 --> 00:05:34.140 to Edit relationships or double-click on the links between the tables. 00:05:34.140 --> 00:05:37.340 I want to show you one more thing before we wrap up. 00:05:37.340 --> 00:05:44.370 If I move my tables around in the Relationship Map, the relationship remains intact no matter 00:05:44.370 --> 00:05:46.540 where I move the tables. 00:05:46.540 --> 00:05:51.900 I'm going to move them back to their original spots since this is the easiest way to see 00:05:51.900 --> 00:05:54.740 the relationship. 00:05:54.740 --> 00:06:00.430 My relationships are set so now I can close the Relationships tab. 00:06:00.430 --> 00:06:03.050 You'll be prompted to save your Relationships. 00:06:03.050 --> 00:06:07.020 Click Yes to save. 00:06:07.020 --> 00:06:12.510 Our tables are related, next we'll start adding records also known as populating the database.