Única SGBD - Schema RH


--CREATE Schema rh2;
--ALTER USER XXXXXXXXXXXXXXX WITH DEFAULT_SCHEMA=rh;


--DROP ALL tables of SHEMA RH
DROP TABLE IF EXISTS Trabalha_em;
DROP TABLE IF EXISTS Dependente;
DROP TABLE IF EXISTS Projeto;
DROP TABLE IF EXISTS Localizacao_Depto;
ALTER TABLE Departamento DROP CONSTRAINT fk_depto_func;
DROP TABLE IF EXISTS Empregado;
DROP TABLE IF EXISTS Departamento;


CREATE TABLE Empregado (
    PrimeiroNome    VARCHAR(30)  NOT NULL,
    InicialMeio     CHAR(1)      NOT NULL,
    UltimoNome      VARCHAR(30)  NOT NULL,
    NumEmpregado    NUMERIC(4)   NOT NULL,
    DataNascimento  DATE         NOT NULL,
    Endereco        VARCHAR(50)  NOT NULL,
    Sexo            CHAR(1)      NOT NULL,
    Salario         NUMERIC(8,2) NOT NULL,
    NumSupervisor   NUMERIC(4)   NULL,
    NumDepto        NUMERIC(2)   NOT NULL,
    CONSTRAINT pk_func PRIMARY KEY (NumEmpregado),
    CONSTRAINT ck_sexo CHECK (Sexo='M' OR Sexo='F')
);


CREATE TABLE Departamento (
    NomeDepto           VARCHAR(30)     NOT NULL,
    NumDepto            NUMERIC(2)      NOT NULL,
    NumGerente          NUMERIC(4)      NOT NULL,
    DataInicioGerencia  DATE,
    CONSTRAINT pk_depto PRIMARY KEY (NumDepto),
    CONSTRAINT uk_nome  UNIQUE (NomeDepto)
);

CREATE TABLE Localizacao_Depto (
    NumDepto            NUMERIC(2)      NOT NULL,
    Localizacao         VARCHAR(30)     NOT NULL,
    CONSTRAINT pk_loc_depto PRIMARY KEY (NumDepto, Localizacao)
);

CREATE TABLE Projeto (
    NomeProj      VARCHAR(30)    NOT NULL,
    NumProj       NUMERIC(4)     NOT NULL,
    Localizacao   VARCHAR(30)    NULL,
    NumDepto      NUMERIC(2)     NOT NULL,
    CONSTRAINT pk_proj PRIMARY KEY (NumProj),
    CONSTRAINT uk_nomeProj UNIQUE (NomeProj)
);

CREATE TABLE Trabalha_em (
    NumEmpregado   NUMERIC(4)     NOT NULL,
    NumProj        NUMERIC(4)     NOT NULL,
    Horas          NUMERIC(6,1)   DEFAULT 0,
    CONSTRAINT pk_trab_em PRIMARY KEY (NumEmpregado, NumProj)
);


CREATE TABLE Dependente (
    NumEmpregado    NUMERIC(4)    NOT NULL,
    NomeDependente  VARCHAR(30)   NOT NULL,
    Sexo            CHAR(1)       NOT NULL,
    DataAniversario DATE          NOT NULL,
    Parentesco      CHAR(15)      NULL,
    CONSTRAINT pk_depend PRIMARY KEY (NumEmpregado, NomeDependente),
    CONSTRAINT ck_sexo_dep CHECK (Sexo='M' OR Sexo='F')
);


--EMPREGADOS
INSERT INTO Empregado
  VALUES ('Joao', 'B', 'Silva',        1, '1965-01-09', 'R. da Bahia, 2557',     'M',  300, 2, 5);
INSERT INTO Empregado
 VALUES ('Frank', 'T', 'Santos',       2, '1955-08-12', 'Av. Afonso Pena, 3005', 'M', 4000, 8, 5);
INSERT INTO Empregado
 VALUES ('Alice', 'J', 'Pereira',      3, '1968-07-19', 'Av. do Contorno, 2534', 'F', 2500, 4, 4);
INSERT INTO Empregado
 VALUES ('Luciene', 'S', 'Ferreira',   4, '1951-06-20', 'R. Iraí, 175',          'F',  430, 8, 4);
INSERT INTO Empregado
 VALUES ('Pedro', 'K', 'Magalhães',    5, '1952-09-15', 'Av. Silva Lobo, 2050',  'M', 1200, 2, 5);
INSERT INTO Empregado
 VALUES ('Daniela', 'A', 'Oliveira',   6, '1962-07-31', 'R. Ataliba Lago, 250',  'F', 2500, 2, 5);
INSERT INTO Empregado
 VALUES ('Mateus', 'V', 'Mascarenhas', 7, '1979-03-29', 'R. Contria, 12',        'M', 2500, 4, 4);
INSERT INTO Empregado
 VALUES ('Fábio', 'E', 'Lemos',        8, '1947-11-10', 'R. Chile, 425',         'M', 5500, NULL, 1);


