Slide 7 - Modelagem e Implementação de Banco de Dados com SQL

Prévia do material em texto

MODELAGEM E IMPLEMENTAÇÃO DE
BANCO DE DADOS COM
SQL
Alfredo Boente
alfredo.boente@uva.br
1
Aula 07
Linguagem SQL para Consulta
e Manipulação de Dados
Structure Query Language 
SQL DDL/DML/DQL
Introdução
Quando pretende-se trabalhar com projetos de bancos de dados, precisamos pensar numa estrutura cliente-servidor, pois sempre ocorrem requisições por parte do cliente que são, na sua grande maioria, atendidas pelo servidor de banco de dados.
Neste contexto, iremos utilizar o MySQL como opção de SGBD para realizar as operações de Esquemas e Instâncias de bancos de dados, com base de dados universal, ou seja, SQL.
Structure Query Language 
SQL DDL/DML/DQL
Introdução
Hoje, a melhor solução (SGBD) para a definição, manipulação, consulta, controle e transações de banco de dados é trabalhar com Oracle, segundo a SQL Magazine. 
No entanto, conforme indica as boas práticas, buscando uma solução viável, eficiente e, acima de tudo, gratuita, utilizaremos o MySQL para trabalhar com Banco de Dados Universal.
Structure Query Language 
SQL DDL/DML/DQL
Criando a Modelagem Relacional:
Vamos criar um novo modelo 
 File... 
 New Model...
- Depois vamos iniciar 
a criação do Modelo 
Relacional
 add new diagram...
Structure Query Language 
SQL DDL/DML/DQL
Criando a Modelagem Relacional:
Vamos criar as tabelas do modelo 
- Pessoas
- Telefones
- Projetos
- Projetos_X_Pessoas (EER)
Nota: Depois de criado o modelo lógico, você poderá exportar para criar um arquivo desse modelo relacional na versão png.
Structure Query Language 
SQL DDL/DML/DQL
Criando um Banco de Dados:
Na barra da esquerda, troca-se o Navegador de Gerenciamento (Management) para Esquemas de Bancos de Dados (SCHEMAS).
No editor de QUERY (área central), vamos usar o comando SQL-DDL para a criação do Banco de Dados, Cadastro, neste caso, e em seguida, pressione o botão para realizar a criação propriamente dita (veja na barra inferior):
CREATE DATABASE Cadastro;
Nota: Sugiro trabalhar na configuração de ter sempre aberto, além do Editor Query, também a barra de Navegação (a esquerda) e a barra de Saída (a baixo). 
Structure Query Language 
SQL DDL/DML/DQL
Criando Relações no Banco de Dados:
Nota: Depois de atualizar, ao clicar duas vezes sobre tabelas, já aparecerá a tabela Pessoas devidamente criada e associada ao banco de dados Cadastro.
ATENÇÃO:
Você pode iniciar a criação de uma nova tabela SEM ter clicado (selecionado) o nome do banco de dados sob o qual a tabela será criada. 
Structure Query Language 
SQL DDL/DML/DQL
Criando Relações no Banco de Dados:
No entanto, neste caso, antes de digitar os comandos para a criação da tabela, você deverá colocar o arquivo desejado em uso, no editor Query, dessa forma:
USE Cadastro;
Em seguida, você então estaria criando a tabela Pessoas, no editor Query:
CREATE TABLE Pessoas(
id_pes int primary key not null auto_increment,
nome varchar(40) not null,
email varchar(20));
9
Structure Query Language 
SQL DDL/DML/DQL
Visualizando a Estrutura da Relação:
Visualizando a estrutura da tabela Pessoas, no editor Query:
DESCRIBE Pessoas;
Teremos como resposta:
10
Structure Query Language 
SQL DDL/DML/DQL
Esquema de Banco de Dados:
Vamos agora criar as demais tabelas do modelo relacional:
CREATE TABLE Projetos(
id_proj int primary key not null,
nome varchar(20) not null,
descricao varchar(40) not null,
data date);
CREATE TABLE Telefones(
id_tel int primary key not null auto_increment,
telefone varchar(13) not null);
11
Structure Query Language 
SQL DDL/DML/DQL
Esquema de Banco de Dados:
Vamos agora criar as demais tabelas do modelo relacional:
CREATE TABLE Projetos_X_Pessoas(
id_pxp int primary key not null auto_increment,
id_pes int,
id_proj int,
foreign key (id_pes) references Pessoas(id_pes),
foreign key (id_proj) references Projetos(id_proj));
12
Structure Query Language 
SQL DDL
ATIVIDADE EXTRA CLASSE
 
