LLmNaL7z

1USE [IntermediateDB];
2GO
3
4DROP TABLE IF EXISTS MovieCountry;
5GO
6
7CREATE TABLE [dbo].[MovieCountry](
8	[movieID] [nvarchar](10) NOT NULL,
9	[countryID] [int] NOT NULL,
10 CONSTRAINT [MovieCountryPK] PRIMARY KEY CLUSTERED 
11(
12	[movieID], [countryID] ASC
13) ON [PRIMARY]
14) ON [PRIMARY];
15GO
16
17ALTER TABLE [dbo].[MovieCountry]  WITH CHECK ADD  CONSTRAINT [MovieCountryCountryFK] FOREIGN KEY([countryID])
18REFERENCES [dbo].[Country] ([countryID])
19GO
20
21ALTER TABLE [dbo].[MovieCountry] CHECK CONSTRAINT [MovieCountryCountryFK]
22GO
23
24ALTER TABLE [dbo].[MovieCountry]  WITH CHECK ADD  CONSTRAINT [MovieCountryMovieFK] FOREIGN KEY([movieID])
25REFERENCES [dbo].[Movie] ([movieID])
26GO
27
28ALTER TABLE [dbo].[MovieCountry] CHECK CONSTRAINT [MovieCountryMovieFK]
29GO
30
31INSERT INTO MovieCountry(movieID, countryID)
32SELECT movieID, countryID
33FROM Filmweb.dbo.MovieCountriesData
34
35DROP TABLE IF EXISTS MovieGenre;
36GO
37
38CREATE TABLE [dbo].[MovieGenre](
39	[movieID] [nvarchar](10) NOT NULL,
40	[genreID] [int] NOT NULL,
41 CONSTRAINT [MovieGenrePK] PRIMARY KEY CLUSTERED 
42(
43	[movieID], [genreID] ASC
44) ON [PRIMARY]
45) ON [PRIMARY]
46GO
47
48ALTER TABLE [dbo].[MovieGenre]  WITH CHECK ADD  CONSTRAINT [MovieGenreGenreFK] FOREIGN KEY([genreID])
49REFERENCES [dbo].[Genre] ([genreID])
50GO
51
52ALTER TABLE [dbo].[MovieGenre] CHECK CONSTRAINT [MovieGenreGenreFK]
53GO
54
55ALTER TABLE [dbo].[MovieGenre]  WITH CHECK ADD  CONSTRAINT [MovieGenreMovieFK] FOREIGN KEY([movieID])
56REFERENCES [dbo].[Movie] ([movieID])
57GO
58
59ALTER TABLE [dbo].[MovieGenre] CHECK CONSTRAINT [MovieGenreMovieFK]
60GO
61
62INSERT INTO MovieGenre(movieID, genreID)
63SELECT tconst, (SELECT genreID FROM Genre WHERE Genre.genreName = value) AS genreID
64FROM IMDB.dbo.TitleBasics
65CROSS APPLY STRING_SPLIT(genres, ',') 
66WHERE tconst IN(SELECT movieID FROM Movie) AND LEN(value) >= 9'
67ORDER BY tconst
68GO
69
70DROP TABLE IF EXISTS MovieDirector;
71GO
72
73CREATE TABLE [dbo].[MovieDirector](
74	[movieID] [nvarchar](10) NOT NULL,
75	[personID] [nvarchar](10) NOT NULL,
76 CONSTRAINT [MovieDirectorPK] PRIMARY KEY CLUSTERED 
77(
78	[movieID], [personID] ASC
79) ON [PRIMARY]
80) ON [PRIMARY]
81GO
82
83ALTER TABLE [dbo].[MovieDirector]  WITH CHECK ADD  CONSTRAINT [MovieDirectorPersonFK] FOREIGN KEY([personID])
84REFERENCES [dbo].[Person] ([personID])
85GO
86
87ALTER TABLE [dbo].[MovieDirector] CHECK CONSTRAINT [MovieDirectorPersonFK]
88GO
89
90ALTER TABLE [dbo].[MovieDirector]  WITH CHECK ADD  CONSTRAINT [MovieDirectorMovieFK] FOREIGN KEY([movieID])
91REFERENCES [dbo].[Movie] ([movieID])
92GO
93
94ALTER TABLE [dbo].[MovieDirector] CHECK CONSTRAINT [MovieDirectorMovieFK]
95GO
96
97INSERT INTO MovieDirector(movieID, personID)
98SELECT tconst, value
99FROM IMDB.dbo.TitleCrew
100CROSS APPLY STRING_SPLIT(directors, ',') 
101WHERE tconst IN(SELECT movieID FROM Movie) AND LEN(value) >= 9;
102GO
103
104DROP TABLE IF EXISTS MovieWriter;
105GO
106
107CREATE TABLE [dbo].[MovieWriter](
108	[movieID] [nvarchar](10) NOT NULL,
109	[personID] [nvarchar](10) NOT NULL,
110 CONSTRAINT [MovieWriterPK] PRIMARY KEY CLUSTERED 
111(
112	[movieID], [personID] ASC
113) ON [PRIMARY]
114) ON [PRIMARY]
115GO
116
117ALTER TABLE [dbo].[MovieWriter]  WITH CHECK ADD  CONSTRAINT [MovieWriterPersonFK] FOREIGN KEY([personID])
118REFERENCES [dbo].[Person] ([personID])
119GO
120
121ALTER TABLE [dbo].[MovieWriter] CHECK CONSTRAINT [MovieWriterPersonFK]
122GO
123
124ALTER TABLE [dbo].[MovieWriter]  WITH CHECK ADD  CONSTRAINT [MovieWriterMovieFK] FOREIGN KEY([movieID])
125REFERENCES [dbo].[Movie] ([movieID])
126GO
127
128ALTER TABLE [dbo].[MovieWriter] CHECK CONSTRAINT [MovieWriterMovieFK]
129GO
130
131INSERT INTO MovieWriter(movieID, personID)
132SELECT tconst, value
133FROM IMDB.dbo.TitleCrew
134CROSS APPLY STRING_SPLIT(writers, ',') 
135WHERE tconst IN(SELECT movieID FROM Movie) AND LEN(value) >= 9;
136GO