Рет қаралды 274
Segue a baixo o código SQL pra criar o banco de dados relacional.
CREATE DATABASE loja;
CREATE TABLE `loja`.`login` (
`cod` INT(11) NOT NULL AUTO_INCREMENT ,
`user` VARCHAR(255) NOT NULL ,
`pass` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`cod`)) ENGINE = InnoDB;
CREATE TABLE `loja`.`modelo` (
`cod` INT(11) NOT NULL AUTO_INCREMENT ,
`descricao` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`cod`)) ENGINE = InnoDB;
CREATE TABLE `loja`.`cor` (
`cod` INT(11) NOT NULL AUTO_INCREMENT ,
`descricao` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`cod`)) ENGINE = InnoDB;
CREATE TABLE `loja`.`carro` (
`cod` INT(11) NOT NULL AUTO_INCREMENT ,
`cod_login_c` INT(11) NOT NULL ,
`cod_login_u` INT(11) NOT NULL ,
`cod_modelo` INT(11) NOT NULL ,
`cod_cor` INT(11) NOT NULL ,
`placa` VARCHAR(20) NOT NULL ,
`ano` INT(11) NOT NULL ,
PRIMARY KEY (`cod`),
FOREIGN KEY (`cod_login_c`) REFERENCES login(cod) ON DELETE RESTRICT,
FOREIGN KEY (`cod_login_u`) REFERENCES login(cod) ON DELETE RESTRICT,
FOREIGN KEY (`cod_modelo`) REFERENCES modelo(cod) ON DELETE CASCADE,
FOREIGN KEY (`cod_cor`) REFERENCES cor(cod) ON DELETE RESTRICT
) ENGINE = InnoDB;
INSERT INTO login (user,pass) VALUES
( 'kaio','1234'),
( 'giovane','9999'),
( 'maria','0000'),
( 'pedro','1111'),
( 'lucas','2222'),
( 'fabio','4321'),
( 'ana','5555'),
( 'duda','7777');
INSERT INTO cor (descricao) VALUES
( 'amarelo'),
( 'azul'),
( 'marrom'),
( 'verde'),
( 'branco'),
( 'preto'),
( 'cinza'),
( 'vermelho');
INSERT INTO modelo (descricao) VALUES
( 'fusca'),
( 'gol'),
( 'pálio'),
( 'siena'),
( 'opala');
INSERT INTO carro (cod_login_c, cod_login_u, cod_modelo, cod_cor, placa, ano) VALUES
( 1,1,1, 1, 'ABC-0001', 1940),
( 2,3,1, 2, 'ABC-0002', 1954),
( 2,6,1, 4, 'ABC-0003', 1960),
( 3,1,3, 6, 'ABC-0004', 1990),
( 5,4,5, 3, 'ABC-0005', 1999),
( 3,2,2, 5, 'ABC-0006', 1850),
( 1,4,2, 3, 'ABC-0007', 2020),
( 3,8,4, 3, 'ABC-0008', 1955),
( 4,1,4, 2, 'ABC-0009', 1941),
( 7,2,2, 1, 'ABC-0010', 1941);