bd2

Prévia do material em texto

Banco de Dados II 
Prof. Marcos Miguel 
marcos.a.miguel@gmail.com 
http://marcosmiguel.com 
 
 
2 
Prof. Marcos Miguel - MBI 
BDII(Banco de Dados II) 
2 
¨  OBJETIVO 
¤  Mostrar e definir os conceitos relativos à criação e 
organização de Sistemas de Bancos de Dados. 
Fazer com que o aluno desenvolva habilidades 
necessárias para a utilização da linguagem de 
consulta SQL. Possibilitar ao aluno o 
entendimento da manipulação, criação e 
gerenciamento de um banco de dados. 
 
3 
Prof. Marcos Miguel - MBI 
BDII(Banco de Dados II) 
3 
¨  HABILIDADES 
¤  Desenvolver a habilidade para a implementação de 
banco de dados, manipulação de dados através da 
linguagem SQL e gerenciamento de um Banco de 
Dados 
¨  COMPETÊNCIAS 
¤  Compreender o funcionamento de Sistemas 
Gerenciador de Banco de Dados, desenvolver a 
capacidade de implementar, manipular e gerenciar 
um Banco de Dados 
¨  
 
4 
Prof. Marcos Miguel - MBI 
CONTEÚDO PROGRAMÁTICO 
4 
¨  Unidade 1- LINGUAGEM SQL (Structured 
Query Language) 
¤  1.1. Histórico 
¤  1.2. Conceitos 
¤  1.3. Linguagem 
¤  1.4. Modelo de Exemplo (MER, DTR e SQL) 
¤  1.5. DDL (Data Definition Language) 
¤  1.6. DML (Data Manipulation Language) 
¤  1.7. DCL (Data Control Language) 
¤  1.8. Stored Procedures 
¤  1.9. Triggers 
¤  1.10. Seqüências 
5 
Prof. Marcos Miguel - MBI 
CONTEÚDO PROGRAMÁTICO 
5 
¨  Unidade 2. PROJETO FÍSICO 
¤  2.1. Estrutura geral do SGBD 
¤  2.2. Regras de integridade 
¤  2.3. Gerência de Transação e controle de 
concorrência 
¤  2.4. Implementação e criação do script do banco 
de dados (MySQL e PostgresSQL) 
¤  2.5. Indexação 
6 
Prof. Marcos Miguel - MBI 
CONTEÚDO PROGRAMÁTICO 
6 
¨  Unidade 3. AMBIENTE DE BANCO DE 
DADOS 
¤  3.1. Modelo de Transação 
¤  3.2. Log do Banco de Dados 
¤  3.3. Controle de Concorrência 
¤  3.4. Recuperação de Paradas e Falhas 
¤  3.5. Segurança e Integridade 
¨  Unidade 4. TÓPICOS ESPECIAIS 
¤  4.1 Tendências das novas tecnologias em Banco de Dados 
¤  4.2 Tipos de Banco de Dados (Híbrido, Orientado a Objeto, 
Distribuídos) 
7 
Prof. Marcos Miguel - MBI 
Bibliografia 
7 
¨  ELMASRI, Ramez e NAVATHE, Shamkant 
B. Fundamentals of Database System. 
Third Edition. Ed. Addison-Wesley, 2000. 
¨  KORTH, H. F. e SILBERSCHATZ, A. 
Sistemas de Banco de Dados. Terceira 
edição, São Paulo, Ed. McGraw Hill, 1999. 
¨  HEUSER, Carlos Alberto. Projeto de Banco 
de Dados. Porto Alegre. Ed. Sagra Luzzato, 
1998. 
8 
Prof. Marcos Miguel - MBI 
Bibliografia auxiliar 
8 
¨  DATE, C. J. Introdução a sistemas de bancos de dados. 
8.ed. Rio de Janeiro: Campus, 2004. 
 
¨  STEZER, Valdemar W.. Banco de Dados, São Paulo, Ed. 
Edgar Blucher, 1986. 
 
