Introdução a Bancos de Dados José Maria Monteiro Departamento de Computação Universidade...

Introdução a Bancos de Dados

José Maria MonteiroDepartamento de Computação

Universidade Federal do Ceará

© José Maria Monteiro 2

Sumário

Sistemas de Banco de DadosBancos de Dados Relacionais

O Modelo RelacionalModelagem de DadosÁlgebra RelacionalLinguagem de Manipulação e Consulta

Transações de Bancos de DadosServidores de Bancos de Dados

© José Maria Monteiro 3

Structured Query Language (SQL)

IBM (1973)

Linguagem de SGBDs RelacionaisPadronização pela ISO

SQL-89: Maioria dos SGBDsSQL-92: Alguns SGBDsSQL3: Relacionais-objeto

Mais simples e mais completa que a álgebra

Linguagem declarativaNÃO tem completude computacionalOrientada a conjunto

© José Maria Monteiro 4

Propriedades

Linguagem interativa de consultas ad hocLinguagem de programação de BDLinguagem de definição e manipulação dos

dadosLinguagem de servidores de bancos de

dados em redeProtege os dados em ambientes multi-

usuários

© José Maria Monteiro 5

SQL-89

Definição de dados (DDL)Criação de tabelas, índices, visões,

integridade referencialDefinição de privilégios de acesso

Manipulação de dados (DML)Consulta e atualização dos dadosGerenciamento de transaçõesNavegação através de cursorSQL embutido

© José Maria Monteiro 6

SQL-92 Agentes SQL: programas ou usuários que

produzem comandos SQL Conexões SQL C/S: estabelece uma sessão

SQL entre cliente e servidor Controle transacional mais detalhado:

transações read-only e níveis de isolamento Catálogo padronizado: coleção de esquemas

que descrevem uma base SQL Dinâmico: gera código SQL em tempo

de execução Novos tipos de dados Tabelas temporárias: espaço de trabalho

destruído ao final da sessão

SQL-89 +

© José Maria Monteiro 7

SQL-92

Suporte a operações de junção: diferentes tipos de junções na cláusula Where

Códigos de erro padronizados: SQLSTATE contém valores padronizados

Restrições de domínio: regras definidas pelo usuário que determinam valores possíveis para um atributo

Outros avanços: cursor para frente e para trás; conversão de tipos; refinamento no modelo de integridade referencial

SQL-89 +

© José Maria Monteiro 8

SQL3

Tipo abstratos de dadosTipo abstratos de dadosCall-Level Interface - CLICall-Level Interface - CLI

Persistent Storage Modules - PSMPersistent Storage Modules - PSMTransações globaisTransações globais

SQL multimídiaSQL multimídia

© José Maria Monteiro 9

Ling. de Definição de Dados

CREATE TABLE: criação de uma relação ALTER TABLE: adição de atributos a uma relação DROP TABLE: destruição de uma relação

Exemplo

CREATE TABLE Curso( nome: CHARACTER(30), creditos: INTEGER, aprov_ano: INTEGER, coordenador: CHARACTER(30),);

© José Maria Monteiro 10

Expressão de Restrições

CREATE TABLE Disciplina( codigo: CHAR(5) NOT NULL, nome: CHAR(40) NOT NULL, creditos: INTEGER NOT NULL,

departamento: CHAR(7), CONSTRAINT pk PRIMARY KEY (codigo),

CONSTRAINT depart_fk FOREIGN KEY

(departamento) REFERENCES Departamento (nome),

)

© José Maria Monteiro 11

Ling. Manipulação de Dados

Consultas SQL Três cláusulas: SELECT, FROM, WHERE SELECT: define os valores que constituem cada

linha do resultadoFROM: define as tabelas das quais o resultado é

produzido WHERE: define a condição de seleção que será

satisfeita para as tuplas que formam o resultado

© José Maria Monteiro 12

Exemplos

