MSSQLIMDB.sql

/*
Created: 26-11-2016
Modified: 03-12-2016
Model: IMDB
Database: MS SQL Server 2016
*/


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

-- Table MOVIE

CREATE TABLE [MOVIE]
(
 [MOVIE_ID] Int IDENTITY(1,1) NOT NULL,
 [NAME] Nvarchar(200) NOT NULL,
 [CERTIFICATE] Nvarchar(50) NOT NULL,
 [PLAYTIME] Int NOT NULL,
 [RELEASE_DATE] Date NOT NULL,
 [DETAILS] Nvarchar(500) NULL
)
go

-- Add keys for table MOVIE

ALTER TABLE [MOVIE] ADD CONSTRAINT [Key1] PRIMARY KEY ([MOVIE_ID])
go

-- Table REVIEW

CREATE TABLE [REVIEW]
(
 [REVIEW_ID] Int IDENTITY(1,1) NOT NULL,
 [RATING] Int NULL,
 [DESCRIPTION] Nvarchar(500) NULL,
 [MOVIE_ID] Int NULL,
 [USERID] Int NOT NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table REVIEW

CREATE INDEX [IX_Relationship8] ON [REVIEW] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship48] ON [REVIEW] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table REVIEW

ALTER TABLE [REVIEW] ADD CONSTRAINT [Key2] PRIMARY KEY ([REVIEW_ID],[USERID])
go

-- Table GENRE

CREATE TABLE [GENRE]
(
 [GENREID] Int IDENTITY(1,1) NOT NULL,
 [NAME] Nvarchar(200) NULL,
 [DESCRIPTION] Nvarchar(200) NULL
)
go

-- Add keys for table GENRE

ALTER TABLE [GENRE] ADD CONSTRAINT [Key3] PRIMARY KEY ([GENREID])
go

-- Table MOVIE_GENRE_BRIDGE

CREATE TABLE [MOVIE_GENRE_BRIDGE]
(
 [MOVIE_ID] Int NOT NULL,
 [GENREID] Int NOT NULL
)
go

-- Add keys for table MOVIE_GENRE_BRIDGE

ALTER TABLE [MOVIE_GENRE_BRIDGE] ADD CONSTRAINT [Key4] PRIMARY KEY ([MOVIE_ID],[GENREID])
go

-- Table CAST

CREATE TABLE [CAST]
(
 [CAST_ID] Int IDENTITY(1,1) NOT NULL,
 [FIRST_NAME] Nvarchar(150) NOT NULL,
 [LAST_NAME] Nvarchar(150) NOT NULL,
 [DOB] Datetime NULL,
 [GENDER] Char(1) NULL
)
go

-- Add keys for table CAST

ALTER TABLE [CAST] ADD CONSTRAINT [Key5] PRIMARY KEY ([CAST_ID])
go

-- Table ROLE

CREATE TABLE [ROLE]
(
 [ROLEID] Int IDENTITY(1,1) NOT NULL,
 [ROLE_NAME] Nvarchar(150) NULL,
 [ROLE_DESCRIPTION] Nvarchar(150) NULL
)
go

-- Add keys for table ROLE

ALTER TABLE [ROLE] ADD CONSTRAINT [Key6] PRIMARY KEY ([ROLEID])
go

-- Table MOVIE_CAST_BRIDGE

CREATE TABLE [MOVIE_CAST_BRIDGE]
(
 [MOVIE_ID] Int NOT NULL,
 [CAST_ID] Int NOT NULL
)
go

-- Add keys for table MOVIE_CAST_BRIDGE

ALTER TABLE [MOVIE_CAST_BRIDGE] ADD CONSTRAINT [Key7] PRIMARY KEY ([MOVIE_ID],[CAST_ID])
go

-- Table CAST_ROLE_BRIDGE

CREATE TABLE [CAST_ROLE_BRIDGE]
(
 [CAST_ID] Int NOT NULL,
 [ROLEID] Int NOT NULL
)
go

-- Add keys for table CAST_ROLE_BRIDGE

ALTER TABLE [CAST_ROLE_BRIDGE] ADD CONSTRAINT [Key8] PRIMARY KEY ([CAST_ID],[ROLEID])
go

-- Table MEDIA

CREATE TABLE [MEDIA]
(
 [MEDIA_ID] Int IDENTITY(1,1) NOT NULL,
 [PHOTOS] Image NULL,
 [VIDEOS] Image NULL,
 [MOVIE_ID] Int NULL,
 [CAST_ID] Int NULL,
 [TRAILERID] Int NULL,
 [TV_SHOW_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL
)
go

-- Create indexes for table MEDIA

CREATE INDEX [IX_Relationship8] ON [MEDIA] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship9] ON [MEDIA] ([CAST_ID])
go

