Atividade Contextualizada de Banco de Dados (1)

Prévia do material em texto

UNINASSAU 
ANÁLISE e DESENVOLVIMENTO de SISTEMAS 
Banco de Dados 
Marcelo Silva Lorenço - 01503244 
Atividade Contextualizada 
Enunciado: 
Uma equipe de desenvolvimento, para implantação de um banco de dados, se 
deparou com o seguinte cenário: uma agência de locação de veículos possui toda a 
organização do negócio, como controle de retiradas e recebimento dos veículos, 
lavagens e manutenção, lançamentos financeiros, dados de clientes e veículos 
registrados em planilhas eletrônicas, além de documentos de textos, nos 
 quais, diversas vezes, ocorrem problemas de integridades ou 
inconsistências nas informações. 
Todos os funcionários envolvidos na locação possuem acesso irrestrito aos 
documentos e planilhas, independentemente do seu setor, para consultas e 
atualização dos registros referentes a suas atividades no negócio. Portanto, não há 
qualquer separação ou restrição para acesso a essas informações, que são 
compartilhadas e que deveriam ser melhor geridas. 
Após o devido estudo de viabilidade e levantamento dos requisitos, ficou 
acertado que um novo projeto de reestruturação e implantação de um banco de dados 
será implementado. O projeto contemplará todas as fases de banco de dados do 
modelo conceitual, passando pelo lógico, até o modelo físico, para organização e 
manipulação geral dos principais cadastros e eventos envolvidos no negócio, além do 
controle de usuários e níveis de acesso e emprego de restrica0 de integridade para 
qualidade das informações manipuladas. As entidades importantes levantadas foram: 
Clientes, veículos, Funcionários, Movimento (aluguel, retirada e devolução do veículo) 
e Oficina (Lavagem e manutenção dos veículos). 
A partir de tais informações, elabore um projeto de banco de dados completo, 
com toda a documentação necessária, com diagramas para o modelo conceitual, 
modelo lógico e scripts para o modelo físico e prints constatando sua implementação 
junto ao banco de dados, incluindo aspectos de segurança e restrição de integridade 
para os dados do banco. 
1 - Deve ser apresentado um projeto completo do banco de dados, com os modelos 
conceitual, lógico e físico bem definidos, além de implementação de aspectos de 
restrição de integridade, e segurança do banco de dados. 
2 - O projeto deverá contemplar a criação de, no mínimo, cinco usuários com 
privilégios diferentes para acessarem o banco de dados criado. 
3 - As tabelas deverão ser populadas com, no mínimo, cinco registros cada uma. 
4 - Para o modelo conceitual, deve ser entregue uma proposta de DER simplificado 
sem a necessidade de indicar todos os atributos. 
5 - O modelo Lógico deve ser representado em um diagrama com tabelas que 
demonstrem todos detalhes dos atributos, destacando tipos de campos, chaves 
primárias e estrangeiras, relacionamentos com cardinalidades bem definidas para as 
tabelas que necessitarem, além do esquema relacional de todas as tabelas. 
6 - O modelo físico deve contemplar todos os scripts SQL de todos objetos envolvidos, 
incluindo prints das telas dos objetos populados no SGBD, para formar toda a 
documentação banco de dados proposto 
 
 
MODELO CONCEITUAL 
 
 
 
 
MODELO LÓGICO 
 
 
 
 
 
 
 
 
 
 
 
MODELO FÍSICO 
 
● Scripts de criação das tabelas 
 
CREATE TABLE Cliente ( 
 CPF BIGINT PRIMARY KEY, 
 Nome VARCHAR(100) NOT NULL, 
 Idade INT NOT NULL 
); 
 
CREATE TABLE Veiculo ( 
 Chassi VARCHAR(30) PRIMARY KEY, 
 Placa VARCHAR(100) NOT NULL, 
 Modelo VARCHAR(100) NOT NULL, 
 Cor VARCHAR(100) NOT NULL 
); 
 
