Movie Star database - help_tbl

[lostlad]

Hello everyone.

Firstly thank you for taking the time to read this. I am looking for some guidance with a database design which I am sure is a simple answer for most of this forum.

I have basic database skills however they are normally limited to simple lookup/combo boxes and queries.

I have however just found a simple database book with a load of scenarios that I am working through and I am stuck at this one:

"You are to create an Information Storage database for a film review website. They would like to offer the following queries to their staff:

- Find all movies starring a particular actor

- List all films that an actor has starred together with a particular actor and the number of times that they have starred together.

- Find all films released between two dates and the box office grossings.

- Find all films released on a particular year featuring an actor with a particular nationality.


I know that i need to start with an ERD. This will include tables such as

Movies_tbl
release_year
movie_title
movie_description
BoxOfficeTotal

Actors_tbl
actor_first_name
actor_last_name


actor_nationality (lookup box to a different table)

Does anyone have any words of wisdom about where to start from here? Believe it or not i am ok with working out SQL statements etc to find number of films featuring stars etc it is just the ERD part I struggle with.

The way I see it MANY stars are in a movie and a star can be in MANY movies therefore we have the MANY MANY issue. I would therefore propose that a junction box such as:

MOVIE_CAST
linking the Movie ID to an Actor ID be used to turn these into two 1-many relationships.

Can anyone offer any advice as to where to start this "simple" task.

Any feedback would be appreciated

Thank you

Steve

[June7]

You have already started. Yes, need junction table. Options for data entry:

1. single form with comboboxes to select movie and actor

2. main form bound to Movies and subform bound to junction table with combobox to select actor

3. main form bound to Actors and subform bound to junction table with combobox to select movie

[lostlad]

Thank you very much June7



I am sorry to be a pain. I have created the junction box. The actor_tbl works complete with the lookup for the nationality. I have populated some information in both the actor and the movie table however how would you suggest I link the association between the actor and the movie? I am sorry if it is simple. I have created the relationship, but i can't yet say "Will Smith" was in "MIB" etc

[June7]

Enter records into the junction table. Options for form arrangements in prior post.