CREATE INDEX [IX_Relationship10] ON [MEDIA] ([TRAILERID])
go

CREATE INDEX [IX_Relationship50] ON [MEDIA] ([TV_SHOW_ID])
go

CREATE INDEX [IX_Relationship53] ON [MEDIA] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table MEDIA

ALTER TABLE [MEDIA] ADD CONSTRAINT [Key9] PRIMARY KEY ([MEDIA_ID])
go

-- Table TRAILERS

CREATE TABLE [TRAILERS]
(
 [TRAILERID] Int IDENTITY(1,1) NOT NULL,
 [DURATION] Int NULL,
 [RELEASE_DATE] Datetime NULL,
 [MOVIE_ID] Int NULL,
 [TV_SHOW_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL
)
go

-- Create indexes for table TRAILERS

CREATE INDEX [IX_Relationship9] ON [TRAILERS] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship49] ON [TRAILERS] ([TV_SHOW_ID])
go

CREATE INDEX [IX_Relationship54] ON [TRAILERS] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table TRAILERS

ALTER TABLE [TRAILERS] ADD CONSTRAINT [Key10] PRIMARY KEY ([TRAILERID])
go

-- Table THEATRE

CREATE TABLE [THEATRE]
(
 [THEATRE_ID] Int IDENTITY(1,1) NOT NULL,
 [THEATRE_NAME] Nvarchar(100) NULL,
 [ADDRESS_ID] Int NULL,
 [POSTAL_CODE_ID] Int NULL,
 [CITY_ID] Int NULL,
 [STATE_ID] Int NULL,
 [COUNTRY_ID] Int NULL
)
go

-- Create indexes for table THEATRE

CREATE INDEX [IX_Relationship8] ON [THEATRE] ([ADDRESS_ID],[POSTAL_CODE_ID],[CITY_ID],[STATE_ID],[COUNTRY_ID])
go

-- Add keys for table THEATRE

ALTER TABLE [THEATRE] ADD CONSTRAINT [Key11] PRIMARY KEY ([THEATRE_ID])
go

-- Table SHOWTIME_BRIDGE

CREATE TABLE [SHOWTIME_BRIDGE]
(
 [THEATRE_ID] Int NOT NULL,
 [MOVIE_ID] Int NOT NULL,
 [START_TIME] Datetime NULL,
 [END_TIME] Datetime NULL,
 [SCREEN_NO] Int NULL,
 [SHOWTIME_ID] Int NOT NULL
)
go

-- Add keys for table SHOWTIME_BRIDGE

ALTER TABLE [SHOWTIME_BRIDGE] ADD CONSTRAINT [Key12] PRIMARY KEY ([THEATRE_ID],[MOVIE_ID],[SHOWTIME_ID])
go

-- Table TICKETS

CREATE TABLE [TICKETS]
(
 [TICKETS_ID] Int IDENTITY(1,1) NOT NULL,
 [PRICE] Int NULL,
 [QUANTITY] Int NULL,
 [THEATRE_ID] Int NOT NULL,
 [MOVIE_ID] Int NOT NULL,
 [SHOWTIME_ID] Int NOT NULL
)
go

-- Add keys for table TICKETS

ALTER TABLE [TICKETS] ADD CONSTRAINT [Key13] PRIMARY KEY ([TICKETS_ID],[THEATRE_ID],[MOVIE_ID],[SHOWTIME_ID])
go

-- Table ADDRESS