¨  PERIÓDICOS: Revista SQL Magazine, DevMedia. 
9 
Prof. Marcos Miguel - MBI 
Avaliações 
9 
¨  V1 – 25/09/2013 
¨  V2 – 27/11/2013 
¨  VT – 20/11/2013 * Apresentação do Trabalho 
¨  2ā chamada (V1 e V2) - 04/12/2013 
¨  VS - 11/12/2013 
10 
Prof. Marcos Miguel 
10 
Conceitos 
Introdutórios 
11 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
11 
¨  Matematicamente falando, uma tabela (relação) é um 
conjunto - um conjunto de linhas. 
¨  No modelo relacional temos o BD representado como uma 
coleção de tabelas. Quando queremos manipular 
(recuperar) dados, em geral o resultado nos é apresentado 
como uma tabela, derivada de alguma forma de outras 
tabelas. 
¨  A álgebra relacional é um conjunto de operações sobre as 
relações. 
12 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
12 
¨  OPERAÇÕES TRADICIONAIS 
¤  UNION (UNIÃO) 
 
R S: Retorna uma tabela que inclui todas as tuplas que estão ou 
em R ou em S, ou em ambas. Tuplas duplicadas são eliminadas. 
13 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
13 
¨  OPERAÇÕES TRADICIONAIS 
¤  INTERSECTION (INTERSEÇÃO) 
14 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
14 
¨  OPERAÇÕES TRADICIONAIS 
¤  SET DIFFERENCE (DIFERENÇA) 
15 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
15 
¨  OPERAÇÕES TRADICIONAIS 
¤  CARTESIAN PRODUCT (PRODUTO CARTESIANO) 
 
16 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
16 
¨  OPERAÇÕES ESPECIAIS 
¤  PROJECT (PROJEÇÃO) 
 
17 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
17 
¨  OPERAÇÕES ESPECIAIS 
¤  SELECT (SELEÇÃO) 
 
18 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
18 
¨  OPERAÇÕES ESPECIAIS 
¤  DIVISION (DIVISÃO) 
¨  R1 ÷ R2: A operação de divisão tem tabelas como 
operandos. Os nomes das colunas (e os respectivos 
domínios) da tabela R2 (C2) devem estar contidos 
dentro dos nomes das colunas (e respectivos 
domínios) da tabela R1 (C1). A tabela resultante tem 
como nomes de colunas e domínios aqueles que 
aparecem duas em R1 mas não aparecem em R2 
(C1 - C2). Para que uma linha apareça no resultado é 
necessário que sua concatenação com cada linha da 
tabela R2 apareça também na tabela R1. 
 
Exemplo: Todos os fornecedores das peças 'P2' e 'P4' 
R3 = Fornecimento ÷ Temp 
Divide Fornecimento by Temp over CodPeça giving R 
 
