Capítulo 9: BDs Objecto-Relacional Relações imbricadas Tipos complexos e objectos Criação de valores complexos e de objectos Funções, procedimentos e métodos em SQL Comparação entre os vários modelos Database System Concepts 9.1.1 ©Silberschatz, Korth and Sudarshan (modificado) Modelo de Dados Objecto-Relacional Parte do modelo relacional, e generaliza-o introduzindo noções de objectos e formas de lidar com tipos complexos. Permite que os atributos tenham valores de tipos complexos, incluindo valores não atómicos. Aumenta a expressividade, sem perder as principais vantagens do modelo relacional (incluindo o acesso declarativo aos dados). É compatível com as linguagens relacionais existentes (o que torna este modelo bastante apelativo para sistemas de bases de dados comerciais). Database System Concepts 9.1.2 ©Silberschatz, Korth and Sudarshan (modificado) Tipos complexos no SQL:1999 As extensões do SQL para suportar tipos complexos incluem: Colecções e objectos de grande dimensão Relação imbricadas são um exemplo de colecções Tipos estruturados Tipos com atributos composto (registos) Herança Orientação por objectos Incluindo identificadores de objectos e referências Database System Concepts 9.1.3 ©Silberschatz, Korth and Sudarshan (modificado) Objectos grandes Tipos para objectos grandes clob: Character large objects book-review clob(10KB) blob: binary large objects image blob(10MB) movie blob (2GB) Quer o ODBC quer o JDBC fornecem mecanismos para aceder a grandes objectos por partes pequenas de cada vez Database System Concepts 9.1.4 ©Silberschatz, Korth and Sudarshan (modificado) Tipo complexos em Oracle > create type autor as object ( nome varchar(10) ); / > create type conj_autores as table of autor; / > create type editorT as object (nome varchar(10), branch varchar(20)); / > create type livro as object ( titulo varchar(20), autores conj_autores, data_publ date, editor editorT); / > create table livros of livro nested table autores store as tabaut; Database System Concepts 9.1.5 ©Silberschatz, Korth and Sudarshan (modificado) Tipos complexos em Oracle > create type Empregado as object ( nome varchar(20), salario number, member function anual return number); / > create type body Empregado is member function anual return number is begin return salario*14; end; end; / > create table empregados of Empregado; Database System Concepts 9.1.6 ©Silberschatz, Korth and Sudarshan (modificado) Inserção de dados complexos em Oracle > insert into livros values ( ‘Database Systems Concepts’, conj_autores( autor(‘Silberschatz’), autor(‘Korth’), autor(‘Sudarshan’) ), null, editorT(‘McGraw Hill’,’New York’) ); Database System Concepts 9.1.7 ©Silberschatz, Korth and Sudarshan (modificado) Herança Considere a seguinte definição de tipo para pessoas: create type Pessoa as object (nome varchar(20), morada varchar(20)) not final; Pode-se usar herança para definir os tipos para estudante e professor create type Estudante under Pessoa (grau varchar(20), departamento varchar(20)); create type Docente under Pessoa (salário integer, departamento varchar(20)); Os subtipos podem redefinir os métodos usando (explicitamente) na definição do tipo overriding method em vez de method Database System Concepts 9.1.8 ©Silberschatz, Korth and Sudarshan (modificado) Tipos referência As linguagens de objectos permitem criar e referenciar objectos. No SQL:1999 Podem-se referenciar tuplos Cada referência diz respeito a tuplo de uma tabela específica. I.e. cada referência deve limitar o escopo a uma única tabela Database System Concepts 9.1.9 ©Silberschatz, Korth and Sudarshan (modificado) Declaração de Referências em SQL:1999 E.g. definir o tipo Departamento com atributo nome e atributo presidente que é uma referência para o tipo Pessoa, tendo a tabela pessoas como escopo create type Departamento as object ( nome varchar(20), presidente ref Pessoa scope pessoas) A tabela departamentos pode ser criada como usual: create table departamentos of Departamento Em SQL:1999 pode-se omitir a declaração de scope na definição do tipo, passando-a para a criação da tabela, da seguinte forma: create table departamentos of Departamento (presidente with options scope pessoas) Database System Concepts 9.1.10 ©Silberschatz, Korth and Sudarshan (modificado) Inicialização de referências no Oracle Para criar um tuplo com uma referência, cria-se primeiro o tuplo com uma referência null, actualizando depois a referência com a função ref(p): E.g. para criar um departamento com nome DI e presidente Pedro: insert into departamentos values (`DI’, null) update departamentos set presidente = (select ref(p) from pessoas as p where nome=`Pedro’) where nome = `DI’ Database System Concepts 9.1.11 ©Silberschatz, Korth and Sudarshan (modificado) Inicialização de referências no SQL:1999 O SQL:1999 não suporta a função ref(). Em vez disso exige a declaração de um atributo especial para guardar o identificador dum objecto O atributo com o identificador é declarado usando ref is na criação da tabela: create table pessoas of Pessoa ref is oid system generated Aqui, oid é um nome dum atributo. Para obter a referência para um tuplo usa-se esse atributos. Por exemplo: select p.oid (em vez de select ref(p) Database System Concepts ) 9.1.12 ©Silberschatz, Korth and Sudarshan (modificado) Geração de identificadores O SQL:1999 permite que os identificadores sejam gerados pelo utilizador O tipo do identificador tem que ser declarado como para qualquer outro atributo da tabela E.g. create type Pessoa (nome varchar(20) morada varchar(20)) ref using varchar(20) create table pessoas of Pessoa ref is oid user generated Quando se cria um tuplo, tem que se dizer qual o valor do identificador (que é sempre dado como primeiro atributo): insert into pessoas values (‘01284567’, ‘Pedro’, `Lisboa’) Database System Concepts 9.1.13 ©Silberschatz, Korth and Sudarshan (modificado) Geração de identificadores (Cont.) Pode depois usar-se o valor inserido como referência, ao inserir tuplos noutras tabelas Evita pergunta para saber qual o identificador: E.g. insert into departamentos values(`DI’, `02184567’) Também é possível usar uma chave primária como identificador. Para isso usa-se ref from, e declara-se a referência como sendo derived create type Pessoa (nome varchar(20) primary key, morada varchar(20)) ref from(nome) create table pessoas of Pessoa ref is oid derived Database System Concepts 9.1.14 ©Silberschatz, Korth and Sudarshan (modificado) Path Expressions Quais os nomes e moradas de todos os presidentes de departamentos? select presidente –>nome, presidente –>morada from departamentos Uma expressão como “presidente–>nome” chama-se uma path expression No Oracle usa-se: “DEREF(presidente).nome” O uso de referências e path expressions evita operações de junção Se presidente não fosse uma referência, para obter a morada era necessário fazer a junção de departamentos com pessoas Facilita bastante a formulação de perguntas Database System Concepts 9.1.15 ©Silberschatz, Korth and Sudarshan (modificado) Perguntas em tipos estruturados Quais os títulos e respectivos nomes da editora de todos os livros. select título, editor.nome from livros Note que editor é um atributo (e não uma tabela). Tal como para aceder a atributos de uma tabela, também se usa o ponto para aceder a atributos de atributos com tipos estruturados. No Oracle, a função value() devolve o objecto. Pode-se aceder assim a tipos estruturados: select título, value(L).editor.nome from livros L Database System Concepts 9.1.16 ©Silberschatz, Korth and Sudarshan (modificado) Perguntas a métodos locais Para aceder a métodos usa-se o ponto e o nome do método seguido dos argumentos entre parêntesis. Os parêntesis devem lá estar mesmo que não haja argumentos. Em Oracle um método é chamado com <NomeDoTipo>.<NomeDoMétodo>(<Objecto>,<OutrosArgs>) Quando não há tipo directamente associado, pode usar-se o value: select nome, value(e).anual() from empregados e Database System Concepts 9.1.17 ©Silberschatz, Korth and Sudarshan (modificado) Perguntas a colecções Os atributos com colecções são usados de forma semelhante tabelas, utilizando agora a keyword unnest Quais os títulos dos livros que têm database entre as palavras chave? select título from livros where ‘database’ in (unnest(conj-keyword)) Obter todos os pares (título,autor) em que autor é autor do livro com título título select B.título, A.nome from livros as B, unnest (B.array-autores) as A Pode-se aceder a elementos dum array da forma habitual: select array-autores[1], array-autores[2], array-autores[3] from livros where título = `Database System Concepts’ Database System Concepts 9.1.18 ©Silberschatz, Korth and Sudarshan (modificado) Perguntas a colecções no Oracle Em vez de unnest o Oracle usa a função table que devolve que converte um atributo numa tabela: Quais os títulos dos livros e respectivos autores? select L.título, A.nome from livros L, table(L.autores) A; Database System Concepts 9.1.19 ©Silberschatz, Korth and Sudarshan (modificado) Nesting Agora as tabelas podem conter atributos com relações. Faz pois sentido que o resultado de um select possa conter atributos com relações - Nesting Isto pode ser feito de maneira semelhante às agregações para criar um conjunto, usa-se a função set() em vez da função de agregação Para, em livros-flat, agrupar num atributo o conjunto das keywords: select título, autor, Editor(nome-pub, pub_branch) as editor, set(keyword) as conj-keyword from livros-flat group by título, autor, editor Para agrupar também os vários autores dum livro: select título, set(autor) as autores, Editor(nome-pub, pub_branch) as editor, set(keyword) as conj-keyword from livros-flat group by título, editor Database System Concepts 9.1.20 ©Silberschatz, Korth and Sudarshan (modificado) Nesting (Cont.) Outra abordagem consiste em usar sub-perguntas na cláusula select: select título, ( select M.autor from livros-flat as M where M.título =O.título) as autores, Editor(nome-pub, pub-branch) as editor, (select N.keyword from livros-flat as N where N.título = O.título) as conj-keyword from livros-flat as O Pode usar-se order by em perguntas nested para obter colecções ordenadas Podem-se criar arrays desta forma Database System Concepts 9.1.21 ©Silberschatz, Korth and Sudarshan (modificado) Funções e Procedimentos O SQL:1999 suporta funções e procedimentos As funções e procedimentos podem ser escritas directamente em SQL, ou em linguagens de programação externas (e.g. PL/SQL). Alguns sistemas de bases de dados (entre eles o Oracle) permitem definir funções que devolvem tabelas Grande parte dos sistemas de bases de dados têm linguagens proprietárias onde se podem definir funções e procedimentos, e que diferem bastante do standard SQL:1999 No Oracle podem-se criar funções e procedimentos através da linguagem PL/SQL, ou directamente na base de dados. Database System Concepts 9.1.22 ©Silberschatz, Korth and Sudarshan (modificado) Funções SQL Definir uma função que devolve (no conjunto de relações na 4NF para livros-flat) o número de autores de um dado livro: create function conta_autores(nome varchar(20)) returns integer begin declare a_count integer; select count(autor) into a_count from autores where autores.título=nome; return a_count; end Quais os livros que têm mais que um autor? select título from livros-flat where conta_autores(título)> 1 Database System Concepts 9.1.23 ©Silberschatz, Korth and Sudarshan (modificado) Métodos SQL Os métodos podem ser vistos como funções associadas a tipos estruturados Têm um primeiro parâmetro (implícito) chamado self que “aponta” para o valor do tipo estruturado para o qual o método é chamado O código do método pode-se referir aos atributos do tipo estruturado usando a variável self E.g. Database System Concepts self.a 9.1.24 ©Silberschatz, Korth and Sudarshan (modificado) Funções e procedimentos SQL A função conta_autores poderia também ser escrita como um procedimento: create procedure conta_autores_proc(in nome varchar(20), out a_count integer) begin select count(autor) into a_count from autores where autores.título=nome; end; Os procedimentos podem ser chamados dentro de outros procedimentos SQL, ou de linguagens SQL embedded ou proprietárias. E.g. num procedimento SQL declare a_count integer; call conta_autores_proc (`Database systems Concepts’, a_count); O SQL:1999 permite que haja mais que uma função ou procedimento com o mesmo nome, desde que o número de argumentos (ou, pelo menos, os seus tipos) sejam diferentes Database System Concepts 9.1.25 ©Silberschatz, Korth and Sudarshan (modificado) Funções e procedimentos externos O SQL:1999 permita o uso de funções e procedimentos escritos noutras linguagens (e.g. C ou C++) A declaração de funções e procedimentos externos faz-se da seguinte forma: create procedure conta-autores-proc(in nome varchar(20), out count integer) language C external name’ /usr/aluno111/bin/conta-autores-proc’ create function conta-autores(nome varchar(20)) returns integer language C external name ‘/usr/aluno111/bin/conta-autores’ Database System Concepts 9.1.26 ©Silberschatz, Korth and Sudarshan (modificado) Funções e procedimentos externos (Cont.) Vantagens: Mais eficiente para muitas operações Mais poder expressivo Desvantagens A código que implementa as rotinas externas pode ter que ser carregado no sistema de bases de dados e executado no espaço de endereços deste risco de corromper acidentalmente a estrutura da base de dados risco de segurança dos dados Há alternativas que garante segurança (à custa, por vezes, da deterioração da performance) A execução directa no sistema de bases de dados só é feita se a eficiência for bem mais importante que a segurança Database System Concepts 9.1.27 ©Silberschatz, Korth and Sudarshan (modificado) Segurança para rotinas externas Para lidar com estes problemas de segurança Usar técnicas de sandbox i.e. usar linguagem segura como o Java, que não permite o acesso a outras parte do código da base de dados Ou executar rotinas externas em processo separado, sem acesso à memória usada por outros processos do sistema de bases de dados Os parâmetro e resultados são passados via comunicação entre processos Ambas as alternativas têm custos de performance Database System Concepts 9.1.28 ©Silberschatz, Korth and Sudarshan (modificado) Construtores procedimentais O SQL:1999 suporta uma grande variedade de construtores procedimentais O Oracle suporta aqueles que existem no PL/SQL Expressões com whiles e repeats declare n integer default 0; while n < 10 do set n = n+1; end while; repeat set n = n – 1; until n = 0; end repeat Em Oracle, em vez de set var =… usa-se var :=… Database System Concepts 9.1.29 ©Silberschatz, Korth and Sudarshan (modificado) Construtores procedimentais (Cont.) Ciclos Iterações sobre o resultado de perguntas E.g. soma de todos os saldos da agência Perryridge declare n integer default 0; for r as select balance from account where branch-name = ‘Perryridge’ do set n = n + r.balance; end for Database System Concepts 9.1.30 ©Silberschatz, Korth and Sudarshan (modificado) Construtores procedimentais (cont.) Expressões condicionais (if-then-else) E.g. Soma dos saldos por categorias de contas (com saldo <1000, entre 1000 e 5000, > 5000) if r.balance < 1000 then set l = l + r.balance elseif r.balance =< 5000 then set m = m + r.balance else set h = h + r.balance end if Assinalar condições de excepção e erros, e declaração de tratamento de excepções declare out_of_stock condition; declare exit handler for out_of_stock ; begin … .. signal out-of-stock; end Neste exemplo o tratamento da excepção é exit – sai do bloco begin…end No Oracle em vez de signal usa-se raise Database System Concepts 9.1.31 ©Silberschatz, Korth and Sudarshan (modificado) Comparação entre os vários sistemas Sistemas relacionais Simplicidade no tipo de dados, linguagens declarativa para perguntas, boa segurança. Linguagens persistentes Tipos de dados complexos, fácil integração com linguagens de programação, eficientes. Sistemas Objecto-relacional Tipos de dados complexos, linguagens declarativa para perguntas, boa segurança. Nota: Os sistemas reais, esbatem um pouco estas fronteiras. E.g. linguagens persistentes que funcionem como wrapper sobre uma base de dados relacional, embora permitam tipos de dados complexos e facilidade de integração com linguagens imperativas, nem sempre são muito eficientes… Database System Concepts 9.1.32 ©Silberschatz, Korth and Sudarshan (modificado)