pQmkxkcr

1CREATE DATABASE ministerio;
2
3DROP TABLE IF EXISTS Elabora;
4DROP TABLE IF EXISTS Telefone;
5DROP TABLE IF EXISTS  Dependente;
6DROP TABLE IF EXISTS Funcionario;
7DROP TABLE IF EXISTS Gabinete;
8DROP TABLE IF EXISTS Ministro;
9DROP TABLE IF EXISTS Relatorio;
10
11CREATE TABLE Ministro (
12    Nome varchar(255) not null,
13    CPF bigint(11) not null primary key CHECK (CPF BETWEEN 00000000000 AND 99999999999),
14    Data_Inicio_Gestao date not null,
15    Numero varchar(255) not null,
16    Rua varchar(255) not null,
17    Cep varchar(255) not null
18);
19
20CREATE TABLE Gabinete (
21    Gabinete_ID varchar(255) not null primary key,
22    fk_ministro_cpf bigint(11) not null,
23    FOREIGN KEY (fk_ministro_cpf) REFERENCES Ministro (CPF)
24);
25
26CREATE TABLE Funcionario (
27    Nome varchar(255) not null,
28    Salario BIGINT(10) not null,
29    CPF bigint(11) not null primary key CHECK (CPF BETWEEN 00000000000 AND 99999999999),
30    Secretario INT(1) not null CHECK (Secretario BETWEEN 0 AND 1),
31    Chefe_Gabinete INT(1) not null CHECK (Chefe_Gabinete BETWEEN 0 AND 1),
32    Tecnico INT(1) not null CHECK (Tecnico BETWEEN 0 AND 1),
33    fk_Gabinete_Id varchar(255) not null,
34    fk_FuncionarioChefe_Cpf  bigint(11) default null,
35    FOREIGN KEY (fk_Gabinete_Id) REFERENCES Gabinete (Gabinete_ID),
36    FOREIGN KEY (fk_FuncionarioChefe_Cpf) REFERENCES Funcionario (CPF)
37    ON DELETE SET NULL
38);
39
40CREATE TABLE Relatorio (
41 Codigo_Identificador varchar(255) not null primary key,
42 De_Custo INT(1) default 0 CHECK (De_Custo BETWEEN 0 AND 1),
43 De_Lei INT(1) default 0 CHECK (De_Lei BETWEEN 0 AND 1),
44 De_Gasto INT(1) default 0 CHECK (De_Gasto BETWEEN 0 AND 1),
45 Teto BIGINT(20),
46 Extrapolou INT(1) default 0 CHECK (Extrapolou BETWEEN 0 AND 1)
47);
48
49CREATE TABLE Dependente (
50    Nome varchar(255) not null,
51    Grau_Parentesco varchar(255) not null,
52    fk_Funcionario_Cpf bigint(11) references Funcionario (CPF) ON DELETE SET NULL,
53    PRIMARY KEY (Nome, fk_Funcionario_Cpf)
54
55);
56
57CREATE TABLE Telefone (
58    Telefone varchar(255) not null,
59    fk_Ministro_Cpf bigint(11) references Ministro (CPF) ON DELETE SET NULL,
60    PRIMARY KEY (Telefone, fk_Ministro_Cpf)
61);
62
63CREATE TABLE Elabora (
64    fk_Ministro_Cpf bigint(11) references Ministro (CPF) ON DELETE SET NULL,
65    fk_Funcionario_Cpf bigint(11) references Funcionario (CPF) ON DELETE SET NULL,
66    Data date not null,
67    fk_Relatorio_Codigo_Identificador varchar(255) references Relatorio (Codigo_Identificador) ON DELETE SET NULL,
68    PRIMARY KEY (fk_Ministro_Cpf, fk_Funcionario_Cpf, fk_Relatorio_Codigo_Identificador)
69
70);