CREATE TABLE Funcionario ( 
 CPF BIGINT PRIMARY KEY, 
 Nome VARCHAR(100) NOT NULL, 
 Idade INT NOT NULL 
); 
 
CREATE TABLE Oficina ( 
 Identificador INT PRIMARY KEY, 
 Descricao VARCHAR(100) NOT NULL, 
 Data DATE NOT NULL, 
 Chassi VARCHAR(30), 
 FOREIGN KEY (Chassi) REFERENCES Veiculo(Chassi) 
); 
 
CREATE TABLE Movimento ( 
 Identificador INT PRIMARY KEY, 
 Descricao VARCHAR(100) NOT NULL, 
 Data DATE NOT NULL, 
 CPF_Cliente BIGINT, 
 CPF_Funcionario BIGINT, 
 Chassi VARCHAR(30), 
 FOREIGN KEY (CPF_Cliente) REFERENCES Cliente(CPF), 
 FOREIGN KEY (CPF_Funcionario) REFERENCES Funcionario(CPF), 
 FOREIGN KEY (Chassi) REFERENCES Veiculo(Chassi) 
); 
 
 
 
 
 
 
● Script de povoamento de tabela 
 
INSERT INTO Cliente (CPF, Nome, Idade) VALUES 
(12345678901, 'João da Silva', 30), 
(98765432101, 'Maria Souza', 25), 
(45678912301, 'Carlos Oliveira', 45), 
(78901234501, 'Ana Santos', 28), 
(56789012301, 'Pedro Pereira', 22); 
 
INSERT INTO Veiculo (Chassi, Placa, Modelo, Cor) VALUES 
('ABC123456789', 'XYZ1234', 'Carro A', 'Vermelho'), 
('DEF987654321', 'MNO5678', 'Carro B', 'Azul'), 
('GHI456789123', 'PQR9012', 'Carro C', 'Verde'), 
('JKL789012345', 'STU3456', 'Carro D', 'Preto'), 
('MNO567890123', 'VWX7890', 'Carro E', 'Branco'); 
 
INSERT INTO Funcionario (CPF, Nome, Idade) VALUES 
(11111111111, 'Lucas Lima', 28), 
(22222222222, 'Ana Rodrigues', 35), 
(33333333333, 'Paulo Souza', 40), 
(44444444444, 'Mariana Silva', 22), 
(55555555555, 'Ricardo Santos', 29); 
 
INSERT INTO Oficina (Identificador, Descricao, Data, Chassi) VALUES 
(1, 'Troca de óleo', '2023-08-10', 'ABC123456789'), 
(2, 'Reparo na suspensão', '2023-07-25', 'DEF987654321'), 
(3, 'Substituição de pneus', '2023-09-05', 'GHI456789123'), 
(4, 'Reparo na pintura', '2023-08-15', 'JKL789012345'), 
(5, 'Manutenção geral', '2023-09-20', 'MNO567890123'); 
 
 
INSERT INTO Oficina (Identificador, Descricao, Data, Chassi) VALUES 
(1, 'Troca de óleo', '2023-08-10', 'ABC123456789'), 
(2, 'Reparo na suspensão', '2023-07-25', 'DEF987654321'), 
(3, 'Substituição de pneus', '2023-09-05', 'GHI456789123'), 
(4, 'Reparo na pintura', '2023-08-15', 'JKL789012345'), 
(5, 'Manutenção geral', '2023-09-20', 'MNO567890123'); 
 
INSERT INTO Movimento (Identificador, Descricao, Data, CPF_Cliente, 
CPF_Funcionario, Chassi) VALUES 
(1, 'Aluguel', '2023-08-10', 12345678901, 11111111111, 'ABC123456789'), 
(2, 'Retirada', '2023-07-25', 98765432101, 22222222222, 'DEF987654321'), 
(3, 'Devolução', '2023-09-05', 45678912301, 33333333333, 'GHI456789123'), 
(4, 'Aluguel', '2023-08-15', 78901234501, 44444444444, 'JKL789012345'), 
(5, 'Devolução', '2023-09-20', 56789012301, 55555555555, 'MNO567890123'); 
 
