ORACLE_IMDB.SQL

/*
Created: 03-12-2016
Modified: 03-12-2016
Model: Oracle 11g Release 1
Database: Oracle 11g Release 1
*/


-- Create tables section -------------------------------------------------

-- Table MOVIE

CREATE TABLE "MOVIE"(
  "MOVIE_ID" Integer NOT NULL,
  "NAME" NVarchar2(200) NOT NULL,
  "CERTIFICATE" NVarchar2(50) NOT NULL,
  "PLAYTIME" Integer NOT NULL,
  "RELEASE_DATE" Date NOT NULL,
  "DETAILS" NVarchar2(500)
)
/

-- Add keys for table MOVIE

ALTER TABLE "MOVIE" ADD CONSTRAINT "Key1" PRIMARY KEY ("MOVIE_ID")
/

-- Table REVIEW

CREATE TABLE "REVIEW"(
  "REVIEW_ID" Integer NOT NULL,
  "RATING" Integer,
  "DESCRIPTION" NVarchar2(500),
  "MOVIE_ID" Integer,
  "USERID" Integer NOT NULL,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table REVIEW

CREATE INDEX "IX_Relationship83" ON "REVIEW" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship48" ON "REVIEW" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table REVIEW

ALTER TABLE "REVIEW" ADD CONSTRAINT "Key2" PRIMARY KEY ("REVIEW_ID","USERID")
/

-- Table GENRE

CREATE TABLE "GENRE"(
  "GENREID" Integer NOT NULL,
  "NAME" NVarchar2(200),
  "DESCRIPTION" NVarchar2(200)
)
/

-- Add keys for table GENRE

ALTER TABLE "GENRE" ADD CONSTRAINT "Key3" PRIMARY KEY ("GENREID")
/

-- Table MOVIE_GENRE_BRIDGE

CREATE TABLE "MOVIE_GENRE_BRIDGE"(
  "MOVIE_ID" Integer NOT NULL,
  "GENREID" Integer NOT NULL
)
/

-- Add keys for table MOVIE_GENRE_BRIDGE

ALTER TABLE "MOVIE_GENRE_BRIDGE" ADD CONSTRAINT "Key4" PRIMARY KEY ("MOVIE_ID","GENREID")
/

-- Table CAST

CREATE TABLE "CAST"(
  "CAST_ID" Integer NOT NULL,
  "FIRST_NAME" NVarchar2(150) NOT NULL,
  "LAST_NAME" NVarchar2(150) NOT NULL,
  "DOB" Timestamp(9),
  "GENDER" Char(1 )
)
/

-- Add keys for table CAST

ALTER TABLE "CAST" ADD CONSTRAINT "Key5" PRIMARY KEY ("CAST_ID")
/

-- Table ROLE

CREATE TABLE "ROLE"(
  "ROLEID" Integer NOT NULL,
  "ROLE_NAME" NVarchar2(150),
  "ROLE_DESCRIPTION" NVarchar2(150)
)
/

-- Add keys for table ROLE

ALTER TABLE "ROLE" ADD CONSTRAINT "Key6" PRIMARY KEY ("ROLEID")
/

-- Table MOVIE_CAST_BRIDGE

CREATE TABLE "MOVIE_CAST_BRIDGE"(
  "MOVIE_ID" Integer NOT NULL,
  "CAST_ID" Integer NOT NULL
)
/

-- Add keys for table MOVIE_CAST_BRIDGE

ALTER TABLE "MOVIE_CAST_BRIDGE" ADD CONSTRAINT "Key7" PRIMARY KEY ("MOVIE_ID","CAST_ID")
/

-- Table CAST_ROLE_BRIDGE

CREATE TABLE "CAST_ROLE_BRIDGE"(
  "CAST_ID" Integer NOT NULL,
  "ROLEID" Integer NOT NULL
)
/

-- Add keys for table CAST_ROLE_BRIDGE

ALTER TABLE "CAST_ROLE_BRIDGE" ADD CONSTRAINT "Key8" PRIMARY KEY ("CAST_ID","ROLEID")
/

-- Table MEDIA

CREATE TABLE "MEDIA"(
  "MEDIA_ID" Integer NOT NULL,
  "PHOTOS" Long raw,
  "VIDEOS" Long raw,
  "MOVIE_ID" Integer,
  "CAST_ID" Integer,
  "TRAILERID" Integer,
  "TV_SHOW_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer
)
/

-- Create indexes for table MEDIA

CREATE INDEX "IX_Relationship81" ON "MEDIA" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship92" ON "MEDIA" ("CAST_ID")
/

CREATE INDEX "IX_Relationship10" ON "MEDIA" ("TRAILERID")
/

CREATE INDEX "IX_Relationship50" ON "MEDIA" ("TV_SHOW_ID")
/

CREATE INDEX "IX_Relationship53" ON "MEDIA" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table MEDIA

ALTER TABLE "MEDIA" ADD CONSTRAINT "Key9" PRIMARY KEY ("MEDIA_ID")
/

-- Table TRAILERS

CREATE TABLE "TRAILERS"(
  "TRAILERID" Integer NOT NULL,
  "DURATION" Integer,
  "RELEASE_DATE" Timestamp(9),
  "MOVIE_ID" Integer,
  "TV_SHOW_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer
)
/

-- Create indexes for table TRAILERS

CREATE INDEX "IX_Relationship91" ON "TRAILERS" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship49" ON "TRAILERS" ("TV_SHOW_ID")
/

CREATE INDEX "IX_Relationship54" ON "TRAILERS" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table TRAILERS

ALTER TABLE "TRAILERS" ADD CONSTRAINT "Key10" PRIMARY KEY ("TRAILERID")
/

-- Table THEATRE

CREATE TABLE "THEATRE"(
  "THEATRE_ID" Integer NOT NULL,
  "THEATRE_NAME" NVarchar2(100),
  "ADDRESS_ID" Integer,
  "POSTAL_CODE_ID" Integer,
  "CITY_ID" Integer,
  "STATE_ID" Integer,
  "COUNTRY_ID" Integer
)
/

-- Create indexes for table THEATRE

CREATE INDEX "IX_Relationship82" ON "THEATRE" ("ADDRESS_ID","POSTAL_CODE_ID","CITY_ID","STATE_ID","COUNTRY_ID")
/

-- Add keys for table THEATRE

ALTER TABLE "THEATRE" ADD CONSTRAINT "Key11" PRIMARY KEY ("THEATRE_ID")
/

-- Table SHOWTIME_BRIDGE

CREATE TABLE "SHOWTIME_BRIDGE"(
  "THEATRE_ID" Integer NOT NULL,
  "MOVIE_ID" Integer NOT NULL,
  "START_TIME" Timestamp(9),
  "END_TIME" Timestamp(9),
  "SCREEN_NO" Integer,
  "SHOWTIME_ID" Integer NOT NULL
)
/

-- Add keys for table SHOWTIME_BRIDGE

ALTER TABLE "SHOWTIME_BRIDGE" ADD CONSTRAINT "Key12" PRIMARY KEY ("THEATRE_ID","MOVIE_ID","SHOWTIME_ID")
/

-- Table TICKETS

CREATE TABLE "TICKETS"(
  "TICKETS_ID" Integer NOT NULL,
  "PRICE" Integer,
  "QUANTITY" Integer,
  "THEATRE_ID" Integer NOT NULL,
  "MOVIE_ID" Integer NOT NULL,
  "SHOWTIME_ID" Integer NOT NULL
)
/

-- Add keys for table TICKETS

ALTER TABLE "TICKETS" ADD CONSTRAINT "Key13" PRIMARY KEY ("TICKETS_ID","THEATRE_ID","MOVIE_ID","SHOWTIME_ID")
/

-- Table ADDRESS

CREATE TABLE "ADDRESS"(
  "ADDRESS_ID" Integer NOT NULL,
  "ADDRESS_LINE1" NVarchar2(100) NOT NULL,
  "ADDRESS_LINE2" NVarchar2(100),
  "POSTAL_CODE_ID" Integer NOT NULL,
  "CITY_ID" Integer NOT NULL,
  "STATE_ID" Integer NOT NULL,
  "COUNTRY_ID" Integer NOT NULL,
  "MOVIE_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table ADDRESS

CREATE INDEX "IX_Relationship11" ON "ADDRESS" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship33" ON "ADDRESS" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table ADDRESS

ALTER TABLE "ADDRESS" ADD CONSTRAINT "Key14" PRIMARY KEY ("ADDRESS_ID","POSTAL_CODE_ID","CITY_ID","STATE_ID","COUNTRY_ID")
/

-- Table POSTAL_CODE

CREATE TABLE "POSTAL_CODE"(
  "POSTAL_CODE_ID" Integer NOT NULL,
  "ZIP_CODE" Integer,
  "CITY_ID" Integer NOT NULL,
  "STATE_ID" Integer NOT NULL,
  "COUNTRY_ID" Integer NOT NULL
)
/

-- Add keys for table POSTAL_CODE

ALTER TABLE "POSTAL_CODE" ADD CONSTRAINT "Key15" PRIMARY KEY ("POSTAL_CODE_ID","CITY_ID","STATE_ID","COUNTRY_ID")
/

-- Table CITY

CREATE TABLE "CITY"(
  "CITY_ID" Integer NOT NULL,
  "CITY_NAME" NVarchar2(100),
  "STATE_ID" Integer NOT NULL,
  "COUNTRY_ID" Integer NOT NULL
)
/

-- Add keys for table CITY

ALTER TABLE "CITY" ADD CONSTRAINT "Key16" PRIMARY KEY ("CITY_ID","STATE_ID","COUNTRY_ID")
/

-- Table STATE

CREATE TABLE "STATE"(
  "STATE_ID" Integer NOT NULL,
  "STATE_NAME" NVarchar2(100),
  "COUNTRY_ID" Integer NOT NULL
)
/

-- Add keys for table STATE

ALTER TABLE "STATE" ADD CONSTRAINT "Key17" PRIMARY KEY ("STATE_ID","COUNTRY_ID")
/

-- Table COUNTRY_ID

CREATE TABLE "COUNTRY_ID"(
  "COUNTRY_NAME" NVarchar2(100) NOT NULL,
  "COUNTRY_ID" Integer NOT NULL
)
/

-- Add keys for table COUNTRY_ID

ALTER TABLE "COUNTRY_ID" ADD CONSTRAINT "Key18" PRIMARY KEY ("COUNTRY_ID")
/

-- Table TV_SHOW

CREATE TABLE "TV_SHOW"(
  "TV_SHOW_ID" Integer NOT NULL,
  "TV_SHOW_NAME" NClob NOT NULL,
  "DESCRIPTION" NClob,
  "RELEASE_DATE" NClob,
  "CERTIFICATION" NClob,
  "STORYLINE" NClob,
  "FK_TV_SHOW_ID" Integer
)
/

-- Create indexes for table TV_SHOW

CREATE INDEX "IX_Relationship1" ON "TV_SHOW" ("FK_TV_SHOW_ID")
/

-- Add keys for table TV_SHOW

ALTER TABLE "TV_SHOW" ADD CONSTRAINT "Key19" PRIMARY KEY ("TV_SHOW_ID")
/

-- Table CHANNEL

CREATE TABLE "CHANNEL"(
  "CHANNEL_ID" Integer NOT NULL,
  "CHANNEL_NAME" NClob
)
/

-- Add keys for table CHANNEL

ALTER TABLE "CHANNEL" ADD CONSTRAINT "Key20" PRIMARY KEY ("CHANNEL_ID")
/

-- Table SEASON

CREATE TABLE "SEASON"(
  "SEASON_ID" Integer NOT NULL,
  "SEASON_NUMBER" Integer NOT NULL,
  "TV_SHOW_ID" Integer NOT NULL
)
/

-- Add keys for table SEASON

ALTER TABLE "SEASON" ADD CONSTRAINT "Key21" PRIMARY KEY ("SEASON_ID","TV_SHOW_ID")
/

-- Table SHOW_CHANNEL_BRIDGE

CREATE TABLE "SHOW_CHANNEL_BRIDGE"(
  "CHANNEL_ID" Integer NOT NULL,
  "TV_SHOW_ID" Integer NOT NULL,
  "START_TIME" Timestamp(9),
  "END_TIME" Timestamp(9)
)
/

-- Add keys for table SHOW_CHANNEL_BRIDGE

ALTER TABLE "SHOW_CHANNEL_BRIDGE" ADD CONSTRAINT "Key22" PRIMARY KEY ("CHANNEL_ID","TV_SHOW_ID")
/

-- Table MOVIE_CHANNEL_BRIDGE

CREATE TABLE "MOVIE_CHANNEL_BRIDGE"(
  "MOVIE_ID" Integer NOT NULL,
  "CHANNEL_ID" Integer NOT NULL,
  "START_TIME" Timestamp(9),
  "END_TIME" Timestamp(9)
)
/

-- Add keys for table MOVIE_CHANNEL_BRIDGE

ALTER TABLE "MOVIE_CHANNEL_BRIDGE" ADD CONSTRAINT "Key23" PRIMARY KEY ("MOVIE_ID","CHANNEL_ID")
/

-- Table LANGUAGE

CREATE TABLE "LANGUAGE"(
  "LANGUAGE_ID" Char(1 ) NOT NULL,
  "LANGUAGE_NAME" Char(1 ),
  "LANGUAGE_SCRIPT" Char(1 ),
  "MOVIE_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table LANGUAGE

CREATE INDEX "IX_Relationship12" ON "LANGUAGE" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship2" ON "LANGUAGE" ("TV_SHOW_ID")
/

-- Add keys for table LANGUAGE

ALTER TABLE "LANGUAGE" ADD CONSTRAINT "Key24" PRIMARY KEY ("LANGUAGE_ID")
/

-- Table EPISODES

CREATE TABLE "EPISODES"(
  "EPISODE_ID" Integer NOT NULL,
  "EPISODE_NUMBER" Integer NOT NULL,
  "EPISODE_NAME" NClob NOT NULL,
  "EPISODE_STORYLINE" NClob,
  "EPISODE_DESCRIPTION" NClob,
  "EPISODE_RUNTIME" Integer NOT NULL,
  "EPISODE_RELEASEDATE" Date NOT NULL,
  "SEASON_ID" Integer NOT NULL,
  "TV_SHOW_ID" Integer NOT NULL
)
/

-- Add keys for table EPISODES

ALTER TABLE "EPISODES" ADD CONSTRAINT "Key25" PRIMARY KEY ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Table TEECHNICAL SPECS

CREATE TABLE "TEECHNICAL SPECS"(
  "SPECS_ID" Integer NOT NULL,
  "COLOR" NClob,
  "ASPECT_RATIO" NClob,
  "SOUND_MIX" NClob,
  "NEGATIVE_FORMAT" NClob,
  "RUNTIME" Integer,
  "LABORATORY" NClob,
  "MOVIE_ID" Integer,
  "TV_SHOW_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer
)
/

-- Create indexes for table TEECHNICAL SPECS

CREATE INDEX "IX_Relationship17" ON "TEECHNICAL SPECS" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship18" ON "TEECHNICAL SPECS" ("TV_SHOW_ID")
/

CREATE INDEX "IX_Relationship19" ON "TEECHNICAL SPECS" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table TEECHNICAL SPECS

ALTER TABLE "TEECHNICAL SPECS" ADD CONSTRAINT "Key26" PRIMARY KEY ("SPECS_ID")
/

-- Table TRIVIA

CREATE TABLE "TRIVIA"(
  "TRIVIA_ID" Integer NOT NULL,
  "TRIVIA_NAME" NClob,
  "TRIVIA_DESCRIPTION" NClob,
  "INTERESTING_COUNT" Integer,
  "SHARE_COUNT" Integer,
  "MOVIE_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table TRIVIA

CREATE INDEX "IX_Relationship27" ON "TRIVIA" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship37" ON "TRIVIA" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table TRIVIA

ALTER TABLE "TRIVIA" ADD CONSTRAINT "Key41" PRIMARY KEY ("TRIVIA_ID")
/

-- Table CRAZY_CREDITS

CREATE TABLE "CRAZY_CREDITS"(
  "CRAZY_CREDITS_ID" Integer NOT NULL,
  "CRAZY_CREDITS_NAME" NClob,
  "CRAZY_CREDITS_DESCRIPTION" NClob,
  "INTERESTING_COUNT" Integer,
  "SHARE_COUNT" Integer,
  "MOVIE_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table CRAZY_CREDITS

CREATE INDEX "IX_Relationship30" ON "CRAZY_CREDITS" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship35" ON "CRAZY_CREDITS" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table CRAZY_CREDITS

ALTER TABLE "CRAZY_CREDITS" ADD CONSTRAINT "Key39" PRIMARY KEY ("CRAZY_CREDITS_ID")
/

-- Table GOOFS

CREATE TABLE "GOOFS"(
  "GOOF_ID" Integer NOT NULL,
  "GOOF_NAME" NClob,
  "GOOF_DESCRIPTION" NClob,
  "INTERESTING_COUNT" Integer,
  "SHARE_COUNT" Integer,
  "GOOF_TYPE" NClob,
  "MOVIE_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table GOOFS

CREATE INDEX "IX_Relationship25" ON "GOOFS" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship38" ON "GOOFS" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table GOOFS

ALTER TABLE "GOOFS" ADD CONSTRAINT "Key40" PRIMARY KEY ("GOOF_ID")
/

-- Table QUOTES

CREATE TABLE "QUOTES"(
  "QUOTES_ID" Integer NOT NULL,
  "QUOTES_NAME" NClob,
  "QUOTES_DESCRIPTION" NClob,
  "INTERESTING_COUNT" Integer,
  "SHARE_COUNT" Integer,
  "MOVIE_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table QUOTES

CREATE INDEX "IX_Relationship29" ON "QUOTES" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship34" ON "QUOTES" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table QUOTES

ALTER TABLE "QUOTES" ADD CONSTRAINT "Key42" PRIMARY KEY ("QUOTES_ID")
/

-- Table ALTERNATE_VERSIONS

CREATE TABLE "ALTERNATE_VERSIONS"(
  "ALTERNATE_VERSIONS_ID" Integer NOT NULL,
  "ALTERNATE_VERSIONS_NAME" NClob,
  "ALTERNATE_VERSIONS_DESCRIPTION" NClob,
  "MOVIE_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table ALTERNATE_VERSIONS

CREATE INDEX "IX_Relationship28" ON "ALTERNATE_VERSIONS" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship36" ON "ALTERNATE_VERSIONS" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table ALTERNATE_VERSIONS

ALTER TABLE "ALTERNATE_VERSIONS" ADD CONSTRAINT "Key27" PRIMARY KEY ("ALTERNATE_VERSIONS_ID")
/

-- Table CONNECTIONS

CREATE TABLE "CONNECTIONS"(
  "CONNECITONS_ID" Integer NOT NULL,
  "CONNECTIONS_NAME" NClob,
  "CONNECTION_TYPE" NClob,
  "MOVIE_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer
)
/

-- Create indexes for table CONNECTIONS

CREATE INDEX "IX_Relationship22" ON "CONNECTIONS" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship32" ON "CONNECTIONS" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table CONNECTIONS

ALTER TABLE "CONNECTIONS" ADD CONSTRAINT "Key28" PRIMARY KEY ("CONNECITONS_ID")
/

-- Table REFERENCE_BRIDGE

CREATE TABLE "REFERENCE_BRIDGE"(
  "REFERENCE_ID" Integer NOT NULL,
  "TV_SHOW_ID" Integer,
  "CONNECITONS_ID" Integer NOT NULL,
  "MOVIE_ID" Integer
)
/

-- Create indexes for table REFERENCE_BRIDGE

CREATE INDEX "IX_Relationship21" ON "REFERENCE_BRIDGE" ("TV_SHOW_ID")
/

CREATE INDEX "IX_Relationship24" ON "REFERENCE_BRIDGE" ("MOVIE_ID")
/

-- Add keys for table REFERENCE_BRIDGE

ALTER TABLE "REFERENCE_BRIDGE" ADD CONSTRAINT "Key29" PRIMARY KEY ("REFERENCE_ID","CONNECITONS_ID")
/

-- Table TVSHOW_GENRE_BRIDGE

CREATE TABLE "TVSHOW_GENRE_BRIDGE"(
  "TV_SHOW_ID" Integer NOT NULL,
  "GENREID" Integer NOT NULL
)
/

-- Add keys for table TVSHOW_GENRE_BRIDGE

ALTER TABLE "TVSHOW_GENRE_BRIDGE" ADD CONSTRAINT "Key30" PRIMARY KEY ("TV_SHOW_ID","GENREID")
/

-- Table MESSAGE_BOARD

CREATE TABLE "MESSAGE_BOARD"(
  "BOARD_ID" Integer NOT NULL,
  "BOARD_NAME" Clob NOT NULL,
  "BOARD_DATA" Clob NOT NULL,
  "MOVIE_ID" Integer,
  "EPISODE_ID" Integer,
  "SEASON_ID" Integer,
  "TV_SHOW_ID" Integer,
  "USERID" Integer NOT NULL
)
/

-- Create indexes for table MESSAGE_BOARD

CREATE INDEX "IX_Relationship40" ON "MESSAGE_BOARD" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship41" ON "MESSAGE_BOARD" ("EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Add keys for table MESSAGE_BOARD

ALTER TABLE "MESSAGE_BOARD" ADD CONSTRAINT "Key31" PRIMARY KEY ("BOARD_ID","USERID")
/

-- Table THREAD

CREATE TABLE "THREAD"(
  "THREAD_ID" Integer NOT NULL,
  "THREAD_NAME" Clob NOT NULL,
  "THREAD_DATA" Clob NOT NULL,
  "BOARD_ID" Integer NOT NULL,
  "REVIEW_USERID" Integer,
  "USERID" Integer NOT NULL
)
/

-- Create indexes for table THREAD

CREATE INDEX "IX_Relationship43" ON "THREAD" ("REVIEW_USERID")
/

-- Add keys for table THREAD

ALTER TABLE "THREAD" ADD CONSTRAINT "Key32" PRIMARY KEY ("THREAD_ID","BOARD_ID","USERID")
/

-- Table USERACCOUNT

CREATE TABLE "USERACCOUNT"(
  "USERID" Integer NOT NULL,
  "NAME" NClob NOT NULL,
  "NATIONALITY" NClob,
  "GENDER" Char(1 ) NOT NULL,
  "USERNAME" NClob
)
/

-- Add keys for table USERACCOUNT

ALTER TABLE "USERACCOUNT" ADD CONSTRAINT "Key33" PRIMARY KEY ("USERID")
/

-- Table EPISODE_CAST_BRIDGE

CREATE TABLE "EPISODE_CAST_BRIDGE"(
  "CAST_ID" Integer NOT NULL,
  "EPISODE_ID" Integer NOT NULL,
  "SEASON_ID" Integer NOT NULL,
  "TV_SHOW_ID" Integer NOT NULL
)
/

-- Add keys for table EPISODE_CAST_BRIDGE

ALTER TABLE "EPISODE_CAST_BRIDGE" ADD CONSTRAINT "Key34" PRIMARY KEY ("CAST_ID","EPISODE_ID","SEASON_ID","TV_SHOW_ID")
/

-- Table AWARD

CREATE TABLE "AWARD"(
  "AWARD_ID" Integer NOT NULL,
  "AWARD_NAME" NClob NOT NULL,
  "AWARD_CATEGORY" NClob NOT NULL,
  "YEAR" Integer NOT NULL
)
/

-- Add keys for table AWARD

ALTER TABLE "AWARD" ADD CONSTRAINT "Key35" PRIMARY KEY ("AWARD_ID")
/

-- Table NOMINEE

CREATE TABLE "NOMINEE"(
  "NOMINEE_ID" Integer NOT NULL,
  "AWARD_ID" Integer NOT NULL,
  "IS_WINNER" Char(1 ),
  "TV_SHOW_ID" Integer,
  "MOVIE_ID" Integer,
  "CAST_ID" Integer
)
/

-- Create indexes for table NOMINEE

CREATE INDEX "IX_Relationship56" ON "NOMINEE" ("TV_SHOW_ID")
/

CREATE INDEX "IX_Relationship57" ON "NOMINEE" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship58" ON "NOMINEE" ("CAST_ID")
/

-- Add keys for table NOMINEE

ALTER TABLE "NOMINEE" ADD CONSTRAINT "Key36" PRIMARY KEY ("NOMINEE_ID","AWARD_ID")
/

-- Table NEWS

CREATE TABLE "NEWS"(
  "NEWS_ID" Integer NOT NULL,
  "PUBLISH_DATE" Date NOT NULL,
  "TITLE" NClob NOT NULL,
  "DETAILS" NClob,
  "AWARD_ID" Integer,
  "CAST_ID" Integer,
  "MOVIE_ID" Integer,
  "TV_SHOW_ID" Integer,
  "THEATRE_ID" Integer,
  "SHOWTIME_ID" Integer
)
/

-- Create indexes for table NEWS

CREATE INDEX "IX_Relationship59" ON "NEWS" ("AWARD_ID")
/

CREATE INDEX "IX_Relationship60" ON "NEWS" ("CAST_ID")
/

CREATE INDEX "IX_Relationship61" ON "NEWS" ("MOVIE_ID")
/

CREATE INDEX "IX_Relationship62" ON "NEWS" ("TV_SHOW_ID")
/

CREATE INDEX "IX_Relationship63" ON "NEWS" ("THEATRE_ID","SHOWTIME_ID","MOVIE_ID")
/

-- Add keys for table NEWS

ALTER TABLE "NEWS" ADD CONSTRAINT "Key37" PRIMARY KEY ("NEWS_ID")
/

-- Table POLLS

CREATE TABLE "POLLS"(
  "POLL_ID" Integer NOT NULL,
  "POLL_NAME" NClob NOT NULL,
  "POLL_DATE" Date,
  "USERID" Integer
)
/

-- Create indexes for table POLLS

CREATE INDEX "IX_Relationship64" ON "POLLS" ("USERID")
/

-- Add keys for table POLLS

ALTER TABLE "POLLS" ADD CONSTRAINT "Key38" PRIMARY KEY ("POLL_ID")
/

-- Table POLL_OPTIONS

CREATE TABLE "POLL_OPTIONS"(
  "OPTION_ID" Integer NOT NULL,
  "OPTION_NAME" NClob NOT NULL,
  "VOTE_COUNT" Integer,
  "HAS_SELECTED" Char(1 ),
  "POLL_ID" Integer NOT NULL
)
/

-- Add keys for table POLL_OPTIONS

ALTER TABLE "POLL_OPTIONS" ADD CONSTRAINT "Key43" PRIMARY KEY ("OPTION_ID","POLL_ID")
/

-- Create relationships section ------------------------------------------------- 

ALTER TABLE "POLL_OPTIONS" ADD CONSTRAINT "HAS POLL OPTIONS" FOREIGN KEY ("POLL_ID") REFERENCES "POLLS" ("POLL_ID")
/

ALTER TABLE "LANGUAGE" ADD CONSTRAINT "RV SHOW HAS LANGUAGES" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "TV_SHOW" ADD CONSTRAINT "Relationship1" FOREIGN KEY ("FK_TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "POLLS" ADD CONSTRAINT "VOTE FOR POLLS" FOREIGN KEY ("USERID") REFERENCES "USERACCOUNT" ("USERID")
/

ALTER TABLE "NEWS" ADD CONSTRAINT "SHOWTIME NEWS" FOREIGN KEY ("THEATRE_ID", "MOVIE_ID", "SHOWTIME_ID") REFERENCES "SHOWTIME_BRIDGE" ("THEATRE_ID", "MOVIE_ID", "SHOWTIME_ID")
/

ALTER TABLE "NEWS" ADD CONSTRAINT "TV SHOW NEWS" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "NEWS" ADD CONSTRAINT "MOVIE NEWS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "NEWS" ADD CONSTRAINT "CELEB NEWS" FOREIGN KEY ("CAST_ID") REFERENCES "CAST" ("CAST_ID")
/

ALTER TABLE "NEWS" ADD CONSTRAINT "AWARD NEWS" FOREIGN KEY ("AWARD_ID") REFERENCES "AWARD" ("AWARD_ID")
/

ALTER TABLE "NOMINEE" ADD CONSTRAINT "CAST_NOMINEE" FOREIGN KEY ("CAST_ID") REFERENCES "CAST" ("CAST_ID")
/

ALTER TABLE "NOMINEE" ADD CONSTRAINT "MOVIE NOMINEE" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "NOMINEE" ADD CONSTRAINT "TV SHOW NOMINEE" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "NOMINEE" ADD CONSTRAINT "HAS MANY NOMINEES" FOREIGN KEY ("AWARD_ID") REFERENCES "AWARD" ("AWARD_ID")
/

ALTER TABLE "TRAILERS" ADD CONSTRAINT "EPISODE HAS TRAILER" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "MEDIA" ADD CONSTRAINT "EPISODE HAS MEDIA" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "EPISODE_CAST_BRIDGE" ADD CONSTRAINT "EPISODE HAS CASTS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "EPISODE_CAST_BRIDGE" ADD CONSTRAINT "CAST BELONG TO EPISODES" FOREIGN KEY ("CAST_ID") REFERENCES "CAST" ("CAST_ID")
/

ALTER TABLE "MEDIA" ADD CONSTRAINT "TV SHOW HAS MEDIA" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "TRAILERS" ADD CONSTRAINT "TV SHOWS HAVE TRAILERS" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "REVIEW" ADD CONSTRAINT "EPISODES HAVE REVIEWS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "REVIEW" ADD CONSTRAINT "GIVES REVIEW" FOREIGN KEY ("USERID") REFERENCES "USERACCOUNT" ("USERID")
/

ALTER TABLE "MESSAGE_BOARD" ADD CONSTRAINT "CREATES THREAD" FOREIGN KEY ("USERID") REFERENCES "USERACCOUNT" ("USERID")
/

ALTER TABLE "THREAD" ADD CONSTRAINT "REPLIES TO THREAD" FOREIGN KEY ("REVIEW_USERID") REFERENCES "USERACCOUNT" ("USERID")
/

ALTER TABLE "MESSAGE_BOARD" ADD CONSTRAINT "EPISODE HAS MESSAGE THREADS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "MESSAGE_BOARD" ADD CONSTRAINT "MOVIE HAS MESSAGE THREADS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "THREAD" ADD CONSTRAINT "HAS REPLY THREADS" FOREIGN KEY ("BOARD_ID", "USERID") REFERENCES "MESSAGE_BOARD" ("BOARD_ID", "USERID")
/

ALTER TABLE "GOOFS" ADD CONSTRAINT "EPISODES HAVE GOOFS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "TRIVIA" ADD CONSTRAINT "EPISODES HAVE TRIVIA" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "ALTERNATE_VERSIONS" ADD CONSTRAINT "EPISODES HAVE ALTERNATE VERSIONS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "CRAZY_CREDITS" ADD CONSTRAINT "EPISODES HAVE CRAZY CREDITS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "QUOTES" ADD CONSTRAINT "EPISODES HAVE QUOTES" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "ADDRESS" ADD CONSTRAINT "EPISODES SHOT AT LOCATIONS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "CONNECTIONS" ADD CONSTRAINT "EPISODES HAVE CONNECTIONS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "CRAZY_CREDITS" ADD CONSTRAINT "MOVIE HAS CRAZY CREDITS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "QUOTES" ADD CONSTRAINT "MOVIE HAS QUOTES" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "ALTERNATE_VERSIONS" ADD CONSTRAINT "MOVIE HAS ALTERNATE VERSIONS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "TRIVIA" ADD CONSTRAINT "MOVIE HAS TRIVIA" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "GOOFS" ADD CONSTRAINT "MOVIE HAS GOOFS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "REFERENCE_BRIDGE" ADD CONSTRAINT "MOVIE REFERENCE" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "REFERENCE_BRIDGE" ADD CONSTRAINT "CONNECTION REFERENCE" FOREIGN KEY ("CONNECITONS_ID") REFERENCES "CONNECTIONS" ("CONNECITONS_ID")
/

ALTER TABLE "CONNECTIONS" ADD CONSTRAINT "MOVIE HAS CONNECTIONS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "REFERENCE_BRIDGE" ADD CONSTRAINT "TV SHOW REFERENCES" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "TEECHNICAL SPECS" ADD CONSTRAINT "EPISODES HAVE TECHNICAL SPECS" FOREIGN KEY ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID") REFERENCES "EPISODES" ("EPISODE_ID", "SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "TEECHNICAL SPECS" ADD CONSTRAINT "TVSHOW HAS TECHNICAL SPECS" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "TEECHNICAL SPECS" ADD CONSTRAINT "MOVIE HAS TECHNICAL SPECS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "TVSHOW_GENRE_BRIDGE" ADD CONSTRAINT "HAS MANY TV SHOWS" FOREIGN KEY ("GENREID") REFERENCES "GENRE" ("GENREID")
/

ALTER TABLE "TVSHOW_GENRE_BRIDGE" ADD CONSTRAINT "HAS MANY GENRE" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "EPISODES" ADD CONSTRAINT "HAS EPISODES" FOREIGN KEY ("SEASON_ID", "TV_SHOW_ID") REFERENCES "SEASON" ("SEASON_ID", "TV_SHOW_ID")
/

ALTER TABLE "SEASON" ADD CONSTRAINT "HAS SEASONS" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "LANGUAGE" ADD CONSTRAINT "MOVIE HAS LANGUAGES" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "ADDRESS" ADD CONSTRAINT "MOVIE HAS LOCATIONS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "MEDIA" ADD CONSTRAINT "TRAILER HAS MEDIA" FOREIGN KEY ("TRAILERID") REFERENCES "TRAILERS" ("TRAILERID")
/

ALTER TABLE "TRAILERS" ADD CONSTRAINT "MOVIE HAS TRAILERS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "REVIEW" ADD CONSTRAINT "HAS REVIEW" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "MOVIE_CHANNEL_BRIDGE" ADD CONSTRAINT "CHANNEL SHOWS MULTIPLE MOVIES" FOREIGN KEY ("CHANNEL_ID") REFERENCES "CHANNEL" ("CHANNEL_ID")
/

ALTER TABLE "MOVIE_CHANNEL_BRIDGE" ADD CONSTRAINT "MOVIE IN MULTIPLE CHANNELS" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "SHOW_CHANNEL_BRIDGE" ADD CONSTRAINT "SHOWS IN MULTIPLE CHANNELS" FOREIGN KEY ("TV_SHOW_ID") REFERENCES "TV_SHOW" ("TV_SHOW_ID")
/

ALTER TABLE "SHOW_CHANNEL_BRIDGE" ADD CONSTRAINT "CHANNEL HAS SHOWS" FOREIGN KEY ("CHANNEL_ID") REFERENCES "CHANNEL" ("CHANNEL_ID")
/

ALTER TABLE "THEATRE" ADD CONSTRAINT "THEATRE HAS ADDRESS" FOREIGN KEY ("ADDRESS_ID", "POSTAL_CODE_ID", "CITY_ID", "STATE_ID", "COUNTRY_ID") REFERENCES "ADDRESS" ("ADDRESS_ID", "POSTAL_CODE_ID", "CITY_ID", "STATE_ID", "COUNTRY_ID")
/

ALTER TABLE "STATE" ADD CONSTRAINT "HAS STATES" FOREIGN KEY ("COUNTRY_ID") REFERENCES "COUNTRY_ID" ("COUNTRY_ID")
/

ALTER TABLE "CITY" ADD CONSTRAINT "HAS CITIES" FOREIGN KEY ("STATE_ID", "COUNTRY_ID") REFERENCES "STATE" ("STATE_ID", "COUNTRY_ID")
/

ALTER TABLE "POSTAL_CODE" ADD CONSTRAINT "HAS POSTAL CODES" FOREIGN KEY ("CITY_ID", "STATE_ID", "COUNTRY_ID") REFERENCES "CITY" ("CITY_ID", "STATE_ID", "COUNTRY_ID")
/

ALTER TABLE "ADDRESS" ADD CONSTRAINT "HAS ADDRESSES" FOREIGN KEY ("POSTAL_CODE_ID", "CITY_ID", "STATE_ID", "COUNTRY_ID") REFERENCES "POSTAL_CODE" ("POSTAL_CODE_ID", "CITY_ID", "STATE_ID", "COUNTRY_ID")
/

ALTER TABLE "TICKETS" ADD CONSTRAINT "HAS MANY TICKETS" FOREIGN KEY ("THEATRE_ID", "MOVIE_ID", "SHOWTIME_ID") REFERENCES "SHOWTIME_BRIDGE" ("THEATRE_ID", "MOVIE_ID", "SHOWTIME_ID")
/

ALTER TABLE "SHOWTIME_BRIDGE" ADD CONSTRAINT "MOVIE HAS SHOWTIME" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "SHOWTIME_BRIDGE" ADD CONSTRAINT "HAS SHOW TIME" FOREIGN KEY ("THEATRE_ID") REFERENCES "THEATRE" ("THEATRE_ID")
/

ALTER TABLE "MEDIA" ADD CONSTRAINT "CAST_HAS_MEDIA" FOREIGN KEY ("CAST_ID") REFERENCES "CAST" ("CAST_ID")
/

ALTER TABLE "MEDIA" ADD CONSTRAINT "MOVIES HAVE MEDIA" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "CAST_ROLE_BRIDGE" ADD CONSTRAINT "ROLES BY A CAST" FOREIGN KEY ("ROLEID") REFERENCES "ROLE" ("ROLEID")
/

ALTER TABLE "CAST_ROLE_BRIDGE" ADD CONSTRAINT "CAST HAS ROLES" FOREIGN KEY ("CAST_ID") REFERENCES "CAST" ("CAST_ID")
/

ALTER TABLE "MOVIE_CAST_BRIDGE" ADD CONSTRAINT "CAST IS IN MOVIES" FOREIGN KEY ("CAST_ID") REFERENCES "CAST" ("CAST_ID")
/

ALTER TABLE "MOVIE_CAST_BRIDGE" ADD CONSTRAINT "MOVIE HAS A CAST" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/

ALTER TABLE "MOVIE_GENRE_BRIDGE" ADD CONSTRAINT "GENRE HAS MOVIES" FOREIGN KEY ("GENREID") REFERENCES "GENRE" ("GENREID")
/

ALTER TABLE "MOVIE_GENRE_BRIDGE" ADD CONSTRAINT "MOVIES HAVE A GENRE" FOREIGN KEY ("MOVIE_ID") REFERENCES "MOVIE" ("MOVIE_ID")
/