imdb2.sql

CREATE SCHEMA imdb2;

CREATE TABLE imdb2.awards ( 
	award_id             integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	award_name           varchar  NOT NULL ,
	CONSTRAINT pk_awards_awards_id PRIMARY KEY ( award_id )
 );

CREATE TABLE imdb2.awards_categories_names ( 
	category_id          integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	category_name        varchar  NOT NULL ,
	CONSTRAINT pk_awards_categories_names_category_id PRIMARY KEY ( category_id )
 );

CREATE TABLE imdb2.company ( 
	company_id           integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	name                 varchar  NOT NULL ,
	CONSTRAINT pk_company_company_id PRIMARY KEY ( company_id )
 );

CREATE TABLE imdb2.country ( 
	country_id           integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	name                 varchar(75)  NOT NULL ,
	CONSTRAINT pk_country_country_id PRIMARY KEY ( country_id )
 );

CREATE TABLE imdb2.genre ( 
	genre_id             integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	genre                varchar(30)  NOT NULL ,
	CONSTRAINT pk_country_country_id_2 PRIMARY KEY ( genre_id )
 );

CREATE TABLE imdb2."language" ( 
	language_id          integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	"language"           varchar(75)  NOT NULL ,
	CONSTRAINT pk_country_country_id_0 PRIMARY KEY ( language_id )
 );

CREATE TABLE imdb2.movie ( 
	movie_id             integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	title                varchar  NOT NULL ,
	release_date         date   ,
	production_date      date   ,
	runtime              cidr  NOT NULL ,
	CONSTRAINT pk_movie_movie_id PRIMARY KEY ( movie_id )
 );

CREATE TABLE imdb2.movie_awards ( 
	movie_id             integer  NOT NULL ,
	awards_id            integer  NOT NULL ,
	category             integer   ,
	nomination_or_win    char(1)  NOT NULL ,
	"year"               date  NOT NULL 
 );

ALTER TABLE imdb2.movie_awards ADD CONSTRAINT nomination_or_win CHECK ( nomination_or_win='N' OR nomination_or_win='W' );

CREATE INDEX idx_awards_movie_id ON imdb2.movie_awards ( movie_id );

CREATE INDEX idx_movie_awards_awards_id ON imdb2.movie_awards ( awards_id );

COMMENT ON COLUMN imdb2.movie_awards.nomination_or_win IS 'N-Nomination\nW-winner';

CREATE TABLE imdb2.movie_genre ( 
	genre_id             integer  NOT NULL ,
	movie_id             integer  NOT NULL 
 );

CREATE INDEX idx_production_1_movie_id ON imdb2.movie_genre ( movie_id );

CREATE INDEX idx_movie_genre_genre_id ON imdb2.movie_genre ( genre_id );

CREATE TABLE imdb2.movie_language ( 
	language_id          integer  NOT NULL ,
	movie_id             integer  NOT NULL 
 );

CREATE INDEX idx_movie_language_movie_id ON imdb2.movie_language ( movie_id );

CREATE INDEX idx_movie_language_language_id ON imdb2.movie_language ( language_id );

CREATE TABLE imdb2.people ( 
	person_id            integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	first_name           varchar  NOT NULL ,
	last_name            varchar   ,
	age                  integer   ,
	born                 date   ,
	died                 date   ,
	alive                char(1)   ,
	birth_country        integer   ,
	CONSTRAINT pk_people_person_id PRIMARY KEY ( person_id )
 );

CREATE INDEX idx_people_birth_country ON imdb2.people ( birth_country );

COMMENT ON COLUMN imdb2.people.alive IS 'Y/N';

CREATE TABLE imdb2.production ( 
	country_id           integer  NOT NULL ,
	movie_id             integer  NOT NULL 
 );

CREATE INDEX idx_production_movie_id ON imdb2.production ( movie_id );

CREATE INDEX idx_production_country_id ON imdb2.production ( country_id );

CREATE TABLE imdb2.production_company ( 
	movie_id             integer  NOT NULL ,
	company_id           integer  NOT NULL 
 );

CREATE INDEX idx_production_company_movie_id ON imdb2.production_company ( movie_id );

CREATE INDEX idx_production_company_company_id ON imdb2.production_company ( company_id );

CREATE TABLE imdb2."table" ( 
 );

CREATE TABLE imdb2.table_2 ( 
 );

CREATE TABLE imdb2.writers ( 
	movie_id             integer  NOT NULL ,
	person_id            integer  NOT NULL 
 );

CREATE INDEX idx_writers_movie_id ON imdb2.writers ( movie_id );

CREATE INDEX idx_writers_person_id ON imdb2.writers ( person_id );

CREATE TABLE imdb2.actors ( 
	movie_id             integer  NOT NULL ,
	person_id            integer  NOT NULL ,
	"character/s"        varchar  NOT NULL 
 );

CREATE INDEX idx_actors_movie_id ON imdb2.actors ( movie_id );

CREATE INDEX idx_actors_person_id ON imdb2.actors ( person_id );

CREATE TABLE imdb2.alternative_title ( 
	movie_id             integer  NOT NULL ,
	movie_title          integer  NOT NULL 
 );

CREATE INDEX idx_alternative_title_movie_id ON imdb2.alternative_title ( movie_id );

CREATE TABLE imdb2.awards_categories ( 
	award_id             integer  NOT NULL ,
	category_id          integer  NOT NULL 
 );

CREATE INDEX idx_awards_categories_award_id ON imdb2.awards_categories ( award_id );

CREATE INDEX idx_awards_categories_category_id ON imdb2.awards_categories ( category_id );

