/**
 * Laboratorio de Base de Dados 
 * Profa. Dra. Cristina Dutra de Aguiar Ciferri
 * Estagiária PAE: jaqueline Joice Brito
 * Script de criação e inserção de dados no esquema da empresa de aluguel de carros
*/

--Drop em todas as tabelas, eliminando também suas restrições

DROP TABLE aluguel CASCADE CONSTRAINTS;
DROP TABLE cliente CASCADE CONSTRAINTS;
DROP TABLE dependente CASCADE CONSTRAINTS;
DROP TABLE corrida CASCADE CONSTRAINTS;
DROP TABLE carro CASCADE CONSTRAINTS;
DROP TABLE motorista CASCADE CONSTRAINTS;
DROP TABLE secretario CASCADE CONSTRAINTS;
DROP TABLE funcionario CASCADE CONSTRAINTS;
DROP TABLE filial CASCADE CONSTRAINTS;
DROP TABLE empresa CASCADE CONSTRAINTS;
DROP TABLE valor_locacao CASCADE CONSTRAINTS;


/**
 * Tabela empresa
 * @emp_codigo 	 	chave primária
 * @emp_nome 	 	razão social da empresa
 * @emp_endereco 	endereço da empresa
 * @emp_telefone 	telefone da empresa
 * @PK_EMPRESA   	restrição de chave primária
 */
CREATE TABLE empresa (
emp_codigo NUMBER(5) NOT NULL,
emp_nome VARCHAR2(30),
emp_endereco VARCHAR2(100),
emp_telefone VARCHAR2(15),
CONSTRAINT PK_EMPRESA PRIMARY KEY (emp_codigo) 
);


/**
 * Tabela filial
 * @emp_codigo, filial_nro  chave primária
 * @emp_codigo		    código da empresa a qual pertence a filial
 * @filial_nro		    número da filial
 * @filial_local 	    local da filial
 * @filial_data_abertura    data de abertura da filial
 * @PK_FILIAL               restrição de chave primária
 * @FK_FILIAL               restrição de chave estrangeira com a tabela empresa
 */
CREATE TABLE filial (
emp_codigo NUMBER(5) NOT NULL,
filial_nro NUMBER(5) NOT NULL,
filial_local VARCHAR2(30),
filial_data_abertura DATE,
CONSTRAINT PK_FILIAL PRIMARY KEY (emp_codigo, filial_nro),
CONSTRAINT FK_FILIAL FOREIGN KEY (emp_codigo) REFERENCES empresa(emp_codigo) ON DELETE CASCADE  
);



/**
 * Tabela funcionario 
 * @fun_codigo          chave primária  
 * @primeiro_nome       primeiro nome do funcionário 
 * @sobrenome           sobrenome do funcionário
 * @fun_endereco        endereço do funcionário
 * @fun_sexo            sexo do funcionário
 * @fun_niver           data de aniversário
 * @fun_salario         salário do funcionário
 * @tipo_funcionario    tipo de funcionário
 * @emp_codigo          código da empresa em que o funcionário trabalha
 * @cod_supervisor      código do supervisor 
 * @PK_FUNCIONARIO      restrição de chave primária
 * @FK_FUNCIONARIO1     restrição de chave estrangeira com a tabela empresa
 * @FK_FUNCIONARIO2     restrição de chave estrangeira com a tabela funcionario 
 * @CH_FUNCIONARIO1     checagem do sexo do funcionário
 * @CH_FUNCIONARIO2     checagem do tipo de funcionário
 */

