Unidade III - BD Relacional Structured Query Language (SQL) Conceitos Iniciais Álgebra Relacional - muito formal Cálculo Relacional de Tupla SEQUEL (Structured English QUEry Language) IBM - Sistema R SQL-86 SQL-92 SQL-3 102 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) Comandos DDL SQL 2 Consultas SQL Consultas Avançadas Inserção, Deleção e Atualização Visões em SQL 103 GZK/2000 1 Unidade III - BD Relacional Structured Query Language (SQL) - DDL Esquemas Elementos do esquemas incluem tabelas, restrições, visões, domínios, autorizações CREATE SCHEMA EMPRESA AUTHORIZATION GUSTAVO 104 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DDL Tabelas Especifica uma nova relação através de seus atributos e restrições CREATE TABLE FUNCIONARIOS . . . . . CREATE TABLE EMPRESA.FUNCIONARIOS . . . 105 GZK/2000 2 Unidade III - BD Relacional Structured Query Language (SQL) - DDL Tabelas - Tipos de dados integer, smallint, float, real, double precision, decimal(i,j), numeric(i,j), char(n), varchar(n), bit(n), date, time, timestamp Blob, Clob, BigInt, Long Varchar, Graphic, Vargraphic, DataLink, . . . Criar novos tipos de dados 106 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DDL Tabelas - restrições e valores default NOT NULL DEFAULT <valor> PRIMARY KEY UNIQUE FOREIGN KEY Restrições de Foreign key: SET NULL, CASCADE, RESTRICT, SET DEFAULT sobre ON DELETE e ON UPDATE 107 GZK/2000 3 Unidade III - BD Relacional Structured Query Language (SQL) - DDL Tabelas CREATE TABLE FUNCIONARIOS ( PNOME CHAR(30) NOT NULL , MNOME CHAR(30) , UNOME CHAR(30) NOT NULL , ANIVERSARIO DATE , ENDERECO VARCHAR(40) , SALARIO DECIMAL(10,2) , CI INTEGER NOT NULL , SEXO CHAR(1) , SUPERVISOR INTEGER , DNRO INTEGER NOT NULL DEFAULT 1 , PRIMARY KEY (CI) , CONSTRAINT EMPSUPER FOREIGN KEY (SUPERVISOR) REFERENCES FUNCIONARIOS (CI) ON DELETE SET NULL ON UPDATE CASCADE , CONSTRAINT DEPTO FOREIGN KEY (DNRO) REFERENCES DEPARTAMENTOS (NUMERO) ON DELETE RESTRICT ON UPDATE CASCADE 108 GZK/2000 ) ; Unidade III - BD Relacional Structured Query Language (SQL) - DDL Tabelas - Alteração e Eliminação DROP TABLE FUNCIONARIOS CASCADE; DROP TABLE EMPRESA.FUNCIONARIOS CASCADE; DROP SCHEMA EMPRESA CASCADE; ALTER TABLE FUNCIONARIOS ADD CPF CHAR(20); ALTER TABLE FUNCIONARIOS DROP ENDERECO CASCADE ALTER TABLE FUNCIONARIOS DROP CONSTRAINT EMSUPER CASCADE; 109 GZK/2000 4 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL SELECT FROM WHERE <lista de atributos> <lista de tabelas> <condição> 110 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar os aniversários e endereços dos funcionários cujo nome seja ‘Gustavo Zanini’ SELECT ENDERECO, ANIVERSARIO FROM FUNCIONARIOS WHERE PNOME = ‘GUSTAVO’AND UNOME = ‘ZANINI’ 111 GZK/2000 5 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar o nome e endereco de todos os funcionarios que trabalham no departamento ‘Pesquisa’ SELECT PNOME, MNOME, UNOME FROM FUNCIONARIOS, DEPARTAMENTOS WHERE NOME = ‘Pesquisa’AND NUMERO = DNRO 112 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Para cada projeto localizado em ‘UFSM’, listar o numero do projeto, o departamento que controla e o ultimo nome, endereço e aniversário do gerente do departamento SELECT PNUMERO, DNUM, UNOME, ENDEREÇO, ANIVERSARIO FROM FUNCIONARIOS, DEPARTAMENTOS, PROJETOS WHERE DNUM = NUMERO AND GERENTE = CI AND PLOCAL = ‘UFSM’ 113 GZK/2000 6 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Para cada funcionário, recuperar o primeiro e ultimo nome e o primeiro e ultimo nome do seu supervisor SELECT F.PNOME, F.UNOME, S.PNOME, S.UNOME FROM FUNCIONARIOS AS F, FUNCIONARIOS AS S WHERE S.SUPERVISOR = F.CI 114 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar todos os CI dos funcionarios SELECT CI FROM FUNCIONARIOS 115 GZK/2000 7 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar todos os atributos dos funcionarios que estão no departamento 5 SELECT * FROM FUNCIONARIOS WHERE DNRO = 5 116 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar o salario de todos os funcionários e todos os salários distintos SELECT ALL SALARIO FROM FUNCIONARIOS SELECT DISTINCT SALARIO FROM FUNCIONARIOS 117 GZK/2000 8 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Fazer uma lista de todos os número de projetos que envolvem um funcionário chamado ‘Smith’, ou como trabalhador ou como gerente de um departamento que controla o projeto. SELECT DISTINCT PNUMERO FROM PROJETOS, DEPARTAMENTOS, FUNCIONARIOS WHERE NUMERO = DNRO AND GERENTE = CI AND UNOME = ‘Smith’ UNION SELECT DISTINCT PNUMERO FROM PROJETOS, TRABALHAM, FUNCIONARIOS WHERE PNUMERO = PNO AND SUPERVISOR = CI AND UNOME = ‘Smith’ 118 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar todos os empregados cujo endereco está em Porto. . . SELECT PNOME,UNOME FROM FUNCIONARIOS WHERE ENDERECO LIKE ‘Porto%’ 119 GZK/2000 9 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar todos os empregados que nasceram durante 1950. SELECT PNOME,UNOME FROM FUNCIONARIOS WHERE ANIVERSARIO LIKE ‘_ _5_ _ _ _ _ _ _’ 120 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Mostrar os salários que trabalham no projeto ‘Projeto X’ com 10% de aumento. SELECT PNOME,UNOME, 1.1*SALARIO FROM FUNCIONARIOS AS F, PROJETOS AS P, TRABALHAM AS T WHERE F.CI = T.CI AND T.PNO = P.PNUMERO AND P.PNOME = ‘Projeto X’ 121 GZK/2000 10 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar todos os empregados do departamento 5 que recebem salario entre R$ 2.000,00 e R$ 5.000,00 SELECT * FROM FUNCIONARIOS WHERE DNRO = 5 AND (SALARIO BETWEEN 2000 AND 5000) 122 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas em SQL - Exemplos Recuperar a lista de funcionarios e os projetos onde trabalham, ordenado por departamento e em cada departamento, ordenar alfabeticamente pelo ultimo nome e primeiro nome SELECT NOME, UNOME, F.PNOME, P.PNOME FROM DEPARTAMENTOS D, FUNCIONARIOS F, TRABALHAM T , PROJETOS P WHERE NUMERO=DNRO AND F.CI= T.CI AND T.PNO = P.PNUMERO ORDER BY NOME, UNOME, F.PNOME 123 GZK/2000 11 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Recuperar o nome de cada funcionario que tem um dependente com o mesmo primeiro nome e o mesmo sexo do funcionario. SELECT FROM WHERE F.NOME, F.UNOME FUNCIONARIOS F F.CI IN (SELECT (SELECT CI FROM DEPENDENTES WHERE F.PNOME = NOME_DEP AND F.SEXO = SEXO ) 124 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Recuperar o nome de cada funcionario que tem um dependente com o mesmo primeiro nome e o mesmo sexo do funcionario. SELECT FROM WHERE 125 F.NOME, F.UNOME FUNCIONARIOS F , DEPENDENTES D F.CI = D.CI AND F.PNOME = D.NOME_DEP AND F.SEXO = D.SEXO GZK/2000 12 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Recuperar o nome de cada funcionário que tem um dependente com o mesmo primeiro nome e o mesmo sexo do funcionário. SELECT FROM WHERE F.NOME, F.UNOME FUNCIONARIOS F EXISTS (SELECT (SELECT * FROM DEPENDENTES WHERE F.PNOME = NOME_DEP AND F.SEXO = SEXO AND F.CI = CI) 126 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Recuperar o nome dos funcionários que não tem dependentes SELECT FROM WHERE 127 F.NOME, F.UNOME FUNCIONARIOS F NOT EXISTS (SELECT (SELECT * FROM DEPENDENTES WHERE F.CI = CI ) GZK/2000 13 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Recuperar o nome dos gerentes que tem pelo menos um dependente. SELECT FROM WHERE F.NOME, F.UNOME FUNCIONARIOS F EXISTS (SELECT (SELECT * FROM DEPENDENTES WHERE F.CI = CI ) AND EXISTS (SELECT (SELECT * FROM DEPARTAMENTOS WHERE F.CI = GERENTE ) 128 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Recuperar a carteira de identidade de todos os funcionários que trabalham no projeto 1, 2 ou 3. SELECT FROM WHERE 129 DISTINCT CI TRABALHAM PNO IN (1,2,3) GZK/2000 14 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Recuperar os nomes de todos os funcionários que não tem supervisores. SELECT FROM WHERE NOME, UNOME FUNCIONARIOS SUPERVISOR IS NULL 130 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Recuperar o nome e endereco de todos os funcionarios que trabalham no departamento ‘Pesquisa’ (ver slide 112) SELECT PNOME, MNOME, UNOME FROM FUNCIONARIOS JOIN DEPARTAMENTOS ON NUMERO = DNROS WHERE NOME = ‘Pesquisa’ 131 GZK/2000 15 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Para cada funcionário, recuperar o primeiro e ultimo nome e o primeiro e ultimo nome do seu supervisor (ver slide 114) SELECT F.PNOME AS NOME_EMPREGADO, S.PNOME AS NOME_SUPERVISOR FROM FUNCIONARIOS AS F LEFT JOIN FUNCIONARIOS AS S ON F.SUPERVISOR = S.CI 132 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Encontre a soma de salários de todos os funcionários, o salário máximo, o salário mínimo e a média de salário. SELECT SUM(SALARIO) , MAX (SALARIO), SUM MIN (SALARIO) , AVG (SALARIO) FROM FUNCIONARIOS 133 GZK/2000 16 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Encontre o número total de funcionários na empresa que trabalham no departamento ‘Pesquisa’. SELECT COUNT(*) COUNT FROM FUNCIONARIOS , DEPARTAMENTOS WHERE DNRO = NUMERO AND NOME = ‘PESQUISA’ 134 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Para cada departamento, recupere o número do departamento, o número de funcionários no departamento e a média salarial do departamento. SELECT DNRO, COUNT(*) COUNT , AVG (SALARIO) FROM FUNCIONARIOS GROUP BY DNRO 135 GZK/2000 17 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Para cada projeto, recupere o número do projeto, o nome do projeto e o número de funcionários que trabalham no projeto. SELECT FROM WHERE GROUP BY PNUMERO, PNOME, COUNT(*) COUNT PROJETOS, TRABALHAM PNUMERO = PNO PNUMERO, PNOME 136 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL - Exemplos Para cada projeto, no qual trabalham mais que 2 funcionários, recupere o número do projeto, o nome do projeto e o número de funcionários que trabalham no projeto. SELECT FROM WHERE GROUP BY HAVING 137 PNUMERO, PNOME, COUNT(*) COUNT PROJETOS, TRABALHAM PNUMERO = PNO PNUMERO, PNOME COUNT(*) > 2 COUNT GZK/2000 18 Unidade III - BD Relacional Structured Query Language (SQL) - DML Consultas mais complexas em SQL SELECT <lista de atributos e funções> FROM <lista de tabelas> [ WHERE <condição> ] [ GROUP BY <atributos de agrupamento> ] [ HAVING <condição de agrupamento> ] [ ORDER BY <lista de atributos> ] 138 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Insert, Update e Delete em SQL Comandos para modificação dos dados no banco de dados. INSERT INTO FUNCIONARIOS VALUES (‘Ricardo’,’K’,’Marini’,10370706334,’10.04.2000’,’M’,’Dr. Bozano,113’, 2500, 567456755, 4) INSERT INTO FUNCIONARIOS (PNOME, UNOME,DNRO, CI) VALUES (‘Ricardo’,’Marini’, 4, 10370706334) 139 GZK/2000 19 Unidade III - BD Relacional Structured Query Language (SQL) - DML Insert, Update e Delete em SQL INSERT INTO FUNCIONARIOS (CI, PNOME, UNOME, DNRO, SEXO) SELECT COD, NOME1, NOME2, DEPTO, SEXO FROM EMPREGADOS 140 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Insert, Update e Delete em SQL DELETE FROM FUNCIONARIOS WHERE UNOME = ‘ZANINI’ DELETE FROM FUNCIONARIOS WHERE CI = 12345678 DELETE FROM FUNCIONARIOS WHERE DNRO IN (SELECT SELECT NUMERO FROM DEPARTAMENTOS WHERE NOME = ‘Pesquisa’) 141 GZK/2000 20 Unidade III - BD Relacional Structured Query Language (SQL) - DML Insert, Update e Delete em SQL UPDATE SET WHERE PROJETOS PLOCAL = ‘UFSM’, DNO = 5 PNO = 10 UPDATE SET WHERE FUNCIONARIOS SALARIO = SALARIO * 1.1 DNRO IN (SELECT SELECT NUMERO FROM DEPARTAMENTOS WHERE NOME = ‘Pesquisa’) 142 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Views (Tabelas Virtuais) em SQL CREATE VIEW TRABALHAM1 AS (SELECT SELECT PNOME,UNOME, PNO, HOURS FROM FUNCIONARIOS F, TRABALHAM T, PROJETOS P WHERE F.CI = T.CI AND T.PNO = P.PNUMERO ) 143 GZK/2000 21 Unidade III - BD Relacional Structured Query Language (SQL) - DML Views (Tabelas Virtuais) em SQL CREATE VIEW DEPTO_INFO (DEPTO_NOME, NRO_FUNC, SAL_TOTAL) AS (SELECT SELECT NOME, COUNT(*), SUM (SALARIO) COUNT FROM FUNCIONARIOS F, DEPARTAMENTOS D WHERE NUMERO = DNRO GROUP BY NOME ) 144 GZK/2000 Unidade III - BD Relacional Structured Query Language (SQL) - DML Views (Tabelas Virtuais) em SQL DROP VIEW DEPTO_INFO DROP VIEW TRABALHAM1 145 GZK/2000 22 Unidade III - BD Relacional Structured Query Language (SQL) - DML Views (Tabelas Virtuais) em SQL DROP VIEW DEPTO_INFO DROP VIEW TRABALHAM1 146 GZK/2000 Unidade IV - BD Relacional Transações Uma transação é uma unidade de execução de programa que acessa e, possivelmente, atualiza vários itens de dados. Propriedades de Transações: Atomicidade Consistência Isolamento Durabilidade ACID 147 GZK/2000 23 Unidade IV - BD Relacional Transações - Propriedades Atomicidade Ou todas as operações da transação são refletidas corretamente no banco de dados ou nenhuma o será. Consistência A execução de uma transação isolada (ou seja, sem a execução concorrente de outra transação) preserva a consistência do banco de dados. 148 GZK/2000 Unidade IV - BD Relacional Transações - Propriedades Isolamento Embora diversas transações possam ser executadas de forma concorrente, o sistema garante que, para todo par de transações Ti e Tj, Ti tem a sensação de que Tj terminou sua execução antes de Ti começar, ou que Tj começou sua execução após Ti terminar. Assim, cada transação não toma conhecimento de outras transações concorrentes no sistema. 149 GZK/2000 24 Unidade IV - BD Relacional Transações - Propriedades Durabilidade Depois da transação completar-se com sucesso, as mudanças que ela faz no banco de dados persistem, até mesmo se houver falhas no sistema 150 GZK/2000 Unidade IV - BD Relacional Transações - Exemplo Ti: 151 read (A); A = A - 50; write (A); read (B); B = B + 50; write (B); GZK/2000 25 Unidade IV - BD Relacional Transações - Estado da Transação 152 Ativa: estado em que a aplicação se encontra enquanto estiver executando Efetivação Parcial: após a execução da última declaração Em falha: após a descoberta de que a execução normal não pode ser realizada Abortada (rollback): depois que a transação foi desfeita e o banco de dados está no estado anterior ao início da transação Em efetivação (commit): após a conclusão com sucesso GZK/2000 Unidade IV - BD Relacional Transações - Estado da Transação 153 Ativa: estado em que a aplicação se encontra enquanto estiver executando Efetivação Parcial: após a execução da última declaração Em falha: após a descoberta de que a execução normal não pode ser realizada Abortada (rollback): depois que a transação foi desfeita e o banco de dados está no estado anterior ao início da transação Em efetivação (commit): após a conclusão com sucesso GZK/2000 26 Unidade IV - BD Relacional Transações - Estado da Transação Efetivação Parcial Efetivação Commit Em Falha Abortada Rollback Ativa 154 GZK/2000 Unidade IV - BD Relacional Segurança e Integridade 155 O mal uso do BD pode ser classificado como intencional ou acidental. Acidental: Quedas durante o processamento de transações; Anomalias causadas por acesso concorrente ao BD; Anomalias causadas pela distribuição de dados pelos diversos computadores; Erros lógicos que violam as regras impostas para que as transações preservem GZK/2000 restrições de consistência do BD. 27 Unidade IV - BD Relacional Segurança e Integridade Intencional: Leitura não autorizada de dados (roubo de informações); Modificação não autorizada de dados; Destruição não autorizada de dados. 156 GZK/2000 Unidade IV - BD Relacional Segurança e Integridade Níveis de segurança Físico Humanos: Usuário devem ser cuidadosamente autorizados. Sistema Operacional: a debilidade do sistema operacional pode servir como meio de acesso não autorizado ao BD. Rede: a segurança a nível de software de rede é tão importante quanto a segurança física. Sistema de Banco de Dados 157 GZK/2000 28 Unidade IV - BD Relacional Segurança e Integridade - Autorização Formas de Autorização Autorização read Autorização insert Autorização update Autorização delete Autorização index Autorização resource Autorização alteration Autorização drop 158 GZK/2000 Unidade IV - BD Relacional Segurança e Integridade - Privilégios Concessão de Privilégios DBA U1 U4 U2 U5 U3 159 GZK/2000 29 Unidade IV - BD Relacional Segurança e Integridade - Privilégios Concessão de Privilégios U1 DBA U2 U3 U1 DBA U2 U1 DBA U3 160 U2 U3 GZK/2000 Unidade IV - BD Relacional Segurança e Integridade Segurança em SQL - Conferindo Autorização GRANT <lista-privilégios> ON <nome relação ou visao> TO <lista-usuarios> [ WITH GRANT OPTION ] 161 GZK/2000 30 Unidade IV - BD Relacional Segurança e Integridade Segurança em SQL - Conferindo Autorização Lista de Privilégios: Select Delete Insert Update References 162 GZK/2000 Unidade IV - BD Relacional Segurança e Integridade Segurança em SQL - Conferindo Autorização GRANT SELECT ON PROJETOS TO U1 GRANT UPDATE (salario) ON FUNCIONARIOS TO U1, U2, U3 163 GZK/2000 31 Unidade IV - BD Relacional Segurança e Integridade Segurança em SQL - Conferindo Autorização GRANT REFERENCES (gerente) ON DEPARTAMENTOS TO U1 GRANT ALL ON DEPARTAMENTOS TO U1, U2, U3 164 GZK/2000 Unidade IV - BD Relacional Segurança e Integridade Segurança em SQL - Conferindo Autorização GRANT ALL ON FUNCIONARIOS TO PUBLIC GRANT SELECT ON DEPARTAMENTOS TO U1, U2, U3 WITH GRANT OPTION 165 GZK/2000 32 Unidade IV - BD Relacional Segurança e Integridade Segurança em SQL - Revogando Autorização REVOKE <lista-privilégios> ON <nome relação ou visao> FROM <lista-usuarios> [ RESTRICT / CASCADE ] 166 GZK/2000 Unidade IV - BD Relacional Segurança e Integridade Segurança em SQL - Revogando Autorização 167 REVOKE ON FROM SELECT PROJETOS U1 CASCADE REVOKE ON FROM UPDATE (salario) FUNCIONARIOS U1, U2, U3 CASCADE GZK/2000 33 Unidade IV - BD Relacional Segurança e Integridade Segurança em SQL - Revogando Autorização REVOKE FOR ON FROM GRANT OPTION SELECT DEPARTAMENTOS U1, U2, U3 168 GZK/2000 FIM 169 GZK/2000 34