19 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
19 
¨  OPERAÇÕES ESPECIAIS 
¤  JOIN 
20 
Prof. Marcos Miguel - MBI 
ÁLGEBRA RELACIONAL 
20 
¨  OPERAÇÕES ESPECIAIS 
¤  JOIN cont… 
¨  Estamos trabalhando com join baseado em igualdade de valores de diferentes tabelas 
(EQUIJOIN). Mas poderíamos ter join com qualquer expressão booleana ("maior que", "menor 
que", "não igual", etc). Esta operação genérica de junção é chamada THETAJOIN. 
¨  Uma operaçao de EQUIJOIN também é representada com a seguinte sintaxe: 
 
¨  Onde listaR e listaS indicam os nomes das colunas das tabelas R e S cujos valores serão 
comparados um a um para fazer a junção. A segunda coluna é eliminada em cada um dos pares 
que são comparados, ja ́ que os valores são idênticos. 
 R = R1 * (A),(A) R2 
¨  Um EQUIJOIN em que com os nomes das colunas sejam iguais é chamado NATURAL JOIN. 
Neste caso, o nome das colunas não precisam ser especificados. 
 R = R1 * R2 
21 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
21 
¨  Structured Query Language, ou Linguagem de Consulta Estruturada ou 
SQL, é a linguagem de pesquisa declarativa padrão para banco de dados 
relacional (base de dados relacional). Muitas das características originais do 
SQL foram inspiradas na álgebra relacional. 
¨  Histórico 
¤  SEQUEL (Structured English Query Language) - Centro de Pesquisa 
San Jose da IBM (Projeto System R) 
n  Rapidamente surgiram vários "dialectos" desenvolvidos por outros produtores. 
Essa expansão levou à necessidade de ser criado e adaptado um padrão para a 
linguagem. Esta tarefa foi realizada pela American National Standards Institute 
(ANSI) em 1986 e ISO em 1987. 
¤  SQL 1 (SQL-86): Padrão ISO/ANSI. Não havia cláusula para especificar 
chave; modificado em 1989 
¤  SQL 2 (SQL-92): Implementa conexão cliente/servidor 
¤  SQL 3: Em fase de aprovação; implementa o Modelo Orientado a 
Objeto 
22 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
22 
¨  Conceitos 
¤  Tabela (table) 
n  Equivale à relação, no Modelo Relacional 
¤  Linha (row) - registros 
n  Equivale à tupla, no Modelo Relacional 
 
¤  Coluna (column) 
n  Equivale aos atributos da relação, no Modelo relacional 
23 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
23 
¨  Tipos de dados (data type) 
¤  Caracter 
n  CHAR(n) ou CHARACTER(n) 
n  VARCHAR(n) ou CHARACTER VARYING(n) ou CHAR VARYING(n) 
¤  Numérico 
n  SMALLINT, INTEGER 
n  FLOAT, DOUBLE PRECISION 
n  NUMERIC(precision, scale), DECIMAL(precision,scale) 
¤  Datas 
n  DATE 
n  TIME 
n  TIMESTAMP 
¤  BLOB (Binary Large Objects) 
24 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
24 
¨  Domínios (domain) 
¤  Determina os valores possíveispara colunas. Domínios 
normalmente são definidos antes da criação das tabelas, 
para que possam ser usadas por estas 
¨  Restrições (constraint) 
¤  Condições necessária para manter a integridade dos 
dados 
¨  Visões (view) 
¤  Uma tabela cujo conteúdo é derivado de outras tabelas 
25 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
25 
¨  Banco de dados (database) 
¤  Conjunto de tabelas, domínios, restrições, visões, etc 
¨  Esquema (schema) 
¤  O conceito de uma esquema SQL foi definido com o SQL2 para agrupar 
tabelas e outros elementos que pertençam a mesma aplicação. Um 
esquema é identificado por um nome, um identificador do usuário ou 
conta que possui o esquema e descritores de cada elemento do 
esquema. 
¨  Catálogo (catalog) – Dicionário de dados 
¤  Em SQL2 um catálogo é uma coleção nomeada de esquemas em um 
ambiente SQL. Pode ser entendido com um BD do sistema, que pode 
ser consultado, contendo: informações sobre as tabelas, visões, direitos 
de acesso, identificações dos usuários, etc. Sua forma exata é uma 
característica de cada sistema e não da SQL 
26 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
26 
¨  Linguagem 
¤  Mais que uma linguagem de consulta, a SQL oferece funções para DEFINIÇÃO, 
MANIPULAÇÃO e CONTROLE dos dados de um Banco de dados. 
¤  DDL (Data Definition Language) 
n  CREATE: criação de novas estruturas 
n  ALTER: alteração de estruturas existentes § DROP: remoção de estruturas 
¤  DML (Data Manipulation Language) 
n  INSERT: Inserção de registros 
n  DELETE: remoção de registros 
n  UPDATE: atualização de registros 
n  SELECT: Seleção (consulta) de registros 
¤  DCL (Data Control Language) 
n  GRANT: concessão de direitos de acesso a tabelas e visões 
n  REVOKE: revogação de direitos de acesso a tabelas e visões 
27 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
27 
¨  Exemplo 
28 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
28 
¨  Exemplo 
29 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
29 
¨  DDL (Data Definition Language) 
30 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
30 
¨  DDL (Data Definition Language) 
31 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
31 
32 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
32 
33 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
33 
34 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
34 
35 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
35 
36 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
36 
37 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
37 
38 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
38 
39 
Prof. Marcos Miguel - MBI 
SQL-STRUCTURED QUERY LANGUAGE 
39 
Caso de uso 
40 
Caso de uso 
41 
Caso de uso 
42 
DDL – Banco de Dados Matricula 
43 
DDL – Banco de Dados Matricula 
44 
DDL – Banco de Dados Matricula 
45 
DDL - Foreign Key 
¨  Criar 
¨  Deletar 
46 
46 
DML – SQL 
¨  Insert único registro 
¨  Insert múltiplos registros 
47 
47 
DML – SQL 
¨  Delete - único registro 
¨  Delete - múltiplos registros 
48 
Cuidado! 
Esta ação 
apaga todos 
os registros 
da tabela	
48 
DML – SQL 
¨  Update – único registro 
¨  Update – múltiplos registros 
49 
49 
DML – SQL 
¨  Select - único registro 
¨  Select - múltiplos registros 
50 
50 
¨  Select 
¤  Inner join 
¤  Left join 
DML – SQL 
51 
51 
DML – SQL 
¨  Select – Funções 
¤  Count - Conta o total de registros 
¤  Min – Calcula o menor valor de uma campo 
52 
52 
DML – SQL 
¨  Select – Funções 
¤  Max – Calcula o maior valor de uma campo 
¤  AVG – Calcula a média dos valores de um campo 
53 
	
	
	
	
	
	
	
	
	
	
53 
DML – SQL 
¨  Select – Funções 
¤  SUM – Soma os valores de um campo 
54 
	
	
	
	
	
54 
DML – SQL 
¤  Select – Funções 
n  Like – manipulação de Strings 
n  “%” - Busca zero ou mais caracteres 
n  “%miguel” -> todos os caracteres terminados com “miguel” 
n  “miguel%” -> todos os caracteres começados com “miguel” 
55 
	
	
	
	
	
55 
DML – SQL 
¤  Select – Funções 
n  Like 
n  “_” - desconsidera o caracter 
n  “_x” -> todos os caracteres desconsiderando o 
primeiro 
 
 
 
56 
Linha Inicial	

 Máximo de linhas 	
56 
DML – SQL 
¨  Select – Funções 
¤  Left – retorna os primeiros caracteres à esquerda 
de uma string 
¤  Rigth – retorna os últimos caracteres à direita de 
uma string 
57 
	
	
	
	
	

	
	
	
	
	
57 
Cláusula GROUP BY 
¨  Na grande maioria dos casos, as funções de 
agregação são usadas em cálculos baseados 
em grupos de linhas da tabela. A cláusula 
GROUP BY é usada para dividir as linhas de 
uma tabela em subgrupos menores. 
¨  O SQL recupera cada grupo de linhas de 
acordo com os valores da(s) expressão(ões) 
especificada(s) na cláusula GROUP BY. 
58 
58 
Cláusula GROUP BY 
¨  A cláusula GROUP BY devera ́ vir sempre após 
a cláusula WHERE (ou após a cláusula FROM 
quando não existir WHERE). 
¨  Quando a cláusula GROUP BY é utilizada, é 
possível combinar resultados individuais com 
funções de grupo na cláusula SELECT, desde 
que aqueles resultados individuais sejam 
usados no GROUP BY. 
59 
59 
Cláusula GROUP BY 
¨  Exemplo: 
¤  Conta o total de pessoas utilizando a primeira 
letra do alfabeto 
 
 
 
 
 
¨  Obs: 
¤  Quando usar GROUP BY todas as colunas que são usadas no SELECT mas não são usadas 
na função de grupo devem ser incluídas na clausula GROUP BY. 
¤  Usando a cláusula WHERE pode-se selecionar as linhas antes de agrupá-las. 
 
60 
	
	
	
	
	
60 
Cláusula GROUP BY com Having 
¨  A cláusula WHERE não pode ser usada para 
restringir funções de grupo. Assim, o exemplo 
seguinte não é válido: 
¨  Os grupos definidos pela cláusula GROUP BY 
podem ser filtrados pela cláusula HAVING. 
61 
61 
Cláusula GROUP BY com Having 
¨  Exemplo: 
¤  Total de pessoas agrupados por nome e filtrado 
com having para as pessoas iniciadas com a letra 
“m”. 
62 
	
	
	
	
	
62 
Processamento SQL 
¨  O processamento da instrução SQL pode ser 
visto da seguinte forma: 
n  É feito o produto cartesiano das tabelas envolvidas; 
n  São selecionadas as linhas da tabela que obedecem 
ao critério da cláusula WHERE; 
n  São criados grupos de linhas que contenham valores 
idênticos nas colunas do GROUP BY; 
n  São selecionados os grupos que atendem ao critério 
da cláusula HAVING; 
n  É feita a classificação do resultado pelos valores das 
colunas da cláusula ORDER BY; 
n  É feita a projeção sobre as colunas especificadas no 
SELECT. 
63 
63 
Subqueries 
¨  Subqueries são comandos SELECT utilizados 
em condições de cláusulas WHERE ou 
HAVING, para prover resultados que são 
utilizados para completar a consulta principal. 
¨  Uma subquery que retorna apenas uma linha 
como resultado é chamada "single-row 
subquery". Caso mais de uma linha seja 
retornada, a subquery é chamada "multiple-
row subquery". 
64 
64 
Subqueries 
¨  Os operadores = , > , >= , < , <= e <> podem 
ser usados em comparações com "single-row 
subqueries". Os operadores IN, ANY e ALL 
são usados em "multiple-row subqueries". 
¨  Exemplo: 
¤  Listar o Aluno 
com o maior ID 
65 
65 
Subqueries 
¨  Operadores ANY e ALL 
¤  Quando a subquery retornar mais de um valor, os 
operadores ANY e ALL podem ser utilizados para 
compatibilizar o resultado da subquery com o tipo 
do operador de comparação. 
¨  Essa condição será verdadeira quando 
<campo> for maior que qualquer um dos 
resultados da subquery. 
66 
66 
Subqueries 
¨  Operadores ANY e ALL 
¤  Essa condição será verdadeira quando <campo> 
for menor que qualquer um dos resultados da 
subquery 
¤Essa condição será verdadeira quando <campo> 
for maior que todos os resultados da subquery. 
¤  Essa condição será verdadeira quando <campo> 
for menor que todos os resultados da subquery. 
67 
67 
Subqueries 
¨  Operadores ANY e ALL 
¤  Exemplo: Busca todas as pessoas em que o id 
seja maior que qualquer resultado no subselect 
68 
Tabela Aluno	
	
	
	
	
	
68 
Subqueries 
¨  Operadores IN e NOT IN 
¤  Verifica se o dado faz parte ou não da lista 
fornecida. A lista pode ser formada por valores 
retornados por uma subquery. 
¤  Exemplo: 
n  Busca todas as pessoas que estejam na tabela de 
professor 
69 
	
	
	
	
	
69 
Operadores de Conjuntos 
¨  Como o resultado de um query é um conjunto de 
linhas você pode realizar operações de conjuntos 
entre queries. 
n  UNION : União entre os resultados das queries; 
n  INTERSECT : Interseção entre os resultados das 
queries; 
n  MINUS : Subtração entre os resultados das queries. 
¨  A operação de união permite reunir os resultados de 
duas consultas distintas em um só resultado. 
Equivale à operação de União da Álgebra 
Relacional. A operação de união elimina as linhas 
duplicadas. 
70 70 
Operadores de Conjuntos 
¨  Exemplo: 
¤  Unificar os resultados da seleção das tabelas de 
professor e aluno. 
71 
	
	
	
	
	
71 
Operadores EXISTS e NOT EXISTS 
 
¨  EXIST: retorna “verdadeiro” se uma 
determinada subquery retornar ao menos uma 
linha e “falso” caso contrário. 
¨  NOT EXIST: retorna o resultado contrário do 
operador EXIST. 
¤  Exemplo: Selecionar todas as pessoas que sejam 
alunos. 
72 
	
	
	
	
	
72 
Visões (views) 
¨  Uma visão na terminologia SQL é uma tabela que é derivada 
de outras tabelas. Uma visão não precisa existir fisicamente. 
Por isso, pode ser considerada como uma tabela virtual, isto 
é, uma tabela que realmente não existe como tal, mas sim 
como derivação de uma ou mais tabelas básicas. 
¨  A definição da visão fica armazenada no dicionário de dados; 
esta definição mostra como ela é derivada das tabelas 
básicas. 
¨  O objetivo básico no uso de visões é restringir o acesso a 
certas porções dos dados por questões de segurança, além 
de pré-definir certas consultas através de tabelas virtuais que 
poderão ser utilizadas por outras consultas. 
73 
73 
Visões (views) 
¨  Exemplo: 
¤  Visão que retornar os professores cadastrados no banco de dados 
74 
	
	
	
	
	
74 
Visões (views) 
¨  DROP VIEW 
75 
75 
Links úteis Prof. Marcos Miguel	
76