Recuperar o código e o nome das disciplinas de 6 créditos

SELECT codigo, nomeFROM DisciplinaWHERE creditos=6

Recuperar todos os cursos SELECT *FROM Cursos

© José Maria Monteiro 13

Exemplos

Recuperar todos os alunos do oitavo semestre dos cursos com mais de 30 alunos aprovados por ano

SELECT matricula, Estudante.nomeFROM Estudante, CursoWHERE Estudante.semestre = 8 and

Curso.aprov_ano > 30 and Estudante.curso = Curso.nome

© José Maria Monteiro 14

Banco de Dados Companhia

Nome CPF Endereco Nasc Sexo Salario Chefe CDep

DNome Codigo Gerente

Pcodigo PNome Cidade CDep

Empregado

Departamento

Projeto

Cpf Pcodigo Horas

Tarefa

© José Maria Monteiro 15

Eliminação de Duplicatas

SQL não trata relação como um conjunto

Resultado de uma consulta pode conter tuplas duplicadas

Cláusula DISTINCT: elimina duplicações

Recupere os salários pagos pela empresa

SELECT DISTINCT salarioFROM Empregados

© José Maria Monteiro 16

Operações de Conjunto

(SELECT CPFFROM EmpregadosWHERE CDep = 2)UNION(SELECT DISTINCT ChefeFROM EmpregadosWHERE CDep = 2)

SQL2: UNION, MINUS (EXCEPT), INTERSECT

© José Maria Monteiro 17

Consultas Aninhadas IN, NOT IN

SELECT DISTINCT CPFFROM EmpregadosWHERE CPF IN (SELECT CPF

FROM EmpregadosWHERE CDep = 2)

OR CPF IN (SELECT Chefe

FROM EmpregadosWHERE CDep = 2)

Consulta completa na cláusula WHERE WHERE retorna TRUE se atributo está no resultado da

consulta aninhada

© José Maria Monteiro 18

Consultas Aninhadas ALL, ANY (SOME)

SELECT ENomeFROM EmpregadosWHERE Salario > ALL(SELECT Salario

FROM EmpregadosWHERE CDep = 2)

WHERE retorna TRUE se atributo satisfaz a condição em todas as tuplas resultantes da consulta aninhada

© José Maria Monteiro 19

Cláusula Exist

Verifica se o resultado de uma consulta aninhada não é vazio

SELECT DNomeFROM DepartamentosWHERE Exist (SELECT *

FROM ProjetosWHERE CDep = Código)

Recupere o nome dos departamentos que têm pelo menos um projeto

© José Maria Monteiro 20

Cláusula Not Exist

Verifica se o resultado de uma consulta aninhada é vazio

SELECT DNomeFROM DepartamentosWHERE Not Exist (SELECT *

FROM ProjetosWHERE CDep = Código)

Recupere o nome dos departamentos que não têm projetos

© José Maria Monteiro 21

Cláusula NULLIS NULL, IS NOT NULL

Permite recuperar tuplas onde o valor de um atributo (não) é NULL

SELECT ENomeFROM EmpregadosWHERE Chefe IS NULL

Recupere o nome dos empregados que não têm chefe

© José Maria Monteiro 22

Aliases

Renomear atributos e relações

SELECT ENome as Empregado_Nome, SalárioFROM Empregados AS E, Empregados AS CWHERE E.Salario > C.Salario AND

E.Chefe = C.CPF

Recupere o nome e o salário dos empregados cujo salário é maior do que o salário do seu chefe

© José Maria Monteiro 23

Funções Agregadas

SUM, AVG, MAX, MIN

SELECT SUM(Salario), AVG(Salario)FROM EmpregadosWHERE CDep = 3

Recupere a média e o total dos saláriosdos empregados do Departamento 3

© José Maria Monteiro 24

Funções Agregadas - COUNT

Retorna o número de tuplas de uma relação

