The situation here is slightly different than in the previous two examples.
To be able to show the names of movies, you obviously need to consult
the table FILM. Here is a short fragment from that table:
The titles of the movies can clearly be extracted from this table. The
directors are mentioned in the column DIRECTOR. In this exercise, you
are asked to show the names of all the directors. This is were we run
into difficulties. The full names of the directors are not in this table.
The column DIRECOR only contains numbers. These numbers, as can be seen
from the database definition, refer to the primary keys in the table PERSON.
Here is a fragment from the table PERSON:
Each record in the table PERSON has been given a unique code in P_ID.
This code can be used to uniquely address this record. The numbers which
are given in the column DIRECTOR in the table FILM do indeed uniquely
address a single row in the table PERSON. The column DIRECTOR should thus
be interpreted as follows: Movie 1 (One Wonderful Night, 1914) is directed
by person whose primary key P_ID is 7 (Elisha Calvert). Movie 2 (One Wonderful
Night, 1944) is directed by the person whose primary key is 9 (Stuart
Paton), and so on.
To be able to complete the assignment, you should be able to combine
the information from these two tables. The full details of the person
that is referred to by the number in the column DIRECTOR should be available
in the table FILM. This can be achieved in SQL by using a so-called INNER
JOIN. Creating such a join involves two steps :
- After FROM, you mention the two tables that need to be combined
- After WHERE, you explain how these two tables are linked.
In the vast majority of the cases, the relationship will be based on
the fact that theprimary key in one table functions as a foreign key in
the second table. In our current exercise, the primary key P_ID functions
as a foreign key in the column DIRECTOR. Thus, to link FILM and PERSON
we need to state: FROM PERSON, FILM WHERE P_ID = DIRECTOR.
The result of this JOIN is that, if a match is found between the value
in the column DIRECTOR in FILM and the value in P_ID in PERSON, the record
from person simply be "glued" to the record in the table FILM.
This will produce a rather long result set whic looks as follows:
Note that, in this join, the value in DIRECTOR (originally from FILM)
is exactly the same as the value in P_ID (originally from PERSON). If
there is no match between FK and PK, the row will not be shown. Not all
records will be shown in an INNER JOIN. This is also why the exercise
has included the phrase "for all the movies whose directors are known".
Here, you can clearly see the efficiency of relational databases at work.
The full details for each person will need to be recorded only once, and
it is possible to refer to these details by making use of a simple and
short code, the primary key. An SQL query can then combine information
from different tables. If one person has directed many movies, the full
details of this person can still be displayed next to each movie. Whereas
the details are maintained centrally in the table PERSON, these details
can be copied and displayed as many times as is necessary in the result
of a query.