Álgebra Relacional e Consultas SQL

Prévia do material em texto

1
 
 
Aula de Álgebra Relacional e consultas SQL. 
Dadas as seguintes tabelas: 
 
pr nome 
100 
200 
JOÃO 
MARIA 
 
codigo nome creditos 
10 
20 
30 
Banco de Dados 
Matemática 
Linguagem C 
4 
2 
4 
 
pr codigo_disciplina nota faltas 
100 
200 
100 
200 
200 
10 
10 
20 
20 
30 
8 
6 
10 
4 
8 
4 
20 
0 
12 
4 
 
 ALGEBRA RELACIONAL 
 
 A Álgebra Relacional foi desenvolvida a partir de modelo de banco de dados relacional, e 
trata-se de uma linguagem de consulta Procedural. 
 Existem cinco operações básicas na álgebra relacional: Seleção, Projeção, Produto 
Cartesiano, União e Diferença entre conjuntos. Através da utilização dessas cinco operações, é 
possível realizar qualquer consulta num banco de dados. 
 Todas essas operações produzem uma nova relação como resultado. Devemos entender por 
relação, toda a tabela que venha a compor o banco de dados. 
 
 SELEÇÃO 
 
 A operação de Seleção () seleciona tuplas (linhas da relação) que satisfazem um 
determinado predicado (condição). 
 Sintaxe: 
 (relação) 
  predicado 
 
A operação de seleção é chamada de unária, pois opera em apenas uma relação. Em 
geral são permitidos os usos dos seguintes sinais de comparação no predicado: 
=, >=, <, >, <= e <>, nos predicados de seleção. 
Também é permitida a combinação de vários predicados compondo um predicado maior 
de seleção, com a utilização dos conectores lógicos:  (and) e  (or). 
 
Exemplo: Encontrar as linhas(tuplas) das disciplinas com 4 créditos 
 
 (diciplina) 
  creditos = 4 
10 
30 
Banco de Dados 
Linguagem C 
4 
4 
 
aluno 
 historico 
disciplina 
 2
 
 
SELECT tabela.atributo-1, tabela.atributo-2, ..., tabela.atributo-n 
 * (para todos os atributos) 
FROM tabela-1 [, tabela-2, ... tabela-n] 
[WHERE predicado(condição)] 
 
SELECT * 
FROM disciplina 
WHERE disciplina.creditos = 4 
 
PROJEÇÃO 
 
 A operação de projeção (), que também é uma operação unária, permite selecionar atributos 
de uma relação, gerando uma nova relação com os atributos selecionados. 
 
 Sintaxe: 
 (relação) 
  atributos 
 
 Exemplo: Encontrar os nomes e os créditos de todas as disciplinas da tabela discipli na 
 (disciplina) 
  nome, creditos 
Banco de Dados 
Matemática 
Linguagem C 
4 
2 
4 
 
SELECT disciplina.nome, disciplina.creditos 
FROM disciplina 
 
Combinando as duas formas 
 
Encontrar os nomes e os créditos das disciplinas com 4 créditos 
 
 ( nome, creditos (diciplina)) 
  creditos = 4 
Ou 
 ( creditos = 4 (diciplina)) 
  nome, creditos 
 
SELECT disciplina.nome, disciplina.creditos 
FROM disciplina 
WHERE disciplina.creditos = 4 
Banco de Dados 
Linguagem C 
4 
4 
 
 
 3
 
 
PRODUTO CARTESIANO (X) 
 
 A operação de Produto Cartesiano (X), permite combinar informações de diversas relações. 
 Sintaxe: r1 X r2, onde r1 e r2 são relações distintas. 
Encontrar os alunos da disciplina 10 com Nota maior ou igual a 6 
 (HISTORICO) 
 historico.codigo_disciplina=10 AND historico.nota >= 6 
 
SELECT * 
FROM historico 
WHERE historico.codigo_disciplina =10 AND 
 historico.nota >= 6 
 
 
Encontrar os Nomes dos alunos da disciplina 10 com Nota maior ou igual a 6. 
 
 (historico x aluno) 
 aluno.nome  historico.pr = aluno.pr AND 
 historico.codigo_disciplina=10 AND 
 historico.nota >= 6 
 
(historico x aluno) ou (historico |x| aluno) 
pr Código_disciplina nota faltas pr nome 
100 
100 
 
200 
200 
 
100 
100 
 
200 
200 
 
200 
200 
10 
10 
 
10 
10 
 
20 
20 
 
20 
20 
 
30 
30 
8 
8 
 
