Movies

CREATE DATABASE `Movies`;

USE `Movies`;

CREATE TABLE IF NOT EXISTS `directors`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `director_name` VARCHAR(50) NOT NULL,
    `notes` TEXT
);

INSERT INTO `directors` (`director_name`)
    VALUES ('Kiro'),
        ('Miro'),
        ('Tosho'),
        ('Gosho'),
        ('Ani');

CREATE TABLE IF NOT EXISTS `genres`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `genre_name` VARCHAR(50) NOT NULL,
    `notes` TEXT
);

INSERT INTO `genres` (`genre_name`)
    VALUES ('KiroG'),
        ('MiroG'),
        ('ToshoG'),
        ('GoshoG'),
        ('AniG');

CREATE TABLE IF NOT EXISTS `categories`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_name` VARCHAR(50) NOT NULL,
    `notes` TEXT
);

INSERT INTO `categories` (`category_name`)
    VALUES ('KiroC'),
        ('MiroC'),
        ('ToshoC'),
        ('GoshoC'),
        ('AniC');

CREATE TABLE IF NOT EXISTS `movies`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(50) NOT NULL UNIQUE,
    `director_id` INT,
    `copyright_year` INT(4),
    `length` INT(3),
    `genre_id` INT,
    `category_id` INT,
    `rating` DECIMAL,
    `notes` TEXT
);

ALTER TABLE `movies`
    ADD CONSTRAINT `FK_movies_directors`
    FOREIGN KEY (`director_id`) REFERENCES `directors` (`id`);

ALTER TABLE `movies`
    ADD CONSTRAINT `FK_movies_genres`
    FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`);

ALTER TABLE `movies`
    ADD CONSTRAINT `FK_movies_categories`
    FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);

INSERT INTO `movies` (`title`,`director_id`,`genre_id`,`category_id`)
    VALUES ('KiroM',1,1,1),
        ('MiroM',2,2,2),
        ('ToshoM',3,3,3),
        ('GoshoM',4,4,4),
        ('AniM',5,5,5);