Introdução a Bancos de Dados José Maria Monteiro Departamento de Computação Universidade Federal do Ceará

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


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


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


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


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 +


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 +


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


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),);


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),

)


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


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


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


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


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


Operações de Conjunto

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

SQL2: UNION, MINUS (EXCEPT), INTERSECT


Consultas Aninhadas IN, NOT IN

SELECT DISTINCT CPFFROM EmpregadosWHERE CPF IN (SELECT CPF

FROM EmpregadosWHERE CDep = 2)

OR CPF IN (SELECT Chefe


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

consulta aninhada


Consultas Aninhadas ALL, ANY (SOME)

SELECT ENomeFROM EmpregadosWHERE Salario > ALL(SELECT Salario


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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)


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)


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


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’.


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


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


Comando DELETE

DELETE FROM EmpregadosWHERE CPF = ‘3456’

DELETE FROM EmpregadosWHERE CDep IN (SELECT Codigo

FROM Departamentos WHERE DNome =

‘Administração’)


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’)


Visões

Tabelas virtuais

Derivadas de tabelas base através de comandos SQL

Limitações de atualização

Interessantes para consultas freqüentes


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


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


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


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


Triggers

insert

update

delete

Eventos

ComandoSQL

StoredProcedureReação automática a

eventos monitorados


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


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


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


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


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!!!


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


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;


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;


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


SQL Call-Level Interfaces


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


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


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

Documents

Introdução a Bancos de Dados José Maria Monteiro Departamento de Computação Universidade Federal do Ceará