6 
6 
 
10 
10 
 
4 
4 
 
8 
8 
4 
4 
 
20 
20 
 
0 
0 
 
12 
12 
 
4 
4 
100 
200 
 
100 
200 
 
100 
200 
 
100 
200 
 
100 
200 
JOÃO 
MARIA 
 
JOÃO 
MARIA 
 
JOÃO 
MARIA 
 
JOÃO 
MARIA 
 
JOÃO 
MARIA 
 
SELECT aluno.nome 
FROM historico,aluno 
WHERE historico.codigo_disciplina = 10 AND 
 historico.nota >= 6 AND 
 historico.pr = aluno.pr 
 
Usando JOIN: 
SELECT aluno.nome 
FROM histórico INNER JOIN aluno ON historico.pr = aluno.pr 
WHERE historico.codigo_disciplina = 10 AND 
 historico.nota >= 6 
 
 4
 
 
Encontrar os Nomes dos alunos da disciplina “Matemática” com Nota maior igual a 6. 
 
SELECT aluno.nome 
FROM historico,aluno,disciplina 
WHERE historico.pr = aluno.pr AND 
 historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Matemática" AND 
 historico.nota >= 6 
 
Usando JOIN: 
SELECT aluno.nome 
FROM historico INNER JOIN aluno ON historico.pr = aluno.pr 
 INNER JOIN disciplina ON historico.codigo_disciplina = disciplina.codigo 
WHERE disciplina.nome = "Matemática" AND 
 historico.nota >= 6 
 
 
Encontrar os Nomes dos alunos e das disciplinas, que estão reprovados, ou por faltas ou por 
notas. 
 
 
SELECT aluno.nome, disciplina.nome 
FROM historico, aluno, disciplina 
WHERE ( (historico.nota < 6) OR historico.faltas > 18 ) AND 
 historico.codigo_disciplina = disciplina.codigo AND 
 historico.pr = aluno.pr 
 
 DIFERENÇA () 
A Diferença entre duas relações r1 e r2, ambas com mesmo número de atributos e de mesma 
ordem, terá como resultado uma relação contendo todas as tuplas que estão em r1 mas não em 
r2. 
 
 
Encontrar os Nomes dos alunos, que não cursaram a disciplina “Linguagem C”. 
R1 =  aluno.nome (aluno) 
 
nome 
JOÃO 
MARIA 
 
R2 =  disciplina.nome = “Linguagem C” 
 
codigo nome creditos 
30 Linguagem C 4 
 
R3 =  disciplina.codigo (R2) 
 
codigo 
30 
 5
 
 
 
R4 = R3 X historico 
historico.pr 
historico. 
Código_disciplina 
historico.nota historico. 
faltas 
disciplina. 
codigo 
100 
200 
100 
200 
200 
10 
10 
20 
20 
30 
8 
6 
10 
4 
8 
4 
20 
0 
12 
4 
30 
30 
30 
30 
30 
 
R5 =  historico.codigo_disciplina = disciplina.codigo (R4) 
 
histórico.pr 
historico. 
Código_disciplina 
historico.nota historico. 
faltas 
disciplina. 
codigo 
200 30 8 4 30 
 
R6 =  historico.pr (R5) 
 
historico.pr 
200 
 
R7 = R6 X aluno 
 
aluno.pr aluno.nome historico.pr 
100 
200 
JOÃO 
MARIA 
200 
200 
 
R8 =  historico.pr = aluno.pr (R7) 
 
aluno.pr aluno.nome historico.pr 
200 MARIA 200 
 
R9 =  aluno.nome (R8) 
 
aluno.nome 
MARIA 
 
R10 = R1 – R9 
 
nome aluno. nome aluno.nome 
JOÃO 
MARIA 
 MARIA = JOÃO 
 
O resultado será todas as tuplas que estão na primeira relação e não estão na segunda, ou seja, 
todas as que estão na primeira menos as que estão na segunda. 
 6
 
 
Ou 
 
R1 =  aluno.nome (aluno) 
 
nome 
JOÃO 
MARIA 
 
 
 (historico x aluno x disciplina) 
R2 =  aluno.nome  historico.pr = aluno.pr AND 
 historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome= “Linguagem C” 
 
 
nome 
MARIA 
 
R3 = R1 – R2 
 
nome aluno. nome aluno.nome 
JOÃO 
MARIA 
 MARIA = JOÃO 
 
SQL 
Selecionando todos os alunos 
 
SELECT aluno.nome 
FROM aluno 
 
