· 8 years ago · Mar 21, 2018, 10:28 AM
1/*SCRIPT DATABASE GENERATOR*/
2
3USE FATTO;
4
5/*CREATE TABLE GENRES*/
6
7DROP TABLE IF EXISTS `genres`;
8CREATE TABLE `genres` (
9 `id_gr` int NOT NULL AUTO_INCREMENT COMMENT'idx',
10 `name` varchar(50) NOT NULL COMMENT'the genre type ie. terror, comedy, etc)',
11 `description` varchar(100) NOT NULL COMMENT'description of genre',
12 KEY `idx_genres_id_gr` (`id_gr`) COMMENT'index',
13 PRIMARY KEY (id_gr) COMMENT'unique key'
14)
15ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the genre type:';
16;
17
18/*CREATE TABLE GENRES*/
19
20DROP TABLE IF EXISTS `genres_audit`;
21CREATE TABLE `genres_audit` (
22 `id_gr` int NOT NULL AUTO_INCREMENT COMMENT'idx',
23 `name_old` varchar(50) NOT NULL COMMENT'the genre type ie. terror, comedy, etc)',
24 `name_new` varchar(50) NOT NULL COMMENT'the genre type ie. terror, comedy, etc)',
25 `description_old` varchar(100) NOT NULL COMMENT'description of genre',
26 `description_new` varchar(100) NOT NULL COMMENT'description of genre',
27 KEY `idx_genres_id_gr` (`id_gr`) COMMENT'index',
28 PRIMARY KEY (id_gr) COMMENT'unique key'
29)
30ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the genre type:';
31;
32
33insert into `genres`(`name`, `description`) values('Ação','');
34insert into `genres`(`name`, `description`) values('Animação','');
35insert into `genres`(`name`, `description`) values('Aventura','');
36insert into `genres`(`name`, `description`) values('Cinema de arte','');
37insert into `genres`(`name`, `description`) values('Chanchada','');
38insert into `genres`(`name`, `description`) values('Cinema catástrofe','');
39insert into `genres`(`name`, `description`) values('Comédia','');
40insert into `genres`(`name`, `description`) values('Comédia romântica','');
41insert into `genres`(`name`, `description`) values('Comédia dramática','');
42insert into `genres`(`name`, `description`) values('Comédia de ação','');
43insert into `genres`(`name`, `description`) values('Cult','');
44insert into `genres`(`name`, `description`) values('Dança','');
45insert into `genres`(`name`, `description`) values('Documentários','');
46insert into `genres`(`name`, `description`) values('Drama','');
47insert into `genres`(`name`, `description`) values('Espionagem','');
48insert into `genres`(`name`, `description`) values('Erótico','');
49insert into `genres`(`name`, `description`) values('Fantasia','');
50insert into `genres`(`name`, `description`) values('Faroeste','');
51insert into `genres`(`name`, `description`) values('Ficção cientÃfica','');
52insert into `genres`(`name`, `description`) values('Series','');
53insert into `genres`(`name`, `description`) values('Guerra','');
54insert into `genres`(`name`, `description`) values('Machinima','');
55insert into `genres`(`name`, `description`) values('Masala','');
56insert into `genres`(`name`, `description`) values('Musical','');
57insert into `genres`(`name`, `description`) values('Filme noir','');
58insert into `genres`(`name`, `description`) values('Policial','');
59insert into `genres`(`name`, `description`) values('Pornochanchada','');
60insert into `genres`(`name`, `description`) values('Pornográfico','');
61insert into `genres`(`name`, `description`) values('Robologia','');
62insert into `genres`(`name`, `description`) values('Romance','');
63insert into `genres`(`name`, `description`) values('Seriado','');
64insert into `genres`(`name`, `description`) values('Suspense','');
65insert into `genres`(`name`, `description`) values('Terror','');
66insert into `genres`(`name`, `description`) values('Trash','');
67
68==============================================================================================
69
70/*CREATE TABLE title basics*/
71
72DROP TABLE IF EXISTS `title.basics`;
73CREATE TABLE `title.basics` (
74 `id_tb` int NOT NULL AUTO_INCREMENT COMMENT'idx',
75 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric unique identifier of the title',
76 `titleType` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
77 `primaryTitle` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
78 `originalTitle` varchar(100) NOT NULL COMMENT'original title, in the original language',
79 `isAdult` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
80 `startYear` smallint(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
81 `endYear` smallint(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
82 `runtimeMinutes` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
83 `genres` SMALLINT COMMENT 'includes up to three genres associated with the title',
84 KEY `idx_title.basics_id_tb` (`id_tb`) COMMENT'index',
85 KEY `idx_title.basics_tconst` (`tconst`) COMMENT'index',
86 PRIMARY KEY (tconst) COMMENT'unique key'
87)
88ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the following information for titles:';
89;
90
91ALTER TABLE `title.basics` ADD CONSTRAINT `fk_genre` FOREIGN KEY ( `genres` ) REFERENCES `genres` (`id_gr`);
92-------------------------------------------------------------------------------------------------
93
94/*CREATE TABLE title basics audit*/
95
96DROP TABLE IF EXISTS `title.basics.Audit`;
97CREATE TABLE `title.basics.Audit` (
98 `id_tba` int NOT NULL AUTO_INCREMENT COMMENT'idx',
99 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric unique identifier of the title',
100 `titleType_old` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
101 `titleType_new` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
102 `primaryTitle_old` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
103 `primaryTitle_new` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
104 `originalTitle_old` varchar(100) NOT NULL COMMENT'original title, in the original language',
105 `originalTitle_new` varchar(100) NOT NULL COMMENT'original title, in the original language',
106 `isAdult_old` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
107 `isAdult_new` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
108 `startYear_old` int(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
109 `startYear_new` int(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
110 `endYear_old` int(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
111 `endYear_new` int(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
112 `runtimeMinutes_old` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
113 `runtimeMinutes_new` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
114 `genres_old` smallint DEFAULT NULL COMMENT 'includes up to three genres associated with the title',
115 `genres_new` smallint DEFAULT NULL COMMENT 'includes up to three genres associated with the title',
116 `logged_user` nvarchar(50) COMMENT'user action',
117 `oper_type` varchar(10) COMMENT'Operation type',
118 `tstamp` datetime,
119 KEY `idx_title.basics.Audit_id_tba` (`id_tba`) COMMENT'index',
120 KEY `idx_title.basics.Audit_tconst` (`tconst`) COMMENT'index',
121 PRIMARY KEY (tconst) COMMENT'unique key'
122)
123ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the following information for titles:';
124;
125ALTER TABLE `title.basics.Audit` ADD CONSTRAINT `fk_genre_old` FOREIGN KEY ( `genres_old` ) REFERENCES `genres` (`id_gr`);
126ALTER TABLE `title.basics.Audit` ADD CONSTRAINT `fk_genre_new` FOREIGN KEY ( `genres_new` ) REFERENCES `genres` (`id_gr`);
127
128-------------------------------------------------------------------------------------------------
129
130/*CREATE TABLE title episode*/
131
132DROP TABLE IF EXISTS `title.episode`;
133CREATE TABLE `title.episode`(
134 `id_te` int NOT NULL AUTO_INCREMENT COMMENT'idx',
135 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier of episode',
136 `parentTconst` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
137 `seasonNumber` SMALLINT COMMENT'season number the episode belongs to',
138 `episodeNumber` SMALLINT COMMENT'episode number of the tconst in the TV series',
139 KEY `idx_title.episode_id_te` (`id_te`) COMMENT'index',
140 KEY `idx_title.episode_tconst` (`tconst`) COMMENT'index',
141 PRIMARY KEY (tconst) COMMENT'unique key'
142 )
143ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the tv episode information. Fields include'
144;
145-------------------------------------------------------------------------------------------------
146
147/*CREATE TABLE title episode audit*/
148
149DROP TABLE IF EXISTS `title.episode.Audit`;
150CREATE TABLE `title.episode.Audit`(
151 `id_tea` int NOT NULL AUTO_INCREMENT COMMENT'idx',
152 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier of episode',
153 `parentTconst_old` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
154 `parentTconst_new` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
155 `seasonNumber_old` SMALLINT COMMENT'season number the episode belongs to',
156 `seasonNumber_new` SMALLINT COMMENT'season number the episode belongs to',
157 `episodeNumber_old` SMALLINT COMMENT'episode number of the tconst in the TV series',
158 `episodeNumber_new` SMALLINT COMMENT'episode number of the tconst in the TV series',
159 `logged_user` nvarchar(50) COMMENT'user action',
160 `oper_type` varchar(10) COMMENT'Operation type',
161 `tstamp` datetime,
162 KEY `idx_title.episode.Audit_id_tea` (`id_tea`) COMMENT'index',
163 KEY `idx_title.episode.Audit_tconst` (`tconst`) COMMENT'index',
164 PRIMARY KEY (tconst) COMMENT'unique key'
165 )
166ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the tv episode information. Fields include'
167;
168-------------------------------------------------------------------------------------------------
169
170/*CREATE TABLE resources basics*/
171
172DROP TABLE IF EXISTS `resource.basics`;
173CREATE TABLE `resource.basics`(
174 `id_rb` int NOT NULL AUTO_INCREMENT COMMENT'idx',
175 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
176 `url` varchar(100) COMMENT'Media URL',
177 `poster` varchar(100) COMMENT'Poster URL',
178 /*`views` bigint COMMENT'vews count',*/
179 KEY `idx_resource.basics_id_rb` (`id_rb`) COMMENT'index',
180 KEY `idx_tresource.basics_tconst` (`tconst`) COMMENT'index',
181 PRIMARY KEY (tconst) COMMENT'unique key'
182 )
183ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contem os recursos para assistir'
184;
185-------------------------------------------------------------------------------------------------
186
187/*CREATE TABLE resources basics audit*/
188
189DROP TABLE IF EXISTS `resource.basics.Audit`;
190CREATE TABLE `resource.basics.Audit`(
191 `id_rba` int NOT NULL AUTO_INCREMENT COMMENT'idx',
192 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
193 `url_old` varchar(100) COMMENT'Media URL',
194 `url_new` varchar(100) COMMENT'Media URL',
195 `poster_old` varchar(100) COMMENT'Poster URL',
196 `poster_new` varchar(100) COMMENT'Poster URL',
197 /*`views` bigint COMMENT'vews count',*/
198 `logged_user` nvarchar(50) COMMENT'user action',
199 `oper_type` varchar(10) COMMENT'Operation type',
200 `tstamp` datetime,
201 KEY `idx_resource.basics.Audit_id_rba` (`id_rba`) COMMENT'index',
202 KEY `idx_resource.basics.Audit_tconst` (`tconst`) COMMENT'index',
203 PRIMARY KEY (tconst) COMMENT'unique key'
204 )
205ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contem os recursos para assistir'
206;
207
208==============================================================================================
209
210/* create trigger audit ins_resource.basics*/
211
212DELIMITER //
213drop trigger `ins_resource.basics`;
214create trigger `ins_resource.basics`
215 after Insert on `resource.basics`
216 FOR EACH ROW
217 BEGIN
218 INSERT INTO `resource.basics.audit`
219 (
220 `tconst`
221 ,`titleType_new`
222 ,`primaryTitle_new`
223 ,`originalTitle_new`
224 ,`isAdult_new`
225 ,`startYear_new`
226 ,`endYear_new`
227 ,`runtimeMinutes_new`
228 ,`genres_new`
229 ,`logged_user`
230 ,`oper_type`
231 ,`tstamp`
232 )
233 Values
234 (
235 `new.tconst`
236 ,`new.titleType_new`
237 ,`new.primaryTitle`
238 ,`new.originalTitle`
239 ,`new.isAdult`
240 ,`new.startYear`
241 ,`new.endYear`
242 ,`new.runtimeMinutes`
243 ,`new.genres`
244 , current_user()
245 ,'Insert'
246 ,getDate()
247 );
248 END//
249
250-------------------------------------------------------------------------------------------------
251
252DELIMITER //
253
254drop trigger `upd_resource.basics`;
255create trigger `upd_resource.basics`
256 after update on `resource.basics`
257 FOR EACH ROW
258 BEGIN
259 INSERT INTO `resource.basics.audit`
260 (
261 `tconst`
262 ,`titleType_old`
263 ,`titleType_new`
264 ,`primaryTitle_old`
265 ,`primaryTitle_new`
266 ,`originalTitle_old`
267 ,`originalTitle_new`
268 ,`isAdult_old`
269 ,`isAdult_new`
270 ,`startYear_old`
271 ,`startYear_new`
272 ,`endYear_old`
273 ,`endYear_new`
274 ,`runtimeMinutes_old`
275 ,`runtimeMinutes_new`
276 ,`genres_old`
277 ,`genres_new`
278 ,`logged_user`
279 ,`oper_type`
280 ,`tstamp`
281 )
282 Values
283 (
284 `new.tconst`
285 ,`old.titleType_new`
286 ,`new.titleType_new`
287 ,`old.primaryTitle`
288 ,`new.primaryTitle`
289 ,`old.originalTitle`
290 ,`new.originalTitle`
291 ,`old.isAdult`
292 ,`new.isAdult`
293 ,`old.startYear`
294 ,`new.startYear`
295 ,`old.endYear`
296 ,`new.endYear`
297 ,`old.runtimeMinutes`
298 ,`new.runtimeMinutes`
299 ,`old.genres`
300 ,`new.genres`
301 , current_user()
302 ,'update'
303 ,getDate()
304 );
305 END//
306
307-------------------------------------------------------------------------------------------------
308
309DELIMITER //
310
311drop trigger `del_resource.basics`;
312create trigger `del_resource.basics`
313 after delete on `resource.basics`
314 FOR EACH ROW
315 BEGIN
316 INSERT INTO `resource.basics.audit`
317 (
318 `tconst`
319 ,`titleType_old`
320 ,`primaryTitle_old`
321 ,`originalTitle_old`
322 ,`isAdult_old`
323 ,`startYear_old`
324 ,`endYear_old`
325 ,`runtimeMinutes_old`
326 ,`genres_old`
327 ,`logged_user`
328 ,`oper_type`
329 ,`tstamp`
330 )
331 Values
332 (
333 `new.tconst`
334 ,`old.titleType_new`
335 ,`old.primaryTitle`
336 ,`old.originalTitle`
337 ,`old.isAdult`
338 ,`old.startYear`
339 ,`old.endYear`
340 ,`old.runtimeMinutes`
341 ,`old.genres`
342 , current_user()
343 ,'delete'
344 ,getDate()
345 );
346 END//
347
348==============================================================================================
349
350
351/* model #2*/
352
353DROP TABLE IF EXISTS `title.ratings`;
354CREATE TABLE `title.ratings`(
355 `id_tr` int NOT NULL AUTO_INCREMENT COMMENT'idx',
356 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
357 `averageRating` decimal(5,2) NOT NULL COMMENT'weighted average of all the individual user ratings',
358 `numVotes` int(10) COMMENT'number of votes the title has received',
359 KEY `idx_title.ratings_id_tr` (`id_tr`) COMMENT'index',
360 KEY `idx_title.ratings_tconst` (`tconst`) COMMENT'index',
361 PRIMARY KEY (tconst) COMMENT'unique key'
362)
363ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT' Contains the IMDb rating and votes information for titles'
364;
365
366DROP TABLE IF EXISTS `title.crew`;
367CREATE TABLE `title.crew`(
368 `id_tc` int NOT NULL AUTO_INCREMENT COMMENT'idx',
369 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
370 `directors` varchar(500) NOT NULL COMMENT'(array of nconsts) - director(s) of the given title',
371 `writers` varchar(500) NOT NULL COMMENT'(array of nconsts) – writer(s) of the given title',
372 KEY `idx_title.crew_iid_tc` (`id_tc`) COMMENT'index',
373 KEY `idx_title.crew_tconst` (`tconst`) COMMENT'index',
374 PRIMARY KEY (tconst) COMMENT'unique key'
375)
376ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the director and writer information for all the titles in IMDb'
377;
378
379DROP TABLE IF EXISTS `title.principals`;
380CREATE TABLE `title.principals`(
381 `id_tp` int NOT NULL AUTO_INCREMENT COMMENT'idx',
382 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
383 `principalCast` varchar(200) NOT NULL COMMENT'(array of nconsts) – title’s top-billed cast/crew',
384 KEY `idx_title.principals_id_tp` (`id_tp`) COMMENT'index',
385 KEY `idx_title.principals_tconst` (`tconst`) COMMENT'index',
386 PRIMARY KEY (tconst) COMMENT'unique key'
387)
388ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT' – Contains the principal cast/crew for titles'
389;
390
391DROP TABLE IF EXISTS `name.basics`;
392CREATE TABLE `name.basics`(
393 `id_nb` int NOT NULL AUTO_INCREMENT COMMENT'idx',
394 `nconst` varchar(10) NOT NULL COMMENT'alphanumeric unique identifier of the name/person',
395 `primaryName` varchar(100) NOT NULL COMMENT'(string)– name by which the person is most often credited',
396 `birthYear` smallint(4) NULL COMMENT'in YYYY format',
397 `deathYear` smallint(4) NULL COMMENT'in YYYY format if applicable, else ‘\N’',
398 `primaryProfession` varchar(250) NOT NULL COMMENT'(array of strings)– the top-3 professions of the person',
399 `knownForTitles` varchar(1000) NOT NULL COMMENT'(array of tconsts) – titles the person is known for',
400 KEY `idx_name.basics_id_nb` (`id_nb`) COMMENT'index',
401 KEY `idx_name.basics_tconst` (`nconst`) COMMENT'index',
402 PRIMARY KEY (nconst) COMMENT'unique key'
403)
404ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the following information for names'
405;