ATIVIDADE EXTRA CLASSE
 
13
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
Vamos criar um banco de dados chamado Banco, só que dessa ver, vamos embutir alguns aprimoramentos (definir cláusulas padrões para aceitação de acentuação de palavras [utf8] segundo default Português-Brasil. Para tanto, utilizaremos como default: (1) character set utf8 e (2) collate utf8_general_ci.
CREATE DATABASE Banco
default character set utf8
default collate utf8_general_ci;
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
Em seguida, criaremos a tabela Contas, Clientes e Contas_X_Clientes (EER) com alguns Constraints de Configurações aplicáveis.
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
CREATE TABLE Clientes(
cod_cli tinyint not null,
nome varchar(40) not null,
cpf int not null,
dt_nasc date not null,
nacional varchar(20) default ‘Brasil’,
sexo enum (‘F’, ‘M’),
email varchar(20),
tel varchar(13),
cel varchar(13),
primary key (cod_cli))
default charset = utf8;
Structure Query Language
SQL DDL/DML/DQL
Expandindo Conhecimento (Tipos de Dados):+
Structure Query Language
SQL DDL/DML/DQL
Expandindo Conhecimento (Tipos de Dados):+
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
CREATE TABLE Contas(
num_conta int not null auto_increment,
tipo varchar(15) not null,
categ enum (‘Especial’, ‘Comum’),
saldo decimal (9, 2),
Primary key (num_conta))
default charset = utf8;
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
CREATE TABLE Projetos_X_Pessoas(
id_cxc int not null auto_increment,
cod_cli tinyint,
num_conta int,
primary key (id_cxc),
foreign key (cod_cli) references Clientes(cod_cli),
foreign key (num_conta) references Contas(num_conta));
Structure Query Language 
SQL DDL/DML/DQL
Instância de Banco de Dados:
Vamos inserir registros na Tabela:
INSERT INTO 
(campo1, campo2, ..., campon)
VALUES
(‘valor1’, ‘valor2’, ..., ‘valorn’);
INSERT INTO Clientes
(cod_cli, nome, cpf, dt_nasc, nacional, sexo, email, tel, cel)
VALUES
(‘11’, ‘Ana Lee’, ‘123234101’, ‘1995-06-22’, ‘Brasileira’, ‘F’, ‘lee.ana@gmail.com’, ‘(21) 98526-0221’, ‘(21) 3506-2009’);
21
Structure Query Language 
SQL DDL/DML/DQL
Instância de Banco de Dados:
Agora vamos incluir duas tuplas, uma para cada operação, na relação Contas. Note que num_conta é AUTO_INCREMENT e, portanto, não requer preenchimento de dados. Logo:
INSERT INTO Contas 
(tipo, categ, saldo)
VALUES
(‘Corrente’, ‘Especial’, ‘2598.55’);
22
Structure Query Language 
SQL DDL/DML/DQL
Instância de Banco de Dados:
Agora vamos incluir duas tuplas, uma para cada operação, na relação Contas. Note que num_conta é AUTO_INCREMENT e, portanto, não requer preenchimento de dados. Logo:
INSERT INTO Contas
(tipo, categ, saldo)
VALUES
(‘Poupança’, ‘Comum’, ‘1050.38’);
23
Structure Query Language 
SQL DDL/DML/DQL
Instância de Banco de Dados:
Você pode inserir mais de um registro ao mesmo tempo, ou seja, num mesmo comando de inserção. Veja:
INSERT INTO Contas
(tipo, categ, saldo)
VALUES
(‘Corrente’, ‘Especial’, ‘2598.55’),
(‘Poupança’, ‘Comum’, ‘1050.38’),
(‘Poupança’, ‘Especial’, ‘2000.99’),
(‘Corrente’, ‘Comum’, ‘680.59’);
24
Consultando Registros na Tabela:
SELECT * FROM ;
SELECT * FROM Contas;
Nota: Podemos realizar diversos tipos de consultas, apresentando pequenas variações na instrução. 
Structure Query Language 
SQL DDL/DML/DQL
25
Consultando Registros na Tabela:
Veja os exemplos a seguir:
a) Consultar todas as tuplas da relação Contas, cuja categoria seja ‘Especial’:
SELECT * FROM Contas
WHERE categ=‘Especial’;
b) Consultar todas as tuplas da relação Contas, cujos saldos sejam maior que 1000 e menor que 3000.
SELECT * FROM Contas
WHERE saldo > 1000 and saldosexo=‘F’ or nacional‘Brasileira’;
d) Selecionar nome, telefone e celular de todas as tuplas da relação Clientes, cujo sexo seja Masculino.
SELECT nome, tel, cel FROM Clientes
WHERE sexo=‘M’;
Structure Query Language 
SQL DDL/DML/DQL
27
Consultando Registros na Tabela:
e) Selecionar número da conta e o saldo das tuplas da relação Contas, cujo tipo seja Corrente e cuja categoria seja igual a Especial.
SELECT num_conta, saldo FROM Contas
WHERE tipo=‘Corrente’ and categ=‘Especial’;
f) Consultar todas as tuplas da relação Clientes, cujo nome inicie com a letra “A”:
SELECT * FROM Clientes
WHERE nome like ‘A%’;
Structure Query Language 
SQL DDL/DML/DQL
28
Consultando Registros na Tabela:
g) Selecionar nome e telefone de todas as tuplas da relação Clientes ordenado ascendentemente pelo nome.
SELECT nome, tel FROM Clientes
ORDER BY nome;
h) Selecionar nome, telefone e celular de todas as tuplas da relação Clientes, cujo sexo seja Feminino, ordenado descendentemente pelo nome.
SELECT nome, tel, cel FROM Clientes
WHERE sexo=‘F’
ORDER BY nome DESC;
Structure Query Language 
SQL DDL/DML/DQL
29
Consultando Registros na Tabela:
i) Selecionar e somar todas as quantidades como ‘Quantidade em Estoque’ das tuplas da relação Produtos, agrupando pelo ‘Tipo’.
SELECT Tipo, SUM(Quantidade) AS 'Quantidade em Estoque'
FROM Produtos
GROUP BY Tipo;
j) Selecionar o número de empresas de cada país da relação Empresas, agrupadas por país.
SELECT Pais, COUNT(*) AS Tot_Empresas
FROM Empresas
GROUP BY Pais;
Structure Query Language 
SQL DDL/DML/DQL
30
Consultando Registros na Tabela:
k) Selecionar todas as tuplas em ordem decrescente pelo Total, da relação Vendas, agrupando por ‘Linha_Produto’, calculando o Preco_Medio a partir do Preco_Unit.
SELECT Linha_Produto,
 AVG(Preco_Unit) AS Preco_Medio,
