0n6jGxi8

1SET FOREIGN_KEY_CHECKS = 0;
2SET GROUP_CONCAT_MAX_LEN=32768;
3SET @tables = NULL;
4SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
5  FROM information_schema.tables
6  WHERE table_schema = (SELECT DATABASE());
7SELECT IFNULL(@tables,'dummy') INTO @tables;
8
9SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
10PREPARE stmt FROM @tables;
11EXECUTE stmt;
12DEALLOCATE PREPARE stmt;
13SET FOREIGN_KEY_CHECKS = 1;
14
15create table Cidade(
16CidadeId int primary key auto_increment,
17    Nome varchar(30) not null,
18    UF char(2) not null
19);
20
21create table Endereco(
22EnderecoId int primary key auto_increment,
23Logradouro varchar(30) not null,
24Bairro varchar(30) not null,
25CidadeId int not null,
26    constraint fk_endereco_cidade FOREIGN KEY(CidadeId) References Cidade(CidadeId),
27CEP CHAR(8) not null
28);
29
30Create Table Industria(
31IndustriaCNPJ CHAR(14) primary key,
32Nome varchar(30) not null,
33Tipo varchar(30) not null,
34EnderecoId int  not null,
35    constraint fk_industria_endereco FOREIGN KEY(EnderecoId) References Endereco(EnderecoId)
36);
37
38Create Table Departamento(
39DepartamentoId int primary key auto_increment,
40    IndustriaCNPJ CHAR(14) not null,
41    Nome varchar(30) unique,
42    constraint fk_departamento_industria FOREIGN KEY(IndustriaCNPJ) References Industria(IndustriaCNPJ)
43);
44
45Create Table Empregado(
46Matricula int primary key auto_increment,
47Nome varchar(30) not null,
48EnderecoId int,
49Funcao ENUM('engenheiro', 'operario'),
50constraint fk_empregado_endereco FOREIGN KEY(EnderecoId) References Endereco(EnderecoId)
51);
52
53
54Create Table Sensor(
55  SensorId INT primary key auto_increment,
56  Protocolo varchar(10) not null
57);
58
59Create Table Maquina(
60MaquinaNS varchar(20) primary key
61);
62
63Create Table Leitura(
64LeituraId int primary key auto_increment,
65    Valor FLOAT(3,2) not null,
66    Unidade enum('C', 'Pa', 'm/s2'),
67    MaquinaNS varchar(20),
68    SensorId int,
69    constraint fk_leitura_maquina FOREIGN KEY(MaquinaNS) References Maquina(MaquinaNS),
70    constraint fk_leitura_sensor FOREIGN KEY(SensorId) References Sensor(SensorId)
71);
72
73Create Table Acesso(
74AcessoId int primary key auto_increment,
75    Matricula int,
76    HoraData TIMESTAMP,
77    constraint fk_acesso_empregado FOREIGN KEY(Matricula) References Empregado(Matricula)
78);
79
80Create Table ReMaquinaSensor(
81MaquinaNS varchar(20),
82    SensorId INT,
83    PRIMARY KEY(MaquinaNS, SensorId),
84    constraint fk_ReMaquinaSensor_Maquina FOREIGN KEY(MaquinaNS) References Maquina(MaquinaNS),
85    constraint fk_ReMaquinaSensor_Sensor FOREIGN KEY(SensorId) References Sensor(SensorId)
86);
87
88insert into Cidade