Trabalho Banco de Dados

Prévia do material em texto

BANCO DE DADOS
Trabalho – Relatório
	Curso:
	
	Aluno(a):
	
	RU:
	
1ª Etapa – Modelagem
Pontuação: 25 pontos.
Dadas as regras de negócio abaixo listadas referentes ao estudo de caso de uma Clínica Médica, elabore o Modelo Entidade-Relacionamento (MER), isto é, o modelo conceitual. O Modelo Entidade-Relacionamento (MER) deve contemplar os seguintes itens:
Entidades;
Atributos;
Relacionamentos;
Cardinalidades;
Chaves primárias;
Chaves estrangeiras.
Uma Clínica Médica necessita controlar os dados das consultas realizadas. Para isso, contratou um profissional de Banco de Dados a fim de modelar o Banco de Dados que armazenará os dados das consultas.
Entidades e Atributos:
- Médico
 - CRM (PK)
 - Especialidade
 - Nome
 - Telefone
 - E-mail
 - Endereço (Rua, Número, Complemento, Bairro, CEP, Cidade, Estado)
- Consulta
 - ID_Consulta (PK)
 - Data
 - Horário
 - CRM_Médico (FK)
 - CPF_Paciente (FK)
- Paciente
 - CPF (PK)
 - Nome
 - Telefone
 - E-mail
 - Endereço (Rua, Número, Complemento, Bairro, CEP, Cidade, Estado)
- Convênio
 - ID_Convênio (PK)
 - Empresa
 - Tipo
 - Vencimento
 - Percentual_Coparticipação
- Médico_Convênio
 - CRM_Médico (PK, FK)
 - ID_Convênio (PK, FK)
- Paciente_Convênio
 - CPF_Paciente (PK, FK)
 - ID_Convênio (PK, FK)
Relacionamentos e Cardinalidades:
- Um médico pode realizar zero ou várias consultas (1:N) - Relacionamento entre Médico e Consulta
- Um paciente pode marcar zero ou várias consultas (1:N) - Relacionamento entre Paciente e Consulta
- Um médico pode atender zero ou vários convênios (N:M) - Relacionamento entre Médico e Convênio (entidade associativa Médico_Convênio)
- Um paciente pode possuir zero ou vários convênios (N:M) - Relacionamento entre Paciente e Convênio (entidade associativa Paciente_Convênio)
2ª Etapa – Implementação
Considere o seguinte Modelo Relacional (modelo lógico) referente ao estudo de caso de uma Livraria:
Com base no Modelo Relacional dado e utilizando a Structured Query Language (SQL) no MySQL Workbench, implemente o que se pede.
Observação: Para testar o Banco de Dados após a implementação, utilize os comandos contidos no arquivo “Trabalho – Populando o Banco de Dados” para popular as tabelas. Tal arquivo contém todos os comandos de inserção dos dados (fictícios) necessários para a realização dos testes.
Pontuação: 25 pontos.
Implemente um Banco de Dados chamado “Livraria”. Após, implemente as tabelas conforme o Modelo Relacional dado observando as chaves primárias e as chaves estrangeiras. Todos os campos de todas as tabelas não podem ser nulos (not null).
Cole o código aqui:
```sql
-- Criação do Banco de Dados
CREATE DATABASE Livraria;
USE Livraria;
-- Criação das Tabelas
CREATE TABLE Cliente (
 ID_Cliente INT PRIMARY KEY,
 Nome VARCHAR(100) NOT NULL,
 Telefone VARCHAR(15) NOT NULL,
 Email VARCHAR(100) NOT NULL
);
CREATE TABLE Editora (
 ID_Editora INT PRIMARY KEY,
 Nome VARCHAR(100) NOT NULL
);
CREATE TABLE Livro (
 ID_Livro INT PRIMARY KEY,
 Titulo VARCHAR(100) NOT NULL,
 Autor VARCHAR(100) NOT NULL,
 Preco DECIMAL(10,2) NOT NULL,
 ID_Editora INT NOT NULL,
 FOREIGN KEY (ID_Editora) REFERENCES Editora(ID_Editora)
);
CREATE TABLE Venda (
 ID_Venda INT PRIMARY KEY,
 Data DATE NOT NULL,
 ID_Cliente INT NOT NULL,
 FOREIGN KEY (ID_Cliente) REFERENCES Cliente(ID_Cliente)
);
CREATE TABLE Item_Venda (
 ID_Venda INT NOT NULL,
 ID_Livro INT NOT NULL,
 Quantidade INT NOT NULL,
 PRIMARY KEY (ID_Venda, ID_Livro),
 FOREIGN KEY (ID_Venda) REFERENCES Venda(ID_Venda),
 FOREIGN KEY (ID_Livro) REFERENCES Livro(ID_Livro)
);
```
Pontuação: 10 pontos.
Implemente uma consulta para listar o quantitativo de livros cadastrados independentemente da editora.
Cole o código e o print resultante da consulta aqui:
```sql
SELECT COUNT(*) AS Quantitativo_Livros FROM Livro;
```
Pontuação: 10 pontos.
Implemente uma consulta para listar o nome dos clientes cadastrados. A listagem deve ser mostrada em ordem crescente.
Cole o código e o print resultante da consulta aqui:
```sql
SELECT Nome FROM Cliente ORDER BY Nome ASC;
```
Pontuação: 10 pontos.
Implemente uma consulta para listar o nome de todas as editoras e os títulos de seus respectivos livros. A listagem deve ser mostrada em ordem decrescente pelo nome das editoras.
Cole o código e o print resultante da consulta aqui:
```sql
SELECT Editora.Nome AS Nome_Editora, Livro.Titulo AS Titulo_Livro FROM Editora JOIN Livro ON Editora.ID_Editora = Livro.ID_Editora ORDER BY Editora.Nome DESC;
```
Pontuação: 10 pontos.
Implemente uma consulta para listar o nome das editoras e a média de preço de seus respectivos livros. Para isso, utilize o comando group by.
Cole o código e o print resultante da consulta aqui:
```sql
SELECT Editora.Nome AS Nome_Editora, AVG(Livro.Preco) AS Media_Preco FROM Editora JOIN Livro ON Editora.ID_Editora = Livro.ID_Editora GROUP BY Editora.Nome;
```
Pontuação: 10 pontos.
Implemente uma consulta para listar o nome de todos os clientes e a quantidade de livros comprados pelos mesmos. Para isso, utilize o comando group by.
Cole o código e o print resultante da consulta aqui:
```sql
SELECT Cliente.Nome AS Nome_Cliente, SUM(Item_Venda.Quantidade) AS Quantidade_Livros FROM Cliente JOIN Venda ON Cliente.ID_Cliente = Venda.ID_Cliente JOIN Item_Venda ON Venda.ID_Venda = Item_Venda.ID_Venda GROUP BY Cliente.Nome;
```
Imagens dos Resultados de Código SQL 
image4.png
image5.png
image1.png
image2.png
image3.png