Baixe o app para aproveitar ainda mais
Prévia do material em texto
Bancos de Dados Prof. Ronaldo Lopes de Oliveira Modelo Relacional (MR) • Histórico – Proposto por E.F.Codd em 1970 – Testado na IBM no projeto System-R (1973-1979) – Baseado na teoria de relações entre conjuntos e álgebra relacional – Em 1974 Don Chamberlin e outros pesquisadores propuseram a linguagem SQL – Em 1979 a empresa Relational Software lança o primeiro SGBD relacional comercial: Oracle – Em 1981 a IBM lança SQL/DS e em 1983 DB2 – Demorou mais de duas décadas para se firmar como principal modelo utilizado em SBDs Modelos de Dados Relacional • Definição Formal – Domínio: Conjunto de valores atômicos (indivisíveis, relacionados com tipo de dados). – Esquema de relação: R(A1, A2, ..., An), onde R é o nome da relação e Ai representa um atributo da relação R. – Atributo: Nome dado ao papel de um domínio na relação. – Domínio de atributo: dom(Ai) Modelos de Dados Relacional • Definição Formal (continuação) – Tupla: Lista ordenada de valores t=<v1, v2, ..., vn>, onde vi ∈ dom(Ai) ou vi é nulo Definição Alternativa: Tupla é um conjunto de pares ordenados (<atributo>,<valor>) onde: vi � dom(Ai) – Grau da Relação: número de atributos da relação – Cardinalidade da Relação: número de tuplas da relação – Instância de relação: r(R), conjunto de tuplas: r={t1,t2,..., tn} Modelos de Dados – Modelo Relacional • Definição Formal (continuação) – Intenção: esquema de relação – R – Extensão: instância de relação - r(R) – r(R) é um conjunto finito de mapeamentos r = {t1, t2, ..., tn}, ti = R x D, R={A1, A2, ..., An} e D = D1 U D2 U ... U Dn – Valor Nulo: valor desconhecido ou não existente – Instância corrente: instância atual de uma relação Modelos de Dados Relacional • Definição Formal – Observações Importantes: • A relação R, pode ser vista como um produto cartesiano de domínios: r(R) ⊆ (dom(A1) x dom(A2) x ... x dom(An) • Relação é diferente de arquivo: arquivo é uma implementação física de uma ou mais relações. No arquivo os registros obedecem a uma seqüência particular e numa relação não é exigido a ordenação das tuplas Modelos de Dados Relacional • Características das relações – Ordenamento de tuplas: não é exigido – Ordenamento de valores: é exigido formalmente de acordo com a definição de tupla; uma definição alternativa de relação pode relaxar a exigência do ordenamento de valores na tupla. – Valores atômicos: modelo relacional normalizado na 1FN exige que os atributos tenham valores atômicos, diferentemente do modelo relacional não normalizado que permite atributos multivalorados Modelos de Dados Relacional • Características das relações – Esquema de relação: pode ser interpretado como uma assertiva que define o que pode ser armazenado – Instância de relação: fato ou instância da assertiva – A relação é o único construtor para entidades e relacionamentos Modelos de Dados Relacional • Notação Formal – Esquema de relação: R(A1, A2, ..., An) – Instância de relação: r(R) – Tupla: t = <v1, v2, ..., vn> – Valor do atributo Ai em uma tupla t: t[Ai] ou t.Ai – Subtupla: t[Av, Aw, ..., Az] – Nomes de relações: R, S, Q – Nomes de instâncias de relações: r, s, q – Nomes de tuplas: t, u, v Modelos de Dados Relacional • Chaves de Relação – Chave: qualquer subconjunto de atributos que identifica univocamente uma tupla da relação – Chave candidata: cada chave de uma relação é uma chave candidata a ser a chave primária da relação – Chave primária: é uma chave candidata que foi considerada principal pelo projetista e foi designada para determinar a ordenação lógica das tuplas na relação quando da sua implementação física Modelos de Dados Relacional • Chaves de Relação (continuação) – Chave alternativa (chave única): é uma chave candidata que não foi designada para ser chave primária – Chave estrangeira (chave externa): é um atributo incluído em uma relação para permitir a representação de uma associação lógica entre tuplas de duas relações Modelos de Dados Relacional • Chaves de Relação (continuação) – Chave simples: é uma chave composta por um único atributo. – Chave composta: é uma chave composta pela concatenação de mais de um atributo. Modelos de Dados Relacional • Esquema de um banco de dados relacional: É o conjunto de esquemas de relações: S = {R1, R2, ..., Rn} e um conjunto de restrições de integridade definidas sobre S • Instância (ou estado) de um bd relacional: É o conjunto de instâncias de relações que compõem o banco de dados em um determinado instante de tempo Modelos de Dados Relacional • Restrições básicas representadas – Restrição de unicidade (ou de identificação): • especificação de chave primária e chave única de entidade – Restrição de domínio: • especificação dos valores que podem ser vinculados aos atributos da relação – Restrição de opcionalidade: • Representada por restrição Null/Not Null para definir opcionalidade lógica de propriedades (atributos) da relação Modelos de Dados Relacional • Restrições básicas (continuação) – Restrição referencial: • especificação de dependência entre valores armazenados na chave estrangeira de uma tabela em relação aos valores de um atributo (geralmente a chave primária) de outra relação – Restrições de dependência funcional: • especificação de atributos da relação que determinam funcionalmente outros atributos da relação • conceito usado no processo de normalização de relações Modelos de Dados Relacional • Existem regras de conversão para: – Hierarquia de especialização – Tipo regular de entidade • Atributos simples e atributos compostos • Atributos monovalorados e multivalorados • Atributos identificadores e não identificadores • Atributos opcionais e não opcionais – Tipo fraco de entidade – Tipo de relacionamento 1:1 – Tipo de relacionamento 1:N – Tipo de relacionamento N:N Conversão MER-MR • Passos: 1. Conversão de Tipo Regular de Entidade que não pertence a hierarquia de especialização • Cada tipo regular de entidade que não estiver em uma hierarquia de especialização vai gerar uma relação (tabela) contendo os atributos simples e monovalorados do tipo de entidade. As características de tipo e tamanho serão ajustadas de acordo com as possibilidades providas pelo SGBD alvo. . • Os atributos opcionais serão definidos na relação com restrição NULL e os obrigatórios com a restrição NOT NULL. Conversão MER-MR • Passos: 1. Conversão de Tipo Regular de Entidade que não pertence a hierarquia de especialização • Devido a exigência da Primeira Forma Normal na maioria dos SGBDs relacionais, os atributos compostos e multivalorados tem tratamento diferenciado. • Somente os atributos componentes monovalorados são levados para a relação gerada para o tipo regular de entidade. • Cada atributo multivalorado será colocado em uma tabela separada cuja chave será uma chave composta pelo atributo identificador do tipo de entidade que está sendo convertida mais o próprio atributo multivalorado. Conversão MER-MR • Passos: 1. Conversão de Tipo Regular de Entidade que não pertence a hierarquia de especialização • Cada conjunto de atributos identificador do tipo de entidade será convertido para uma chave da relação. O conjunto que for considerado principal (mais conhecido ou mais utilizado).será convertido como chave primária da relação. Os demais conjuntos de atributos identificadores, caso existam, serão convertidos como chaves alternativas. • As restrições de domínio contidas na definição do tipo de entidade devem ser incluídas na definição da relação (tabela). Conversão MER-MR • Passos: 2. Conversão de Tipo Fraco de Entidade que não pertencea hierarquia de especialização • Cada tipo fraco de entidade deve gerar uma relação (tabela) cuja chave será composta pela chave primária da tabela que representa o tipo regular de entidade do qual o tipo fraco depende mais o conjunto de atributos que é identificador parcial do tipo fraco em questão. • O tratamento dos demais atributos do tipo fraco de entidade seguem as regras definidas para a conversão de tipo regular de entidade. Conversão MER-MR • Passos: 3. Conversão de hierarquia de especialização • Para converter uma hierarquia de especialização existem três opções:. a) Gerar uma única relação para representar toda a hierarquia de especialização b) Gerar relações apenas para os tipos de entidade especializados na hierarquia de especialização c) Gerar uma relação para cada tipo de entidade envolvida na hierarquia de especialização. Conversão MER-MR • Passos: 3. Conversão de hierarquia de especialização a) Gerar uma única relação para representar toda a hierarquia de especialização – Todos os atributos do tipo de entidade genérica (super-entidade) são colocadas na relação observando-se as mesmas regras de conversão mencionadas na conversão de tipo regular de entidade. – Deve ser gerado um atributo do tipo booleano para cada tipo de entidade especializada (sub-entidade) indicando se a tupla (linha da tabela) vai representar ou não uma instância daquele tipo de entidade. Conversão MER-MR • Passos: 3. Conversão de hierarquia de especialização a) Gerar uma única relação para representar toda a hierarquia de especialização – Caso a hierarquia de especialização for do tipo disjunta, então pode ser feita uma simplificação incluindo apenas um atributo indicando qual o tipo de entidade especializada cada tupla (linha da tabela) irá representar. – Os atributos das sub-entidades deverão ser incluídos com a restrição NULL, mesmo que tenham sido definidos como obrigatórios nas sub-entidades. Conversão MER-MR • Passos: 3. Conversão de hierarquia de especialização a) Gerar uma única relação para representar toda a hierarquia de especialização – Deverão ser incluídas na definição da relação as restrições semânticas que vão indicar as regras de preenchimento dos valores dos atributos originados das sub-entidades, de acordo com o tipo de entidade que cada tupla da relação está representando. Conversão MER-MR • Passos: 3. Conversão de hierarquia de especialização b) Gerar uma relação para cada tipo de entidade especializada (sub-entidade) – Cada sub-entidade vai dar origem a uma relação distinta. – Os atributos da super-entidade serão repetidos em cada uma das relações geradas obedecendo as regras de conversão de atributos. – Caso uma sub-entidade tenha atributos específicos eles deverão ser incluídos apenas na relação que a representa obedecendo as regras de conversão de atributos. Conversão MER-MR • Passos: 3. Conversão de hierarquia de especialização b) Gerar uma relação para cada tipo de entidade especializada (sub-entidade) – Restrições de domínio que existam na definição da super-entidade devem ser repetidas em todas as relações geradas.. – Restrições de domínio que existam na definição de uma sub-entidade devem ser colocadas apenas na relação que a representa. Conversão MER-MR • Passos: 3. Conversão de hierarquia de especialização c) Gerar uma relação para cada tipo de entidade envolvida da hierarquia de especialização – Gerar uma relação para a super-entidade e uma relação para cada sub-entidade. – Os atributos definidos na super-entidade são colocados na tabela que a representa respeitando-se as regras de conversão de atributos. – O atributo identificador principal da super-entidade será a chave primária da tabela que a representa. Conversão MER-MR • Passos: 3. Conversão de hierarquia de especialização c) Gerar uma relação para cada tipo de entidade envolvida da hierarquia de especialização – O único atributo da super-entidade que deve ser replicado nas tabelas que representam as sub- entidades é a chave primária que também será a chave primária de cada uma das tabelas que representam as sub-entidades. Além disso, será ao mesmo tempo uma chave estrangeira referenciando a chave primária da tabela que representa a super- entidade. – Os demais atributos de uma tabela que representa a sub-entidade serão os atributos específicos da sub- entidade respeitando-se as regras de conversão de atributos. Conversão MER-MR Conversão MER-MR • Passos: 4.Conversão de tipos de relacionamento a) Relacionamento 1:1 – Caso o relacionamento seja binário, incluir em cada uma das tabelas que representa um tipo de entidade envolvido a chave estrangeira referenciando a chave da tabela que representa o outro tipo de entidade. Conversão MER-MR • Passos: 4.Conversão de tipos de relacionamento a) Relacionamento 1:1 (continuação) – Caso o relacionamento seja unário, incluir como chave estrangeira na tabela que representa o tipo de entidade envolvido a chave primária desta mesma tabela com nome diferente da chave primária e que indique a semântica do relacionamento representado. – Atribuir a restrição NULL ou NOT NULL nas chaves estrangeiras de acordo com a cardinalidade mínima que caracteriza a associação entre as entidades. Conversão MER-MR • Passos: 4.Conversão de tipos de relacionamento b) Relacionamento 1:N – Caso o relacionamento seja binário, incluir uma chave estrangeira na tabela que representa o tipo de entidade do lado N referenciando a chave da tabela que representa o tipo de entidade do lado 1. Conversão MER-MR • Passos: 4.Conversão de tipos de relacionamento b) Relacionamento 1:N (continuação) – Caso o relacionamento seja unário, incluir como chave estrangeira na tabela que representa o tipo de entidade envolvido a chave primária desta mesma tabela com nome diferente da chave primária e que indique a semântica do relacionamento representado. – Atribuir a restrição NULL ou NOT NULL nas chaves estrangeiras de acordo com a cardinalidade mínima que caracteriza a associação entre as entidades. Conversão MER-MR • Passos: 4.Conversão de tipos de relacionamento c) Relacionamento N:N – Caso o relacionamento seja binário, incluir uma tabela distinta para representar o tipo de relacionamento. A chave primária desta nova tabela será composta pelas chaves primárias das tabelas que representam os dois tipos de entidade envolvidos no relacionamento. Cada uma das chaves estrangeiras que compõem a chave primária deve referenciar a tabela que representa o tipo de entidade envolvido. Conversão MER-MR • Passos: • 4.Conversão de tipos de relacionamento c) Relacionamento N:N (continuação) – Caso o relacionamento seja unário, incluir uma tabela distinta para representar o tipo de relacionamento. A chave primária desta nova tabela será composta por dois atributos, sendo que cada um deles é uma chave estrangeira que referencia a chave da tabela que representa o tipo de entidade envolvido no tipo de relacionamento unário. Os nomes dos atributos que compõem a chave primária composta devem refletir o papel desempenhado pelos tipos de entidade no tipo de relacionamento • Outros conceitos importantes – Visões • São relações virtuais definidas sobre relações armazenadas no banco de dados • Úteis para simplificar consultas complexas e implementar restrições de acesso às relações definidas a partir de predicados de seleção • Atualizações através de visões são limitadas Modelos de Dados Relacional • Outros conceitos importantes – Gatilhos • São procedimentos disparados executados automaticamente pelo SGBD todavez que acontece um ação de atualização de dados pré-determinados; • Na definição de um gatilho além de se indicar a ação de atualização que será considerada e o objeto de dados sobre o qual a ação deverá ser considerada, também deve ser indicado o momento reativo à atualização em que o procedimento deve ser executado Modelos de Dados Relacional • Outros conceitos importantes – Procedimentos e Funções Armazenados • São procedimentos e funções similares aos que são feitos normalmente nas aplicações. A diferença é que estes procedimentos são armazenados diretamente no Sistema de Banco de Dados e portanto ficam sob o gerenciamento do SGBD; • Procedimentos e funções armazenadas para serem executadas devem ser explicitamente ativadas nas aplicações; • A principal vantagem do uso de procedimentos e funções armazenados é que eles podem ser definidos uma única vez e compartilhados por diversas aplicações. Modelos de Dados Relacional Normalização Normalização • Objetiva eliminar ou reduzir redundâncias de dados em relações • Redundâncias de dados podem causar problemas conhecidos como anomalias de atualização • Algumas regras formais conhecidas como formas normais permitem avaliar o grau de redundância de dados em relações • Normalização é o processo de decompor relações que não obedecem a alguma forma normal em outras relações que passam a obedecê-la. Normalização • As formas normais mais utilizadas em normalização de esquemas de bancos de dados relacionais são: – Primeira Forma Normal – Segunda Forma Normal – Terceira Forma Normal – Forma Normal Boyce-Codd • A primeira forma normal veio de uma imposição original do modelo relacional que não tratava atributos não atômicos e não tem a ver realmente com redundância de dados. • As demais formas normais tratam de eliminação de redundâncias e usam como base formal o conceito de dependência funcional. Normalização • Dependências Funcionais (DF) – Restrição de Integridade envolvendo dois conjuntos de atributos de uma relação – Notação Formal Seja α ⊆ R um conjunto de atributos de uma relação R Seja β ⊆ R um conjunto de atributos de uma relação R A dependência funcional α → β vale em R se para quaisquer tuplas t1 e t2 temos: t1[α] = t2[α] ⇒ t1[β] = t2[β] • Dependências Funcionais (DF) – DF é uma generalização do conceito de superchave Se K é uma superchave de R então K � R, ou seja, t1[K] = t2[K] ⇒ t1[R] = t2[R] Normalização • Utilização de DFs: – um conjunto F de DFs serve para: • Restringir o conjunto de relações válidas, diz-se então que: F vale em R • Testar se uma instância r de relação R satisfaz F, diz-se então que: R satisfaz F Normalização • DFs Triviais Se β ⊆ α então α → β Exemplos: A → A AB → A AB → B • Fecho (F+) de um conjunto F de DFs É o conjunto F mais as DFs que podem ser inferidas logicamente por F Pode ser obtida aplicando-se regras de inferência Normalização • DFs Triviais Se β ⊆ α então α → β Exemplos: A → A AB → A AB → B • Fecho (F+) de um conjunto F de DFs É o conjunto F mais as DFs que podem ser inferidas logicamente por F Pode ser obtida aplicando-se regras de inferência Normalização • Principais regras de Inferência (Axiomas de Armstrong) – Regra do aumento – Regra da decomposição – Regra da transitividade Normalização • Formas Normais São regras que se apóiam fortemente no conceito de dependências funcionais para estabelecer um “bom” projeto de banco de dados relacional. A normalização é o processo em que relações que não obedecem a certas formas normais são decompostas em outras relações que obedecem às mesmas. Normalização • Primeira Forma Normal (1FN) Uma relação está em 1FN se todos os seus atributos são definidos sobre domínios atômicos e cada atributo está associado a um único valor do seu domínio. • Segunda Forma Normal (2FN) Uma relação está em 2FN se cada atributo que não compõe a chave primária é funcionalmente dependente de toda a chave primária (definição mais conhecida). Uma relação está em 2FN se todo atributo que não compõe uma chave candidata depende funcionalmente de toda uma chave candidata. Trata-se, portanto, de eliminar dependências funcionais parciais (definição mais correta). Normalização • Terceira Forma Normal (3FN) Uma relação está em 3FN se ela está em 2FN e um atributo que não compõe uma chave candidata não depende funcionalmente de maneira transitiva de uma chave candidata, ou em outros termos, uma relação está em 3FN se para cada dependência funcional α → β que vale na relação, uma das seguintes condições for satisfeita: » α → β é uma dependência funcional trivial » α é uma superchave da relação » β é um atributo que compõe uma chave candidata Normalização • Forma Normal Boyce-Codd (FNBC) Uma relação R está em FNBC em relação a um conjunto de dependências funcionais F se para toda DF em F+ da forma α→β onde α⊆R e β⊆R, pelo menos uma das seguintes dependências funcionais é válida: » α → β é uma dependência funcional trivial » α é uma superchave da relação Normalização • Propriedades desejáveis na decomposição de relações – Decomposição com junção sem perdas (ligação não perdedora) Seja R1 e R2 duas relações obtidas da decomposição da relação R em que vale o conjunto de dependências funcionais F. A decomposição é sem perdas se uma das seguintes condições for satisfeita: R1 ∩ R2 → R1 estiver contida em F+ R1 ∩ R2 → R2 estiver contida em F+ Normalização Exemplo de ligação com perdas: Empréstimo(nome_agência,tot_ativos,cidade,numero,nome_cliente,quantia) em Emprest_cliente(nome_cliente,quantia,nome-agência) Emprest-agencia(nome-agência,numero,quantia) Normalização • Propriedades desejáveis na decomposição de relações – Preservação de dependências funcionais Definição: Uma Restrição Fi para uma relação Ri obtida pela decomposição de R (R é decomposta em R1,R2,...,RN) é o conjunto de DFs em F+ que incluem apenas atributos de Ri Seja Fd = F1 ∪ F2 ∪ ... Fn-1 ∪ Fn A decomposição de R em R1,R2,...,RN preserva DFs se Fd+ = F+ Normalização Exemplo de decomposição de relação na 3FN para relações em FNBC que não preserva dependências funcionais: Gerente(nome-agência,nome-cliente,nome-gerente) nome-gerente → nome-agencia nome-cliente nome-agencia → nome-gerente Normalizando temos: gerente-agencia(nome-gerente,nome-agência) cliente-gerente(nome-cliente,nome-gerente) Normalização • Comparação entre FNBC e 3FN – A FNBC é mais restritiva, isto é, toda relação em FNBC está em 3FN mas não vice-versa – A decomposição em relações na 3FN sempre preserva as propriedades e em FNBC nem sempre – Relações em 3FN podem conter repetições de informação (redundâncias) e podem exigir valores nulos o que não acontece em FNBC – Se não for possível obter relações em FNBC sem perda de junção e com preservação das dependências funcionais então obter relações em 3FN que mantenham as demais propriedades. Normalização Controle de Acesso • Restrições para acesso aos dados – São as regras que devem ser obedecidas para garantir acessos legais e evitar acessos ilegais – Mecanismo básico é a criação de usuários / contas/ perfis e associação de privilégios a esses usuários/ contas/ perfis – Em SBDs Relacionais são usados comandos SQL para autorização e revogação de privilégios • Autorização de Privilégios em SQL: GRANT – Autorização com propagação ou sem propagação • Revogação de Privilégios em SQL: REVOKE Controle de Acesso • Tipos de Privilégios: – Privilégios de Sistema (ou de conta) – Privilégios de Objetos • Visões também podem ser utilizadas como mecanismo de controle de acesso aos dados– Necessário quando o controle de acesso envolve predicados sobre os dados Controle de Acesso Linguagens Relacionais Álgebra Relacional • Linguagem formal de manipulação de dados em modelo relacional • Baseado em grande parte em teoria de conjuntos • Linguagem procedimental • Principais Operações: – Seleção – Projeção – Produto Cartesiano – Junção – União – Intersecção – Diferença • Operação Seleção (σ) σ<condição> (<relação>) <atributo> <op> <atributo> <condição> <atributo> <op> <constante> –operação unária – condições podem envolver conectores lógicos –operação comutativa – relação resultante com mesmo grau – relação resultante com cardinalidade menor ou igual Álgebra Relacional • Operação Projeção (pi): pi<lista-atributos>(<relação>) –operação unária – relação resultante com mesmo cardinalidade – relação resultante com grau menor ou igual – operação não comutativa – remoção implícita de duplicatas • Operação Renomear (ρ): ρ<relação-renomeada>(<relação>) –operação utilizada para distinguir a utilização de uma relação duas vezes na mesma consulta Álgebra Relacional • Operação União (∪) (<relação>) ∪ (<relação>) – operação comutativa – união de tuplas – remoção de duplicatas – compatibilidade de união – tabela resultante com cardinalidade maior e mesmo grau • Operação Intersecção (∩) (<relação>) ∩ (<relação>) – operação comutativa – união de tuplas – remoção de duplicatas – compatibilidade de união – tabela resultante com cardinalidade maior e mesmo grau Álgebra Relacional • Operação Diferença (-) (<relação>) - (<relação>) – operação não comutativa – compatibilidade de união – tabela resultante com cardinalidade menor ou igual • Operação Produto Cartesiano ( X ) (<relação>) X (<relação>) – operação não comutativa – não necessita compatibilidade de união – tabela resultante com cardinalidade mxn e grau m+n Álgebra Relacional • Operação Junção |X| (<relação>) |X| (<relação>) <condição-junção> – equivale a produto cartesiano seguido de seleção – permite reunir dados de relações diferentes seguindo critérios da aplicação – operação não comutativa – também conhecida como junção theta ou junção interna (inner join) • Variações da operação Junção – equi-junção (equi-join) – Junção natural (natural join) – Junção externa (outer join) » À esquerda (left outer join) » À direita (right outer join) » À esquerda e à direita (left tight outer join) Álgebra Relacional • Modos de Operação com SQL – SQL interativa •Executada a partir de ferramenta específica que fornece ambiente de execução direta de comandos SQL – SQL embutida •Comandos SQL embutidos em uma linguagem hospedeira como C, JAVA, C++, etc •Comandos SQL são delimitados por comandos sinalizadores específicos como EXEC-SQL, END-SQL •Comandos sinalizadores depende da linguagem hospedeira (reconhecida pelo pré-compilador) •Variáveis contidas no programa hospedeiro podem ser referenciadas nos comandos SQL para receber valores retornados do banco de dados ou servir como origem para atualizações dos dados no banco de dados SQL • TIPOS de SQL – SQL não procedimental •Comandos SQL básicos (INSERT, UPDATE, DELETE, SELECT) orientados por conjunto de tuplas – tratam um conjunto de tuplas de uma ou mais relações que satisfazem determinados critérios de seleção •Não inclui comandos para tratamento tupla a tupla SQL • TIPOS de SQL – SQL procedimental •Inclui comandos adicionais para criação de tabelas temporárias (cursor) que permitem recuperação e tratamento tupla a tupla –DECLARE CURSOR –OPEN –FETCH –CLOSE •Alguns SGBDs criaram versões de SQL procedimental que incluem comandos de linguagens convencionais como comandos de repetição, condição, desvio –Exemplo PL-SQL da Oracle SQL SQL • Padrões SQL – Primeiras padronizações: 1986 (ANSI), 1987 (ISO) – 1989 (ISO) •Integridade referencial •Null •Default •Check constraints – 1992: SQL2 •Criação explícita de schema (CREATE SCHEMA) •Criação de domínio (CREATE DOMAIN) •Stored Procedures •Novos tipos de dados (time e date) •Tabelas temporárias •Tabelas derivadas da cláusula FROM •Restrição UNIQUE •UNION SQL • Padrões SQL – 1999: SQL3 •Extensões para O.O (SGBD objeto-relacionais) – Objetos complexos (vídeo, imagem, texto) – Hierarquia de tabelas (herança simples) – Tipos de dados definidos pelo usuário – coleções •Expressões regulares de emparelhamento •Queries recursivas •Gatilhos •OLAP SQL • Padrões SQL – 2003: SQL4 •Composto por nove partes – Parte 1: SQL Framework: – Parte 2: SQL Foundation – Parte 3: SQL CLI (Call-Level-Interface) – Parte 4: SQL-PSM (Persistent Stored Modules) – Parte 9: SQL-MED (Management of External Data) – Parte 10: SQL-OLB (Object Language Binding) – Parte 11: SQL-Schemata – Parte 13: SQL-JRT (Java Routines and Types) – Parte 14: SQL-XML – Partes 5, 6, 7, 8 e 12 não foram aproveitadas SQL • Padrões SQL – 2003: SQL4 •Parte 1: SQL Framework: – Define estrutura do padrão e relacionamento entre as partes – Apresenta conceitos e definições gerais – Define os requisitos de conformidade – Atualizações que vierem a ser feitas nesta parte refletem nas demais partes •Parte 2: SQL Foundation – Parte maior e mais importante – Define o núcleo da linguagem – Inclui todas as definições da SQL1999 Foundation com as devidas correções • Padrões SQL – 2003: SQL4 •Parte 3: SQL CLI – Define um rotinas padrão para invocar dinamicamente SQL a partir de programas de aplicação – Consiste em 60 especificações de rotinas para: » Controlar conexões a servidores SQL » Alocar e deslocar recursos » Executar comandos SQL » Controlar terminação de transação » Obter informação sobre implementaçao – Dispensa pré-compilação SQL • Padrões SQL – 2003: SQL4 •Principais novas características – Novos tipos de dados: BIGINT, MULTISET – Extensões a tipos de dados: ARRAY ilimitado – Remoção de tipos de dados: BIT, BIT VARYING – Geração de Sequências (SEQUENCE) – Colunas Identidade – Colunas geradas (derivadas) – Aumento de funcionalidade de CREATE LIKE – Tabelas Base criadas a partir de queries – Funções que retornam tabelas (“TABLE” Functions) SQL • Padrões SQL – 2003: SQL4 •Principais novas características (continuação) – SQL dinâmico e SQL-DDL dentro de rotinas (procedures, functions) – Novas funções para escalares: LN, EXP, POWER, SQRT, FLOOR, CEILING, WIDTH-BUCKET – Novas funções de agregação de um argumento: STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP – Novas funções de agregação com dois argumentos: COVAR, CORR, ... – Novas funções para tabelas e tabelas particionadas: RANK, ROWNUMBER... – Comando DML Merge SQL • SQL-DDL Comandos Principais – CREATE SCHEMA – DROP SCHEMA – CREATE TABLE – ALTER TABLE – DROP TABLE – CREATE VIEW – ALTER VIEW – DROP VIEW – CREATE INDEX – ALTER INDEX – DROP INDEX SQL • CREATE TABLE CREATE TABLE Departamentos (codigo numeric(2) NOT NULL PRIMARY KEY, nome Varchar(30) NOT NULL) CREATE TABLE Cursos (codigo Numeric(3) NOT NULL, nome Varchar(40) NOT NULL, codigoDepartamento Numeric(2) NOT NULL DEFAULT 1, CONSTRAINT PrkCurso PRIMARY KEY (Codigo), CONSTRAINT FrkDepCurso FOREIGN KEY (codigoDepartamento) REFERENCES Departamentos (Codigo) ON DELETE SET DEFAULT ON UPDATE CASCADE) SQL • SQL-DDL: Tratamento de restrições – CREATE TABLE •Podem ser especificadas ações a serem disparadas quando uma operação causar uma potencial violação através de cláusulas referencial triggering action ON DELETE e ON UPDATE com opções: – SET DEFAULT – SET NULL – CASCADE – DROP SCHEMA •DROP SCHEMA nome-esquema CASCADE •DROP SCHEMA nome-esquema RESTRICT – DROP TABLE •DROP TABLEnome-tabela CASCADE •DROP TABLE nome-tabela RESTRICT SQL • SQL-DDL: Tratamento de restrições – ALTER TABLE •Adição de atributo (coluna) –ALTER TABLE nome-tabela ADD nome-atributo... » Se o atributo não puder ter valor NULL então deve ser especificado valor default Exemplo: ALTER TABLE Empregados ADD estCivil char(1) DEFAULT ‘S’ •Remoção de atributo (coluna) –ALTER TABLE nome-tabela DROP nome-atributo » Deve ser especificada opção RESTRICT ou CASCADE para tratar restrições de integridade referencia Exemplo: ALTER TABLE Empregados DROP estCivil CASCADE SQL • SQL-DDL: Tratamento de restrições – ALTER TABLE •Alteração de atributo (coluna) –Adicionar/remover valores default Exemplo: ALTER TABLE Empregados ALTER estCivil DROP DEFAULT –Adicionar/remover restrições Exemplo: ALTER TABLE Empregados DROP CONSTRAINT deptoFK CASCADE SQL • SQL-DML – INSERT •Acréscimo de múltiplos registros: INSERT INTO tabdestino [(campo1[, campo2[, ...]])] <comando-select> Exemplo: INSERT INTO Empregados (nome, matricula, salario) SELECT * FROM EmpregadosAntigos; •Acréscimo de registro único: INSERT INTO tabdestino [(campo1[, campo2[, ...]])] VALUES (valor1[, valor2[, ...]) Exemplo: INSERT INTO Empregados (nome, matricula, salario) VALUES (‘Ronaldo Lopes’,3523,5000); SQL • SQL-DML – UPDATE UPDATE tabela SET campo1 = exprValor [,campo2 = exprValor...] WHERE condições Exemplo: UPDATE Pedidos SET QuantiaDoPedido = QuantiaDoPedido * 1.1, Frete = Frete * 1.03 WHERE PaísDeDestino = 'UK'; SQL • SQL-DML – DELETE DELETE FROM tabela WHERE condições Exemplo: DELETE FROM Pedidos WHERE PaísDeDestino = 'UK'; SQL • SQL-DML: Tratamento de restrições – DELETE •O tratamento das restrições de integridade referencial de tupla(s) de outra(s) tabela(s) que referencia(m) a(s) tupla(s) removida(s) segue o que foi especificado no comando de criação da tabela que referencia. – UPDATE •O tratamento das restrições de integridade referencial de tupla(s) de outra(s) tabela(s) que referencia(m) a(s) tupla(s) atualizada(s) segue o que foi especificado no comando de criação da tabela que referencia. SQL • SQL-DQL – SELECT Sintaxe básica: SELECT <lista-atributos> FROM <lista-tabelas> [WHERE <condições>] [GROUP BY <atributos-grupo>] [HAVING <condições-grupo>] [ORDER BY <lista-atributos-ordenação] SQL • SQL-DQL – SELECT – Variantes sintáticas importantes •Convenção * •Tratamento de ambiguidade de nomes •Cláusula DISTINCT •Operações de conjunto –UNION [ALL] – INTERSECT [ALL] –EXCEPT [ALL] •Ordenação ascendente e descendente SQL • SQL-DQL – SELECT – Variantes sintáticas importantes •Comparações de strings – LIKE » Convenção % » Convenção _ » Convenção \ – Concatenação (Convenção || ) •Comparações numéricas – Operadores de comparação (<, >, <>, =) – Between •Pertinência de valor de atributo em conjunto (IN, NOT IN) •Comparação com NULL (IS NULL, IS NOT NULL) SQL • SQL-DQL – SELECT – Variantes sintáticas importantes •Funções de tratamento de tipos de dados específicos (date, time, strings, timestamp, numeric, etc) – Em geral cada SGBD implementa seu subconjunto particular de funções e procedimentos embora com semânticas similares •Consultas aninhadas (subconsultas) – Verificar se o valor de um ou mais atributos da consulta externa está contido no conjunto de valores retornados por uma consulta interna (SELECT... FROM...WHERE atributo IN/NOT IN (SELECT ...) – Comparação de valor de atributo com valor(es) retornados pela subconsulta (SELECT... FROM...WHERE atributo > ALL (SELECT ...) SQL • SQL-DQL – SELECT – Variantes sintáticas importantes •Consultas aninhadas (subconsultas) –Correlacionar atributos da consulta externa com atributos da consulta interna Exemplo (SELECT e.nome FROM empregados AS e WHERE e.mat in (SELECT d.mat FROM Dependentes AS d WHERE d.sexo = e.sexo) SQL • SQL-DQL – SELECT – Variantes sintáticas importantes •EXISTS / NOT EXISTS Exemplo (SELECT e.nome FROM empregados AS e WHERE EXISTS (SELECT d.mat FROM Dependentes AS d WHERE d.sexo = e.sexo) SQL • SQL-DQL – SELECT – Variantes sintáticas importantes •Tabelas de Junção – Incorporado na SQL2 – Permite a inclusão de condição de junção diretamente na cláusula FROM ao invés de incluí-las na cláusula WHERE – Facilita a incorporação de variantes de junção (outer join, natural join) Exemplo: SELECT e.nome AS ‘Nome Empregado’, d.nome AS ‘Departamento’ FROM Depto AS d LEFT OUTER JOIN Emp AS e ON d.codigo=e.depto) SQL • SQL-DQL – SELECT – Variantes sintáticas importantes •Funções agregadas e agrupamento » COUNT » SUM » MAX » MIN » AVG » AVGP » DEV » DEVP Exemplo: SELECT depto, max(sal), min(sal), avg(sal) FROM empregados WHERE sexo = ‘M’ GROUP BY depto HAVING count(*) > 10; SQL • SQL-DTL – BEGIN (TRANSACTION/WORK) – COMMIT – ROLLBACK • SQL-DCL – CREATE USER – ALTER USER – DROP USER – GRANT – REVOKE – CREATE SYNONYM SQL • Padrões de Conectividade – API para conectividade com banco de dados – Permite que programas em linguagens convencionais enviem instruções SQL através de chamadas padronizadas para qualquer banco de dados que disponibilize driver que implemente a API – Nesta abordagem, a aplicação, em tempo de execução: •Especifica qual a fonte de dados a que deseja ter acesso. •Faz a vinculação entre a aplicação e a fonte de dados, através de um módulo (driver). •O driver converte os formatos de dados e os comandos padronizados para os formatos compreendidos pelo SGBD-alvo. SQL • Padrões de Conectividade – Vantagens • Não é necessário utilizar várias linguagens para acesso a dados de diferentes SGBDs – Menor exigência de treinamento dos desenvolvedores – Menos erros de programação – Mais rápido desenvolvimento – Os desenvolvedores não precisam se preocupar com as particularidades dos bancos de dados que irão acessar e trabalhar. – Desvantagens • Necessidade de mais camadas de processamento • Desempenho SQL • ODBC (Open Database Connectivity): – Criado no início da década de 1990 pelo SQL Access Group liderado pela Microsoft – Através de chamadas ODBC em um programa podem ser acessados diferentes bancos de dados sem utilizar as interfaces proprietárias de cada um. – Driver para cada SGBD alvo conecta dinamicamente uma biblioteca à aplicação – Driver máscara a heterogeneidade de SGBD, sistema operacional e protocolo de rede. •Exemplo (Sybase, Windows/NT, TCP/IP driver) SQL Arquitetura ODBC SQL Aplicação ODBC driver Driver (SGBD/SO/rede) Fonte de Dados • JDBC (Java Database Connectivity) – API Java para conectar programas escritos em Java com bancos de dados relaconais. – Composto por um conjunto de classes e interfaces escritos em Java – Padrão definido pela Sun Microsystems – Permite que fornecedores de soluções em bancos de dados, implementem e extendam o padrão usando seus próprios drivers JDBC. – Permite aos programadores Java conectar-se a bancos de dados e a acessá-lo e manipulá-lo utilizando SQL. SQL Arquitetura JDBC Aplicação Java JDBC driver manager Tradução JDBC/nativo DBMS Driver Nativo (SGBD) Tradução JDBC/ODBC ODBC Driver JDBC middleware (SGBDs variados) JDBC Driver (SGBD) SQL
Compartilhar