Projeto Final Banco de Dados

Prévia do material em texto

Curso: Superior de Tecnologia em Telemática
Disciplina: Banco de Dados 2019.1
Discentes: Sidney e Werbbet
Prof. Samuel Soares
Projeto Banco de Dados
Projeto
● Projeto de Banco de Dados voltado para uma empresa de Serviço de instalação e 
manutenção de centrais de ar, que possuem vendas de peças para a instalação.
02
Requisitos do Projeto
● A empresa possui um nome, cnpj, endereço, telefone, onde o cnpj é um número único para a empresa. 
A empresa contém funcionários, que possuem um nome, cpf, data de nascimento, endereço e 
telefone, onde o cpf é um número único para cada funcionário.
● Um cliente possui um nome, endereço e telefone, onde o mesmo é identificado pelo nome e telefone. 
O cliente solicita uma visita técnica da empresa, onde é informado a data e hora da visita e a 
observação do que será realizado. 
● A empresa envia um funcionário ao local, onde será feito os procedimentos para realização do 
serviço. No serviço contém os materiais utilizados, valor do serviço, funcionário que executaram, a 
observação do serviço executado e um número de ordem de serviço, onde esse número é único para 
cada serviço.
● Em um ordem de serviço pode possuir materiais utilizados, que contém o nome do material, 
quantidade utilizada, valor unitário, observação e código do material, onde o código identifica cada 
material.
03
Modelo Entidade Relacionamento
04
Diagrama Entidade Relacionamento DER
05
Instruções de definição em SQL
06
● CREATE DATABASE bd_final;
● CREATE SCHEMA projeto;
● CREATE TABLE projeto.empresa (cnpj CHAR(18) NOT NULL, nome VARCHAR(60) NOT NULL, cidade 
VARCHAR(30) NOT NULL, rua VARCHAR(60) NOT NULL, bairro VARCHAR(40) NOT NULL, numero 
CHAR(10) NOT NULL, telefone VARCHAR(18) NOT NULL, CONSTRAINT PK_CNPJ PRIMARY KEY(cnpj));
● CREATE TABLE projeto.funcionario (cpf CHAR(14) NOT NULL, data_nasc DATE NOT NULL, nome 
VARCHAR(60) NOT NULL, cidade VARCHAR(30) NOT NULL, rua VARCHAR(60) NOT NULL, bairro 
VARCHAR(40) NOT NULL, numero CHAR(10) NOT NULL, telefone VARCHAR(18) NOT NULL, 
cnpj_empresa CHAR(18) NOT NULL, CONSTRAINT PK_CPF PRIMARY KEY (cpf), CONSTRAINT 
FK_CNPJ_EMPRESA FOREIGN KEY (cnpj_empresa) REFERENCES projeto.empresa (cnpj) ON DELETE 
SET NULL ON UPDATE CASCADE);
● CREATE TABLE projeto.cliente (nome VARCHAR(60) NOT NULL, telefone VARCHAR(18) NOT NULL, 
cidade VARCHAR(30) NOT NULL, rua VARCHAR(60) NOT NULL, bairro VARCHAR(40) NOT NULL, 
numero CHAR(10) NOT NULL, cnpj_empresa CHAR(18) NOT NULL, CONSTRAINT PK_NOME_TELEFONE 
PRIMARY KEY (nome, telefone), CONSTRAINT FK_CNPJ_EMPRESA FOREIGN KEY (cnpj_empresa) 
REFERENCES projeto.empresa (cnpj) ON DELETE SET NULL ON UPDATE CASCADE);
● CREATE TABLE projeto.servico (num_ordem INT NOT NULL, valor_servico DECIMAL(8,2) NOT NULL, 
observacao VARCHAR(120), CONSTRAINT PK_NUM_ORDEM PRIMARY KEY (num_ordem));
● CREATE TABLE projeto.material (codigo INT NOT NULL, nome VARCHAR(60) NOT NULL, valor_unitario 
DECIMAL(8,2) NOT NULL, observacao VARCHAR(120), CONSTRAINT PK_CODIGO PRIMARY KEY 
(codigo));
Instruções de definição em SQL
07
● CREATE TABLE projeto.visita_tecnica (data_hora_visita TIMESTAMP NOT NULL, nome_cliente 
VARCHAR(60) NOT NULL, telefone_cliente VARCHAR(18) NOT NULL, observacao VARCHAR(120), 
CONSTRAINT PK_DATA_HORA_VISITA_NOME_CLIENTE_TELEFONE_CLIENTE PRIMARY KEY 
(data_hora_visita, nome_cliente, telefone_cliente), CONSTRAINT FK_NOME_CLIENTE_COMPOSTA 
FOREIGN KEY (nome_cliente, telefone_cliente) REFERENCES projeto.cliente (nome, telefone) ON 
DELETE SET NULL ON UPDATE CASCADE);
Instruções de definição em SQL
08
● CREATE TABLE projeto.servico_executado (cpf_funcionario CHAR(14) NOT NULL, num_ordem_servico 
INT NOT NULL, data_hora_visita TIMESTAMP NOT NULL, nome_cliente VARCHAR(60) NOT NULL, 
telefone_cliente VARCHAR(18) NOT NULL, CONSTRAINT 
PK_CPF_FUNC_NUM_ORDEM_SERV_DATA_HORA_VIS_NOME_CLI_TELEFONE_CLI PRIMARY KEY 
(cpf_funcionario, num_ordem_servico, data_hora_visita, nome_cliente, telefone_cliente), 
CONSTRAINT FK_CPF_FUNCIONARIO FOREIGN KEY (cpf_funcionario) REFERENCES projeto.funcionario 
(cpf) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT FK_NUM_ORDEM_SERVICO FOREIGN KEY (num_ordem_servico) REFERENCES 
projeto.servico (num_ordem) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT 
FK_DATA_HORA_VISITA_NOME_TELEFONE_COMPOSTA FOREIGN KEY (data_hora_visita, 
nome_cliente, telefone_cliente) REFERENCES projeto.visita_tecnica (data_hora_visita, nome_cliente, 
telefone_cliente) ON DELETE SET NULL ON UPDATE CASCADE);
Instruções de definição em SQL
09
● CREATE TABLE projeto.fornecimento_material_os (cnpj_empresa CHAR(18) NOT NULL, 
codigo_material INT NOT NULL, num_ordem_servico INT NOT NULL, quantidade REAL NOT NULL, 
observacao VARCHAR(120), CONSTRAINT 
PK_CNPJ_EMPRESA_CODIGO_MATERIAL_NUM_ORDEM_SERVICO PRIMARY KEY (cnpj_empresa, 
codigo_material, num_ordem_servico), CONSTRAINT FK_CNPJ_EMPRESA FOREIGN KEY 
(cnpj_empresa) REFERENCES projeto.empresa (cnpj) ON DELETE SET NULL ON UPDATE CASCADE, 
CONSTRAINT FK_CODIGO_MATERIAL FOREIGN KEY (codigo_material) REFERENCES projeto.material 
(codigo) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT FK_NUM_ORDEM_SERVICO 
FOREIGN KEY (num_ordem_servico) REFERENCES projeto.servico (num_ordem) ON DELETE SET NULL 
ON UPDATE CASCADE);
Instruções de definição em SQL
10
● INSERT INTO projeto.empresa (cnpj, nome, cidade, rua, bairro, numero, telefone) VALUES 
('69.143.755/0001-41','TecnoFrio Ar Condicionado', 'Tauá','Rua Júlio Gonçalves', 'Alto Brilhante', '321', 
'041 88 9.9999-9999');
● INSERT INTO projeto.funcionario (cpf, data_nasc, nome, cidade, rua, bairro, numero, telefone, 
cnpj_empresa) VALUES ('909.399.950-85', '1980-01-01','Werbet Richers', 'Tauá','Rua Júlio Gonçalves', 
'Alto Brilhante', '321', '041 88 9.9000-0000', '69.143.755/0001-41'), ('660.091.550-10', 
'1996-08-07','Sidney Magal', 'Tauá','Rua Valdizar Alexandrino', 'Cidade Nova', '300', '041 88 
9.8888-8888', '69.143.755/0001-41');
● INSERT INTO projeto.cliente (nome, telefone, cidade, rua, bairro, numero, cnpj_empresa) VALUES 
('Samuel Soares', '041 88 9.8585-8585', 'Tauá', 'Rua Dos Professores', 'Centro', '123', 
'69.143.755/0001-41'), ('Leonardo Alves', '041 88 9.8282-8282', 'Tauá', 'Rua Sebastião Leitão', 'Cidade 
Nova', '99', '69.143.755/0001-41'), ('João Batista', '041 88 9.9900-0099', 'Parambu', 'Rua Hidelbrando 
Alves', 'Centro', '111', '69.143.755/0001-41'), ('Neto Alves', '041 85 9.8800-1122', 'Tauá', 'Rua Cel. 
Vicente Alexandrino', 'Tauazinho', '256', '69.143.755/0001-41');
Instruções de Inserção SQL
11
● INSERT INTO projeto.material (codigo, nome, valor_unitario, observacao) VALUES (1111,'Cano de 
Cobre 1/4 mm', 49.00, 'Valor referente ao Kilo'), (1212,'Cano de Cobre 3/8 mm', 55.50, 'Valor referente 
ao Kilo'), (1313,'Bucha 10 mm', 0.55, 'Bucha para tijolo furado'), (1414,'Bucha 8 mm', 0.35, 'Bucha para 
tijolo furado'), (1515,'Cabo PP 1,5 mm', 3.00, 'Valor referente ao Metro'), (1616,'Suporte para 
Condensador', 35.00, 'Referente ao Par'), (1717,'Fita autoadesiva de Aluminio', 8.00, 'Contendo 4M'), 
(1818,'Tudo esponjoso Isolante', 10.00, 'Valor por peça'), (1919,'Parafuso 8 mm', 0.40, 'Rosca 
soberba'), (2020,'Parafuso 10 mm', 0.65, 'Rosca soberba');
● INSERT INTO projeto.visita_tecnica (data_hora_visita, nome_cliente, telefone_cliente, observacao) 
VALUES ('2019-07-01 10:30:00', 'Leonardo Alves', '041 88 9.8282-8282','Cliente solicita visita técnica 
para limpeza de equipamento'), ('2019-06-30 15:30:00', 'João Batista', '041 88 9.9900-0099','Cliente 
solicita instalação de equipamento'), ('2019-07-08 08:00:00', 'Samuel Soares', '041 88 
9.8585-8585','Cliente solicita instalação e manutenção de equipamentos');
Instruções de Inserção SQL
12
● INSERT INTO projeto.servico (num_ordem, valor_servico, observacao) VALUES (1, 70.0, 'Realizado 
limpeza do equipamento sem usode materais'), (2, 250.0, 'Realizado instalação de central de ar com 
uso de materais'), (3, 450.0, 'Realizado manutenção e instalação de central de ar com uso de 
materais');
● INSERT INTO projeto.servico_executado (cpf_funcionario, num_ordem_servico, data_hora_visita, 
nome_cliente, telefone_cliente) VALUES ('909.399.950-85', 1, '2019-07-01 10:30:00', 'Leonardo Alves', 
'041 88 9.8282-8282'), ('660.091.550-10', 2, '2019-06-30 15:30:00', 'João Batista', '041 88 
9.9900-0099'), ('909.399.950-85', 3, '2019-07-08 08:00:00', 'Samuel Soares', '041 88 9.8585-8585'), 
('660.091.550-10', 3, '2019-07-08 08:00:00', 'Samuel Soares', '041 88 9.8585-8585');
Instruções de Inserção SQL
13
● INSERT INTO projeto.fornecimento_material_os (cnpj_empresa, codigo_material, num_ordem_servico, 
quantidade, observacao) VALUES ('69.143.755/0001-41', 1111, 2, 0.1, 'Utilizando 0.100 kg'), 
('69.143.755/0001-41', 1212, 2, 0.2, 'Utilizando 0.200 kg'), ('69.143.755/0001-41', 1313, 2, 4, ''), 
('69.143.755/0001-41', 1414, 2, 4, ''), ('69.143.755/0001-41', 1919, 2, 4, ''), ('69.143.755/0001-41', 
2020, 2, 4, ''), ('69.143.755/0001-41', 1818, 2, 2, 'Utilizado 1 peça de 1/4 mm e 1 peça de 3/8 mm'), 
('69.143.755/0001-41', 1515, 2, 3, 'Cabo PP 3x1/2 mm'), ('69.143.755/0001-41', 1616, 2, 1, 'Suporte de 
ferro 1 PAR'), ('69.143.755/0001-41', 1717, 2, 3, 'Utilizado 3 metros de fita aluminio'), 
('69.143.755/0001-41', 1111, 3, 0.3, 'Utilizando 0.300 kg'), ('69.143.755/0001-41', 1212, 3, 0.5, 
'Utilizando 0.500 kg'), ('69.143.755/0001-41', 1313, 3, 4, ''), ('69.143.755/0001-41', 1414, 3, 4, ''), 
('69.143.755/0001-41', 1919, 3, 4, ''), ('69.143.755/0001-41', 2020, 3, 4, ''), ('69.143.755/0001-41', 
1818, 3, 4, 'Utilizado 2 peça de 1/4 mm e 2 peça de 3/8 mm'), ('69.143.755/0001-41', 1515, 3, 6, 'Cabo 
PP 3x1/2 mm'), ('69.143.755/0001-41', 1616, 3, 1, 'Suporte de ferro 1 PAR'), ('69.143.755/0001-41', 
1717, 3, 5, 'Utilizado 5 metros de fita aluminio');
Instruções de Inserção SQL
14
Consultas SQL
15
• Liste a quantidade de serviços que cada funcionário executou no período entre 01/07/2019 a 
09/07/2019.
• SELECT func.nome, func.cpf, COUNT(DISTINCT serv_exc.num_ordem_servico) AS 
quantidade_servico_executado FROM projeto.servico_executado AS serv_exc INNER JOIN 
projeto.funcionario AS func ON func.cpf = serv_exc.cpf_funcionario WHERE serv_exc.data_hora_visita 
>= '2019-07-01' AND serv_exc.data_hora_visita <= '2019-07-09' GROUP BY func.nome, func.cpf ORDER 
BY func.nome ASC;
Consultas SQL
16
• Liste a quantidade total de material e o valor total do serviço para cada ordem de serviço e ordene 
pelo número da ordem de serviço.
• SELECT sv.num_ordem, SUM(forn_os.quantidade) AS quantidade_total_material, sv.valor_servico AS 
valor_total_servico FROM projeto.fornecimento_material_os AS forn_os INNER JOIN projeto.servico 
AS sv ON sv.num_ordem = forn_os.num_ordem_servico GROUP BY sv.num_ordem ORDER BY 
sv.num_ordem ASC;
Consultas SQL
17
• Liste o nome, telefone, data e hora da visita de todos os cliente que possuem ou não uma visita técnica 
agendada, a consulta deve ser ordenada pelo nome do cliente, data e hora da visita.
• SELECT cli.nome, cli.telefone, vt.data_hora_visita FROM projeto. cliente AS cli LEFT JOIN 
projeto.visita_tecnica AS vt ON vt.nome_cliente = cli.nome AND vt.telefone_cliente = cli.telefone 
ORDER BY cli.nome, vt.data_hora_visita;
Consultas SQL
18
• Liste todos os funcionários que nasceram depois de 1990 e quem moram na cidade de Tauá.
• SELECT func.nome, func.cpf, func.data_nasc FROM projeto.funcionario AS func WHERE func.data_nasc 
>= '1990-01-01' AND func.cidade iLIKE 'Tauá'
Referências Bibliográficas
● ELMASRI , Ramez; NAVATHE, Shamkank B. Sistemas de banco de dados. 6 ed. 
São Paulo: Pearson Addison Wesley, 2011.
19