CREATE TABLE funcionario (
fun_codigo NUMBER(5) NOT NULL,
primeiro_nome VARCHAR2(30),
sobrenome VARCHAR2(30),
fun_endereco VARCHAR2(100),
fun_sexo CHAR(1),
fun_niver DATE,
fun_salario NUMBER(10,2),
tipo_funcionario VARCHAR2(10) NOT NULL,
emp_codigo NUMBER(5),
cod_supervisor NUMBER(5),
CONSTRAINT PK_FUNCIONARIO  PRIMARY KEY (fun_codigo),
CONSTRAINT FK_FUNCIONARIO1 FOREIGN KEY (emp_codigo) REFERENCES  empresa(emp_codigo) ON DELETE SET NULL,
CONSTRAINT FK_FUNCIONARIO2 FOREIGN KEY (cod_supervisor) REFERENCES funcionario(fun_codigo) ON DELETE SET NULL,
CONSTRAINT CH_FUNCIONARIO1 CHECK (fun_sexo IN ('m', 'f')),
CONSTRAINT CH_FUNCIONARIO2 CHECK (tipo_funcionario IN ('secretario', 'motorista'))
);



/**
 * Tabela secretario
 * @fun_codigo       chave primária
 * @num_certificado  número do certificado
 * @data_expedicao   data de expedição do certificado
 * @PK_SECRETARIO    restrição de chave primária
 * @FK_SECRETARIO    restrição de chave estrangeira com a tabela funcionario
 */
CREATE TABLE secretario (
fun_codigo NUMBER(5) NOT NULL,
num_certificado NUMBER(5),
data_expedicao DATE,
CONSTRAINT PK_SECRETARIO  PRIMARY KEY (fun_codigo),
CONSTRAINT FK_SECRETARIO  FOREIGN KEY (fun_codigo) REFERENCES funcionario(fun_codigo) ON DELETE CASCADE  
);


/**
 * Tabela motorista
 * @fun_codigo      chave primária
 * @num_carteira    número da CNH
 * @classe          classe da CNH
 * @PK_MOTORISTA    restrição de chave primária
 * @FK_MOTORISTA    restrição de chave estrangeira com a tabela funcionario
 */
CREATE TABLE motorista (
fun_codigo NUMBER(5) NOT NULL,
num_carteira NUMBER(10),
classe CHAR(1),
CONSTRAINT PK_MOTORISTA  PRIMARY KEY (fun_codigo),
CONSTRAINT FK_MOTORISTA  FOREIGN KEY (fun_codigo) REFERENCES funcionario(fun_codigo) ON DELETE CASCADE  
);


/**
 * Tabela carro
 * @car_chassi      chave primária
 * @car_modelo      modelo do carro
 * @car_ano         ano do carro
 * @emp_codigo      código da empresa a qual o carro pertence
 * @PK_CARRO        restrição de chave primária
 * @FK_CARRO        restrição de chave estrangeira com a tabela empresa
 */
CREATE TABLE carro (
car_chassi VARCHAR2(30) NOT NULL,
car_modelo VARCHAR2(30),
car_ano NUMBER(5),
emp_codigo NUMBER(5),
CONSTRAINT PK_CARRO PRIMARY KEY (car_chassi),
CONSTRAINT FK_CARRO FOREIGN KEY (emp_codigo) REFERENCES empresa(emp_codigo) ON DELETE SET NULL
);


/**
 * Tabela corrida
 * @car_chassi, fun_codigo, data       chave primária
 * @car_chassi                         chassi do carro dirigido  
 * @fun_codigo                         código do funcionário que dirigiu o carro
 * @data                               data em que o motorista dirigiu o carro
 * @nro_horas                          número total de horas que o funcionário dirigiu o carro
 * @PK_CORRIDA                          restrição de chave primária
 * @FK_CORRIDA1                         restrição de chave estrangeira com a tabela carro
 * @FK_CORRIDA2                         restrição de chave estrangeira com a tabela motorista
 */
CREATE TABLE corrida (
car_chassi VARCHAR2(30) NOT NULL,
fun_codigo NUMBER(5) NOT NULL,
data DATE NOT NULL,
nro_horas NUMBER(5),
CONSTRAINT PK_CORRIDA PRIMARY KEY (car_chassi, fun_codigo, data),
CONSTRAINT FK_CORRIDA1 FOREIGN KEY (car_chassi) REFERENCES carro(car_chassi) ON DELETE CASCADE,
CONSTRAINT FK_CORRIDA2 FOREIGN KEY (fun_codigo) REFERENCES motorista(fun_codigo) ON DELETE CASCADE
);