FROM Vendas
GROUP BY Linha_Produto
ORDER BY Total DESC;
Structure Query Language 
SQL DDL/DML/DQL
31
Consultando Registros na Tabela:
l) Selecionar e somar todas as quantidades como ‘Quantidade em Estoque’ das tuplas da relação Produtos, agrupando pelo ‘Tipo’, imprimindo as tuplas cujo somatório da Quantidade deja maior que 200.
SELECT Tipo, SUM(Quantidade) AS 'Quantidade em Estoque'
FROM Produtos
GROUP BY Tipo
HAVING SUM(Quantidade) > 200;
Structure Query Language 
SQL DDL/DML/DQL
32
Consultando Registros na Tabela:
m) Selecionar o maior ‘Preco’ como ‘MAIOR_PRECO’ das tuplas da relação Produtos.
SELECT
 MAX(Preco) as MAIOR_PRECO
FROM
 Produtos;
Structure Query Language 
SQL DDL/DML/DQL
33
Consultando Registros na Tabela:
n) Selecionar o menor ‘Preco’ como ‘MENOR_PRECO’ das tuplas da relação Produtos.
SELECT
 MIN(Preco) as MENOR_PRECO
FROM
 Produtos;
Structure Query Language 
SQL DDL/DML/DQL
34
Consultando Registros na Tabela:
o) Selecionar os e-mails de todas as tuplas da relação Cliente, sem que haja ocorrência de duplicidades (neste caso, e-mails repetidos).
SELECT
 DISTINCT email
FROM
 Cliente;
Structure Query Language 
SQL DDL/DML/DQL
35
Consultando Registros na Tabela:
p) Selecionar os e-mails de todas as tuplas da relação Cliente, sem que haja ocorrência de duplicidades (neste caso, e-mails repetidos).
SELECT
 DISTINCT email
