ESIII - AP3 - Schema

CREATE TABLE `arma` (
    `id` int NOT NULL AUTO_INCREMENT,
    `nome` varchar(50) NOT NULL,
    `tipo` varchar(50) NOT NULL,
    `id_crime` int,
    PRIMARY KEY (`id`)
);

CREATE TABLE `crime` (
    `id` int NOT NULL AUTO_INCREMENT,
    `tipo` varchar(50) NOT NULL,
    `data_hora` DATETIME NOT NULL,
    `local` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `criminoso` (
    `id` int NOT NULL AUTO_INCREMENT,
    `nome` varchar(100) NOT NULL AUTO_INCREMENT,
    `foto` varchar(150) NOT NULL AUTO_INCREMENT,
    `cpf` varchar(14),
    PRIMARY KEY (`id`)
);

CREATE TABLE `crime_criminoso` (
    `id` int NOT NULL AUTO_INCREMENT,
    `id_crime` int NOT NULL,
    `id_criminoso` int NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `vitima` (
    `id` int NOT NULL AUTO_INCREMENT,
    `nome` int NOT NULL AUTO_INCREMENT,
    `cpf` varchar(14) NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `crime_vitima` (
    `id` int NOT NULL AUTO_INCREMENT,
    `id_crime` int NOT NULL,
    `id_vitima` int NOT NULL,
    PRIMARY KEY (`id`)
);

ALTER TABLE `arma` ADD CONSTRAINT `arma_fk0` FOREIGN KEY (`id_crime`) REFERENCES `crime`(`id`);

ALTER TABLE `crime_criminoso` ADD CONSTRAINT `crime_criminoso_fk0` FOREIGN KEY (`id_crime`) REFERENCES `crime`(`id`);

ALTER TABLE `crime_criminoso` ADD CONSTRAINT `crime_criminoso_fk1` FOREIGN KEY (`id_criminoso`) REFERENCES `criminoso`(`id`);

ALTER TABLE `crime_vitima` ADD CONSTRAINT `crime_vitima_fk0` FOREIGN KEY (`id_crime`) REFERENCES `crime`(`id`);

ALTER TABLE `crime_vitima` ADD CONSTRAINT `crime_vitima_fk1` FOREIGN KEY (`id_vitima`) REFERENCES `vitima`(`id`);