/**
 * Tabela dependente
 * @fun_codigo, dep_nome       chave primária
 * @dep_nome                   nome do dependente
 * @dep_sexo                   sexo do dependente 
 * @dep_data_niver             data de aniversário do dependente
 * @parentesco                 grau de parentesco com o funcionário  
 * @PK_DEPENDENTE              restrição de chave primária
 * @FK_DEPENDENTE              restrição de chave estrangeira com a tabela funcionario
 * @CH_DEPENDENTE              checagem do sexo do dependente
 */
CREATE TABLE dependente (
fun_codigo NUMBER(5) NOT NULL,
dep_nome VARCHAR2(50) NOT NULL,
dep_sexo VARCHAR2(10),
dep_data_niver DATE,
parentesco VARCHAR2(20),
CONSTRAINT PK_DEPENDENTE PRIMARY KEY (fun_codigo, dep_nome),
CONSTRAINT FK_DEPENDENTE FOREIGN KEY (fun_codigo) REFERENCES funcionario(fun_codigo) ON DELETE CASCADE,
CONSTRAINT CH_DEPENDENTE CHECK (dep_sexo IN ('m', 'f'))  
);


/**
 * Tabela cliente
 * @cli_codigo       chave primária
 * @cli_nome         nome do cliente
 * @cli_telefone     telefone do cliente
 * @emp_codigo       código da empresa em que o cliente está cadastrado
 * @PK_CLIENTE       restrição de chave primária
 * @FK_CLIENTE       restrição de chave estrangeira com a tabela empresa
 */
CREATE TABLE cliente (
cli_codigo NUMBER(5) NOT NULL,
cli_nome VARCHAR2(30),
cli_telefone VARCHAR2(15),
emp_codigo NUMBER(5),
CONSTRAINT PK_CLIENTE PRIMARY KEY (cli_codigo),
CONSTRAINT FK_CLIENTE FOREIGN KEY (emp_codigo) REFERENCES empresa(emp_codigo) ON DELETE SET NULL 
);





/**
 * Tabela aluga
 * @cli_codigo, car_chassi   chave primária
 * @cli_codigo               código do cliente que alugou o carro
 * @car_chassi               chassi do carro alugado
 * @data                     data do locação
 * @NF_numero                número da nota fiscal
 * @NF_valor                 valor da nota fiscal 
 * @desconto                 desconto obtido no aluguel do carro
 * @PK_ALUGA                 restrição de chave primária
 * @FK_ALUGA1                restrição de chave estrangeira com a tabela cliente
 * @FK_ALUGA2                restrição de chave estrangeira com a tabela carro
 */
CREATE TABLE aluguel (
cli_codigo NUMBER(5) NOT NULL,
car_chassi VARCHAR2(30) NOT NULL,
data DATE  NOT NULL,
NF_numero NUMBER(5),
NF_valor NUMBER(10,2),
desconto NUMBER(10,2),
CONSTRAINT PK_ALUGUEL PRIMARY KEY (cli_codigo, car_chassi, data),
CONSTRAINT FK_ALUGUEL1  FOREIGN KEY (cli_codigo) REFERENCES cliente(cli_codigo) ON DELETE CASCADE,
CONSTRAINT FK_ALUGUEL2  FOREIGN KEY (car_chassi) REFERENCES carro(car_chassi) ON DELETE CASCADE
);


/**
 * Tabela valor_locacao
 * @data_reajuste, car_ano, car_modelo    chave primária
 * @data_reajuste                         data do reajuste do preço
 * @car_ano                               ano do carro
 * @car_modelo                            modelo do carro
 * @PK_VALOR_LOCACAO                      restrição de chave primária
 */
CREATE TABLE valor_locacao (
data_reajuste DATE  NOT NULL,
car_ano NUMBER(5) NOT NULL,
car_modelo VARCHAR2(30) NOT NULL,
valor NUMBER(10,2),
CONSTRAINT PK_VALOR_LOCACAO PRIMARY KEY (data_reajuste, car_ano, car_modelo)  
);