1 / 54

Regras (Restrições) de Integridade

Regras (Restrições) de Integridade. Sistemas comerciais relacionais são muito finos de restrições para garantir a qualidade dos dados Em essência: Programação SQL é usada para descrever restrições. Resumo Declarações de chave primária (já cobertas).

mingan
Download Presentation

Regras (Restrições) de Integridade

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Regras (Restrições) de Integridade Sistemas comerciais relacionais são muito finos de restrições para garantir a qualidade dos dados • Em essência: Programação SQL é usada para descrever restrições. Resumo • Declarações de chave primária (já cobertas). • Chaves externas (estrangeiras) = restrições de integridade referencial. • Verificações (“checks”) nos níveis Atributo e Tupla • Triggers. MCS

  2. Chaves Externas Numa relação R uma cláusula “attribute A references S(B)” diz que qualquer valor que aparece na coluna A de R deve também aparecer na coluna B da relação S. • B deve ser declarada a chave primária de S. Exemplo CREATE TABLE Cerveja ( nome CHAR(20) PRIMARY KEY, fab CHAR(20) ); CREATE TABLE Vende( bar CHAR(20), cerveja CHAR(20) REFERENCES Cerveja(nome), preço REAL ); MCS

  3. Alternativa: adicionar outro elemento declarando a chave externa, como: CREATE TABLE Vende ( bar CHAR(20), cerveja CHAR(20), preço REAL, FOREIGN KEY cerveja REFERENCES Cerveja(nome) ); • O elemento extra é essencial se a chave externa é composta de mais de um atributo. MCS

  4. O Que Acontece se a Regra de Integridade Referencial é Violada? • Três políticas: • Inserir ou atualizar uma tupla de Vende referindo-se a uma cerveja inexistente. • Sempre rejeitada. • Remover ou atualizar uma tupla de Cerveja que tem tuplas em Vende que a referenciam. • Default: rejeitar. • Cascade: Propaga as mudanças para as tuplas de Vende. Exemplo • Delete “Bud.” Cascade remove todas as tuplas de Vende que mencionam Bud. • Update “Bud” to “Budweiser.” Muda todas as tuplas de Vende com “Bud” para “Budweiser.” MCS

  5. Set Null: Muda as tuplas que referenciam para NULL nas colunas correspondentes. Exemplo • Delete “Bud.” Set-null faz todas as tuplas de Vende com “Bud” no componente cerveja passar a ter cerveja ISNULL. • Update “Bud” to “Budweiser.” Mesma mudança. MCS

  6. Selecionando uma Política Add ON [DELETE, UPDATE] [CASCADE, SET NULL] à declaração de chave externa. Exemplo CREATE TABLE Vende( bar CHAR(20), cerveja CHAR(20), preço REAL, FOREIGN KEY cerveja REFERENCES Cerveja(nome) ON DELETE SET NULL ON UPDATE CASCADE ); • A política “correta” é uma decisão do projetista. MCS

  7. Verificação no Nível Atributo Segue um atributo com uma condição que deve ser verdadeira para o valor do atributo em cada tupla da relação. • Forma: CHECK (condição). • A condição pode envolver o atributo. • Outros atributos e relações podem ser envolvidos, mas somente em subconsultas. • Oracle: subconsultas não são permitidas. • A condição é verificada somente quando o atributo muda (i.e., uma inserção ou uma atualização). MCS

  8. Exemplo CREATE TABLE Vende( bar CHAR(20), cerveja CHAR(20) CHECK( cerveja IN (SELECT nome FROM Cerveja), preço REAL CHECK( preço <= 5.00) ); • Check em cerveja é como uma integridade referencial, exceto: • O check ocorre somente na inclusão da tupla ou na mudança da cerveja em uma tupla existente, não quando a tupla é removida de Cerveja. MCS

  9. Verificação no Nível Tupla Elemento separado na declaração de tabela. • Forma: como verificação no nível atributo. • Mas a condição pode se referir a qualquer atributo da relação. • Ou a outros atributos/relações em subconsultas. • Outra vez: Oracle proíbe o uso de subconsulta. • Verificação quando a tupla é inserida ou atualizada. MCS

  10. Exemplo Somente o bar Tricolor pode vender cerveja por mais de 5,00. CREATE TABLE Vende( bar CHAR(20), cerveja CHAR(20), preço REAL, CHECK(bar = `Tricolor´ OR preço <= 5.00) ); MCS

  11. IX- Triggers (Versão Oracle) Chamados de regras ECAevento-condição-ação. • Evento = uma mudança no BD, i.e., INSERT, UPDATE, DELETE • Condição = um teste como em uma cláusula where para ver se o trigger se aplica. • Ação = um ou mais comandos SQL. • Difere de check: • Triggers são invocados por um evento; não existe a idéia de violação de triggers. • Checks não suportam <condição>. MCS

  12. Exemplo Quando uma tupla é inserida em Vende, a cerveja deve existir em Cerveja; se não, insere-a (com fab IS NULL). Vende(bar, cerveja, preço) CREATE OR REPLACE TRIGGER TCerveja AFTER INSERT ON Vende FOR EACH ROW WHEN(new.cerveja NOT IN (SELECT nome FROM Cerveja)) BEGIN INSERT INTO Cerveja(nome) VALUES(:new.cerveja); END; MCS

  13. Opções • Pode omitir OR REPLACE. Mas se fizer assim, será erro se o trigger já existir. • AFTER pode ser BEFORE. • Se uma relação é visão, AFTER pode ser INSTEADOF. • Útil por permitir “modificações” em uma visão; em vez disso, as relações concernentes são modificadas. • O evento pode ser INSERT, DELETE,UPDATEOF <atributo>. • Também, diversas condições como INSERT ON Vende podem ser conectadas por OR. • FOR EACH ROW pode ser omitida, com um importante efeito: a ação é feita uma só vez, antes ou depois de todos os eventos ocorrerem. MCS

  14. Notas • Há duas variáveis especiais new e old, representando as tuplas nova e velha na mudança. • old não faz sentido em inserção, e new não faz sentido em remoção. • Observe: em WHEN usamos new e old sem dois pontos, mas em ações, os dois pontos precedentes são necessários. • A ação é um comando PL/SQL. • Forma mais simples: um ou mais comandos SQL dentro de um bloco BEGIN - END. • Entretanto, select-from-where pode ser limitado. MCS

  15. Triggers são parte do esquema do BD, como tabelas ou visões. • Importante restrição do Oracle: a ação não pode alterar a relação que dispara a ação. • Pior, a ação não pode nem mesmo mudar a relação conectada à relação disparadora por uma restrição, i.e., uma integridade referencial. MCS

  16. Exemplo Manter uma lista de todos os bares que aumentaram o preço de alguma cerveja em mais de 1,00. Vende(bar, cerveja, preço) Lista(bar) CREATE TRIGGER TrigPreçc AFTER UPDATE OF preço ON Vende FOR EACH ROW WHEN(new.preço > old.preço + 1.00) BEGIN INSERT INTO Lista VALUES(:new.bar); END; . run MCS

  17. Modificando Visões Via Triggers Oracle permite-nos “interceptar” uma modificação em uma visão através de um instead of trigger. Exemplo Gosta(boêmio,cerveja) Vende(bar, cerveja, preço) Freqüenta(boêmio, bar) CREATE VIEW Sinergia AS SELECT Gosta.boêmio, Gosta.cerveja Vende.bar FROM Gosta, Vende, Freqüenta WHERE Gosta.boêmio = Freqüenta.boêmio AND Gosta..cerveja = Vende.cerveja AND Vende.bar = Freqüenta.bar; MCS

  18. CREATE TRIGGER VisaoTrig INSTEAD OF INSERT ON Sinergia FOR EACH ROW BEGIN INSERT INTO Gosta VALUES( :new.boêmio, :new.cerveja); INSERT INTO Vende(bar, cerveja) VALUES(:new.bar, :new.cerveja); INSERT INTO Freqüenta VALUES( :new.boêmio, :new.bar); END; . run MCS

  19. X- PL/SQL • Linguagem de desenvolvimento do Oracle. • Usada via a ferramenta Sqlplus. • Um compromisso entre uma linguagem de programação totalmente `procedural´ e a linguagem declarativa SQL. • Permite variáveis locais, laços, procedures, consulta a relações “one tuple at a time”. • Forma geral: DECLARE declarações BEGIN comandos executáveis END; . run; • A parte DECLARE é opcional. • `Dot and run´ finalizam o comando e o executam. MCS

  20. Forma Mais Simples: Uma Seqüência de Modificações Gosta(boêmio, cerveja) BEGIN INSERT INTO Gosta VALUES('Salete, 'Bud'); DELETE FROM Gosta WHERE boêmio = 'Fred' AND cerveja = 'Muller'; END; MCS

  21. Procedures Objetos armazenados no BD, que usam comandos PL/SQL em seus corpos. Declarações de Procedure CREATE OR REPLACE PROCEDURE <nome>(<lista_argumentos>) AS <declarações> BEGIN <comandos PL/SQL> END; MCS

  22. <Lista_argumentos> tem triplas nome-modo-tipo. • Modo: IN, OUT, ou IN OUT para read-only, write-only, read/write, respectivamente. • Tipos: padrão SQL + tipos genéricos como NUMBER = qualquer tipo inteiro ou real. • Desde que tipos nas procedures devem casar com tipos no esquema do BD, pode-se usar uma expressão da forma relação.atributo %TYPE para capturar o tipo corretamente. MCS

  23. Exemplo Uma procedure que inclui uma nova cerveja e seu preço no menu do bar RubroNegro. Vende(bar, cerveja, preço) CREATE PROCEDURE MenuRubroNegro( c IN Vende.cerveja %TYPE, p IN Vende.preço %TYPE ) AS BEGIN INSERT INTO Vende VALUES(`RubroNegro´´, c, p); END; . run; • Note “run” somente armazena a procedure, não a executando. MCS

  24. Invocando Procedures Uma chamada a uma procedure pode aparecer no corpo de um comando PL/SQL. • Exemplo: BEGIN MenuRubroNegro('Bud', 2,50); MenuRubroNegro('MooseDrool', 5,00); END; MCS

  25. Atribuição Atribuição de valores a variáveis é denotada por :=. Desvio IF <condição> THEN <comando(s)> ELSE <comando(s)> END IF; • Em `ninhos´ de IFs, use ELSIF em lugar de ELSE IF. Laço LOOP . . . EXIT WHEN <condição> . . . END LOOP; MCS

  26. Consultas em PL/SQL • Single-row selects permitem atribuir a uma variável o resultado de uma consulta que produz uma única tupla. • Cursors permitem a recuperação de muitas tuplas, com o cursor e um laço sendo usados para processar tupla-a-tupla. MCS

  27. Single-Row Select • Um select-from-where em PL/SQL deve ter uma cláusula INTO listando as variáveis que recebem os resultados da consulta. • É erro se o select-from-where retorna mais de uma tupla; neste caso, é preciso usar um cursor. Exemplo • Encontrar o preço da cerveja Schincarioll no bar Tricolor. Vende(bar, cerveja, preço) DECLARE p Vende.preço %TYPE; BEGIN SELECT preço INTO p FROM Vende WHERE bar = `Tricolor´ AND cerveja = `Schincarioll´; END; MCS

  28. Cursores Declarados por: CURSOR <nome> IS comando select-from-where • O cursor aponta para cada tupla por vez da relação-resultado da consulta select-from-where, usando um fetch statement dentro de um laço. • Fetch statement: FETCH <nome_cursor> INTO lista_variáveis; • Um laço é interrompido por: EXIT WHEN <nome_cursor> %NOTFOUND; • O valor é Verdade se não houver mais tupla a apontar. • OPEN e CLOSE abrem e fecham um cursor, respectivamente. MCS

  29. Exemplo Uma procedure que examina o menu do bar Tricolor e aumenta de 1,00 todos os preços que são menores que 3,00. Vende(bar, cerveja, preço) • Um simples UPDATE podia resolver o problema, mas mudanças mais complicadas podiam exigir uma procedure. MCS

  30. CREATE PROCEDURE Aumento() AS aCerveja Vende.cerveja%TYPE; oPreço Vende.preço%TYPE; CURSOR c IS SELECT cerveja, preço FROM Vende WHERE bar =`Tricolor´; BEGIN OPEN c; LOOP FETCH c INTO aCerveja, oPreço; EXIT WHEN c%NOTFOUND; IF oPreço < 3.00 THEN UDPATE Vende SET preço = oPreço + 1.00 WHERE bar = `Tricolor´ AND cerveja = aCerveja; END IF; END LOOP; CLOSE c; END; MCS

  31. Tipo Row Qualquer coisa (i.e., cursores, nomes de tabela) que tem um tipo tupla pode ter seu tipo capturado com %ROWTYPE. • Pode-se criar variáveis temporárias tipo tupla e acessar seus componentes como variável.componente (“dot notation”). • Muito útil, principalmente se a tupla tem muitos componentes. MCS

  32. Exemplo A mesme procedure com a variável tupla cp. CREATE PROCEDURE Aumento() AS CURSOR c IS SELECT cerveja, preço FROM Vende WHERE bar = `Tricolor´; cp c%ROWTYPE; BEGIN OPEN c; LOOP FETCH c INTO cp; EXIT WHEN c%NOTFOUND; IF cp.preço < 3.00 THEN UDPATE Vende SET preço= cp.preço + 1.00 WHERE bar = `Tricolor´ AND cerveja = cp.cerveja; END IF; END LOOP; CLOSE c; END; MCS

  33. XI- Linguagens Hospedeiras de SQL Acrescenta a uma linguagem de programação convencional certos comandos que representam operações SQL. • Cada comando SQL é introduzido com EXEC SQL. • Um preprocessador converte p.e. C + SQL para puro C. • Os comandos SQL tornam-se chamadas de procedure. MCS

  34. Variáveis Compartilhadas Um trecho especial de declarações de variáveis em C é acessível tanto por SQL quanto por C. EXEC SQL BEGIN/END DECLARE SECTION; • Em Oracle Pro/C (não C++) os delimitadores são opcionais. • Em C, as variáveis são usadas normalmente; em SQL, elas devem ser precedidas por :. MCS

  35. Exemplo Encontre o preço de uma cerveja em um dado bar. Vende(bar, cerveja, preço) EXEC SQL BEGIN DECLARE SECTION; char oBar[21], aCerveja[21]; float oPreco; EXEC SQL END DECLARE SECTION; . . . /* inicializa oBar e aCerveja */ . . . EXEC SQL SELECT preco INTO :oPreco FROM Vende WHERE cerveja = :aCerveja AND bar = :oBar; . . . MCS

  36. Cursores Similar a PL/SQL, com algumas diferenças sintáticas. Exemplo Imprima o menu do bar Tricolor. Vende(bar, cerveja, preco EXEC SQL BEGIN DECLARE SECTION; char aCerveja[21]; float oPreco; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE c CURSOR FOR SELECT cerveja preco FROM Vende WHERE bar = `Tricolor´; EXEC SQL OPEN CURSOR c; while(1) { EXEC SQL FETCH c INTO :aCerveja, :oPreco; if(NOT FOUND) break; /* formata e imprime a cerveja e o preço */ } EXEC SQL CLOSE CURSOR c; MCS

  37. Oracle Vs. SQL • SQL define um array de caracteres SQLSTATE que é atualizado cada vez que o sistema é chamado. • Os erros são assinalados no array. • Uma falha do cursor para encontrar qq tupla é registrado no array. • Entretanto, Oracle provê um arquivo sqlca.h que declara uma communication area e define macros para acessá-la. • Em particular, NOT FOUND é uma macro que diz “the no-tuple-found signal was set.” MCS

  38. SQL Dinâmico Motivação: • SQL em uma linguagem hospedeira é bom em aplicações estáticas, p.e., um programa de reserva de passagens aéreas. • Não serve para escrever um programa como sqlplus, porque não se sabe de antemão qual o código que segue um sinal de prontidão SQL>. • Para resolver, precisamos de dois comandos: • PREPARE converte uma cadeia de caracteres em um comando SQL. • EXECUTE executa o comando. MCS

  39. Exemplo: Versão Simplificada do Sqlplus EXEC SQL BEGIN DECLARE SECTION; char query[MAX_QUERY_LENGTH]; EXEC SQL END DECLARE SECTION; /* issue SQL> prompt */ /* read user's text into array query */ EXEC SQL PREPARE q FROM :query; EXEC SQL EXECUTE q; /* go back to reissue prompt */ • Uma vez preparada, uma consulta pode ser executada muitas vezes. • “Prepare” = otimiza a consulta, i.e., encontra um meio de executá-la com um mínimo de I/O’s. • Alternativamente, PREPARE e EXECUTE podem ser combinadas em: EXEC SQL EXECUTE IMMEDIATE :query; MCS

  40. Desvantagens da Abordagem 'Linguagens Hospedeira' • Nem é C e nem é SQL, nem é Java e nem é SQL, … • O programa é executado no lado cliente da arquitetura cliente-servidor • Problema de desempenho MCS

  41. XII- Interfaces “Call-Level” Nesta abordagem, C (ou outra linguagem) cria comandos SQL como cadeias de caracteres passados como argumentos de funções que são parte de uma biblioteca. • SQL/CLI (ODBC = open database connectivity) e JDBC (Java database connectivity). • Grande vantagem em relação ao enfoque Linguagem Hospedeira: o programa continua C puro MCS

  42. ODBC • Em C, a biblioteca permite que você crie um statement handle = estrutura em que você coloca um comando SQL. • Use SQLPrepare(myHandle, <statement>,...) para fazer myHandle representar o comando SQL no segundo argumento. • Use SQLExecute(myHandle) para executar o comando. Exemplo SQLPrepare(handle1, "SELECT cerveja, preco FROM Vende WHERE bar = `Tricolor´";) SQLExecute(handle1); MCS

  43. Recuperando Dados Para obter os dados retornados por uma consulta, usa-se: • Variáveis de ligação (“bind”) para os componentes das tuplas retornadas. • SQLBindCol aplica-se a um handle, o número da coluna, e a variável de ligação, mais outros argumentos. • Comando “Fetch”, usando o handle. • SQLFetch aplica-se a um handle. Exemplo SQLBindCol(handle1, 1, SQL_CHAR, &oBar,...) SQLBindCol(handle1, 2, SQL_REAL, &oPreco...) SQLExecute(handle1); ... while(SQLFetch(handle1) != SQL_NO_DATA) { ...} MCS

  44. JDBC • Começa com o objeto Connection, obtido do SGBD. • O método createStatement() retorna um objeto da classe Statement (se não houver argumento) ou PreparedStatement se houver um comando SQL como argumento ("overload" de métodos). Exemplo Statement stat1 = myCon.createStatement(); PreparedStatement stat2 = myCon.createStatement( "SELECT cerveja, preco" + "FROM Vende" + "WHERE bar = ‘Tricolor'" ); • myCon é uma conexão, stat1 é um objeto “vazio” da classe Statement, e stat2 é um objeto da classe PreparedStatement que tem um comando SQL associado. MCS

  45. Executando Comandos • JDBC distingue consultas (comandos que retornam dados) de updates (comandos que somente afetam o BD). • Os métodos executeQuery() e executeUpdate() são usados para executar essas duas espécies de comandos. • Eles devem ter um argumento se aplicados a Statement, nunca se aplicados a PreparedStatement. • Quando uma consulta é executada, retorna um objeto da classe ResultSet. Exemplo stat1.executeUpdate( "INSERT INTO "Vende" + "VALUES(‘RubroNegro', ‘Cristal', 3.00)" ); ResultSet Menu = stat2.executeQuery(); MCS

  46. Obtendo as Tuplas de um ResultSet • O método Next() se aplica a um ResultSet e move um “cursor” para a próxima tupla do conjunto. • Aplique Next() para obter a primeira tupla. • Next() returna FALSE se não houver mais tuplas. • Para a tupla corrente do cursor, você pode obter seu i-ésimo componente aplicando o método getX(i), onde X é o nome para o tipo do argumento. Exemplo while(Menu.Next()) { aCerveja = Menu.getString(1); oPreco = Menu.getFloat(2); ... } MCS

  47. Desvantagens de ODBC/JDBC • Os programas C, Java, … , ainda são executados no lado cliente MCS

  48. Integração Estreita com SGBDs • PL/SQL, JSQL, TransactSQL, … , são extensões da SQL • Processadas no lado servidor da arquitetura cliente - servidor • Isto é muito bom para o desempenho MCS

  49. XIII- Transações, Segurança Transação = unidade de trabalho que deve ser: • Atômica = ou todo o trabalho é feito, ou nada é feito • Consistente = as regras de integridade são asseguradas • Isolada = tudo se parece como se ela executasse sozinha 4. Durável = seus efeitos são permanentes mesmo em presença de falha MCS

  50. Decisão deValidar / Abortar Uma transação pode terminar de duas maneiras: • Commit = todo o trabalho da transação é refletido no BD; todas as mudanças antes de “commit” são invisíveis a outras transações • Abort = como se a transação nunca tivesse ocorrido • ROLLBACK é o termo da SQL e do Oracle • Transações podem ser um simples comando SQL • Oracle tem a opçãp SET TRANSACTION READ ONLY que começa uma transação multi-comandos que não modifica dados, mas necessita ter uma visão consistente do BD • Em interfaces de programação, as transações começam quando o usuário se conecta, e terminam ou quando um comando COMMIT ou ROLLBACK é executado MCS

More Related