SELECT COUNT *FROM Projetos, TarefasWHERE Projetos.Nome = ‘Informatização’ AND

Projetos.PCodigo = Tarefas.PCodigo

Recupere o número de empregados que trabalham no projeto de Informatização

© José Maria Monteiro 25

Agrupamento de Tuplas

Subgrupo de tuplas segundo o valor de um atributo

Aplicar funções de agregação sobre subgrupos

Atributo de agrupamento deve estar na cláusula SELECT

GROUB BY é aplicado após junção

© José Maria Monteiro 26

GROUP BY

SELECT CDep, COUNT(*), AVG(Salario)FROM EmpregadosGROUP BY CDep

CDep COUNT(*) AVG(Salario)

3 3 11000,00

2 2 10334,00

4 1 15000,00

© José Maria Monteiro 27

Cláusula Having

Usada em conjunto com cláusula GROUP BY

Determina condição sobre subgrupos de tuplas

Condição aplicada após a cláusula WHERE

SELECT Projetos.PCodigo, PNome, COUNT(*)FROM Projetos, TarefasWHERE Projetos.PCodigo = Tarefas.PCodigoGROUP BY Projetos.PCodigoHAVING COUNT(*) > 2

Para cada projeto onde trabalham mais de 2 empregados, recupere o código do projeto, seu nome e o número de empregados que trabalham no projeto

© José Maria Monteiro 28

Cláusula Like

Permite comparação de substrings% representa strings parciais_ representa um único caracter

SELECT ENome, CPFFROM EmpregadosWHERE Nasc LIKE ‘______6_’

Recupere o nome e o CPF de cada empregado que nasceu nos anos 60

© José Maria Monteiro 29

Operações Aritméticas

Uso de operações aritméticas sobre valores numéricos

Adição, subtração, multiplicação e divisão

SELECT DISTINCT ENome, 1.1*SalarioFROM Empregados, Tarefas, ProjetosWHERE Empregados.CPF = Tarefas.CPF AND

Tarefas.PCodigo = Projetos.PCodigo AND Projetos.PName LIKE ‘Inf%’

Mostre o salário resultante de um aumento de 10% sobre o salário dos empregados que trabalham na Informatização

© José Maria Monteiro 30

Cláusula Order ByClassificação do resultado segundo uma

ordem total sobre os valores de um ou mais atributos

SELECT Codigo, DNome, ENomeFROM Departamentos, EmpregadosWHERE Departamentos.Codigo = Empregados.CDepORDER BY Codigo DESC, ENome ASC

Recupere o código e o nome dos departamentos e de todos os seus empregados na ordem decrescente de departamento e crescente de empregado

© José Maria Monteiro 31

Subconsultas na Cláusula FROM

SELECT dnomeFROM (SELECT DNome, AVG(Sal)

FROM Departamentos D, Empregados E WHERE D.Codigo = E.Cdep GROUP BY DNome) AS resultado(dnome,avgsal)

WHERE avgsal > 2.000

Quais departamentos têm uma média de salários maior que R$ 2.000

© José Maria Monteiro 32

Compute e Compute By

SELECT tipo, precoFROM TITULOSWHERE tipo like ‘%info’ORDER BY tipo, precoCOMPUTE SUM(preco)

Liste todos os tipos de livros que terminam com ‘info’ e a soma total dos seus preços. TITULO(titulo, tipo, preco)

© José Maria Monteiro 33

Compute e Compute By

SELECT tipo, precoFROM TITULOSWHERE tipo like ‘%info’ORDER BY tipo, precoCOMPUTE SUM(preco) By tipoCOMPUTE SUM(preco)

Liste todos os tipos de livros que terminam com ‘info’ e a soma total dos seus preços. TITULO(titulo, tipo, preco)

© José Maria Monteiro 34

Contains e Not Contains

SQL tem especificado o operador de comparação CONTAINS

S1 CONTAINS S2 Retorna verdadeiro se S1 contém todos os

