DROP DATABASE IF EXISTS Offense;
CREATE DATABASE Offense
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci;
USE Offense;
COMMIT;

/*** Человек ***/

CREATE TABLE People (
    HUMANID INT NOT NULL AUTO_INCREMENT,
    Name1 varchar(20),                      /* Имя */
    Name2 varchar(20),                      /* Фамилия */
    Name3 varchar(20),                      /* Отчество */
    God INT(12),                            /* Дата рождения */
    Place varchar(20),                      /* Место проживания */
    CONSTRAINT PK_HUMAN
        PRIMARY KEY (HUMANID)
) DEFAULT CHARSET utf8;
COMMIT;

/*** Правонарушения ***/

CREATE TABLE Off (
    Vid VARCHAR(20),                        /* Вид правонарушения */
    Statya varchar(20) NOT NULL,            /* Статья */
    Opisanie varchar(1000) NOT NULL,        /* Описание */
    CONSTRAINT PK_OFF
        PRIMARY KEY (Vid)
) DEFAULT CHARSET utf8;
COMMIT;

/*** Связь ***/

CREATE TABLE OFF_PEOPLE (
    HUMANID INT NOT NULL,
    Vid VARCHAR(20) NOT NULL,
    CONSTRAINT PK_PEOPLE_OFF
        PRIMARY KEY (HUMANID, Vid),
    CONSTRAINT FK_OFFPEOPLE1
        FOREIGN KEY (HUMANID)
        REFERENCES People (HUMANID),
    CONSTRAINT FK_OFFPEOPLE2
        FOREIGN KEY (Vid)
        REFERENCES Off (Vid)
) DEFAULT CHARSET utf8;
COMMIT;

INSERT INTO PEOPLE VALUES ();
INSERT INTO OFF_PEOPLE VALUES ();
INSERT INTO OFF_PEOPLE VALUES ();

SELECT * FROM OFF_PEOPLE;