drop database if exists imdb;

create database imdb;

use imdb;

drop table if exists keyword;

create table keyword (
	word varchar(200) primary key
);

drop table if exists key2movie;

create table key2movie (
	movie int(8),
	keyword varchar(200),
	primary key (movie,keyword)
	);

drop table if exists plot;

create table plot (
	id int(8) primary key,
	plot longtext,
	author varchar(200)
);

drop table if exists plot2movie;

create table plot2movie ( 
	plot int(8) primary key,
	movie int(8)
);


drop table if exists goof;

create table goof (
	id int(8) primary key,
	goof longtext
);

drop table if exists goof2movie;

create table goof2movie (
	goof int(8) primary key,
	movie int(8)
);


drop table if exists altversion;

create table altversion (
	id int(8) primary key,
	version longtext
);

drop table if exists altversion2movie;

create table altversion2movie (
	altversion int(8) primary key,
	movie int(8)
);

drop table if exists trivia;

create table trivia (
	id int(8) primary key,
	trivia longtext
);

drop table if exists trivia2movies;

create table trivia2movies (
	trivia int(8) primary key,
	movie int(8)
);

drop table if exists tag;

create table tag (
	id int(8) primary key,
	tag longtext
);

drop table if exists tag2movie;

create table tag2movie (
	tag int(8) primary key,
	movie int(8)
	
);



drop table if exists crazycredit;

create table crazycredit (
	id int(8) primary key,
	credit longtext
);

drop table if exists crazycredit2movie;

create table crazycredit2movie (
	credit int(8) primary key,
	movie int(8)
);


drop table if exists language;

create table language (
	name varchar(200) primary key,
	check (name in (select language from lang2movie))
);

drop table if exists lang2movie;

create table lang2movie (
	movie int(8),
	language varchar(200),
	primary key(movie,language)
);


drop table if exists composer;

create table composer (
	name varchar(200),
	surname varchar(200),
	primary key (name,surname),
	check (name,surname in (select composerN,composerS from musicby))
);

drop table if exists musicby;

create table musicby (
	composerN varchar(200),
	composerS varchar(200),
	movie int(8),
	primary key(composerN,composerS,movie)

);


drop table if exists editby;

create table editby (
	movie int(8) primary key,
	editorN varchar(200),
	editorS varchar(200)
);


drop table if exists editor;

create table editor (
	name varchar(200),
	surname varchar(200),
	primary key (name,surname),
	check (name,surname in (select editorN,editorS from editby))
);


drop table if exists writer;

create table writer (
	name varchar(200),
	surname varchar(200),
	primary key (name,surname),
	check (name,surname in (select writerN,writerS from writerby))
);

drop table if exists writerby;

create table writerby (
	writerN varchar(200),
	writerS varchar(200),
	movie int(8),
	primary key (writerN,writerS,movie)
);

drop table if exists soundtrack;

create table soundtrack (
	name varchar(200) primary key,
	writer varchar(200),
	composer varchar(200),
	musician varchar(200)
);

drop table if exists tracklist;

create table tracklist (
	soundtrack varchar(200),
	movie int(8),
	primary key (soundtrack,movie)
);

drop table if exists prodcompany;

create table prodcompany (
	name varchar(200) primary key,
	check (name in (select prodcompany from prodcompany2movie))
);

drop table if exists prodcompany2movie;

create table prodcompany2movie (
	prodcompany varchar(200),
	movie int(8),
	primary key (prodcompany,movie)
);

drop table if exists actor;

create table actor (
	id int(8) primary key,
	name varchar(200),
	surname varchar(200),
	sex enum('M','F'),
	unique(name,surname),
	index(name,surname),
	check (id in (select actor from actor2movie))
);

drop table if exists actor2movie;

create table actor2movie (
	actor int(8),
	movie int(8),
	role varchar(200),
	primary key(actor,movie)
);

drop table if exists akaname;

create table akaname (
	akaname varchar(200),
	akasurname varchar(200),
	primary key (akaname,akasurname)
);

drop table if exists actorsAka;

create table actorsAka (
	actor int(8),
	akaname varchar(200),
	akasurname varchar(200),
	primary key (akaname,akasurname)
);

drop table if exists proddesigner;

create table proddesigner (
	name varchar(200),
	surname varchar(200),
	primary key(name,surname),
	check (name,surname in (select proddesignerN,proddesignerS from proddesignby))
);


drop table if exists proddesignby;

create table proddesignby (
	movie int(8) primary key,
	proddesignerN varchar(200),
	proddesignerS varchar(200)
);		


drop table if exists director;

create table director (
	name varchar(200),
	surname varchar(200),
	primary key (name,surname),
	check (name,surname in (select directorN,directorS from directedby))
);