valores de S2A maioria dos sistemas não implementam

este operador

© José Maria Monteiro 35

Contains e Not Contains

SELECT DISTINCT S.cliente-nomeFROM deposito SWHERE (SELECT T.agencia-nome

FROM deposito T WHERE S.cliente-nome = T.cliente-

nome) CONTAINS (SELECT agencia-nome FROM agencia WHERE agencia-

cidade=‘Fortaleza’)

Liste todos os clientes que possuem uma conta em todas as agências localizadas em ‘Fortaleza’.

© José Maria Monteiro 36

Comandos de Atualização

INSERT: Adiciona tuplas a uma relação

DELETE: Exclui tuplas de uma relação

UPDATE: Modifica valores de atributos de uma relação

© José Maria Monteiro 37

Comando INSERT

INSERT INTO EmpregadosVALUES (‘Chiquin’,‘1234’, ‘rua 1, 1’, ‘02/02/62’,

‘M’, 10000.00, ‘8765’, ‘3’)

INSERT INTO Empregados (ENome, CPF, Salário)VALUES (‘Zulmira’,‘3456’, 12000.00,)

INSERT INTO DUnidadeSELECT CDep,

CidadeFROM ProjetosGROUP BY CDep

© José Maria Monteiro 38

Comando DELETE

DELETE FROM EmpregadosWHERE CPF = ‘3456’

DELETE FROM EmpregadosWHERE CDep IN (SELECT Codigo

FROM Departamentos WHERE DNome =

‘Administração’)

© José Maria Monteiro 39

Comando UPDATE

UPDATE ProjetosSET Cidade = ‘Taíba’WHERE CDep = ‘3’

UPDATE EmpregadosSET Salario = Salario * 1.5WHERE CDep IN (SELECT Codigo

FROM Departamentos WHERE DNome = ‘Pesquisa’)

© José Maria Monteiro 40

Visões

Tabelas virtuais

Derivadas de tabelas base através de comandos SQL

Limitações de atualização

Interessantes para consultas freqüentes

© José Maria Monteiro 41

Especificação de Visões

CREATE VIEW INFO_DEP (DNome, NEmp, SalTotal)AS SELECT DNome, COUNT(*), SUM(Salario)

FROM Departamentos, EmpregadosWHERE Departamentos.Codigo =

Empregados.DCodGROUP BY DNome

SELECT DNomeFROM Info_DepWHERE NEmp < 10

Recupere o nome dos departamentosque têm menos de 10 empregados

© José Maria Monteiro 42

Integridade Semântica

Restrição do domínio de um atributoCláusula CHECK do comando CREATE TABLE

Asserções declarativas (SQL2)Especificadas através de uma condição sobre a baseSGBD assegura que condição nunca é violada

Gatilhos (“Triggers”)Especifica ação no lugar do “abort transaction”Condição é equivalente a uma asserçãoAção é procedural

© José Maria Monteiro 43

CHECK

CREATE TABLE Empregados ( ....

salario DECIMAL(6,2),....sexo CHAR,....CHECK (salario <= 11000.00),CHECK (sexo IN ( ‘M’, ‘F’ ) )

)

O salário de um empregado não pode ser maior do que R$11000,00 e ele sópode ser homem ou mulher

© José Maria Monteiro 44

Asserções

CREATE ASSERTION SalEmpCHECK ( NOT EXISTS (SELECT *

FROM Departamentos D, Empregados E, Empregados G

WHERE E.Salário > G.Salario AND E.CDep = D.Codigo AND D.Gerente = G.CPF) )

O salário de um empregado não pode ser maior do que o salário do gerente do departamento em que ele trabalha

© José Maria Monteiro 45

Triggers

insert

update

delete

Eventos

ComandoSQL

StoredProcedureReação automática a

eventos monitorados

© José Maria Monteiro 46

Triggers

Usar 650 parafusos

“estoque de parafusos: 850”