FROM
 Cliente;
Structure Query Language 
SQL DDL/DML/DQL
36
Structure Query Language 
SQL DDL/DML/DQL
ATIVIDADE EXTRA CLASSE
 
ATIVIDADE EXTRA CLASSE
 
37
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
1. Tomando como base o banco de dados Cadastro, considerando o modelo relacional abaixo, faça o que se pede:
a) Incluir 10 pessoas
b) Cadastrar 5 projetos
c) Listar todas as pessoas cadastradas 
d) Pesquisar todos os projetos inclusos
e) Selecionar todas as tuplas da tabela
pessoas, ordenado ascendentemente 
pelo nome
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
2. Tomando como base o banco de dados Banco, considerando o modelo relacional abaixo, faça o que se pede:
a) Incluir 10 clientes
b) Cadastrar 15 contas
c) Listar todos os clientes da relação cliente em ordem descendente por nome 
d) Pesquisar todas as tuplas de contas
cujo tipo seja Corrente e cuja catego
ria seja igual a Comum, em Ordem 
ascendente pelo tipo da conta
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
2. Tomando como base o banco de dados Banco, considerando o modelo relacional abaixo, faça o que se pede:
e) Selecionar todas as tuplas da tabela clientes, para todos os clientes do sexo Feminino, e cujo nome inicie com “Mari...”
f) Selecione todos os e-mails das tuplas da tabela clientes, sem que haja ocorrências de duplicidades.
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
3. Considere o seguinte modelo Relacional de Banco de Dados:
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
Com base no modelo relacional apresentado, faça o que se pede:
a) Crie um banco de dados chamado LOCAÇÃO
b) Crie todas as relações do modelo: Clientes, Dependentes, Endereços, Carros e Locação
c) Cadastre 10 Clientes com pelo menos dois dependentes cada
d) Os endereços são associados diretamente aos clientes já cadastrados
e) Registre um cadastro com 15 carros (mínimo)
f) Cadastre pelo menos 5 locações
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
Com base no modelo relacional apresentado, faça o que se pede:
g) Listar todas as locações cadastradas
h) Selecionar nome, telefone, celular e e-mail, de todos os Clientes, ordenados ascendentemente pelo nome
i) Pesquisar todas as alocações realizadas cujo preço seja maior que R$1.000,00
j) Consultar todos os carros da relação Carros cujo ano seja maior ou igual a 2023, imprimindo 
Mais Consultas SQL:
Situação: Selecionar código e nome das tuplas da tabela CLIENTES, data e valor das tuplas da tabela PEDIDOS onde o código da tabela CLIENTES seja igual ao COD_CLI da tabela PEDIDOS.
SELECT A.codigo, A.nome, B.data, B.valor 
FROM CLIENTES A, PEDIDOS B 
WHERE A.codigo = B.cod_cli;
Structure Query Language 
SQL DDL/DML/DQL
44
Mais Consultas SQL:
Situação: Selecione código da tabela Produtos e o código da tabela Componentes, comparando linha a linha, tomando como base código do produto e código do Componente.
SELECT A.codigo, B.codigo 
FROM PRODUTOS as A 
INNER JOIN COMPONENTES as B 
ON (A.codigo = B.codigo);
Structure Query Language 
SQL DDL/DML/DQL
45
Mais Consultas SQL:
Situação: Selecionar código e nome das tuplas da tabela CLIENTES, código e nome das tuplas da tabela FUNCIONARIOS buscando unificar o resultado.
SELECT codigo, nome 
 FROM CLIENTES 
