Código SQL e Modelo Relacional

MODELO CONCEITUAL (ENTIDADE - RELACIONAMENTO). MODELO LÓGICO. CONSULTAS SQL. DEFINIÇÃO DO PROBLEMA Temos como escopo do nosso projeto, a empresa ACME, uma multifuncional do ramo de auto-peças, a empresa precisa de um software que auxilie no controle da vida útil de ferramentas produtoras de peças, utilizadas por seus fornecedores. A ACME terceiriza a produção de peças, contratado empresas para fabricar uma ferramenta para cada tipo de componente, e a empresa fabricante deve garantir a entrega de determinado número de peças. A tabela alocacao relacionará a tabela de ferramenta com a de fornecedor vinculando a chave de cada tabela e a data, assim será possível saber qual ferramenta está em qual fornecedor, bem como seus dados, e também por quais fornecedores cada ferramenta já passou, bem como o tempo que esteve em cada um deles.

A tabela tecnicos tem por objetivo armazenar as informações a respeito dos técnicos que farão a vistoria das ferramentas, as informações a serem armazenadas são: nome, e-mail e cpf. A tabela de avaliacao irá armazenar o histórico das vistorias feitas pelos técnicos, armazenando: motivo, acrescimento, decréscimo, data e a chave da ferramenta avaliada e a do técnico que fez a avaliação. Dessa forma será possível saber por quantas vistorias cada ferramenta já passou, por qual técnico foi executada, e quais foram os acréscimos/ decréscimos feitos para cada ferramenta. Como restrição temos que os campos de acrescimo/ decrescimo devem ter valores em 0 e 100. Resumindo, uma empresa produz uma ou mais ferramentas, essas ferramentas ficam alocadas em um fornecedor, que pode ser ou não a empresa que a produziu, a alocação dessa ferramenta pode ser trocada entre os fornecedores, e é necessário manter esse histórico, o fornecedor controla a produção e diferentes técnicos podem avaliar a ferramenta.

MODELO FISICO Para modelo fisico foi utilizado o Oracle 10g, os códigos de criação das tabelas com suas chaves estrangeiras estão abaixo: CREATE TABLE "Empresa" ( "em_cod" INT, "nome" varchar, "cnpj" varchar, "telefone" varchar, "responsavel" varchar, constraint EMPRESA_PK PRIMARY KEY ("em_cod") CREATE sequence "EMPRESA_SEQ" / CREATE trigger "BI_EMPRESA" before insert on "EMPRESA" for each row begin select "EMPRESA_SEQ". nextval into :NEW. em_cod" from dual; end; /) / CREATE TABLE "Ferramenta" ( "fr_cod" INT, "tipo_peca" INT, "limite" INT, "capacidade_producao" INT, "responsavel" varchar, "email" TEXT, "em_cod" INT, constraint FERRAMENTA_PK PRIMARY KEY ("fr_cod") CREATE sequence "FERRAMENTA_SEQ" / CREATE trigger "BI_FERRAMENTA" before insert on "FERRAMENTA" for each row begin select "FERRAMENTA_SEQ". nextval into :NEW. av_cod" from dual; end; /) / ALTER TABLE "Ferramenta" ADD CONSTRAINT "Ferramenta_fk0" FOREIGN KEY ("em_cod") REFERENCES Empresa("em_cod"); ALTER TABLE "Alocacao" ADD CONSTRAINT "Alocacao_fk0" FOREIGN KEY ("fr_cod") REFERENCES Ferramenta("fr_cod"); ALTER TABLE "Alocacao" ADD CONSTRAINT "Alocacao_fk1" FOREIGN KEY ("fn_cod") REFERENCES Fornecedor("fn_cod"); ALTER TABLE "Producao" ADD CONSTRAINT "Producao_fk0" FOREIGN KEY ("fr_cod") REFERENCES Ferramenta("fr_cod"); ALTER TABLE "Avaliacao" ADD CONSTRAINT "Avaliacao_fk0" FOREIGN KEY ("fr_cod") REFERENCES Ferramenta("fr_cod"); ALTER TABLE "Avaliacao" ADD CONSTRAINT "Avaliacao_fk1" FOREIGN KEY ("tc_cod") REFERENCES Tecnicos("tc_cod"); Abaixo estão os códigos sql para responder as perguntas propostas no trabalho: • Em qual fornecedor uma ferramenta está alocada? Considerando 1 o código da ferramenta que queremos verificar onde está alocada.

Dessa forma fazemos um inner join para unir as tabelas de ferramentas com fornecedor, filtrando pelo código da ferramenta escolhida, ordenando pela data e limitando a consulta a trazer o último registro. SELECT fn. FROM Fornecedor fn INNER JOIN Alocacao al ON (al. fn_cod = fn. fr_cod = fr. fr_cod) GROUP BY fr. fr_cod having fr. capacidade_producao > SUM(produzidas) • Qual empresa foi responsável pela produção dessas ferramentas que não atingiram a capacidade de produção contratada? SELECT em. nome, fr.