parafusos 1500 850

Se quantidade de parafusos em estoque é menor que 1000, então “acordar” comprador

= > comprar 1000

SGBD Ativo

© José Maria Monteiro 47

Trigger - Exemplo

DEFINE TRIGGER Departamentos D, Empregados E, Empregados G: WHERE E.Salario > G.Salario AND

E.CDep = D.Codigo AND D.Gerente = G.CPF

ACTION_PROCEDURE(NOTIF_GERENTE(D.Gerente);

Notificar o gerente se qualquer empregado do seu departamento tem o salário maior do que o seu

© José Maria Monteiro 48

Stored Procedures

Coleção denominada de comandos SQL

Mecanismo semelhante ao RPC Código sob o controle do SGBD Passagem de parâmetros 1 chamada n procedures Diminui tráfego de rede

Aplicação

StoredProcedures

execprocedure

retornaresultado

© José Maria Monteiro 49

Stored Procedures

FunçõesRegras de negócio e integridade dos dados no SGBDAdministração do sistemaLado “servidor” da lógica da aplicação

CaracterísticasComandos SQL compartilhados por aplicaçõesMelhor desempenho em ambientes C/SMelhora autonomia de sítio

© José Maria Monteiro 50

Stored Procedures

Menor flexibilidade

Pode piorar desempenho se o plano de execução não for periodicamente refeito

Cada procedure é por default uma transação separada

Falta de padronização!!!

© José Maria Monteiro 51

Integração com LPs

Servidor de BD

Protocolos

SQL FAP

SQLembutido

Chamadas CLI

SQL-CLI

Run-time dopré-compilador

Run-timedo CLI

Drivers do BD

Protocolos

Pré-compilador

© José Maria Monteiro 52

SQL Embutido

Padronização na ISO SQL-92

Fonte SQL tratado por um pré-compilador

Pré-compilador é projetado para um servidor de BD específico

Exec SQLselect nome, endereçofrom Empregadoswhere idade<30;

© José Maria Monteiro 53

SQL Embutido

DCL apnome char (30);DCL apmatricula char (7);DCL apfone char (8);

EXEC SQL DECLARE curs CURSOR FORSELECT nome, matricula, telefoneFROM EstudanteWHERE Semestre = 8

EXEC SQL OPEN curs;IF SQLCODE <> 0

THEN erro;Else

DO WHILE SQLCODE = 0;EXEC SQL FETCH curs

INTO apnome, apmat, apfone;END;

EXEC SQL CLOSE curs;

© José Maria Monteiro 54

SQL Embutido

Código mudado

Código SQL

Cod. Objeto

Plano de acesso

Pré-compilador

Compilador

SQL Binder

Ligação

Programa ExecutávelPrograma Executável

Código fonte

© José Maria Monteiro 55

SQL Call-Level Interfaces

© José Maria Monteiro 56

X/Open SAG CLI

DB2

Aplicação Aplicação

Gateway - DRDA

Driver DB2 CLI

X/Open CLI

Idéia: mesma interface de programação para acesso a qualquer BD

Funções que correspondem a especificação SQL-89

Somente SQL dinâmico

X/Open CLI SQL3 CLI

© José Maria Monteiro 57

Microsoft ODBCAPI Windows outras plataformas

Chamadas API divididas em níveisNúcleo: conexão, comandos SQL, transaçõesNível 1: catálogo, BLOBs, funções de driversNível 2: recuperação de dados usando cursor

Fabricantes de SGBDs fornecem drivers em um dos níveis

Fabricantes suportam ODBC e APIs SQL nativas

© José Maria Monteiro 58

ODBC

DB2 Oracle NonStop SQL SQL Server

Gerenciador de driver ODBC

API ODBC

Aplicação Aplicação Aplicação

API de Serviço

DriverOracle

DriverDB2

DriverTandem

DriverSQL Server

ESQL/DRDA SQL*Net NS SQL Net Lib