JOÃO 
MARIA 
 
Selecionando quem cursouLinguagem C 
 
SELECT aluno.nome 
FROM historico, aluno, disciplina 
WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = “Linguagem C” AND 
 historico.pr = aluno.pr 
MARIA 
 
 7
 
 
Retirando quem cursou Linguagem “C” de todos os alunos 
 
JOÃO 
MARIA 
__ 
MARIA = JOÃO 
 
SELECT aluno.nome 
FROM aluno 
WHERE aluno.nome NOT IN 
 (SELECT aluno.nome 
 FROM historico, aluno, disciplina 
 WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Linguagem C" AND 
 historico.pr = aluno.pr) 
JOÃO 
 
Usando JOIN: 
 
SELECT aluno.nome 
FROM aluno 
WHERE aluno.nome NOT IN 
 (SELECT aluno.nome 
 FROM histórico INNER JOIN aluno ON historico.pr = aluno.pr 
 INNER JOIN disciplina ON historico.codigo_disciplina = 
 disciplina.codigo 
 WHERE disciplina.nome = "Linguagem C") 
 8
 
 
 
Exercício 
Encontrar os Nomes das disciplinas não cursadas pelo aluno “João”. 
R1 =  disciplina.nome
(disciplina)
 
nome 
Banco de Dados 
Matemática 
Linguagem C 
 
 
 (historico x aluno x disciplina) 
R2 =  disciplina.nome  historico.pr = aluno.pr AND 
 historico.codigo_disciplina = disciplina.codigo AND 
 aluno.nome= “JOÃO” 
 
 
nome 
Banco de Dados 
Matemática 
 
 
 
 
R3 = R1 – R2= -- 
 
 
 
 
 
SELECT disciplina.nome 
FROM disciplina 
WHERE disciplina.nome NOT IN 
 (SELECT disciplina.nome 
 FROM historico, aluno, disciplina 
 WHERE historico.codigo_disciplina = disciplina.codigo AND 
 aluno.nome = "JOÃO" AND 
 historico.pr = aluno.pr) 
 
Encontrar os Nomes dos alunos, que cursaram as disciplinas “Banco de Dados” e “Linguagem 
C”. 
 
Selecionando todos os alunos que cursaram Linguagem C 
 
SELECT aluno.nome 
FROM historico, aluno, disciplina 
WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Linguagem C" AND 
 historico.pr = aluno.pr 
MARIA 
nome 
Banco de Dados 
Matemática 
Linguagem C 
nome 
Banco de Dados 
Matemática 
nome 
Linguagem C 
 9
 
 
 
Selecionando todos os alunos que cursaram Banco de Dados 
 
SELECT aluno.nome 
FROM historico, aluno, disciplina 
WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Banco de Dados" AND 
 historico.pr = aluno.pr 
JOÃO 
MARIA 
 
Selecionando quem cursou Linguagem C e Banco de Dados 
 
SELECT aluno.nome 
FROM historico, aluno, disciplina 
WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Banco de Dados" AND 
 historico.pr = aluno.pr AND 
 aluno.nome IN 
 (SELECT aluno.nome 
 FROM historico, aluno, disciplina 
 WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Linguagem C" AND 
 historico.pr = aluno.pr) 
 
 
 
Na Álgebra Relacional é a diferença da diferença 
 
JOÃO 
MARIA 
__ 
MARIA = JOÃO 
 
JOÃO 
MARIA 
__ MARIA 
JOÃO 
__ MARIA 
 
 = 
MARIA 
 
Ou Intersecção 
 
JOÃO 
MARIA  MARIA = MARIA 
 
 
 
 
MARIA 
 1
0
 
 
UNIÃO (U) 
 
A União entre duas relações r1 e r2, ambas com mesmo número de atributos e de mesma 
ordem, terá como resultado uma relação contendo todas as tuplas que somente estão em r1 mas 
não em r2, todas as que somente estão em r2 mas não em r1 e todas as estão em r1 e em r2. 
 
Encontrar os Nomes dos alunos, que cursaram as disciplinas “Banco de Dados” ou 
“Linguagem C” ou ambas. 
 
Selecionando todos os alunos que cursaram Linguagem C 
 
 historico x aluno x disciplina 
 
R1 =  aluno.nome  disciplina.nome = “Linguagem C” AND 
 historico.codigo_disciplina = disciplina.codigo AND 
 historico.pr = aluno.pr 
 
MARIA 
 
Selecionando todos os alunos que cursaram Banco de Dados 
 
 historico x aluno x disciplina 
