Hello again and welcome back to our course on Access 2013.
In the previous section we looked at setting up relationships between tables in an Access
2013 database and in particular we looked at recording the genre for a movie in our
movie database.
In this section we’re going to look at setting up the relationship between a movie and the
actors in it.
Now to some extent this is a similar exercise to the one where we setup the genre but there
are a couple of important differences.
One of them is that when we define the actors that appear in a movie, we define some other
information, notably for example their role in the movie.
This is not the actors name but the name of the character played by the actor, so some
of this is pretty much the same procedure.
We’re going to create a new table.
So we can click on Create.
We’ll go into table design.
We’ll start off with an ID field.
Note that when you go into table design you don’t get one of these by default but we
are going to have one.
So you can define it yourself if it’s not already there.
ID defined as AutoNumber.
The next thing we’re going to want will be the movie.
So we put in here movie.
Now we know that the movie is the foreign key pointing to the primary key in the movie
table so we know that this needs to be a long integer.
So type is number, check we get long integer down there which we do by default, that’s
fine.
The next thing is the actor.
Basically for the actor table the actor ID is also an AutoNumber field.
So again this will need to be a number and a long integer so that’s fine.
But now I’m going to add an additional field for this one and this field for the moment
we’re just going to put in the role, the role of the actor in the movie.
This will be short text, normally the name of the character.
So I’m just about ready to save that.
Note that if I’ve manually added an AutoNumbered ID field to act as the unique key, I need
to mark it as the primary key.
So let me just click there, click on primary key, and then in order to save it with the
name that I want if I right click on the tab, table 1 in this case, and click on Save.
Type in my table name MovieActor, click on OK, and then I’m going to close that.
Let’s now setup the relationship.
So we go to database tools, click on relationships, that’s the diagram that we drew before.
Now we need to show another table so we click on Show table.
We want to show actor and we want to show MovieActor.
Close again.
Now you can arrange these boxes representing these tables in any way you like.
You just drag them round by their headers.
I normally try to arrange them in such a way that they’re easy to read through the relationships.
You could put, for instance, the movie table on here twice if you wanted to.
There’s nothing to stop you having the same table several times.
But while this database is not yet particularly complicated, let’s just stick with one movie
table and what we really need to do now is to do the link from movie to MovieActor which
will be movie ID to movie, referential integrity yes, click on Create.
And then the link from actor, actor ID to actor there, enforce referential integrity,
Create.
So we’ve now got our next relationship defined.
Now we’re going to enter some data now relating to this new relationship, but let me just
point something out to you about this now, one way of reading this diagram that I think
helps.
If you look at the actor table down here and the line to MovieActor, then you see that
for one actor, so for each actor there are many movie actor records.
So an actor can be in many of these MovieActor records.
For each MovieActor record, for that actor it represents one of the movies that they’re
in.
So an actor has well infinity movies.
What it really means is that an actor has many movies.
But this end when you look at it from the movie table says a movie table has many actors.
So one movie many actors, one actor many movies and that’s how it works.
So let’s close the relationships diagram, save the changes, and let’s go to the actor
table.
Let’s just open it up, find an actor, Will Smith is actor number six, and let’s setup
a MovieActor record.
Open it up, movie number we had before was six for Independence Day, actor number by
coincidence is actor number six and the role played was Captain, I’ll just make that
column a little bit wider so you can see it all, and there we have the first actor in
a movie and the role that they played in that movie.
So let me just do one more example of that.
Actor: actor Ingrid Bergman; that’s actor number four.
Let’s go back into movies, Casablanca’s number five, MovieActor, movie number five,
actor number four, and the role is Ilsa Lund.
That’s the role that Ingrid Bergman played in Casablanca.
Now hopefully from that you get the general idea of how these relationships work, but
you’re probably also looking at this and thinking that’s actually quite difficult
to keep track of isn’t it?
Because you’ve got to worry about all those numbers.
Well, in this basic form that’s true but as we will find out in a little while and
particularly when we start to look at forms in detail, there are various ways to make
this whole process much easier.
But the important thing to know is to know what’s going on under the hood or, as you
say in the U.K., under the bonnet.
It is important to understand how this linkage is working but there are ways to make it all
an awful lot easier to use and that’s what we’re going to look at a bit later on in
the course.
So now it’s time for the next piece of work for you to do.
I want you to take this which is currently example-03 in the supplied files and I want
you to make sure that we have data, at least one actor for every movie and at least one
movie for every actor.
So every actor should be in at least one movie and every movie should have at least one actor
in it.
You may have to add an actor.
You may need to add a movie.
I’ll leave that to your discretion.
And the answer to that will be example-04, but in addition I would like you to add genre
values for each of the movies that is in this version of the database.
Now most of them you can get three or four genre values probably by looking at IMDB.
It doesn’t particularly matter that they’re accurate.
It’s more important that you understand how the MovieGenre mechanism works.
So that’s it for this section.
I’ll see you in the next one.