drop table if exists directedby;

create table directedby (
	movie int(8) primary key,
	directorN varchar(200),
	directorS varchar(200)
);		
	

drop table if exists producer;

create table producer (
	name varchar(200),
	surname varchar(200),
	primary key (name,surname),
	check (name,surname in (select producerN,producerS from prodby))
);

drop table if exists prodby;

create table prodby (
	producerN varchar(200),
	producerS varchar(200),
	movie int(8),
	primary key (producerN,producerS,movie)
);

drop table if exists akatitle;

create table akatitle (
	title varchar(200) primary key
);

drop table if exists itakatitle;

create table itakatitle (
	title varchar(200) primary key
);


drop table if exists cinematographer;

create table cinematographer (
	name varchar(200),
	surname varchar(200),
	primary key (name,surname),
	check (name,surname in (select cinematographerN,cinematographerS from cinematographyby))
);


drop table if exists cinematographyby;

create table cinematographyby (
	movie int(8) primary key,
	cinematographerN varchar(200),
	cinematographerS varchar(200)
);



drop table if exists designer;

create table designer (
	name varchar(200),
	surname varchar(200),
	primary key (name,surname),
	check (name,surname in (select designerN,designerS from designer2movie))
);


drop table if exists designer2movie;

create table designer2movie (
	designerN varchar(200),
	designerS varchar(200),
	movie int(8),
	primary key (designerN,designerS,movie)
);

drop table if exists distributor;

create table distributor (
	name varchar(200) primary key,
	check (name in (select distributor from distributed))
);

drop table if exists distributed;

create table distributed (
	distributor varchar(200),
	movie int(8),
	primary key (distributor,movie)
	);

drop table if exists sfxcompany;

create table sfxcompany (
	name varchar(200) primary key,
	check (name in (select sfxcompany from sfxby))
);

drop table if exists sfxby;

create table sfxby (
	sfxcompany varchar(200),
	movie int(8),
	primary key (sfxcompany,movie)
);

drop table if exists location;

create table location (
	city varchar(200),
	country varchar(200),
	primary key(city,country)
);

drop table if exists located;

create table located (
	locationcity varchar(200),
	locationcountry varchar(200),
	movie int(8),
	primary key(locationcity,locationcountry,movie)
	);

drop table if exists country;

create table country (
	name varchar(200) primary key,
	check (name in (select country from location))
);

drop table if exists shotin;

create table shotin (
	country varchar(200),
	movie int(8),
	year varchar(100),
	primary key (country,movie)
);

drop table if exists certificate;

create table certificate (
	country varchar(200),
	movie int(8),
	type varchar(100),
	primary key(country,movie,type)
);

drop table if exists movies;

create table movies (
	id int(8) primary key,
	title varchar(200),
	year varchar(100),
	runtimes varchar(100),
	sound varchar(100),
	genre varchar(100),
	colortype varchar(100),
	crewcoverage varchar(100),
	castcoverage varchar(100),
	votes int(8),
	rank float,
	mpaa longtext,
	index(title),
	index(title,year),
	unique(title,year),
	check (id in (select movie from musicby)),
	check (id in (select movie from lang2movie)),
	check (id in (select movie from writerby)),
	check (id in (select movie from prodcompany2movie)),
	check (id in (select movie from actor2movie)),
	check (id in (select movie from prodby)),
	check (id in (select movie from designer2movie)),
	check (id in (select movie from distributor)),
	check (id in (select movie from located)),
	check (id in (select movie from shotin)),
	check (id in (select movie from releasein))
	
);


drop table if exists moviesAka;

create table moviesAka (
	akatitle varchar(200) primary key,
	movie int(8)
);

drop table if exists moviesItaka;

create table moviesItaka (
	itakatitle varchar(200) primary key,
	movie int(8)
);

drop table if exists distributor2country;

create table distributor2country (
	distributor varchar(200) primary key,
	country varchar(200)
);

drop table if exists country2sfx;

create table country2sfx (
	sfxcompany varchar(200) primary key,
	country varchar(200)
);


drop table if exists prodcompany2country;

create table prodcompany2country (
	prodcompany varchar(200) primary key,
	country varchar(200)
);

drop table if exists releasein;

create table releasein (
	movie int(8),
	country varchar(200),
	primary key(movie,country),
	date varchar(200)
);

drop table if exists link;

create table link (
	id int(8) primary key,
	featured_in varchar(200),
	referenced_in varchar(200),
	spin varchar(200),
	spoofed varchar(200),
	version varchar(200),
	followed varchar(200),
	features varchar(200),
	remake varchar(200),
	follows varchar(200),
	ref varchar(200)
);

drop table if exists movieLinks;

create table movieLinks (
	link int(8) primary key,
	movie int(8)
);