-- 
-- movie.sql
--
-- [movie] --- <actsin> --- [person] 
--
-- Actsin is a simple many-to-many relationship 
--

drop table ActsIn if exists;
drop table Movie if exists;
drop table Person if exists;

create table Movie (
   MovieID int not NULL, 
   title varchar(100), 
   primary key (MovieID)
);

create table Person (
   PersonID int not NULL, 
   first_name varchar(100), 
   last_name varchar(100), 
   primary key (PersonID)
);

create table ActsIn (
   PersonID int not NULL, 
   MovieID int not NULL, 
   primary key (PersonID, MovieID), 
   constraint actsin_movie foreign key (MovieID) references Movie(MovieID), 
   constraint actsin_person foreign key (PersonID) references Person(PersonID)
);  

insert into Movie values (1, 'The Bridge');
insert into Movie values (2, 'The Car');
insert into Movie values (3, 'The Bus');
insert into Movie values (4, 'The Train');
insert into Person values (1, 'John', 'Doe');
insert into Person values (2, 'Sam', 'Smith');
insert into Person values (3, 'Kate', 'Winslet');
insert into actsin values (1, 4), (2, 4), (3, 4);