minions-db

CREATE DATABASE `MinionsDB`;
USE `MinionsDB`;

CREATE TABLE `towns` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    town_name VARCHAR(50),
    country_location VARCHAR(50)
);

CREATE TABLE `minions` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    town_id INT,
    CONSTRAINT fk_minions_towns FOREIGN KEY (town_id)
        REFERENCES `towns` (id)
);

CREATE TABLE `villains` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    evilness_factor VARCHAR(20) CHECK (evilness_factor IN ('good' , 'bad', 'evil', 'super evil'))
);

CREATE TABLE `minions_villains` (
    minion_id INT,
    villain_id INT,
    CONSTRAINT pk_mv PRIMARY KEY (minion_id , villain_id),
    CONSTRAINT fk_mv_minions FOREIGN KEY (minion_id)
        REFERENCES `minions` (id),
    CONSTRAINT fk_mv_villains FOREIGN KEY (villain_id)
        REFERENCES `villains` (id)
);

-- --------------------------------------------------------------------
-- --------------------------------------------------------------------

INSERT INTO `towns`(town_name, country_location)
VALUES
    ('Plovdiv', 'Bulgaria'),
    ('Beograd', 'Serbia'),
    ('Paris', 'France'),
    ('Moskva', 'Rusia'),
    ('Sofia', 'Bulgaria');

INSERT INTO `minions`(name, age, town_id)
VALUES
    ('Ragner', 40, 3),
    ('Tombot', 40, 2),
    ('Killer', 40, 4),
    ('Fairman', 40, 1),
    ('Zombi', 40, 5),
    ('Maina', 25, 1);

INSERT INTO `villains`(name, evilness_factor)
VALUES
    ('Helpercho', 'good'),
    ('Badman', 'bad'),
    ('Evilman', 'evil'),
    ('Sleeper', 'super evil'),
    ('Hopercho', 'good'),
    ('Evill', 'super evil');

INSERT INTO `minions_villains`(minion_id, villain_id)
VALUES
    (3, 1),
    (2, 3),
    (1, 4),
    (5, 1),
    (2, 1),
    (5, 4),
    (4, 2),
    (2, 4),
    (4, 5);