----------------------------------------------------------------------------------
--| 	drop existing tables with the same name

drop table starred_in;
drop table movie_genres;
drop table genres;
drop table movies;
drop table actors;
drop table directors;
drop table ratings;

----------------------------------------------------------------------------------
--| 	create tables using data and structure from the hollywood schema

create table ratings as select * from hollywood.ratings;
create table directors as select * from hollywood.directors;
create table actors as select * from hollywood.actors;
create table movies as select * from hollywood.movies;
create table genres as select * from hollywood.genres;
create table movie_genres as select * from hollywood.movie_genres;
create table starred_in as select * from hollywood.starred_in;

----------------------------------------------------------------------------------
--| 	Add Primary Key constraints to the new tables

alter table ratings add constraint RATINGS_RATING_PK primary key(rating);
alter table directors add constraint DIRECTORS_NAME_PK primary key(name);
alter table actors add constraint ACTORS_NAME_PK primary key(name);
alter table movies add constraint MOVIES_NAME_PK primary key(title);
alter table genres add constraint GENRES_GENRE_PK primary key(genre);
alter table movie_genres add constraint MovieGenres_PK primary key(movie,genre);
alter table starred_in add constraint StarredIn_PK primary key(actor,movie);

----------------------------------------------------------------------------------
--| 	Add Foreign Key constraints to the new tables

alter table movies add constraint MOVIES_RATING_FK foreign key(rating) references ratings(rating);
alter table movies add constraint MOVIES_DIRECTOR_FK foreign key(director) references directors(name);
alter table movie_genres add constraint MovieGenres_movie_FK foreign key(movie) references movies(title);
alter table movie_genres add constraint MovieGenres_genre_FK foreign key(genre) references genres(genre);
alter table starred_in add constraint StarredIn_movie_FK foreign key(movie) references movies(title);
alter table starred_in add constraint StarredIn_actor_FK foreign key(actor) references actors(name);