f0rjtk5s

1CREATE DATABASE `Movies`;
2
3USE `Movies`;
4
5CREATE TABLE IF NOT EXISTS `directors`(
6	`id` INT AUTO_INCREMENT PRIMARY KEY,
7	`director_name` VARCHAR(50) NOT NULL,
8	`notes` TEXT
9);
10
11INSERT INTO `directors` (`director_name`)
12	VALUES ('Kiro'),
13		('Miro'),
14		('Tosho'),
15		('Gosho'),
16		('Ani');
17
18CREATE TABLE IF NOT EXISTS `genres`(
19	`id` INT AUTO_INCREMENT PRIMARY KEY,
20	`genre_name` VARCHAR(50) NOT NULL,
21	`notes` TEXT
22);
23
24INSERT INTO `genres` (`genre_name`)
25	VALUES ('KiroG'),
26		('MiroG'),
27		('ToshoG'),
28		('GoshoG'),
29		('AniG');
30
31CREATE TABLE IF NOT EXISTS `categories`(
32	`id` INT AUTO_INCREMENT PRIMARY KEY,
33	`category_name` VARCHAR(50) NOT NULL,
34	`notes` TEXT
35);
36
37INSERT INTO `categories` (`category_name`)
38	VALUES ('KiroC'),
39		('MiroC'),
40		('ToshoC'),
41		('GoshoC'),
42		('AniC');
43
44CREATE TABLE IF NOT EXISTS `movies`(
45	`id` INT AUTO_INCREMENT PRIMARY KEY,
46	`title` VARCHAR(50) NOT NULL UNIQUE,
47	`director_id` INT,
48	`copyright_year` INT(4),
49	`length` INT(3),
50	`genre_id` INT,
51	`category_id` INT,
52	`rating` DECIMAL,
53	`notes` TEXT
54);
55
56ALTER TABLE `movies`
57	ADD CONSTRAINT `FK_movies_directors` 
58	FOREIGN KEY (`director_id`) REFERENCES `directors` (`id`);
59	
60ALTER TABLE `movies`
61	ADD CONSTRAINT `FK_movies_genres` 
62	FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`);	
63	
64ALTER TABLE `movies`
65	ADD CONSTRAINT `FK_movies_categories` 
66	FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);		
67	
68INSERT INTO `movies` (`title`,`director_id`,`genre_id`,`category_id`)
69	VALUES ('KiroM',1,1,1),
70		('MiroM',2,2,2),
71		('ToshoM',3,3,3),
72		('GoshoM',4,4,4),
73		('AniM',5,5,5);