● Script de criação de triggers: Essa triggers foram criadas para cada linha 
removida de uma tabela remova as linhas relacionadas nas demais tabelas do 
banco de dados. 
 
DELIMITER // 
CREATE TRIGGER cliente_delete_trigger AFTER DELETE ON Cliente FOR 
EACH ROW 
BEGIN 
 DELETE FROM Movimento WHERE CPF_Cliente = OLD.CPF; 
END; 
// 
DELIMITER ; 
 
DELIMITER // 
CREATE TRIGGER funcionario_delete_trigger AFTER DELETE ON Funcionario 
FOR EACH ROW 
BEGIN 
 DELETE FROM Movimento WHERE CPF_Funcionario = OLD.CPF; 
END; 
// 
DELIMITER ; 
 
DELIMITER // 
CREATE TRIGGER veiculo_delete_trigger AFTER DELETE ON Veiculo FOR 
EACH ROW 
BEGIN 
 DELETE FROM Movimento WHERE Chassi = OLD.Chassi; 
 DELETE FROM Oficina WHERE Chassi = OLD.Chassi; 
END; 
// 
DELIMITER ; 
 
● Script criação de usuários com privilégios diferentes. 
 
-- Criar usuario administrador 
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'qP7#rL9&m'; 
GRANT ALL PRIVILEGES ON Locacao.* TO 'admin'@'localhost'; 
 
-- Criar usuario com privilégios de cliente 
CREATE USER 'cliente'@'localhost' IDENTIFIED BY 'xT2@nZ5*s'; 
GRANT SELECT ON Locacao.Veiculo TO 'cliente'@'localhost'; 
 
-- Criar usuario com privilégios de funcionario 
CREATE USER 'funcionario'@'localhost' IDENTIFIED BY 'dF6$wH8%p'; 
GRANT SELECT, INSERT, UPDATE ON Locacao.* TO 'funcionario'@'localhost'; 
 
-- Criar usuario com privilégios de gerente administrativo 
CREATE USER 'gerente_admin'@'localhost' IDENTIFIED BY 'jK4#vB1^q'; 
GRANT SELECT, INSERT, UPDATE ON Locacao.* TO 
'gerente_admin'@'localhost'; 
GRANT DELETE ON Locacao.Funcionario TO 'gerente_admin'@'localhost'; 
GRANT DELETE ON Locacao.Veiculo TO 'gerente_admin'@'localhost'; 
 
-- Criar usuario com privilégios de gerente operacionalCREATE USER 'gerente_op'@'localhost' IDENTIFIED BY 'mG3*yD0#t'; 
GRANT SELECT, INSERT, UPDATE ON Locacao.* TO 'gerente_op'@'localhost'; 
GRANT DELETE ON Locacao.Veiculo TO 'gerente_op'@'localhost'; 
 
 
 
 
REFERÊNCIAS BIBLIOGRÁFICAS 
 
DEVMIDIA. Primeiros Passos no MySQL. Disponível em: 
<https://www.devmedia.com.br/primeiros-passos-no-mysql/28438>. Acesso em: 31 
de agosto de 2023. 
 
CARLOS. Como Criar Usuário MySQL e Conceder Privilégios: Guia para Iniciantes. 
Hostinger Tutoriais. Disponível em: <https://www.hostinger.com.br/tutoriais/como-
criar-usuario-mysql-e-conceder-privilegios>. Acesso em: 31 de agosto de 2023. 
 
ORACLE. Trigger Syntax and Examples. MySQL 8.0 Reference Manual. Disponível 
em: <https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html>. Acesso em: 31 
de agosto de 2023.