-- DEPARTAMENTOS:::
INSERT INTO Departamento VALUES ('Pesquisa',      5, 2, '1998-05-22');
INSERT INTO Departamento VALUES ('Administração', 4, 4, '1995-01-01');
INSERT INTO Departamento VALUES ('Diretoria',     1, 8, '1998-05-22');


-- LOCALIZAÇÃO DEPARTAMENTOS:::
INSERT INTO Localizacao_Depto  VALUES (1, 'Savassi');
INSERT INTO Localizacao_Depto  VALUES (4, 'Centro');
INSERT INTO Localizacao_Depto  VALUES (5, 'Buritis');
INSERT INTO Localizacao_Depto  VALUES (5, 'Pampulha');
INSERT INTO Localizacao_Depto  VALUES (5, 'Contagem');


-- PROJETOS
INSERT INTO Projeto   VALUES ('Produto X',       1, 'Buritis',  5);
INSERT INTO Projeto   VALUES ('Produto Y',       2, 'Pampulha', 5);
INSERT INTO Projeto   VALUES ('Produto Z',       3, 'Contagem', 5);
INSERT INTO Projeto   VALUES ('Informatização',  10, 'Centro',  4);
INSERT INTO Projeto   VALUES ('Reorganização',   20, 'Savassi', 1);
INSERT INTO Projeto   VALUES ('NovosBenefícios', 30, 'Centro',  4);


--TRABALHA_EM
INSERT INTO Trabalha_em  VALUES (1,  1, 32);
INSERT INTO Trabalha_em  VALUES (1,  2,  7);
INSERT INTO Trabalha_em  VALUES (5,  3, 40);
INSERT INTO Trabalha_em  VALUES (6,  1,  20);
INSERT INTO Trabalha_em  VALUES (6,  2, 20);
INSERT INTO Trabalha_em  VALUES (2,  2,  10);
INSERT INTO Trabalha_em  VALUES (2,  3, 10);
INSERT INTO Trabalha_em  VALUES (2,  10,  10);
INSERT INTO Trabalha_em  VALUES (2,  20, 10);
INSERT INTO Trabalha_em  VALUES (3,  30,  30);
INSERT INTO Trabalha_em  VALUES (3,  10,  10);
INSERT INTO Trabalha_em  VALUES (7,  10,  35);
INSERT INTO Trabalha_em  VALUES (7,  30,  5);
INSERT INTO Trabalha_em  VALUES (4,  30,  20);
INSERT INTO Trabalha_em  VALUES (4,  20,  15);
INSERT INTO Trabalha_em  (NumEmpregado, NumProj) VALUES (8,  20);


--DEPENDENTES
INSERT INTO Dependente  VALUES (2,  'Aline',     'F', '1976-04-03', 'Filha');
INSERT INTO Dependente  VALUES (2,  'Victor',    'M', '1973-10-25', 'Filho');
INSERT INTO Dependente  VALUES (2,  'Joana',     'F', '1998-05-03', 'Cônjuge');
INSERT INTO Dependente  VALUES (4,  'Igor',      'M', '1952-02-29', 'Cônjuge');
INSERT INTO Dependente  VALUES (1,  'Michel',    'M', '1988-01-01', 'Filho');
INSERT INTO Dependente  VALUES (1,  'Aline',     'F', '1998-12-31', 'Filha');
INSERT INTO Dependente  VALUES (1,  'Elizabeth', 'F', '1957-05-05', 'Cônjuge');


--CONTRAINTS

ALTER TABLE Empregado
ADD CONSTRAINT fk_func_depto FOREIGN KEY (NumDepto) REFERENCES Departamento (NumDepto);

ALTER TABLE Empregado
ADD CONSTRAINT fk_func_superv FOREIGN KEY (NumSupervisor) REFERENCES Empregado (NumEmpregado);

ALTER TABLE Departamento
ADD CONSTRAINT fk_depto_func FOREIGN KEY (NumGerente) REFERENCES Empregado (NumEmpregado);

ALTER TABLE Projeto
ADD CONSTRAINT fk_proj_depto FOREIGN KEY (NumDepto) REFERENCES Departamento (NumDepto);

ALTER TABLE Dependente
ADD CONSTRAINT fk_dep_func FOREIGN KEY (NumEmpregado) REFERENCES Empregado (NumEmpregado) ON DELETE CASCADE;

ALTER TABLE Trabalha_em
ADD CONSTRAINT fk_trab_func FOREIGN KEY (NumEmpregado) REFERENCES Empregado (NumEmpregado) ON DELETE CASCADE;

ALTER TABLE Trabalha_em
ADD CONSTRAINT fk_trab_proj FOREIGN KEY (NumProj) REFERENCES Projeto (NumProj) ON DELETE CASCADE;

ALTER TABLE Localizacao_Depto
ADD CONSTRAINT fk_loc_depto FOREIGN KEY (NumDepto) REFERENCES Departamento (NumDepto);