Welcome back to our course on Access 2013.
In this section we’re going to look at relationships between tables in an Access 2013 database
and we’re going to begin by looking at genre for a movie.
Now first of all I want to go to the movie table and I want to open it.
So we’re going to look at the data.
So double click to open.
And you may remember that for one movie, Identity Thief, we earlier entered the genre as comedy,
comma, thriller.
And you may also recall that we can have a number of different genre defined here separated
by commas.
But as I pointed out that’s not really good design and it carries with it a number of
problems that will become apparent a bit later on.
So what we’re going to do is to setup a proper relationship between the movie table
and the genre table.
But for the moment let’s now go into the table design for movies.
So I go into Design View.
We had a field there, Genre, which is the one that holds currently the genre values
separated by commas.
I want to delete that row.
It’s straightforward enough.
When I’m in Design View, the contextual tab Design in table tools is available and
one of the options there is delete rows.
So I can just literally delete a field.
Now note the warning you get if you try to delete a field in a table.
Do you want to permanently delete the selected fields and all the data in the fields?
When I’m deleting this I’m deleting any data for any record; any movie that’s got
genre information.
Now at the moment, I’m happy with that because I only put that genre info in as a demonstration
so I’m going to say yes and the genre information is gone.
I’m going to close the movie table again and say yes to saving changes.
Now what I’m going to do is to go to the Database Tools Tab and on there I’m going
to click on Relationships and relationships gives me a diagrammatic representation of
the relationships between the tables in my database.
Now at the moment the diagram there is completely blank.
It’s behind this little dialog but it’s completely blank and I want to add two tables
to it.
I want to add the movie table.
So select it, click on Add, and I want to add the genre table.
Select it, click on Add.
Now what I’m going to be doing is to create another table that links movies with their
genre values.
So for one movie there will be multiple genre values.
Now the way I do this is first of all I will do Create table.
So let me go to Create table and I’m going to go into Design View.
I’m going to call the table tblMovieGenre just to emphasize the fact that it basically
links a movie to a genre or many genre.
By default an ID field is created.
But I also want two more fields in it.
One field I want is a field that will point to the movie that I am referring to.
So I’m going to put in here movie.
The next field will point to the genre and basically what will happen is that in this
table I will have many entries that link the movies to their various genre.
So first of all, let me save this table, the newly defined table.
Let me go back to relationships by clicking on its tab here.
When relationships is selected I can go back to relationship tools, the Design Tab, and
one of the options there is Show table because I now want to show an additional table, and
the additional table is the new one, MovieGenre.
I’m going to add that, close.
I’m just going to pull it down between these two.
The way that this works is this acts as a sort of link between movies and genre and
the way the link works is actually fairly straightforward but it takes a little bit
of getting used to.
The way it works is that in the linking record, the record in tblMovieGenre we have a link
to the primary key in the movie table which is the ID, and the key here, the movie value,
is what’s called a foreign key into the movie table.
Similarly the genre, the value here, is a foreign key into the genre table primary key.
Now if you’ve been paying really close attention, you may see a slight problem here but it’s
a very regular kind of problem and we’re going to have to face it sometime so let’s
face it now.
If you look at the genre table, let me just open the genre table up again in Design View,
its primary key, genre, is a short text field.
If we go into the Datasheet View, we’ll see that the sort of thing we’ve got there,
they’re words like Adult, Adventure, Animation, that’s fine.
Let’s close that.
This field, Genre, in the table MovieGenre, the equivalent, the one that’s going to
be the foreign key is also short text.
So that’s absolutely fine.
If I look at the movie field in the MovieGenre table, the linking table, that’s short text.
But if I go into the movie table, so let me open that in Design View, the ID there is
not short text.
It’s something called AutoNumber and these AutoNumber values, the ones where it automatically
gives it a number when you add a record are actually what are called long integers.
So they’re a special kind of number called a long integer.
In fact in my linking table, my MovieGenre table, the data types must match.
So the movie here in order to link up must not be a data type of short text.
It must be a data type of long integer.
Now the way we do a data type of long integer is to select number and then just make sure
that it says long integer down there.
Now that may seem a little bit strange at this stage if you’ve not seen this before,
but most of the time that you’re setting up relationships you’ll be setting up relationships
to AutoNumbered fields and therefore making the data type of a foreign key into a long
integer will be something that you do a lot.
So once you’ve got your head around this, the first time it actually almost becomes
routine to do it from that point onwards.
So having changed the data type of movie to number and specifically long integer, I’m
going to close the MovieGenre table and save its changes.
I’m also going to close the movie table.
No changes there anyway.
Let’s now look at relationships again.
Now what I can do in order to establish these relationships using keyboard and mouse is
to literally drag fields on to fields.
Now I know that the movie table ID, this field here, let me just click it to select it, corresponds
to movie in the MovieGenre table.
So all I do is grab the ID, pull it over here and drop it on to movie.
What happens is that Access 2013 creates a relationship and what I do in this dialog
is to define some of the properties of that relationship.
Now perhaps the most important of those properties at this stage is the one that is set using
this checkbox, Enforce referential integrity.
Now you may or may not have heard of referential integrity before but what it basically means
is that if you have something like a movie that has various properties such as the genre
it belongs to or the actors who acted in it or the crew that were involved in making the
movie and you’re setting up all these kind of relationships that we’re looking at here,
it means that you cannot delete, for example, that movie without clearing up all of these
relationships as well.
So you can’t leave odd bits of data lying around that don’t have a movie that they
belong to anymore.
Now you will almost always enforce referential integrity and when you do there are another
couple of options here.
I’m not going to look at those at the moment.
We’ll come back to those later.
But basically once you’ve said you want to enforce referential integrity, you can
create the link between those tables.
Now there are a couple of other options.
There’s a join type that again we’ll come back to later on, but for now let’s just
say Create.
What we finish up with is a one, note the one there, to many, note the infinity symbol;
a one to many link which means for each one movie there can be many movie genre records,
which means one movie could have three or four or five or six movie genre records.
It might have none of course.
Each of those will specify one genre that applies to this movie.
So that’s half of the job done.
Now let’s do the other half.
Let us say we click on the genre in the genre table and we drag on to the genre in the MovieGenre
table.
Again we’re going to enforce referential integrity, click on Create, and we have the
other half of the link.
What this says is that for each genre there can be many movie genre records because of
course if the genre is comedy, there are many comedy movies.
In that way we have created all of the linkage we need to create the relationship between
movies and genre.
Now what you have there is an example of quite a tricky concept in database design but it’s
an absolutely fundamental and essential concept to understand because it is at the basis of
the design of all relational databases nowadays.
Now I want to just help a little bit on this by going back and putting in some data to
explain a little bit more about how this works.
So let’s close the relationships diagram.
We’re going to say yes to saving the changes to the layout because we’ll be coming back
to the diagram again later.
Let’s go back into the table MovieGenre, let’s open that up, and what we would normally
do now is to type in a movie number and a genre.
So let’s go for one of the movies.
Let’s open the movie table.
What about Independence Day?
That’s by the way that columns not quite wide enough so let’s make that wide enough.
Independence Day is movie number six.
So let’s go into MovieGenre.
Let’s put in movie number six and one of the genre is Action.
Let’s go to the next one and let’s put in Adventure but let’s spell it wrongly.
There we are.
It’s spelled wrongly.
You cannot add or change a record because a related record is required in the table
genre.
It will only let me add legitimate values for genre here and that’s one of the very
important aspects of applying these relationships.
You make sure that your database is consistent with itself.
So if I now spell that correctly, Adventure, that’ll be much better.
So let me just go through and quickly put in the rest of these for that movie.
So Action, Adventure, Sci-Fi, and the final official one for Independence Day is Thriller.
I could go through the other movies that I’ve currently got in this database and put in
the same sort of information.
But typing it in this way is a little bit laborious and there is a better way of doing
it and we’re going to come back to that later when we look at forms.
But for now let’s turn our attention in the next section to a different sort of relationship
and this time we’re going to look at adding actor information to our movies.
So please join me for that.