CREATE TABLE imdb2.cameraworkers ( 
	movie_id             integer  NOT NULL ,
	person_id            integer  NOT NULL 
 );

CREATE INDEX idx_cameraworkers_movie_id ON imdb2.cameraworkers ( movie_id );

CREATE INDEX idx_cameraworkers_person_id ON imdb2.cameraworkers ( person_id );

CREATE TABLE imdb2.composers ( 
	movie_id             integer  NOT NULL ,
	person_id            integer  NOT NULL 
 );

CREATE INDEX idx_composers_movie_id ON imdb2.composers ( movie_id );

CREATE INDEX idx_composers_person_id ON imdb2.composers ( person_id );

CREATE TABLE imdb2.description ( 
	movie_id             integer  NOT NULL ,
	description          text  NOT NULL ,
	CONSTRAINT pk_description_movie_id PRIMARY KEY ( movie_id )
 );

CREATE TABLE imdb2.directors ( 
	movie_id             integer  NOT NULL ,
	person_id            integer  NOT NULL 
 );

CREATE INDEX idx_directors_movie_id ON imdb2.directors ( movie_id );

CREATE INDEX idx_directors_person_id ON imdb2.directors ( person_id );

CREATE TABLE imdb2.editors ( 
	movie_id             integer  NOT NULL ,
	person_id            integer  NOT NULL 
 );

CREATE INDEX idx_editors_movie_id ON imdb2.editors ( movie_id );

CREATE INDEX idx_editors_person_id ON imdb2.editors ( person_id );

CREATE TABLE imdb2.finance ( 
	movie_id             integer  NOT NULL ,
	budget               integer   ,
	cumulative_gross     integer   ,
	opening_weekend_usa  integer   ,
	CONSTRAINT pk_finance_movie_id PRIMARY KEY ( movie_id )
 );

CREATE TABLE imdb2.others ( 
	movie_id             integer  NOT NULL ,
	person_id            integer  NOT NULL 
 );

CREATE INDEX idx_others_movie_id ON imdb2.others ( movie_id );

CREATE INDEX idx_others_person_id ON imdb2.others ( person_id );

ALTER TABLE imdb2.actors ADD CONSTRAINT fk_actors_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.actors ADD CONSTRAINT fk_actors_people FOREIGN KEY ( person_id ) REFERENCES imdb2.people( person_id );

ALTER TABLE imdb2.alternative_title ADD CONSTRAINT fk_alternative_title_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.awards_categories ADD CONSTRAINT fk_awards_categories_awards FOREIGN KEY ( award_id ) REFERENCES imdb2.awards( award_id );

ALTER TABLE imdb2.awards_categories ADD CONSTRAINT fk_awards_categories_awards_categories_names FOREIGN KEY ( category_id ) REFERENCES imdb2.awards_categories_names( category_id );

ALTER TABLE imdb2.cameraworkers ADD CONSTRAINT fk_cameraworkers_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.cameraworkers ADD CONSTRAINT fk_cameraworkers_people FOREIGN KEY ( person_id ) REFERENCES imdb2.people( person_id );

ALTER TABLE imdb2.composers ADD CONSTRAINT fk_composers_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.composers ADD CONSTRAINT fk_composers_people FOREIGN KEY ( person_id ) REFERENCES imdb2.people( person_id );

ALTER TABLE imdb2.description ADD CONSTRAINT fk_description_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.directors ADD CONSTRAINT fk_directors_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.directors ADD CONSTRAINT fk_directors_people FOREIGN KEY ( person_id ) REFERENCES imdb2.people( person_id );

ALTER TABLE imdb2.editors ADD CONSTRAINT fk_editors_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.editors ADD CONSTRAINT fk_editors_people FOREIGN KEY ( person_id ) REFERENCES imdb2.people( person_id );

ALTER TABLE imdb2.finance ADD CONSTRAINT fk_finance_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.movie_awards ADD CONSTRAINT fk_awards_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.movie_awards ADD CONSTRAINT fk_movie_awards_awards FOREIGN KEY ( awards_id ) REFERENCES imdb2.awards( award_id );

ALTER TABLE imdb2.movie_genre ADD CONSTRAINT fk_production_1_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.movie_genre ADD CONSTRAINT fk_movie_genre_genre FOREIGN KEY ( genre_id ) REFERENCES imdb2.genre( genre_id );

ALTER TABLE imdb2.movie_language ADD CONSTRAINT fk_movie_language_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.movie_language ADD CONSTRAINT fk_movie_language_language FOREIGN KEY ( language_id ) REFERENCES imdb2."language"( language_id );

ALTER TABLE imdb2.others ADD CONSTRAINT fk_others_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.others ADD CONSTRAINT fk_others_people FOREIGN KEY ( person_id ) REFERENCES imdb2.people( person_id );

ALTER TABLE imdb2.people ADD CONSTRAINT fk_people_country FOREIGN KEY ( birth_country ) REFERENCES imdb2.country( country_id );

ALTER TABLE imdb2.production ADD CONSTRAINT fk_production_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.production ADD CONSTRAINT fk_production_country FOREIGN KEY ( country_id ) REFERENCES imdb2.country( country_id );

ALTER TABLE imdb2.production_company ADD CONSTRAINT fk_production_company_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.production_company ADD CONSTRAINT fk_production_company_company FOREIGN KEY ( company_id ) REFERENCES imdb2.company( company_id );

ALTER TABLE imdb2.writers ADD CONSTRAINT fk_writers_movie FOREIGN KEY ( movie_id ) REFERENCES imdb2.movie( movie_id );

ALTER TABLE imdb2.writers ADD CONSTRAINT fk_writers_people FOREIGN KEY ( person_id ) REFERENCES imdb2.people( person_id );