CREATE TABLE [ADDRESS]
(
 [ADDRESS_ID] Int IDENTITY(1,1) NOT NULL,
 [ADDRESS_LINE1] Nvarchar(100) NOT NULL,
 [ADDRESS_LINE2] Nvarchar(100) NULL,
 [POSTAL_CODE_ID] Int NOT NULL,
 [CITY_ID] Int NOT NULL,
 [STATE_ID] Int NOT NULL,
 [COUNTRY_ID] Int NOT NULL,
 [MOVIE_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table ADDRESS

CREATE INDEX [IX_Relationship11] ON [ADDRESS] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship33] ON [ADDRESS] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table ADDRESS

ALTER TABLE [ADDRESS] ADD CONSTRAINT [Key14] PRIMARY KEY ([ADDRESS_ID],[POSTAL_CODE_ID],[CITY_ID],[STATE_ID],[COUNTRY_ID])
go

-- Table POSTAL_CODE

CREATE TABLE [POSTAL_CODE]
(
 [POSTAL_CODE_ID] Int IDENTITY(1,1) NOT NULL,
 [ZIP_CODE] Int NULL,
 [CITY_ID] Int NOT NULL,
 [STATE_ID] Int NOT NULL,
 [COUNTRY_ID] Int NOT NULL
)
go

-- Add keys for table POSTAL_CODE

ALTER TABLE [POSTAL_CODE] ADD CONSTRAINT [Key15] PRIMARY KEY ([POSTAL_CODE_ID],[CITY_ID],[STATE_ID],[COUNTRY_ID])
go

-- Table CITY

CREATE TABLE [CITY]
(
 [CITY_ID] Int IDENTITY(1,1) NOT NULL,
 [CITY_NAME] Nvarchar(100) NULL,
 [STATE_ID] Int NOT NULL,
 [COUNTRY_ID] Int NOT NULL
)
go

-- Add keys for table CITY

ALTER TABLE [CITY] ADD CONSTRAINT [Key16] PRIMARY KEY ([CITY_ID],[STATE_ID],[COUNTRY_ID])
go

-- Table STATE

CREATE TABLE [STATE]
(
 [STATE_ID] Int IDENTITY(1,1) NOT NULL,
 [STATE_NAME] Nvarchar(100) NULL,
 [COUNTRY_ID] Int NOT NULL
)
go

-- Add keys for table STATE

ALTER TABLE [STATE] ADD CONSTRAINT [Key17] PRIMARY KEY ([STATE_ID],[COUNTRY_ID])
go

-- Table COUNTRY_ID

CREATE TABLE [COUNTRY_ID]
(
 [COUNTRY_NAME] Nvarchar(100) NOT NULL,
 [COUNTRY_ID] Int IDENTITY(1,1) NOT NULL
)
go

-- Add keys for table COUNTRY_ID

ALTER TABLE [COUNTRY_ID] ADD CONSTRAINT [Key18] PRIMARY KEY ([COUNTRY_ID])
go

-- Table TV_SHOW

CREATE TABLE [TV_SHOW]
(
 [TV_SHOW_ID] Int IDENTITY(1,1) NOT NULL,
 [TV_SHOW_NAME] Nvarchar(max) NOT NULL,
 [DESCRIPTION] Nvarchar(max) NULL,
 [RELEASE_DATE] Nvarchar(max) NULL,
 [CERTIFICATION] Nvarchar(max) NULL,
 [STORYLINE] Nvarchar(max) NULL,
 [FK_TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table TV_SHOW

CREATE INDEX [IX_Relationship1] ON [TV_SHOW] ([FK_TV_SHOW_ID])
go

-- Add keys for table TV_SHOW

ALTER TABLE [TV_SHOW] ADD CONSTRAINT [Key19] PRIMARY KEY ([TV_SHOW_ID])
go

-- Table CHANNEL

CREATE TABLE [CHANNEL]
(
 [CHANNEL_ID] Int IDENTITY(1,1) NOT NULL,
 [CHANNEL_NAME] Nvarchar(max) NULL
)
go

-- Add keys for table CHANNEL

ALTER TABLE [CHANNEL] ADD CONSTRAINT [Key20] PRIMARY KEY ([CHANNEL_ID])
go

-- Table SEASON

CREATE TABLE [SEASON]
(
 [SEASON_ID] Int IDENTITY(1,1) NOT NULL,
 [SEASON_NUMBER] Int NOT NULL,
 [TV_SHOW_ID] Int NOT NULL
)
go

-- Add keys for table SEASON

ALTER TABLE [SEASON] ADD CONSTRAINT [Key21] PRIMARY KEY ([SEASON_ID],[TV_SHOW_ID])
go

-- Table SHOW_CHANNEL_BRIDGE

CREATE TABLE [SHOW_CHANNEL_BRIDGE]
(
 [CHANNEL_ID] Int NOT NULL,
 [TV_SHOW_ID] Int NOT NULL,
 [START_TIME] Datetime NULL,
 [END_TIME] Datetime NULL
)
go

-- Add keys for table SHOW_CHANNEL_BRIDGE

ALTER TABLE [SHOW_CHANNEL_BRIDGE] ADD CONSTRAINT [Key22] PRIMARY KEY ([CHANNEL_ID],[TV_SHOW_ID])
go

-- Table MOVIE_CHANNEL_BRIDGE

CREATE TABLE [MOVIE_CHANNEL_BRIDGE]
(
 [MOVIE_ID] Int NOT NULL,
 [CHANNEL_ID] Int NOT NULL,
 [START_TIME] Datetime NULL,
 [END_TIME] Datetime NULL
)
go

-- Add keys for table MOVIE_CHANNEL_BRIDGE

ALTER TABLE [MOVIE_CHANNEL_BRIDGE] ADD CONSTRAINT [Key23] PRIMARY KEY ([MOVIE_ID],[CHANNEL_ID])
go

-- Table LANGUAGE

CREATE TABLE [LANGUAGE]
(
 [LANGUAGE_ID] INT IDENTITY(1,1) NOT NULL,
 [LANGUAGE_NAME] Nvarchar(max) NULL,
 [LANGUAGE_SCRIPT] Nvarchar(max) NULL,
 [MOVIE_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table LANGUAGE

CREATE INDEX [IX_Relationship12] ON [LANGUAGE] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship2] ON [LANGUAGE] ([TV_SHOW_ID])
go

-- Add keys for table LANGUAGE

ALTER TABLE [LANGUAGE] ADD CONSTRAINT [Key24] PRIMARY KEY ([LANGUAGE_ID])
go

-- Table EPISODES

CREATE TABLE [EPISODES]
(
 [EPISODE_ID] Int IDENTITY(1,1) NOT NULL,
 [EPISODE_NUMBER] Int NOT NULL,
 [EPISODE_NAME] Nvarchar(max) NOT NULL,
 [EPISODE_STORYLINE] Nvarchar(max) NULL,
 [EPISODE_DESCRIPTION] Nvarchar(max) NULL,
 [EPISODE_RUNTIME] Int NOT NULL,
 [EPISODE_RELEASEDATE] Date NOT NULL,
 [SEASON_ID] Int NOT NULL,
 [TV_SHOW_ID] Int NOT NULL
)
go

-- Add keys for table EPISODES

ALTER TABLE [EPISODES] ADD CONSTRAINT [Key25] PRIMARY KEY ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Table TEECHNICAL SPECS

CREATE TABLE [TEECHNICAL SPECS]
(
 [SPECS_ID] Int IDENTITY(1,1) NOT NULL,
 [COLOR] Nvarchar(max) NULL,
 [ASPECT_RATIO] Nvarchar(max) NULL,
 [SOUND_MIX] Nvarchar(max) NULL,
 [NEGATIVE_FORMAT] Nvarchar(max) NULL,
 [RUNTIME] Int NULL,
 [LABORATORY] Nvarchar(max) NULL,
 [MOVIE_ID] Int NULL,
 [TV_SHOW_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL
)
go

-- Create indexes for table TEECHNICAL SPECS

CREATE INDEX [IX_Relationship17] ON [TEECHNICAL SPECS] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship18] ON [TEECHNICAL SPECS] ([TV_SHOW_ID])
go

CREATE INDEX [IX_Relationship19] ON [TEECHNICAL SPECS] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table TEECHNICAL SPECS

ALTER TABLE [TEECHNICAL SPECS] ADD CONSTRAINT [Key26] PRIMARY KEY ([SPECS_ID])
go

-- Table TRIVIA

CREATE TABLE [TRIVIA]
(
 [TRIVIA_ID] Int IDENTITY(1,1) NOT NULL,
 [TRIVIA_NAME] Nvarchar(max) NULL,
 [TRIVIA_DESCRIPTION] Nvarchar(max) NULL,
 [INTERESTING_COUNT] Int NULL,
 [SHARE_COUNT] Int NULL,
 [MOVIE_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table TRIVIA

CREATE INDEX [IX_Relationship27] ON [TRIVIA] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship37] ON [TRIVIA] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table TRIVIA

ALTER TABLE [TRIVIA] ADD CONSTRAINT [Key41] PRIMARY KEY ([TRIVIA_ID])
go

-- Table CRAZY_CREDITS

CREATE TABLE [CRAZY_CREDITS]
(
 [CRAZY_CREDITS_ID] Int IDENTITY(1,1) NOT NULL,
 [CRAZY_CREDITS_NAME] Nvarchar(max) NULL,
 [CRAZY_CREDITS_DESCRIPTION] Nvarchar(max) NULL,
 [INTERESTING_COUNT] Int NULL,
 [SHARE_COUNT] Int NULL,
 [MOVIE_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table CRAZY_CREDITS

CREATE INDEX [IX_Relationship30] ON [CRAZY_CREDITS] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship35] ON [CRAZY_CREDITS] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table CRAZY_CREDITS

ALTER TABLE [CRAZY_CREDITS] ADD CONSTRAINT [Key39] PRIMARY KEY ([CRAZY_CREDITS_ID])
go

-- Table GOOFS

CREATE TABLE [GOOFS]
(
 [GOOF_ID] Int IDENTITY(1,1) NOT NULL,
 [GOOF_NAME] Nvarchar(max) NULL,
 [GOOF_DESCRIPTION] Nvarchar(max) NULL,
 [INTERESTING_COUNT] Int NULL,
 [SHARE_COUNT] Int NULL,
 [GOOF_TYPE] Nvarchar(max) NULL,
 [MOVIE_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table GOOFS

CREATE INDEX [IX_Relationship25] ON [GOOFS] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship38] ON [GOOFS] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table GOOFS

ALTER TABLE [GOOFS] ADD CONSTRAINT [Key40] PRIMARY KEY ([GOOF_ID])
go

-- Table QUOTES

CREATE TABLE [QUOTES]
(
 [QUOTES_ID] Int IDENTITY(1,1) NOT NULL,
 [QUOTES_NAME] Nvarchar(max) NULL,
 [QUOTES_DESCRIPTION] Nvarchar(max) NULL,
 [INTERESTING_COUNT] Int NULL,
 [SHARE_COUNT] Int NULL,
 [MOVIE_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table QUOTES

CREATE INDEX [IX_Relationship29] ON [QUOTES] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship34] ON [QUOTES] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table QUOTES

ALTER TABLE [QUOTES] ADD CONSTRAINT [Key42] PRIMARY KEY ([QUOTES_ID])
go

-- Table ALTERNATE_VERSIONS

CREATE TABLE [ALTERNATE_VERSIONS]
(
 [ALTERNATE_VERSIONS_ID] Int IDENTITY(1,1) NOT NULL,
 [ALTERNATE_VERSIONS_NAME] Nvarchar(max) NULL,
 [ALTERNATE_VERSIONS_DESCRIPTION] Nvarchar(max) NULL,
 [MOVIE_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table ALTERNATE_VERSIONS

CREATE INDEX [IX_Relationship28] ON [ALTERNATE_VERSIONS] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship36] ON [ALTERNATE_VERSIONS] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table ALTERNATE_VERSIONS

ALTER TABLE [ALTERNATE_VERSIONS] ADD CONSTRAINT [Key27] PRIMARY KEY ([ALTERNATE_VERSIONS_ID])
go

-- Table CONNECTIONS

CREATE TABLE [CONNECTIONS]
(
 [CONNECITONS_ID] Int IDENTITY(1,1) NOT NULL,
 [CONNECTIONS_NAME] Nvarchar(max) NULL,
 [CONNECTION_TYPE] Nvarchar(max) NULL,
 [MOVIE_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL
)
go

-- Create indexes for table CONNECTIONS

CREATE INDEX [IX_Relationship22] ON [CONNECTIONS] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship32] ON [CONNECTIONS] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table CONNECTIONS

ALTER TABLE [CONNECTIONS] ADD CONSTRAINT [Key28] PRIMARY KEY ([CONNECITONS_ID])
go

-- Table REFERENCE_BRIDGE

CREATE TABLE [REFERENCE_BRIDGE]
(
 [REFERENCE_ID] Int NOT NULL,
 [TV_SHOW_ID] Int NULL,
 [CONNECITONS_ID] Int NOT NULL,
 [MOVIE_ID] Int NULL
)
go

-- Create indexes for table REFERENCE_BRIDGE

CREATE INDEX [IX_Relationship21] ON [REFERENCE_BRIDGE] ([TV_SHOW_ID])
go

CREATE INDEX [IX_Relationship24] ON [REFERENCE_BRIDGE] ([MOVIE_ID])
go

-- Add keys for table REFERENCE_BRIDGE

ALTER TABLE [REFERENCE_BRIDGE] ADD CONSTRAINT [Key29] PRIMARY KEY ([REFERENCE_ID],[CONNECITONS_ID])
go

-- Table TVSHOW_GENRE_BRIDGE

CREATE TABLE [TVSHOW_GENRE_BRIDGE]
(
 [TV_SHOW_ID] Int NOT NULL,
 [GENREID] Int NOT NULL
)
go

-- Add keys for table TVSHOW_GENRE_BRIDGE

ALTER TABLE [TVSHOW_GENRE_BRIDGE] ADD CONSTRAINT [Key30] PRIMARY KEY ([TV_SHOW_ID],[GENREID])
go

-- Table MESSAGE_BOARD

CREATE TABLE [MESSAGE_BOARD]
(
 [BOARD_ID] Int IDENTITY(1,1) NOT NULL,
 [BOARD_NAME] Varchar(max) NOT NULL,
 [BOARD_DATA] Varchar(max) NOT NULL,
 [MOVIE_ID] Int NULL,
 [EPISODE_ID] Int NULL,
 [SEASON_ID] Int NULL,
 [TV_SHOW_ID] Int NULL,
 [USERID] Int NOT NULL
)
go

-- Create indexes for table MESSAGE_BOARD

CREATE INDEX [IX_Relationship40] ON [MESSAGE_BOARD] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship41] ON [MESSAGE_BOARD] ([EPISODE_ID],[SEASON_ID],[TV_SHOW_ID])
go

-- Add keys for table MESSAGE_BOARD

ALTER TABLE [MESSAGE_BOARD] ADD CONSTRAINT [Key31] PRIMARY KEY ([BOARD_ID],[USERID])
go

-- Table THREAD

CREATE TABLE [THREAD]
(
 [THREAD_ID] Int IDENTITY(1,1) NOT NULL,
 [THREAD_NAME] Varchar(max) NOT NULL,
 [THREAD_DATA] Varchar(max) NOT NULL,
 [BOARD_ID] Int NOT NULL,
 [REVIEW_USERID] Int NULL,
 [USERID] Int NOT NULL
)
go

-- Create indexes for table THREAD

CREATE INDEX [IX_Relationship43] ON [THREAD] ([REVIEW_USERID])
go

-- Add keys for table THREAD

ALTER TABLE [THREAD] ADD CONSTRAINT [Key32] PRIMARY KEY ([THREAD_ID],[BOARD_ID],[USERID])
go

-- Table USERACCOUNT

CREATE TABLE [USERACCOUNT]
(
 [USERID] Int IDENTITY(1,1) NOT NULL,
 [NAME] Nvarchar(max)  NULL,
 [NATIONALITY] Nvarchar(max) NULL,
 [GENDER] Char(1) NOT NULL,
 [USERNAME] char(40) unique NOT NULL
)
go

-- Add keys for table USERACCOUNT

ALTER TABLE [USERACCOUNT] ADD CONSTRAINT [Key33] PRIMARY KEY ([USERID])
go



-- Table EPISODE_CAST_BRIDGE

CREATE TABLE [EPISODE_CAST_BRIDGE]
(
 [CAST_ID] Int NOT NULL,
 [EPISODE_ID] Int NOT NULL,
 [SEASON_ID] Int NOT NULL,
 [TV_SHOW_ID] Int NOT NULL
)
go

-- 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])
go

-- Table AWARD

CREATE TABLE [AWARD]
(
 [AWARD_ID] Int IDENTITY(1,1) NOT NULL,
 [AWARD_NAME] Nvarchar(max) NOT NULL,
 [AWARD_CATEGORY] Nvarchar(max) NOT NULL,
 [YEAR] Int NOT NULL
)
go

-- Add keys for table AWARD

ALTER TABLE [AWARD] ADD CONSTRAINT [Key35] PRIMARY KEY ([AWARD_ID])
go

-- Table NOMINEE

CREATE TABLE [NOMINEE]
(
 [NOMINEE_ID] Int IDENTITY(1,1) NOT NULL,
 [AWARD_ID] Int NOT NULL,
 [IS_WINNER] Bit NULL,
 [TV_SHOW_ID] Int NULL,
 [MOVIE_ID] Int NULL,
 [CAST_ID] Int NULL
)
go

-- Create indexes for table NOMINEE

CREATE INDEX [IX_Relationship56] ON [NOMINEE] ([TV_SHOW_ID])
go

CREATE INDEX [IX_Relationship57] ON [NOMINEE] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship58] ON [NOMINEE] ([CAST_ID])
go

-- Add keys for table NOMINEE

ALTER TABLE [NOMINEE] ADD CONSTRAINT [Key36] PRIMARY KEY ([NOMINEE_ID],[AWARD_ID])
go

-- Table NEWS

CREATE TABLE [NEWS]
(
 [NEWS_ID] Int IDENTITY(1,1) NOT NULL,
 [PUBLISH_DATE] Date NOT NULL,
 [TITLE] Nvarchar(max) NOT NULL,
 [DETAILS] Nvarchar(max) NULL,
 [AWARD_ID] Int NULL,
 [CAST_ID] Int NULL,
 [MOVIE_ID] Int NULL,
 [TV_SHOW_ID] Int NULL,
 [THEATRE_ID] Int NULL,
 [SHOWTIME_ID] Int NULL
)
go

-- Create indexes for table NEWS

CREATE INDEX [IX_Relationship59] ON [NEWS] ([AWARD_ID])
go

CREATE INDEX [IX_Relationship60] ON [NEWS] ([CAST_ID])
go

CREATE INDEX [IX_Relationship61] ON [NEWS] ([MOVIE_ID])
go

CREATE INDEX [IX_Relationship62] ON [NEWS] ([TV_SHOW_ID])
go

CREATE INDEX [IX_Relationship63] ON [NEWS] ([THEATRE_ID],[SHOWTIME_ID],[MOVIE_ID])
go

-- Add keys for table NEWS

ALTER TABLE [NEWS] ADD CONSTRAINT [Key37] PRIMARY KEY ([NEWS_ID])
go

-- Table POLLS

CREATE TABLE [POLLS]
(
 [POLL_ID] Int IDENTITY(1,1) NOT NULL,
 [POLL_NAME] Nvarchar(max) NOT NULL,
 [POLL_DATE] Date NULL,
 [USERID] Int NULL
)
go

-- Create indexes for table POLLS

CREATE INDEX [IX_Relationship64] ON [POLLS] ([USERID])
go

-- Add keys for table POLLS

ALTER TABLE [POLLS] ADD CONSTRAINT [Key38] PRIMARY KEY ([POLL_ID])
go

-- Table POLL_OPTIONS

CREATE TABLE [POLL_OPTIONS]
(
 [OPTION_ID] Int IDENTITY(1,1) NOT NULL,
 [OPTION_NAME] Nvarchar(max) NOT NULL,
 [VOTE_COUNT] Int NULL,
 [HAS_SELECTED] Bit NULL,
 [POLL_ID] Int NOT NULL
)
go

-- Add keys for table POLL_OPTIONS

ALTER TABLE [POLL_OPTIONS] ADD CONSTRAINT [Key43] PRIMARY KEY ([OPTION_ID],[POLL_ID])
go

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

ALTER TABLE [MOVIE_GENRE_BRIDGE] ADD CONSTRAINT [MOVIES HAVE A GENRE] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MOVIE_GENRE_BRIDGE] ADD CONSTRAINT [GENRE HAS MOVIES] FOREIGN KEY ([GENREID]) REFERENCES [GENRE] ([GENREID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MOVIE_CAST_BRIDGE] ADD CONSTRAINT [MOVIE HAS A CAST] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MOVIE_CAST_BRIDGE] ADD CONSTRAINT [CAST IS IN MOVIES] FOREIGN KEY ([CAST_ID]) REFERENCES [CAST] ([CAST_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [CAST_ROLE_BRIDGE] ADD CONSTRAINT [CAST HAS ROLES] FOREIGN KEY ([CAST_ID]) REFERENCES [CAST] ([CAST_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [CAST_ROLE_BRIDGE] ADD CONSTRAINT [ROLES BY A CAST] FOREIGN KEY ([ROLEID]) REFERENCES [ROLE] ([ROLEID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MEDIA] ADD CONSTRAINT [MOVIES HAVE MEDIA] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MEDIA] ADD CONSTRAINT [CAST_HAS_MEDIA] FOREIGN KEY ([CAST_ID]) REFERENCES [CAST] ([CAST_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [SHOWTIME_BRIDGE] ADD CONSTRAINT [HAS SHOW TIME] FOREIGN KEY ([THEATRE_ID]) REFERENCES [THEATRE] ([THEATRE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [SHOWTIME_BRIDGE] ADD CONSTRAINT [MOVIE HAS SHOWTIME] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [CITY] ADD CONSTRAINT [HAS CITIES] FOREIGN KEY ([STATE_ID], [COUNTRY_ID]) REFERENCES [STATE] ([STATE_ID], [COUNTRY_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [STATE] ADD CONSTRAINT [HAS STATES] FOREIGN KEY ([COUNTRY_ID]) REFERENCES [COUNTRY_ID] ([COUNTRY_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [SHOW_CHANNEL_BRIDGE] ADD CONSTRAINT [CHANNEL HAS SHOWS] FOREIGN KEY ([CHANNEL_ID]) REFERENCES [CHANNEL] ([CHANNEL_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [SHOW_CHANNEL_BRIDGE] ADD CONSTRAINT [SHOWS IN MULTIPLE CHANNELS] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MOVIE_CHANNEL_BRIDGE] ADD CONSTRAINT [MOVIE IN MULTIPLE CHANNELS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MOVIE_CHANNEL_BRIDGE] ADD CONSTRAINT [CHANNEL SHOWS MULTIPLE MOVIES] FOREIGN KEY ([CHANNEL_ID]) REFERENCES [CHANNEL] ([CHANNEL_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [REVIEW] ADD CONSTRAINT [HAS REVIEW] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [TRAILERS] ADD CONSTRAINT [MOVIE HAS TRAILERS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MEDIA] ADD CONSTRAINT [TRAILER HAS MEDIA] FOREIGN KEY ([TRAILERID]) REFERENCES [TRAILERS] ([TRAILERID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [ADDRESS] ADD CONSTRAINT [MOVIE HAS LOCATIONS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [LANGUAGE] ADD CONSTRAINT [MOVIE HAS LANGUAGES] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [SEASON] ADD CONSTRAINT [HAS SEASONS] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [EPISODES] ADD CONSTRAINT [HAS EPISODES] FOREIGN KEY ([SEASON_ID], [TV_SHOW_ID]) REFERENCES [SEASON] ([SEASON_ID], [TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [TVSHOW_GENRE_BRIDGE] ADD CONSTRAINT [HAS MANY GENRE] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [TVSHOW_GENRE_BRIDGE] ADD CONSTRAINT [HAS MANY TV SHOWS] FOREIGN KEY ([GENREID]) REFERENCES [GENRE] ([GENREID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [TEECHNICAL SPECS] ADD CONSTRAINT [MOVIE HAS TECHNICAL SPECS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [TEECHNICAL SPECS] ADD CONSTRAINT [TVSHOW HAS TECHNICAL SPECS] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [REFERENCE_BRIDGE] ADD CONSTRAINT [TV SHOW REFERENCES] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [CONNECTIONS] ADD CONSTRAINT [MOVIE HAS CONNECTIONS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [REFERENCE_BRIDGE] ADD CONSTRAINT [CONNECTION REFERENCE] FOREIGN KEY ([CONNECITONS_ID]) REFERENCES [CONNECTIONS] ([CONNECITONS_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [REFERENCE_BRIDGE] ADD CONSTRAINT [MOVIE REFERENCE] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [GOOFS] ADD CONSTRAINT [MOVIE HAS GOOFS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [TRIVIA] ADD CONSTRAINT [MOVIE HAS TRIVIA] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [ALTERNATE_VERSIONS] ADD CONSTRAINT [MOVIE HAS ALTERNATE VERSIONS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [QUOTES] ADD CONSTRAINT [MOVIE HAS QUOTES] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [CRAZY_CREDITS] ADD CONSTRAINT [MOVIE HAS CRAZY CREDITS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [THREAD] ADD CONSTRAINT [HAS REPLY THREADS] FOREIGN KEY ([BOARD_ID], [USERID]) REFERENCES [MESSAGE_BOARD] ([BOARD_ID], [USERID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MESSAGE_BOARD] ADD CONSTRAINT [MOVIE HAS MESSAGE THREADS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [THREAD] ADD CONSTRAINT [REPLIES TO THREAD] FOREIGN KEY ([REVIEW_USERID]) REFERENCES [USERACCOUNT] ([USERID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MESSAGE_BOARD] ADD CONSTRAINT [CREATES THREAD] FOREIGN KEY ([USERID]) REFERENCES [USERACCOUNT] ([USERID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [REVIEW] ADD CONSTRAINT [GIVES REVIEW] FOREIGN KEY ([USERID]) REFERENCES [USERACCOUNT] ([USERID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [TRAILERS] ADD CONSTRAINT [TV SHOWS HAVE TRAILERS] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [MEDIA] ADD CONSTRAINT [TV SHOW HAS MEDIA] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [EPISODE_CAST_BRIDGE] ADD CONSTRAINT [CAST BELONG TO EPISODES] FOREIGN KEY ([CAST_ID]) REFERENCES [CAST] ([CAST_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

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]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NOMINEE] ADD CONSTRAINT [HAS MANY NOMINEES] FOREIGN KEY ([AWARD_ID]) REFERENCES [AWARD] ([AWARD_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NOMINEE] ADD CONSTRAINT [TV SHOW NOMINEE] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NOMINEE] ADD CONSTRAINT [MOVIE NOMINEE] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NOMINEE] ADD CONSTRAINT [CAST_NOMINEE] FOREIGN KEY ([CAST_ID]) REFERENCES [CAST] ([CAST_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NEWS] ADD CONSTRAINT [AWARD NEWS] FOREIGN KEY ([AWARD_ID]) REFERENCES [AWARD] ([AWARD_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NEWS] ADD CONSTRAINT [CELEB NEWS] FOREIGN KEY ([CAST_ID]) REFERENCES [CAST] ([CAST_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NEWS] ADD CONSTRAINT [MOVIE NEWS] FOREIGN KEY ([MOVIE_ID]) REFERENCES [MOVIE] ([MOVIE_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NEWS] ADD CONSTRAINT [TV SHOW NEWS] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [NEWS] ADD CONSTRAINT [SHOWTIME NEWS] FOREIGN KEY ([THEATRE_ID], [MOVIE_ID], [SHOWTIME_ID]) REFERENCES [SHOWTIME_BRIDGE] ([THEATRE_ID], [MOVIE_ID], [SHOWTIME_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [POLLS] ADD CONSTRAINT [VOTE FOR POLLS] FOREIGN KEY ([USERID]) REFERENCES [USERACCOUNT] ([USERID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [TV_SHOW] ADD CONSTRAINT [Relationship1] FOREIGN KEY ([FK_TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [LANGUAGE] ADD CONSTRAINT [RV SHOW HAS LANGUAGES] FOREIGN KEY ([TV_SHOW_ID]) REFERENCES [TV_SHOW] ([TV_SHOW_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [POLL_OPTIONS] ADD CONSTRAINT [HAS POLL OPTIONS] FOREIGN KEY ([POLL_ID]) REFERENCES [POLLS] ([POLL_ID]) ON UPDATE NO ACTION ON DELETE NO ACTION
go