R2= aluno.nome  disciplina.nome = “Banco de Dados” AND 
 historico.codigo_disciplina = disciplina.codigo AND 
 historico.pr = aluno.pr 
 
JOÃO 
MARIA 
 
Selecionando quem cursou Linguagem C ou Banco de Dados ou ambas. 
 
R1 U R2  
 
Com ALL 
 
( SELECT aluno.nome 
FROM historico, aluno, disciplina 
WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Banco de Dados" AND historico.pr = aluno.pr ) 
UNION ALL 
( SELECT aluno.nome 
 FROM historico, aluno, disciplina 
 WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Linguagem C" AND historico.pr = aluno.pr) 
 
 
 
 
JOÃO 
MARIA 
JOÃO 
MARIA 
MARIA 
 1
1
 
 
 
Sem o ALL 
( SELECT aluno.nome 
FROM historico, aluno, disciplina 
WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Banco de Dados" AND historico.pr = aluno.pr ) 
UNION 
( SELECT aluno.nome 
 FROM historico, aluno, disciplina 
 WHERE historico.codigo_disciplina = disciplina.codigo AND 
 disciplina.nome = "Linguagem C" AND historico.pr = aluno.pr) 
 
JOÃO 
MARIA 
 
Usando JOIN: 
 
( SELECT aluno.nome 
FROM histórico INNER JOIN aluno ON historico.pr = aluno.pr 
 INNER JOIN disciplina ON historico.codigo_disciplina = disciplina.codigo 
WHERE disciplina.nome = "Banco de Dados") 
UNION 
( SELECT aluno.nome 
 FROM histórico INNER JOIN aluno ON historico.pr = aluno.pr 
 INNER JOIN disciplina ON historico.codigo_disciplina = disciplina.codigo 
 WHERE disciplina.nome = "Linguagem C") 
 
 1
2
 
 
Lista de Exercícios de Banco de Dados I (Prof. Rolando) 
2° Semestre – Análise Desenvolvimento de Sistemas 
 
Dadas as seguintes tabelas de um banco de dados chamado escola: 
 
ALUNO PROFESSOR 
Prontuário 
Aluno 
Nome 
Aluno 
Endereço 
Aluno 
 Código 
Professor 
Nome 
Professor 
100 
200 
300 
500 
João 
José 
Maria 
Antonio 
Rua 10 
Rua 20 
Rua 30 
Rua 50 
 10 
20 
30 
50 
Carlos 
Luiz 
Marcos 
Paulo 
DISCIPLINA 
Nro 
Disciplina 
Nome Disciplina Nro 
Créditos 
100 
300 
500 
800 
C 
PASCAL 
Banco de Dados 
Redes 
04 
02 
04 
06 
 TURMA CLASSE 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Em um arquivo Word, escreva as expressões da álgebra relacional e a relação resultante para as 
seguintes consultas: 
 
1) encontrar os alunos da turma A da Disciplina "Banco de Dados". 
2) encontrar as Disciplinas ministradas pelo Professor "Luiz". 
3) encontrar os Códigos das Turmas da Disciplina "Redes". 
4) encontrar os professores que não estão ministrando nenhuma disciplina. 
5) encontrar os alunos que não estão cursando nenhuma disciplina. 
6) encontrar os alunos que não cursam a disciplina "C". 
7) encontrar os alunos que cursam "C" e "PASCAL" ao mesmo tempo. 
8) encontrar os professores que ministram "C" e "Banco de Dados" ao mesmo tempo. 
9) encontrar os alunos que cursam disciplinas de 2 créditos. 
10) encontrar os alunos que assistem aulas com o professor "Marcos". 
 
Em um arquivo SQL, escreva os comandos para criar o banco de dados escola e as tabelas acima. 
Escreva os comandos para inserir os dados nas tabelas. 
Em um outro arquivo SQL escreva os comandos SQL para as consultas acima. 
 
Código 
Turma 
Nro 
Disciplina 
Código 
Professor 
 Nro 
Disciplina 
Código 
Turma 
Prontuário 
Aluno 
A 
B 
A 
B 
A 
A 
B 
100 
100 
300 
300 
500 
800 
800 
10 
20 
30 
30 
10 
10 
20100 
100 
100 
100 
300 
300 
500 
500 
500 
500 
800 
800 
A 
A 
B 
B 
A 
A 
A 
A 
A 
A 
A 
A 
 100 
 200 
 300 
 500 
 100 
 300 
 100 
 200 
 300 
 500 
 200 
 300