More Questions....Relationships

Ok heres the scenario:

3 tables

Movie - MovieID, MovieName, Starring1, Starring 2, Director

Director - MovieID, DirectorID, DirectorName

Actors - MovieID, ActorName

In bold are the names of the tables --

in bold+italic are the PK - Primary Keys.

Ive tried to create a relationship between:

1. Movie - MovieID to Director -MovieID - Movie is related to the director

2. Actors - MovieID to Movie-MovieID - Actor is related to Movie

Second relationship failed for some reason.

Im trying to achieve this result:

Movie = Pulp Fiction

Director = Quentin Tan

Actor = Sam L Jackson

Once i do reports etc.....What am i doing wrong here as i think its my relationships im having difficulty with?

Thank you in advance

try:

Select movieName,

DirectorName,

actorName

from movie m

join director d

on m.movieId = d.movieId

and m.movieName = 'Pulp Fiction'

and d.directorName = 'Quentin Tan'

join actor a

on m.movieId = a.movieId

and a.actorName = 'Sam L Jackson'

an alternative is:

Select movieName,

DirectorName,

actorName

from movie m

join director d

on m.movieId = d.movieId

join actor a

on m.movieId = a.movieId

where m.movieName = 'Pulp Fiction'

and d.directorName = 'Quentin Tan'

and a.actorName = 'Sam L Jackson'

|||Thanks but am i on the right lines about creating these relationships in the way i described or would you do anything differently?|||

Assuming that a movie has only one director, something like this:

Director -- DirectorID (PK), DirecorName

Actor -- ActorID (PK), ActorName

Movie -- MovieID (PK), MovieName, DirectorID

Cast -- MovieID, Order, Part, ActorID, (PK: MovieID, ActorID, Part)

The "Cast" table is necessary so that you can list all of the actors of the movie. I added in "Order" so that you can control "Promotion Order" or "Billing Order" or whatever you want to call it -- it is a control so that you can control the order in which the actors might be listed in credits.

I added in "Part" for movies in which somebody like Eddie Murphy might play multiple parts.

From my perspective the biggest problem here is the "Cast" table as I have made it. I still don't feel that it is general enough. For instance, say that you want to use this as the basis for listing movie credits. You want to include Producers, stunt coordinators, etc. It would be best to generalize the "Cast" table into a "Role" table and along the way establish "Subcategories" of the "Role" table. But now we are getting ahead of ourselves. This should get you started.