DROP DATABASE IF EXISTS MovieCollection;
CREATE DATABASE MovieCollection;
USE MovieCollection

CREATE TABLE movie (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    PRIMARY KEY ( id ),
    title           VARCHAR(50) NOT NULL,
    acquired        DATETIME,
    borrow_id       INT
);

CREATE TABLE person (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    PRIMARY KEY ( id ),
    first           VARCHAR(20) NOT NULL,
    middle          VARCHAR(20),
    last            VARCHAR(20),
    suffix          VARCHAR(20)
);

CREATE TABLE actor (
    movie_id        INT NOT NULL,
    person_id       INT NOT NULL
);

CREATE TABLE director (
    movie_id        INT NOT NULL,
    person_id       INT NOT NULL
);

CREATE TABLE genre (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    PRIMARY KEY ( id ),
    name            VARCHAR(20) NOT NULL,
    description     VARCHAR(200)
);

CREATE TABLE moviegenre (
    movie_id        INT NOT NULL,
    genre_id        INT NOT NULL
);

CREATE TABLE borrow (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    PRIMARY KEY ( id ),
    movie_id        INT NOT NULL,
    person_id       INT NOT NULL,
    checkout        DATETIME NOT NULL,
    checkin         DATETIME,
    comments        VARCHAR(200)
);