UNION SELECT codigo, nome 
FROM FUNCIONARIOS;
Structure Query Language 
SQL DDL/DML/DQL
46
Mais Consultas SQL:
Situação: Verificar o número de tuplas da tabela CLIENTES tomando como base/referência o campo código.
SELECT COUNT(codigo) 
FROM CLIENTES;
Structure Query Language 
SQL DDL/DML/DQL
47
Alterando Dados do Registro:
Update 
Set col1=valor1, col2=valor2, ..., coln=valorn
Where ;
Situação: Atualizar a cidade para “Niterói” e uf para “RJ”, do registro cujo código seja 20, da tabela CLIENTES.
UPDATE CLIENTES 
SET cidade=‘Niteroi’, uf=‘RJ’
WHERE cod_ref=20;
Structure Query Language 
SQL DDL/DML/DQL
48
Alterando Dados do Registro:
Situação: Atualizar o valor com 0.00, para os registros da tabela ITENS, cujo valor esteja NULO.
UPDATE ITENS 
SET valor=0.00
WHERE valor Is NULL;
Structure Query Language 
SQL DDL/DML/DQL
49
Alterando Dados do Registro:
Situação: Atualizar o campo ativo com “N”, da tabela VENDAS, onde a data de venda esteja compreendida entre 01/01/2023 e 30/04/2023.
UPDATE VENDAS 
SET ativo=‘N’
WHERE dt_venda 
BETWEEN ‘01.01.2023’ And ‘30.04.2023’;
Structure Query Language 
SQL DDL/DML/DQL
50
Alterando Dados do Registro:
Situação: Atualizar o tipo para “H”, ativo para “S” e código do fornecedor para 100, onde o código de referência for 1, 5, 10 e 15.
UPDATE PRODUTOS 
SET tipo=‘H’, ativo=‘S’, cod_forn=100
WHERE cod_ref In(1, 5, 10, 15);
Structure Query Language 
SQL DDL/DML/DQL
51
Removendo Registros da Tabela:
Delete From 
Where ;
Situação: Apagar da tabela CLIENTES todos os registros cujo sexo seja igual a “M”.
DELETE FROM CLIENTES
WHERE sexo=‘M’;
Structure Query Language 
SQL DDL/DML/DQL
52
Removendo Registros da Tabela:
Situação: Apagar os registros da tabela PRODUTOS onde o tipo seja igual a 1.
DELETE FROM PRODUTOS
WHERE tipo=1;
Situação: Apagar os registros da tabela VENDAS onde a data de venda estiver compreendida entre 01/01/2023 e 30/04/2023.
DELETE FROM VENDAS
WHERE data_venda
BETWEEN ‘01.01.2023’ And ‘30.04.2023’;
Structure Query Language 
SQL DDL/DML/DQL
53
Removendo Registros da Tabela:
Situação: Apagar todos os registros da tabela DEVEDORES.
DELETE FROM DEVEDORES;
Structure Query Language 
SQL DDL/DML/DQL
54
Structure Query Language 
SQL DDL/DML/DQL
ATIVIDADE EXTRA CLASSE
 
ATIVIDADE EXTRA CLASSE
 
55
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
Faça o que se pede:
a) Modelo Conceitual de banco de dados (ESCOLA);
b) Modelo Relacional de banco de dados ESCOLA;
b) Esquema de banco de dados (ESCOLA);
c) Relações vinculadas ao banco de dados ESCOLA, denominadas: PROFESSORES e ALUNOS;
d) Instanciar as relações PROFESSORES e ALUNOS, incluindo 5 tuplas em cada uma;
e) Faça uma consulta de dados a partir de um critério de seleção criado por você;
Structure Query Language
SQL DDL/DML/DQL
Atividade Dirigida
Faça o que se pede:
f) Altere o telefone para 98226-4508, do registro da tabela PROFESSORES onde o código for igual a 3;
g) Listar todos os registros da tabela PROFESSORES;
h) Incluir mais 2 estudantes na tabela ALUNOS;
i) Apagar o registro 5, da tabela PROFESSORES;
j) Listar todos os registros da tabela ALUNOS;
k) Consultar nomes e telefones de todos os professores da tabela PROFESSORES, cuja disciplina seja Matemática;
l) Listar o total de alunos da tabela ALUNOS.
Structure Query Language 
SQL DDL/DML/DQL
ATIVIDADE EXTRA CLASSE
 
ATIVIDADE EXTRA CLASSE
 
58
Structure Query Language 
SQL DDL/DML/DQL
Exercícios:
1) O professor da disciplina atribui exercícios de modelagem e implementação de banco de dados aos alunos
2) O professor da disciplina atribui exercícios de modelagem e implementação de banco de dados aos alunos
3) O professor da disciplina atribui exercícios de modelagem e implementação de banco de dados aos alunos
image1.png
image2.png
image3.png
image4.png
image5.png
image6.png
image7.png
image8.png
image9.png
image10.png
image11.png
image12.png
image13.png
image14.png
image15.png
image16.png
image17.png
image18.png
image19.png
image20.png
image21.png
image22.png
image23.png
image24.png
image25.png
image26.png
image27.png
image28.png
image29.png
image30.png
image31.png
image32.png