Banco de Dados Relacional

Atividade Avaliativa Projeto de Banco de Dados Relacional Objetivo Desenvolver um sistema de banco de dados relacional para uma concessionária de veículos focando na estruturação e gerenciamento eficiente das informações O sistema será baseado em um modelo relacional que integrará dados diversos utilizando tabelas chaves primárias e estrangeiras para garantir a integridade e a consistência das informações A implementação permitirá a realização de consultas SQL para a extração de dados relevantes facilitando a análise de desempenho e o controle de operações contribuindo assim para uma gestão mais eficaz e informada da concessionária Cenário Imagine que você está projetando um sistema de gerenciamento capaz de gerenciar de maneira eficaz as operações de venda e manutenção de carros novos e usados O sistema deve integrar funções relacionadas a vendedores clientes carros mecânicos e peças além de garantir a geração de relatórios e históricos de serviços Requisitos Iniciais 1 Venda de Carros Envolvimento de Vendedores e Clientes Um vendedor pode vender muitos carros mas cada carro é vendido por apenas um vendedor Essa regra garante que cada transação de venda seja atribuída a um vendedor específico o que facilita o rastreamento de vendas e comissões além de contribuir para a organização interna da concessionária Um cliente pode comprar muitos carros mas cada carro é comprado por apenas um cliente É importante que o sistema registre as compras de clientes de forma individual permitindo o acompanhamento do histórico de cada cliente com controle sobre os veículos adquiridos 2 Faturamento Um vendedor escreve uma única fatura para cada carro que vende Para cada venda uma fatura específica deve ser gerada garantindo a integridade dos registros financeiros e facilitando auditorias e relatórios de vendas Um cliente recebe uma fatura para cada carro que compra Mesmo que o cliente adquira múltiplos veículos cada carro deve ter uma fatura própria o que possibilita uma visão detalhada de cada transação e um controle mais preciso das finanças 3 Serviços de Manutenção Um cliente pode vir apenas para fazer a manutenção de seu carro O sistema deve permitir que qualquer cliente mesmo que não tenha comprado o carro na concessionária possa usufruir dos serviços de manutenção classificandoo como cliente Quando um cliente leva um ou mais carros para reparo ou manutenção um tíquete de serviço é emitido para cada carro Cada carro que passa por manutenção deve ter um registro específico o que facilita o controle de serviços e permite ao cliente ter um histórico detalhado de manutenções A concessionária mantém um histórico de serviços para cada carro atendido referenciado pelo número de série O sistema deve armazenar o histórico de manutenção de cada carro possibilitando consultas e verificações sobre os serviços prestados e auxiliando na avaliação de depreciação e confiabilidade dos veículos 4 Mecânicos e Manutenção Um carro levado para manutenção pode ser consertado por muitos mecânicos e cada mecânico pode consertar muitos carros O sistema deve permitir a atribuição de vários mecânicos a um mesmo carro durante o processo de manutenção garantindo que o trabalho seja distribuído de forma eficiente e rastreável Um carro pode ou não precisar de peças durante a manutenção Nem toda manutenção exige a utilização de peças e o sistema deve ser flexível o suficiente para registrar tanto serviços que envolvem a substituição de peças quanto os que não envolvem como ajustes técnicos 5 Implicações Técnicas O sistema de gerenciamento da concessionária deve ser projetado para garantir a organização dos dados com tabelas que representem as principais entidades como Vendedores Clientes Carros Faturas Tíquetes de Serviço Mecânicos e Peças Além disso deve permitir a geração de relatórios e facilitar o acompanhamento do histórico de vendas e serviços contribuindo para a eficiência operacional e estratégica Regras de Entregas para o Projeto de Banco de Dados 1 Modelo Conceitual Valor 15 pontos Desenvolva um Diagrama de EntidadeRelacionamento DER que represente visualmente o modelo conceitual do sistema O diagrama deve incluir as entidades seus respectivos atributos e os relacionamentos entre elas destacando as chaves primárias e estrangeiras além de quaisquer restrições necessárias para garantir a integridade dos dados 2 Modelo Lógico Valor 15 pontos Converta o modelo conceitual em um modelo lógico de banco de dados transformando as entidades identificadas em tabelas Defina as chaves primárias PK para garantir a unicidade de cada registro e as chaves estrangeiras FK para representar os relacionamentos entre tabelas 3 Modelo Físico Valor 15 pontos Selecione um Sistema de Gerenciamento de Banco de Dados SGBD adequado com preferência pelo PostgreSQL para implementar o modelo físico Utilize a linguagem SQL para criar as tabelas garantindo que todas as chaves primárias estrangeiras e restrições sejam devidamente implementadas conforme especificado no modelo lógico Certifiquese de definir os tipos de dados e regras de integridade para manter a consistência e a eficiência do banco de dados 4 Consultas SQL Valor 15 pontos Crie consultas SQL para recuperar informações importantes e relevantes para a operação da concessionária As consultas devem cobrir os seguintes cenários Consulta 1 Recuperar todos os carros vendidos por um vendedor específico exibindo os detalhes do carro o nome do cliente e o valor da fatura Consulta 2 Listar todos os serviços de manutenção realizados por um determinado mecânico incluindo a descrição do serviço o carro atendido e o cliente Consulta 3 Obter o histórico completo de manutenção de um carro específico baseado no número de série exibindo todos os serviços realizados e as peças utilizadas se houver Consulta 4 Recuperar o total de vendas realizadas por cada vendedor em um determinado período listando o nome do vendedor e o valor total das vendas Consulta 5 Listar todos os clientes que realizaram serviços de manutenção sem ter comprado um carro na concessionária exibindo os detalhes do serviço e do cliente Consulta 6 Obter o estoque atual de todas as peças disponíveis listando o nome da peça e a quantidade Consulta 7 Obter o número total de carros vendidos por tipo novo ou usado Consulta 8 Listar os mecânicos que realizaram mais de 5 serviços listando o nome do mecânico e a quantidade de serviços Consulta 9 Listar os carros que passaram por manutenção e não utilizaram peças Consulta 10 Obter o total gasto em peças por cada cliente em seus serviços O projeto deve ser entregue em um documento ou apresentação com todas as regras de entregas citadas acima codigo documento nome telefone Cliente CompraPossui Solicita codigo nome telefone Mecanico Realiza Tiquete Manutencao codigo data carrofk cliente fk mecanicos fk detalhamento Conserta Utiliza Peca codigo nome valor estoque Tipo proprietário fk modelo marca chassi codigo Carro Registra venda Vende codigo nome telefone Vendedor Escreve Fatura codigo valor datacompra carro fk vendedor fk cliente fk Recebe Cliente codigo documento nome telefone 11 11 Mecanico codigo nome telefone 11 1n MecanicoTiquete fkMecanicocodigo fkTiqueteManutencaocodigo 0n 1n TiqueteManutencao codigo fkCarrocodigo fkClientecodigo detalhamento dataservico 0n 01 PecaTiquete fkPecacodigo fkTiquetecodigo 0n 0n Peca codigo nome valor 11 1n Carro codigo chassi marca modelo fkClientecodigo Tipo 1n 11 Vendedor codigo nome telefone 11 11 Fatura codigo valor datacompra fkVendedorcodigo fkCarrocodigo fkClientecodigo 01 0n Aluno Disciplina Modelo Conceitual Modelo lógico Modelo Físico CREATE DATABASE Concessionaria USE Concessionaria CREATE TABLE Cliente Codigo INTEGER PRIMARY KEY Nome VARCHAR50 NOT NULL Documento VARCHAR11 NOT NULL Telefone VARCHAR11 NOT NULL CREATE TABLE Mecanico Codigo INTEGER PRIMARY KEY Nome VARCHAR50 NOT NULL Telefone VARCHAR11 NOT NULL CREATE TABLE Carro Codigo INTEGER PRIMARY KEY Chassi VARCHAR17 NOT NULL Marca VARCHAR50 NOT NULL Modelo VARCHAR50 NOT NULL Tipo VARCHAR5 NOT NULL Proprietario INTEGER FOREIGN KEY Proprietario REFERENCES ClienteCodigo CREATE TABLE Vendedor Codigo INTEGER PRIMARY KEY Nome VARCHAR50 NOT NULL Telefone VARCHAR11 NOT NULL CREATE TABLE TiqueteManutencao Codigo INTEGER PRIMARY KEY Detalhamento VARCHAR100 Dataservico DATETIME NOT NULL Carro INTEGER NOT NULL Cliente INTEGER NOT NULL FOREIGN KEY Carro REFERENCES Carrocodigo FOREIGN KEY Cliente REFERENCES Clientecodigo CREATE TABLE MecanicoTiquete Mecanico INTEGER NOT NULL Tiquete INTEGER NOT NULL PRIMARY KEY Mecanico Tiquete FOREIGN KEY Mecanico REFERENCES Mecanicocodigo FOREIGN KEY Tiquete REFERENCES TiqueteManutencaocodigo CREATE TABLE Peca Codigo INTEGER PRIMARY KEY Nome VARCHAR50 NOT NULL Valor FLOAT NOT NULL Estoque INT NOT NULL CREATE TABLE PecaTiquete Peca INTEGER NOT NULL Tiquete INTEGER NOT NULL PRIMARY KEY Peca Tiquete FOREIGN KEY Peca REFERENCES Pecacodigo FOREIGN KEY Tiquete REFERENCES TiqueteManutencaocodigo CREATE TABLE Fatura Codigo INTEGER PRIMARY KEY valor FLOAT NOT NULL Datacompra DATETIME NOT NULL Vendedor INTEGER NOT NULL Carro INTEGER NOT NULL Cliente INTEGER NOT NULL FOREIGN KEY Vendedor REFERENCES VendedorCodigo FOREIGN KEY Carro REFERENCES CarroCodigo FOREIGN KEY Cliente REFERENCES ClienteCodigo INSERT INTO Cliente Codigo Nome Documento Telefone VALUES 1 João Silva 12345678901 11987654321 2 Maria Oliveira 98765432100 21987654322 3 Carlos Souza 65498732100 31987654323 4 Fernanda Mora 329832988 83928492839 INSERT INTO Mecanico Codigo Nome Telefone VALUES 1 Pedro Martins 11976543210 2 Lucas Almeida 21976543211 3 Felipe Castro 31976543212 INSERT INTO Carro Codigo Chassi Marca Modelo Tipo Proprietario VALUES 1 9BWZZZ377VT004251 Volkswagen Gol Novo 1 2 93YZZZ377VT004252 Fiat Uno Usado 2 3 8AWZZZ377VT004253 Chevrolet Celta Novo 3 4 82ID2983JNWIWE3R4 Fiat Uno Usado 4 INSERT INTO Vendedor Codigo Nome Telefone VALUES 1 Ricardo Lima 11965432109 2 Fernanda Rocha 21965432108 3 Ana Costa 31965432107 INSERT INTO TiqueteManutencao Codigo Detalhamento Carro Cliente DataServico VALUES 1 Troca de óleo e filtro 1 1 20241010 2 Alinhamento e balanceamento 2 2 20241011 3 Revisão completa 3 3 20241012 4 Revisão e troca de pneus 4 4 20241015 5 Troca de óleo e filtro 2 2 20241016 6 Troca de óleo e filtro 3 3 20241012 INSERT INTO MecanicoTiquete Mecanico Tiquete VALUES 1 1 2 2 2 3 3 3 3 4 1 4 2 1 24 25 26 INSERT INTO Peca Codigo Nome Valor Estoque VALUES 1 Filtro de óleo 3500 13 2 Pneus 20000 8 3 Velas de ignição 15000 3 4 Descarga 80000 4 INSERT INTO PecaTiquetePeca Tiquete VALUES11 15 16 2 4 INSERT INTO Fatura Codigo valor Datacompra Vendedor Carro Cliente VALUES 1 3500000 20241015 1 1 1 2 2500000 20241016 2 2 2 3 2800000 20241017 3 3 3 Consultas SQL Consulta 1 SELECT CarroMarca CarroModelo CarroChassi ClienteNome Faturavalor FROM Fatura JOIN Carro ON FaturaCarro CarroCodigo JOIN Cliente ON FaturaCliente ClienteCodigo WHERE FaturaVendedor 1 Consulta 2 SELECT TiqueteManutencaoDetalhamento CarroMarca CarroModelo CarroChassi ClienteNome AS Cliente MecanicoNome AS Mecanico FROM MecanicoTiquete JOIN TiqueteManutencao ON MecanicoTiqueteTiquete TiqueteManutencaoCodigo JOIN Carro ON TiqueteManutencaoCarro CarroCodigo JOIN Cliente ON TiqueteManutencaoCliente ClienteCodigo JOIN Mecanico ON MecanicoTiqueteMecanico MecanicoCodigo WHERE MecanicoTiqueteMecanico 1 Consulta 3 SELECT TiqueteManutencaoCodigo TiqueteManutencaoDetalhamento TiqueteManutencaoDataservico PecaNome PecaValor FROM TiqueteManutencao JOIN Carro ON TiqueteManutencaoCarro CarroCodigo LEFT JOIN PecaTiquete ON TiqueteManutencaoCodigo PecaTiqueteTiquete LEFT JOIN Peca ON PecaTiquetePeca PecaCodigo WHERE Carrocodigo 1 Consulta 4 SELECT VendedorNome SUMFaturavalor FROM Fatura JOIN Vendedor ON FaturaVendedor VendedorCodigo WHERE FaturaDatacompra BETWEEN 20241016 AND 20241017 GROUP BY VendedorNome Consulta 5 SELECT ClienteNome ClienteDocumento ClienteTelefone TiqueteManutencaoDetalhamento TiqueteManutencaoDataServico FROM TiqueteManutencao JOIN Cliente ON TiqueteManutencaoCliente ClienteCodigo LEFT JOIN Fatura ON FaturaCliente ClienteCodigo WHERE FaturaCliente IS NULL Consulta 6 SELECT Nome Estoque FROM Peca WHERE Estoque 0 Consulta 7 SELECT CarroTipo COUNTFaturaCarro FROM Fatura JOIN Carro ON FaturaCarro CarroCodigo GROUP BY CarroTipo Consulta 8 SELECT MecanicoNome COUNTMecanicoTiqueteTiquete FROM MecanicoTiquete JOIN Mecanico ON MecanicoTiqueteMecanico MecanicoCodigo GROUP BY MecanicoNome HAVING COUNTMecanicoTiqueteTiquete 5 Grupo 9 SELECT CarroMarca CarroModelo CarroChassi TiqueteManutencaoDetalhamento TiqueteManutencaoDataservico FROM TiqueteManutencao JOIN Carro ON TiqueteManutencaoCarro CarroCodigo LEFT JOIN PecaTiquete ON TiqueteManutencaoCodigo PecaTiqueteTiquete WHERE PecaTiquetePeca IS NULL Grupo 10 SELECT ClienteNome SUMPecaValor FROM TiqueteManutencao JOIN PecaTiquete ON TiqueteManutencaoCodigo PecaTiqueteTiquete JOIN Peca ON PecaTiquetePeca PecaCodigo JOIN Cliente ON TiqueteManutencaoCliente ClienteCodigo GROUP BY ClienteNome