CREATE TABLE Fornecedores (
	forcNomeFantasia VARCHAR2 (25) NOT NULL,
	forcCNPJ VARCHAR2(12) NOT NULL,
	forcEndereco VARCHAR2 (50) NOT NULL,
	forcRazaoSocial VARCHAR2 (30) NOT NULL,
	CONSTRAINT pk_fornecedores PRIMARY KEY (forcCNPJ)	
	
);

CREATE TABLE Estoque (
	estcTipo VARCHAR2 (10) NOT NULL,
	estnEstoqueMin NUMBER (5),
	estnEstoqueMax NUMBER (5),
	CONSTRAINT pk_estoque PRIMARY KEY (estcTipo)
);

CREATE TABLE Material (
	matnEstoqueAtual NUMBER (5),
	matcUnidadeDeUso VARCHAR2 (50) NOT NULL,
	matcFabricante VARCHAR2 (40) NOT NULL,
	matcTipo VARCHAR2 (10) NOT NULL,
	matcDescricao VARCHAR2 (50) NOT NULL,
	matcSequencial VARCHAR2 (5) NOT NULL,
	forcCNPJ VARCHAR2(12) NOT NULL,
	CONSTRAINT pk_material PRIMARY KEY (matcSequencial),
	CONSTRAINT fornecedores_fk FOREIGN KEY (forcCNPJ) references Fornecedores (forcCNPJ),
	CONSTRAINT estoque_fk FOREIGN KEY (matcTipo) references Estoque (estcTipo)
);

CREATE TABLE Construtora (
	concCNPJ VaRCHAR2 (12) NOT NULL,
	concNomeFantasia VARCHAR2 (25) NOT NULL,
	concRazaoSocial VARCHAR2 (30) NOT NULL,	
	concEndereco VARCHAR2 (50) NOT NULL,
	CONSTRAINT pk_construtora PRIMARY KEY (concCNPJ)
);


CREATE TABLE Obras (
	obrdDataInicial VARCHAR2 (10),
	obrdDataPrevisao VARCHAR2 (10),
	obrcTipo VARCHAR2 (20) NOT NULL,
	obrdDataConclusao VARCHAR2 (10),
	obrcSequencial VARCHAR2 (5) NOT NULL,
	obrcEndereco VARCHAR2 (50) NOT NULL,
	concCNPJ VaRCHAR2 (12) NOT NULL,
	CONSTRAINT pk_obras PRIMARY KEY (obrcSequencial),
	CONSTRAINT construtora_fk FOREIGN KEY (concCNPJ) references Construtora (concCNPJ)	
);

CREATE TABLE Funcionarios(
	funcCPF VARCHAR2 (11) NOT NULL,
	funcCargo VARCHAR2 (50) NOT NULL,
	funcSetorDeLocacao VARCHAR2(50) NOT NULL,
	funcCargaHoraria VARCHAR2 (10) NOT NULL,
	concCNPJ VaRCHAR2 (12) NOT NULL,
	CONSTRAINT pk_funcionarios PRIMARY KEY (funcCPF),
	CONSTRAINT construtoraFuncionarios_fk FOREIGN KEY (concCNPJ) references Construtora (concCNPJ)
);

CREATE TABLE Atua (
	funcCPF VARCHAR2 (11) NOT NULL,
	obrcSequencial VARCHAR2 (5) NOT NULL,
	CONSTRAINT funcionarios_fk FOREIGN KEY (funcCPF) references Funcionarios (funcCPF),
	CONSTRAINT obrasAtua_fk FOREIGN KEY (obrcSequencial) references Obras (obrcSequencial)
);

CREATE TABLE Uso (
	matcSequencial VARCHAR2 (5) NOT NULL,
	obrcSequencial VARCHAR2 (5) NOT NULL,
	CONSTRAINT obras_fk FOREIGN KEY (obrcSequencial) references Obras (obrcSequencial),
	CONSTRAINT material_fk FOREIGN KEY (matcSequencial) references Material (matcSequencial)
);


CREATE TABLE Logs_Construtora (
	data_atual DATE,
	usuario VARCHAR2 (30),
	msg VARCHAR2 (50),
	v_nome_fantasia VARCHAR2 (25),
	v_razao_social VARCHAR2 (30),
	v_endereco VARCHAR2 (50),
	n_nome_fantasia VARCHAR2 (25),
	n_razao_social VARCHAR2 (30),
	n_endereco VARCHAR2 (50)

);


alter table funcionarios
add funcCpfChefe VARCHAR2 (11);
alter table funcionarios add funcNome VARCHAR2 (20);