Banco de Dados
Pet Shop
Turma: NF Professora: Maria Lencastre Equipe: Diogo Leal e Pitágoras Vieira
Minimundo: Pet Shop
O Pet Shop mantém o cadastro dos clientes da loja. Cada cliente tem seu CPF, nome, endereço e telefone registrados. Os animais dos clientes podem ser cadastrados, cada animal tem código do animal, tipo de animal e nome. Cada cliente pode ter dependentes. Cada dependente tem o código do dependente, nome, endereço e telefone registrados. É feito também o cadastro dos funcionários, onde se registra o CPF do funcionário, função, nome, endereço, telefone e salário. Cada compra de produto(s) que é feita no pet shop envolve o cliente, o funcionário e os produtos que estão envolvidos na transação. Cada compra registrada contém o valor da compra, a data e o código da compra. Cada produto tem o código do produto, nome, preço, quantidade em estoque e descrição dos produtos registrados. Registram-se também os serviços que o pet shop presta aos clientes, serviços esses cujos animais dos clientes estão envolvidos, como por exemplo, tosa ou banho. Cada serviço envolve os funcionários envolvidos, o cliente e o animal. Cada serviço registra o preço, descrição e código do serviço. Há ainda o registro dos fornecedores dos produtos da pet shop. Um fornecedor pode fornecer mais de um produto. Cada fornecedor tem o código do fornecedor, nome, endereço e telefone registrados.
Modelo Entidade Relacional
Modelo Relacional Cliente (CPF_Cliente, Nome, Telefone, Endereço) Animal (CodAnimal, Nome, Tipo, CPF_Cliente(FK)) Serviço_Animal (CodServiço, CPF_Cliente(FK), CPF_Funcionario(FK), CodAnimal(FK), Descricao, Preco) Funcionario (CPF_Funcionario, Nome, Endereco, Telefone, Funcao) FunçãoFuncionário (Funcao, Salario) Compra (CodCompra, CPF_Cliente(FK), CPF_Funcionario(FK), Valor, Data) Produto (CodProduto, CodFornecedor(FK), Nome, Descricao, Preço, QTD_Estoque) Fornecedor (CodFornecedor, Nome, Endereço, Telefone) Dependente (CPF_Cliente, CodDependente, Nome, Endereço, Telefone) CompraProduto (CodProduto, CodCompra) Serviço_AnimalFuncionario (CPF_Funcionario, CodServiço)
Consultas em SQL
1. Liste todos os códigos de produto, nome de produto e descrição do produto que tenham preço entre 1 e 20. SELECT CodProduto, Nome, Descricao FROM Produto WHERE Preço>1 AND Preço<20 2. Quantas vendas foram feitas pelo vendedor Jorge Araújo? SELECT COUNT(*) FROM Funcionario F, Compra C WHERE F.CPF_Funcionario=C.CPF_Funcionario AND F.Nome=’Jorge Araújo’
3. Liste os nomes dos cliente que tem dependentes que tem nomes iniciados com a letra S. SELECT C.Nome FROM Cliente C, Dependente D WHERE C.CPF_Cliente=D.CPF_Cliente AND D.Nome LIKE ‘ S%’ 4. Quantos empregados recebem o maior salário da tabela de salários? SELECT COUNT(*) FROM Funcionario F, FuncaoFuncionario FF WHERE F.Funcao=FF.Funcao AND FF.Salario = (SELECT MAX(Salario) FROM FuncaoFuncionario) 5. Liste os nomes de todos os animais que tem dono que fez compras com o funcionário Pedro Viana. SELECT A.Nome FROM Animal A, Cliente C, Funcionario F, Compra CO WHERE A.CPF_Cliente=C.CPF_Cliente AND C.CPF_Cliente=CO.CPF_Cliente AND CO.CPF_Funcionario=F.CPF_Funcionario AND F.Nome=’Pedro Viana’
6. Liste os produtos que tem quantidade em estoque maior que 50 ordenados por nome. SELECT Nome FROM Produto WHERE QTD_Estoque>50 ORDER BY Nome 7. Mostre todos os serviços que foram prestados ao animal Paco que tem o dono Fábio e que custaram mais de 100 reais ordenados por código de serviço. SELECT AS.CodServico, AS.CPF_Cliente, AS.CPF_Funcionario, AS.CodAnimal, AS.Descricao, AS.Preco FROM Servico_Animal AS, Animal A, Cliente C, WHERE AS.CPF_Cliente=C.CPF_Cliente AND SA.CodAnimal=A.CodAnimal AND C.Nome=’Fábio’ AND A.Nome=’Paco’ AND AS.Preco>100 ORDER BY AS.CodServico
8. Mostre os nomes de todos os clientes e a descrição dos serviços que receberam, se houver serviços prestados. SELECT C.Nome, AS.Descricao FROM Cliente C LEFT JOIN Servico_Animal SA ON (SA.CPF_Cliente=C.CPF_Cliente) 9. Liste todos os códigos de serviço que estão vinculados ao funcionário Walmir e que não tem preço definido no momento. SELECT SA.CodServico FROM Servico_Animal SA, Funcionario F WHERE AS.CPF_Funcionario=F.CPF_Funcionario AND F.Nome=’Walmir’ AND AS.Preco IS NULL 10. Liste todos os nomes de clientes que estão alfabeticamente entre Alan e Fabrício. SELECT Nome FROM Cliente WHERE Nome BETWEEN ‘Alan’ AND ‘Fabrício’ ORDER
BY Nome
