Capítulo 3: Modelo de Dados Objeto-Relacional
Banco de Dados IIBanco de Dados IIProf. Carlos Eduardo PiresProf. Carlos Eduardo Pires
[email protected]@dsc.ufcg.edu.br
Capítulo 3: Modelo de Dados Objeto-Relacional 2
Conteúdo
Bancos de Dados Objeto-Relacional Modelo de Dados Objeto-Relacional Linguagem de Consultas Objeto-Relacional
Visão geral dos conceitos de OO disponíveis no SGBD Oracle 11g
Extensões para criar objetos complexos O impacto da OO em consultas SQL Integração OO-Relacional através de Visões de
Objeto
Capítulo 3: Modelo de Dados Objeto-Relacional 3
Banco de Dados Objeto-Relacional
SGBDs Objeto-Relacional combinam os benefícios do modelo relacional com a capacidade de modelagem do modelo OO
Fornecem suporte para consultas complexas sobre dados complexos
Atendem aos requisitos das novas aplicações e da nova geração de aplicações de negócios
Capítulo 3: Modelo de Dados Objeto-Relacional 4
Banco de Dados Objeto-RelacionalModelos e Linguagens
O Modelo de Dados OR é uma extensão do modelo Relacional Extensões incluem mecanismos para permitir aos usuários
estender o banco de dados com tipos e funções específicas da aplicação
A Linguagem de Consulta OR é uma extensão da linguagem SQL para suportar o modelo de objetos As extensões incluem consultas envolvendo objetos, atributos
multivalorados, Tipos Abstratos de Dados (TADs), métodos e funções como predicados de busca em uma consulta
Capítulo 3: Modelo de Dados Objeto-Relacional 5
Modelo de Dados Objeto-Relacional
Permite especificar e utilizar TADs da mesma forma que os tipos de dados pré-definidos TADs são tipos de dados definidos pelo
usuário que encapsulam comportamento e estrutura interna (atributos)
A tabela convencional é estendida para permitir a referência de objetos (referência de tipos), TADs e valores alfanuméricos como domínio de colunas
Capítulo 3: Modelo de Dados Objeto-Relacional 6
Modelo de Dados Objeto-Relacional
Utiliza referências para representar conexões inter-objetos tornando as consultas baseadas em caminhos de referência mais compactas e eficientes do que as consultas feitas com junção
Herança é implementada organizando todos os tipos em hierarquias
Utiliza construtores set, list, multiset ou array para organizar coleções de objetos
Capítulo 3: Modelo de Dados Objeto-Relacional 7
Benefícios do Modelo de Dados Objeto-Relacional
Nova Funcionalidade Aumenta indefinidamente o conjunto de
tipos e funções fornecidas pelo SGBD Desenvolvimento de aplicações
simplificado Reuso de código por todas as aplicações
Capítulo 3: Modelo de Dados Objeto-Relacional 8
Linguagem de Consultas paraBancos de Dados Objeto-Relacional
O resultado de uma consulta ainda consiste de dados em formato tabular Um SGBDOR ainda é relacional pois suporta dados
armazenados em tabelas formadas por linhas e colunas
A linguagem de consultas para BDOR é uma extensão da linguagem SQL, utilizada para definição e manipulação de dados e consultas
Capítulo 3: Modelo de Dados Objeto-Relacional 9
Padrão SQL:1999 (SQL-3)
É a base para muitos SGBDs OR Oracle, Informix Universal Server, IBM DB2 Universal
Database, entre outros
Caracterizada como “SQL Orientada a Objetos”
Envolve características adicionais classificadas em: Relacionais
Novos tipos de dados e novas cláusulas
Orientadas a Objetos Tipos de dados definidos pelo usuário, definição de
métodos e uso de referências
Capítulo 3: Modelo de Dados Objeto-Relacional 10
Conteúdo do Padrão SQL:1999 Multi-part standard — ISO/IEC 9075-n:1999
Parte 1: SQL/Framework Parte 2: SQL/Foundation Parte 3: SQL/CLI Parte 4: SQL/PSM Parte 5: SQL/Bindings Parte 7: SQL/Temporal Parte 9: SQL/MED (Management of External Data) Parte 10: SQL/OLB (Object Linking Binding: JDBC e SQLJ) SQL/OLAP SQL/MM: Spatial, Text, Image
Capítulo 3: Modelo de Dados Objeto-Relacional 11
Part 2: SQL/Foundation Estamos interessados neste curso na parte do
padrão que trata de Orientação a Objetos: Tipos de dados definidos pelo usuário Atributos e comportamento Encapsulamento: funções e métodos Hierarquias de tipos (herança simples) User-defined CAST, ordenação Tabelas tipadas e tipos referência
Capítulo 3: Modelo de Dados Objeto-Relacional 12
Tipos de Dados Definidos pelo Usuário UDT – User Defined Types
Evolução dos tipos em SQL: Sempre houve os tipos embutidos (built-in)
INTEGER, SMALLINT, CHAR, VARCHAR, DATE, TIME e TIMESTAMP
Alguns tipos proprietários Oracle NUMBER e RAW; Sybase SMALLMONEY e IMAGE
Após alguns anos, surgiram BLOB e CLOB
Houve algumas tentativas de “DBMS vendors” de propor soluções para certos tipos de dados específicos: texto, GIS, imagens e time series
Capítulo 3: Modelo de Dados Objeto-Relacional 13
Tipos de Dados Definidos pelo Usuário Entretanto, o que se queria era uma solução
mais genérica que pudesse estender o SGBD com tipos específicos:
Solução UDT
Observação O termo “User” em UDT não quer dizer usuário final! E sim, desenvolvedor da aplicação
Capítulo 3: Modelo de Dados Objeto-Relacional 14
UDT Definição
Um UDT é um tipo que não é embutido em um SGBD ou linguagem de programação, mas que pode ser definido como parte do desenvolvimento de uma ou mais aplicações, com possível comportamento definido em sua criação
(adaptada de Jim Melton: Advanced SQL:1999, Morgan Kauffman, 2003)
Capítulo 3: Modelo de Dados Objeto-Relacional 15
Tipos Definidos pelo Usuário
Tipos de UDT: Structured types Reference types
Capítulo 3: Modelo de Dados Objeto-Relacional 16
Structured Types Também conhecidos como “Abstract Data
Types” (Tipos Abstratos de Dados) Pode conter uma estrutura complexa Análogo a struct na linguagem C Estado
Atributos
Comportamento Métodos, funções e procedures
Capítulo 3: Modelo de Dados Objeto-Relacional 17
Atributos
Cada atributo pode ser: Tipo built-in, incluindo coleção Tipo definido pelo usuário
O sistema gera uma função “get” (observer) e uma função “set” (mutator) para cada atributo
Capítulo 3: Modelo de Dados Objeto-Relacional 18
Encapsulamento Esconde implementação dos usuários Permite que a implementação mude sem afetar
as aplicações – desde que a interface provida permaneça constante
Aplicações acessam tudo através da interface funcional, incluindo os atributos usando as funções observer e mutator
Capítulo 3: Modelo de Dados Objeto-Relacional 19
Procedures, Funções e Métodos Conceito genérico
Rotina procedure, function, method (normalmente “stored”)
Procedure Parâmetros IN/OUT
Função Apenas parâmetro IN (output retornado como valor
da função)
Métodos Caso especial de funções
Capítulo 3: Modelo de Dados Objeto-Relacional 20
Procedures, Funções e Métodos Procedures
Podem ser overloaded (sobrecarregadas) Mesmo nome, com quantidade diferente de parâmetros
Tipos de dados dos argumentos não são utilizados para overloading
Existem em qualquer esquema (usuário) Não estão ligados a um tipo estruturado
Capítulo 3: Modelo de Dados Objeto-Relacional 21
Procedures, Funções e Métodos Funções
Podem ser overloaded (sobrecarregadas) Várias funções com o mesmo nome, mas com o mesmo
número de parâmetros Distintos pelos tipos de dados dos argumentos
Existem em qualquer esquema (usuário) Não estão ligados a um tipo estruturado
Capítulo 3: Modelo de Dados Objeto-Relacional 22
Procedures, Funções e Métodos Métodos
Podem ser overloaded (sobrecarregados) Fortemente acoplado a um único tipo estruturado Deve estar no mesmo esquema (usuário) da definição
do tipo
Tipo do argumento é associado ao tipo estruturado
Capítulo 3: Modelo de Dados Objeto-Relacional 23
Notação: ponto (.) vs. funcional Notação funcional
c(b(a)) Exemplo: TRIM(TO_CHAR(v_age))
Notação de ponto a.b.c
Funções DEVEM usar a notação funcional Métodos DEVEM usar a notação de ponto
Observer: SELECT EMP.AGE(dt_nasc) IDADE FROM EMPREGADO EMP
Mutator: SET EMP.AGE = 10, onde, EMP = TAD e AGE = Atributo
Capítulo 3: Modelo de Dados Objeto-Relacional 24
Diferenças entre Funções e Métodos
Característica Função Método
Ligado a um tipo específico?
Não Sim
Sintaxe de chamada Notação funcional Notação de ponto
Esquema (usuário) de residência
Qualquer esquema Esquema do tipo associado
Capítulo 3: Modelo de Dados Objeto-Relacional 25
Tipos de Método
Há dois tipos de métodos: Static methods: opera no tipo de dados
propriamente dito Instance methods: opera na instância de um
tipo
Capítulo 3: Modelo de Dados Objeto-Relacional 26
Declaração de Métodos
Há dois lugares para definição de métodos Declaração do Tipo
Os métodos são declarados (suas assinaturas) na definição do tipo de dados
Implementação do Tipo A implementação dos métodos fica em outro lugar,
utilizando-se de um comando SQL específico para tal fim
O código pode ser escrito em SQL ou em outra linguagem de programação (Java e C++)
Capítulo 3: Modelo de Dados Objeto-Relacional 27
Exemplo de Método (Declaração)
CREATE TYPE Filme AS (
título VARCHAR (100),
descricao VARCHAR (500),
duracaoMinutos INT) NOT FINAL
METHOD duracaoEmHoras() RETURNS INTERVAL HOUR(2) TO MINUTE;
TAD
TIPO DE DADOS
ATRIBUTOS
ESPECIFICAÇÃO DO MÉTODO
Permite
HERANÇA
Capítulo 3: Modelo de Dados Objeto-Relacional 28
Exemplo de Método (Implementação)
CREATE INSTANCE METHOD duracaoEmHoras() RETURNS INTERVAL HOUR(2) TO MINUTE FOR Filme
RETURN CAST (SELF.duracaoMinutos AS INTERVAL HOUR(2) TO MINUTE);
Capítulo 3: Modelo de Dados Objeto-Relacional 29
Criação de Tabela
Exemplo
/* Coluna info usa um tipo de dados definido pelo usuário */
CREATE TABLE tb_Filme (
cod INT,
info FILME,
aluguel DECIMAL(5,2));
Capítulo 3: Modelo de Dados Objeto-Relacional 30
Chamada a Método
Recupere a duração em horas e minutos do filme ‘Ghost’
SELECT mt.info.duracaoEmHoras() AS duracao
FROM tb_Filme AS mt
WHERE mt.info.titulo = ‘Ghost’
Capítulo 3: Modelo de Dados Objeto-Relacional 31
Construtores Considere:
CREATE TYPE rational AS (numerator INTEGER,denominator INTEGER);
Nenhum “new object” é criado
Ao invés usa-se da seguinte forma:
DECLARE VARIABLE ratvar rational;SET ratvar = rational(5,7);...
INSERT INTO table1 (ratcol) VALUES (rational(13,131));
Método Construtor
CREATE TABLE table1
(ratcol rational);
Capítulo 3: Modelo de Dados Objeto-Relacional 32
Construtores
Construtor default é gerado pelo sistema, com o mesmo nome do tipo de dados abstratos:CREATE FUNCTION rational() RETURNS rational;
Métodos são overloadable Qualquer número de construtores definidos pelo
usuário: CREATE FUNCTION rational (numer, denom) RETURNS
rational CREATE FUNCTION rational (denom) RETURNS rational
Capítulo 3: Modelo de Dados Objeto-Relacional 33
Sintaxe de Tipo Estruturado
CREATE TYPE name
[ UNDER supertype-name ]
AS ( attrib-name data type,... )
[ [ NOT ] INSTANTIABLE ]
[ NOT ] FINAL
[ REF ref-options ]
[ method-spec,... ]
Capítulo 3: Modelo de Dados Objeto-Relacional 34
Sintaxe Tipo Estruturado
REF ref-options Definido pelo usuário:
REF USING predefined-type [ ref-cast-option ] Derivado:
REF (attrib-name, ... ) Gerado pelo sistema:
REF IS SYSTEM GENERATED
Capítulo 3: Modelo de Dados Objeto-Relacional 35
Sintaxe Tipo Estruturado
method-spec Método original:
[ INSTANCE | STATIC ] METHOD name (param-name type,... ) RETURNS type
Método sobrescrito:
OVERRIDING original-method
Capítulo 3: Modelo de Dados Objeto-Relacional 36
Remoção de um UDT
DROP TYPE nome [CASCADE | RESTRICT];
CASCADE: apaga o tipo e os objetos dependentes (colunas, funções, etc)
RESTRICT (default): não apaga o tipo se há objetos que dependem dele
Capítulo 3: Modelo de Dados Objeto-Relacional 37
Alterando um UDT
<alter type> ::= ALTER TYPE <nome> <action> <action> ::=
ADD ATTRIBUTE <definition> |
DROP ATTRIBUTE <nome> RESTRICT |
ADD <method specification> |
DROP <method specification>
Capítulo 3: Modelo de Dados Objeto-Relacional 38
Alterando um UDT
Exemplos
ALTER TYPE filme ADD ATTRIBUTE year CHAR;
ALTER TYPE filme DROP ATTRIBUTE year RESTRICT;
ALTER TYPE filme ADD METHOD classificacao() RETURNS NUMBER;
ALTER TYPE filme DROP METHOD classificacao;
Capítulo 3: Modelo de Dados Objeto-Relacional 39
Herança de Tipos Permite a especialização dos tipos existentes “Subtype” e “Supertype”
Capítulo 3: Modelo de Dados Objeto-Relacional 40
Herança Subtype herda tudo do supertype SQL:1999 dá suporte apenas a herança simples Na definição de subtype:
Novos atributos podem ser adicionados Novos métodos podem ser adicionados Métodos podem ser sobrescritos (polimorfismo)
Capítulo 3: Modelo de Dados Objeto-Relacional 41
Herança Observações
Pode-se definir UDT contendo atributos cujos tipos são supertipos do tipo definido
Não se pode definir UDT cujos atributos contenham o tipo do próprio tipo que está sendo definido (recursivo)
Não se pode definir UDT contendo atributos com tipos de subtipos que ainda vão ser definidos
Pessoa
Emp Atributo: dependente tipo_pessoa
Capítulo 3: Modelo de Dados Objeto-Relacional 42
UDT: Valores e não instâncias!
Os tipos estruturados possuem valores e não instâncias!
RazãoNão há OID, que só ocorrerá no repositório
que são as tabelas tipadas (vistas mais à frente)
Capítulo 3: Modelo de Dados Objeto-Relacional 43
UDT: Valores e não instâncias!
Exemplo:
BEGIN
DECLARE v1, v2 Filme;
SET v1 = NEW Filme (‘Shrek’, ‘blá blá blá’, 118);
SET v2 = v1;
END;
Capítulo 3: Modelo de Dados Objeto-Relacional 44
User Defined Constructors (Inicializadores)
SQL:1999 dá suporte a métodos construtores (inicializadores) definidos pelo usuário
Podem existir vários destes construtores, com mesmo nome do construtor provido pelo sistema, com lista de parâmetros diferente para cada construtor
São chamados através da palavra-chave NEW Tais construtores devem ter SELF como retorno
Capítulo 3: Modelo de Dados Objeto-Relacional 45
User Defined Constructors (Inicializadores) Exemplo
CREATE METHOD filme (nome VARCHAR(50), descr VARCHAR(500), dur INT) RETURNS filme
BEGIN
SET SELF.titulo= nome;
SET SELF.descricao = descr;
SET SELF.duracao = dur;
RETURN SELF;
END;
Capítulo 3: Modelo de Dados Objeto-Relacional 46
User Defined Constructors (Inicializadores)
BEGIN
DECLARE f filme;
SET f = NEW filme (‘Shrek’, ‘blá blá blá’, 135);
…
END;
Chamada ao Método Construtor
Capítulo 3: Modelo de Dados Objeto-Relacional 47
System Defined Constructors (Inicializadores) Exemplo anterior usando o construtor definido
pelo sistema
BEGIN
DECLARE f filme;
SET f = NEW filme(); -- cria com atributos vazios
SET f.titulo= ‘Shrek’;
SET f.duracao = 135;
END; -- atributo descrição contém valor null
Capítulo 3: Modelo de Dados Objeto-Relacional 48
Inserindo DadosCREATE TABLE tb_Filme (
cod INT,
info FILME,
aluguel DECIMAL(5,2));
INSERT INTO tb_Filme VALUES(
‘1203’, -- código do filme
NEW Filme (‘Gone with the Wind’, ‘blá blá blá’,128), -- instância de Filme
2.99); -- preço aluguel
Capítulo 3: Modelo de Dados Objeto-Relacional 49
Inserindo Dados Mesma inserção anterior usando uma variável
(instanciada)BEGIN
DECLARE f filme;
SET f = NEW filme (‘Shark’, ‘blá blá blá’, 137);
INSERT INTO tb_Filme VALUES (
‘1207’, -- código do filme
f, -- instância de Filme
3.59); -- preço aluguel
END;
Capítulo 3: Modelo de Dados Objeto-Relacional 50
Atualizando Dados
1. Atualizando o valor do aluguel de um filme particular
UPDATE tb_Filme
SET aluguel = 1.99
WHERE codigo = 1203;
Capítulo 3: Modelo de Dados Objeto-Relacional 51
Atualizando Dados
2. Atualizando um filme sem todos os dados
UPDATE tb_Filme
SET info = NEW Filme (‘’, ‘’, 228)
WHERE codigo = 1203;
Capítulo 3: Modelo de Dados Objeto-Relacional 52
Atualizando Dados
3. Atualizando apenas a duração em minutos do filme (título e descrição permanecem inalterados)
UPDATE tb_Filme
SET info = NEW Filme (info.titulo, info.descricao, 228)
WHERE codigo = 1203;
Capítulo 3: Modelo de Dados Objeto-Relacional 53
Atualizando Dados
4. Mesmo que o exemplo anterior, de forma mais simples:
UPDATE tb_Filme
SET info = info.duracaoMinutos(228)
WHERE codigo = 1203;
Capítulo 3: Modelo de Dados Objeto-Relacional 54
Atualizando Dados
5. Mesmo que o exemplo anterior, de forma mais simples ainda:
UPDATE tb_Filme
SET info.duracaoMinutos = 113
WHERE codigo = 1203;
Capítulo 3: Modelo de Dados Objeto-Relacional 55
Recuperando Dados
DECLARE variavel VARCHAR(500); -- opção 1
variavel filme.descricao%TYPE; -- ideal, só em Oracle
variavel tb_filme.info.descricao%TYPE; -- outra opção a ser testada
BEGIN
SELECT mt.info.descricao
INTO variavel
FROM tb_filme AS mt
WHERE mt.codigo = 1203;
END;
Capítulo 3: Modelo de Dados Objeto-Relacional 56
Comparação de UDT A comparação default de UDT compara campo a campo Pode-se também criar uma semântica de comparação
para o UDT Usa-se o comando:
CREATE ORDERING FOR <nome do tipo>[EQUALS ONLY BY <categoria> | ORDER FULL BY <categoria>]
<categoria> ::= RELATIVE WITH <especificação de função> | MAP WITH <especificação de função de mapeamento>] |
STATE [<nome específico>] | RELATIVE WITH COMPARABLE INTERFACE
Capítulo 3: Modelo de Dados Objeto-Relacional 57
Comparação de UDT Comparação STATE
Você pode instruir o sistema a fazer comparações baseadas em valores de atributos
Usa expressão de igualdade, retornando um valor Boolean É a comparação default, campo a campo Usada quando os atributos forem de tipo de dados embutido
Exemplo
CREATE ORDERING FOR Filme
EQUALS ONLY BY STATE;
SELECT mt.* FROM tb_Filme mt ORDER BY info;
coluna da tabela tb_filme
Capítulo 3: Modelo de Dados Objeto-Relacional 58
Comparação de UDT Comparação MAP WITH
Mapear dois tipos estruturados para algum tipo embutido do SQL, e daí fazer a comparação com o resultado deste mapeamento
Este mapeamento é feito com uma map function, que é chamada automaticamente
ExemploCREATE ORDERING FOR FilmeORDER FULL BY MAP WITH FUNCTION filme_mapping (filme);
Capítulo 3: Modelo de Dados Objeto-Relacional 59
Comparação de UDT Comparação MAP (cont.)
CREATE FUNCTION filme_mapping (f filme)
RETURNS INTEGER
RETURN length(f.titulo) + f.duracaoEmMinutos;
Tipo de dados embutido (built-in)
Capítulo 3: Modelo de Dados Objeto-Relacional 60
Comparação de UDT
Comparação RELATIVE Recebe dois “objetos” do mesmo tipo Comparação usando o resultado retornado
por uma relative function, que retorna: -1: o primeiro objeto é menor do que o segundo 0: os dois tipos são iguais +1: o primeiro objeto tem valor maior do que o
segundo
Pode-se especificar a função para o supertipo e os subtipos herdam
Capítulo 3: Modelo de Dados Objeto-Relacional 61
Comparação de UDT Comparação RELATIVE (Exemplo):
CREATE ORDERING FOR FilmeORDER FULL BY RELATIVE WITHFUNCTION filme_comp (filme, filme);
CREATE FUNCTION filme_comp (f1 filme, f2 filme)RETURNS INTEGER
IF (f1.titulo < f2.titulo) -- condição da aplicaçãoTHEN RETURN -1
ELSEIF (f1.titulo > f2.titulo) THEN RETURN 1
ELSEIF (f1.descricao < f2.descricao) THEN RETURN -1
... ELSE RETURN 1 END IF;
Capítulo 3: Modelo de Dados Objeto-Relacional 62
Comparação de UDT Remover uma User Defined Comparison:
DROP ORDERING FOR <nome do tipo>;
Exemplo de uso:
SELECT DISTINCT mt.info
FROM tb_Filme mt;
DISTINCT requer ordenação
Exigem ordenação:
-GROUP BY
-ORDER BY
-CREATE INDEX
-SELECT DISTINCT
Capítulo 3: Modelo de Dados Objeto-Relacional 63
Segurança de UDT Em SQL:1999 não há public, protected, private A restrição de acesso é controlada via privilégios Tais privilégios são atribuídos com o comando
GRANT e podem ter WITH GRANT OPTION Privilégios:
USAGE: permite o tipo ser usado (por exemplo, em colunas)
UNDER: permite que sejam criados subtipos do tipo em questão
EXECUTE: permite usuários executarem um dado método
Capítulo 3: Modelo de Dados Objeto-Relacional 64
Segurança de UDT Exemplo:
GRANT EXECUTE ON INSTANCE METHOD ano_lancamento() FOR filme TO PUBLIC;
Qualquer usuário do banco de dados pode executar o método ano_lancamento pertencente ao tipo filme
Capítulo 3: Modelo de Dados Objeto-Relacional 65
Tabelas Tipadas Instâncias do tipo são linhas de uma tabela Comporta-se como objetos de uma classe
CREATE TABLE rationals OF rational
REF IS id_col ref-option; Cria uma tabela-base com uma coluna por
atributo, além de uma coluna “self-referencing” Esta coluna “self-referencing” contém em cada
linha um valor que identifica univocamente a linha (objeto)
Capítulo 3: Modelo de Dados Objeto-Relacional 66
Tabelas Tipadas Pode-se inserir, remover, alterar e consultar as
linhas da tabela usando SQL Pode-se definir restrições (constraints) e triggers Obs.: o uso de NEW visto anteriormente, não
causa uma instância ser armazenada numa tabela, é preciso executar um INSERT!
Por que não? Podem existir várias tabelas tipadas associadas a um
dado UDT Pode-se criar valores de tipos que não são persistidos
em linhas de uma tabela tipada (como vimos!)
Capítulo 3: Modelo de Dados Objeto-Relacional 67
Tabelas Tipadas Colunas “Self-referencing”
Espécie de OID Seu valor é único globalmente, ou seja, não haverá
duas linhas (objetos) em uma tabela com valor igual da coluna “self-referencing”
As aplicações podem usar os valores das colunas self-referencing como uma espécie de “ponteiros” para as linhas contendo os valores nas tabelas tipadas Por isso, alguns chamam typed tables de referenced tables
Capítulo 3: Modelo de Dados Objeto-Relacional 68
Exemplo de Tabela Tipada
CREATE TYPE pub_type AS (
title VARCHAR(80),
abstract VARCHAR(150))
REF IS SYSTEM GENERATED;
CREATE TABLE pub_table OF pub_type
(REF IS pub_id SYSTEM GENERATED);
Capítulo 3: Modelo de Dados Objeto-Relacional 69
Hierarquias de Tabelas Tipadas Pode-se definir uma tabela tipada como
sendo sub-tabela de uma outra, chamada super-tabela
Só é possível criar hierarquias de tabelas tipadas De tabelas normais não pode!
Deve haver correspondência às hierarquias de tipo Supertable deve ser “of” supertype Subtable deve ser “of” subtype
Capítulo 3: Modelo de Dados Objeto-Relacional 70
Hierarquias de Tabelas Tipadas A hierarquia completa de tabelas não precisa
corresponder à hierarquia completa de tipos Exemplo: Hierarquia de tipos
MovieMovie
VHS TapeVHS Tape DVDDVD BlueRayBlueRay
Dolby Digital SoundDolby Digital Sound
DTS SoundDTS Sound
Capítulo 3: Modelo de Dados Objeto-Relacional 71
Hierarquias de Tabelas Tipadas
Hierarquia de tabelas para apenas DVD
DVDDVD
Dolby Digital SoundDolby Digital Sound
DTS SoundDTS Sound
Capítulo 3: Modelo de Dados Objeto-Relacional 72
Hierarquias de Tabelas Tipadas
Hierarquia de Tabelas sem diferenciação de som
MovieMovie
VHS TapeVHS Tape DVDDVD BlueRayBlueRay
Capítulo 3: Modelo de Dados Objeto-Relacional 73
Hierarquias de Tabelas Tipadas
Hierarquia de Tabelas Inválida!
MovieMovie
VHS TapeVHS Tape BlueRayBlueRay
Dolby Digital SoundDolby Digital Sound
DTS SoundDTS Sound
Capítulo 3: Modelo de Dados Objeto-Relacional 74
Definindo Tabelas Tipadas
<table definition> :: =
CREATE TABLE <table_name>
OF <UDT name>
[ <subtable clause> ]
[ <table element list> ]
<subtable clause> ::=
UNDER <table_name>
Capítulo 3: Modelo de Dados Objeto-Relacional 75
Definindo Tabelas Tipadas
<table element list> ::=
( <table element> [ {, <table element>} … ] )
<table element> ::= <table constraint definition> |
<self-referencing column specification>
| < column option >
Capítulo 3: Modelo de Dados Objeto-Relacional 76
Definindo Tabelas Tipadas
Só é possível criar uma tabela tipada a partir de uma única super-tabela tipada Herança simples
O nome da tabela “pai” na cláusula da sub-tabela dever ser um nome de uma tabela tipada!
O nome dos atributos da sub-tabela não pode ser igual aos herdados
Não se pode definir colunas arbitrárias (fora do UDT) dentro de uma tabela tipada, apenas as que compõem o UDT + self-referencing column
Capítulo 3: Modelo de Dados Objeto-Relacional 77
Definindo Tabelas Tipadas
Observação NÃO é permitido especificar a restrição PRIMARY KEY
em uma subtabela, i.e., deve vir na super-tabela Uma coluna self-referencing NÃO pode ser especificada
na definição de uma sub-tabela, mas deve ser especificada na super-tabela máxima (raiz)
Restrições de integridade só podem ser criadas na tabela tipada e não do tipo de dados definido pelo usuário
Capítulo 3: Modelo de Dados Objeto-Relacional 78
Definindo Tabelas Tipadas
<self-referencing column specification> ::= REF IS <column name> <reference generation>
<reference generation> ::= [ SYSTEM GENERATED | USER GENERATED | DERIVED ]
1 2 3
Capítulo 3: Modelo de Dados Objeto-Relacional 79
Definindo Tabelas Tipadas
<column option> ::=
<column name> WITH OPTIONS <column option list>
<column option list> ::=
[<scope clause>]
[<default clause>]
[<column constraint definition> …]
Capítulo 3: Modelo de Dados Objeto-Relacional 80
Definindo Tabelas Tipadas
[<scope clause>]: se a coluna é do tipo REF, então pode-se especificar o nome da tabela tipada para o UDT associado
[<default clause>]: permite definir valores default para uma coluna
[<column constraint definition> …]: permite definir constraints como NOT NULL, CHECK, etc.
Capítulo 3: Modelo de Dados Objeto-Relacional 81
Definindo Tabelas Tipadas
CREATE TYPE movie AS (
title VARCHAR(100),
desc VARCHAR(500),
runs INTEGER)
INSTANTIABLE
NOT FINAL
REF IS SYSTEM GENERATED;
Movie
Capítulo 3: Modelo de Dados Objeto-Relacional 82
Definindo Tabelas Tipadas
CREATE TYPE dvd UNDER movie AS (
stock_number INTEGER,
rental_price VARCHAR(500))
INSTANTIABLE
NOT FINAL; Movie
DVD
Capítulo 3: Modelo de Dados Objeto-Relacional 83
Definindo Tabelas Tipadas
CREATE TABLE short_movies OF movie
(REF IS movie_id SYSTEM GENERATED,
runs WITH OPTIONS
CONSTRAINT short_movie_check_runs
CHECK (runs < 90));
Short_Movies
Capítulo 3: Modelo de Dados Objeto-Relacional 84
Definindo Tabelas Tipadas
CREATE TABLE short_dvds OF dvd
UNDER short_movies
(rental_price WITH OPTIONS
CONSTRAINT short_dvds_check_price
CHECK (rental_price < 1.99));
Short_Movies
Short_DVDs
Capítulo 3: Modelo de Dados Objeto-Relacional 85
Definindo Tabelas Tipadas Há um relacionamento 1 para 1 entre a especificação
do reference type (no UDT) e o type generation (na tabela tipada)
Obs.: quando se escolhe user-generated para um UDT, é responsabilidade da aplicação escolher o valor armazenado na coluna self-referencing de cada linha inserida na tabela tipada
<reference type specification> <reference generation>
REF USING <tipo predefinido (built-in)> USER GENERATED
REF FROM <lista de atributos> DERIVED
REF IS SYSTEM GENERATED SYSTEM GENERATED
Capítulo 3: Modelo de Dados Objeto-Relacional 86
Exemplo: REF USING <tipo predefinido (built-in)>
CREATE TYPE person AS (name VARCHAR(20),address VARCHAR(20))REF USING VARCHAR(20);
CREATE TABLE people OF personREF IS oid USER GENERATED;
INSERT INTO people VALUES (‘01284567’,’John Bull’,’23 Coyote Run’);
Gerado pela aplicação
Capítulo 3: Modelo de Dados Objeto-Relacional 87
Exemplo: REF FROM <lista de atributos>
CREATE TYPE person AS (codigo NUMBER,name VARCHAR(20),address VARCHAR(20))REF FROM (codigo);
CREATE TABLE people OF person(CONSTRAINT people_codigo_pk PRIMARY KEY
(codigo),REF IS oid DERIVED);
Capítulo 3: Modelo de Dados Objeto-Relacional 88
Reference Types Permite que uma instância de um tipo
estruturado seja referenciada por outra Referencia o valor de uma row/instance
explicitamente representado numa linha Sintaxe:
<reference type> ::=
REF ( <UDT> ) [<scope clause>]
<scope clause> ::= SCOPE <table name>
Capítulo 3: Modelo de Dados Objeto-Relacional 89
Reference Types O SCOPE pode ter um check (espécie de integridade
referencial)
<reference scope check ::=
REFERENCES ARE [NOT] CHECKED
[ON DELETE <referential action>]
<referential action> ::=
[RESTRICT | SET NULL | SET DEFAULT | NO ACTION]
1 2 3 4
Capítulo 3: Modelo de Dados Objeto-Relacional 90
Reference Types
CREATE TYPE department_t AS OBJECT (deptno NUMBER,dname CHAR(30));
CREATE TYPE employee_t AS OBJECT(empid NUMBER,ename CHAR(31),dept REF department_t REFERENCES
ARE CHECKED ON DELETE SET NULL);
Capítulo 3: Modelo de Dados Objeto-Relacional 91
Consultas em Tabelas Tipadas
Recupere os DVD’s “Rose”:
SELECT title, runs
FROM short_dvds
WHERE title LIKE ‘%Rose%’;Short_Movies
Short_DVDs
Herança de Short_movies
Capítulo 3: Modelo de Dados Objeto-Relacional 92
Consultas em Tabelas Tipadas
Recupere filmes de curtíssima duração não em DVD:
SELECT title, runs
FROM ONLY (short_movies)
WHERE runs < 60;
desconsidera objetos armazenados em short_dvds
Short_Movies
Short_DVDs
is-a
Capítulo 3: Modelo de Dados Objeto-Relacional 93
Consultas em Tabelas Tipadas Usando REFCREATE TYPE movie AS (
title varchar(100),desc varchar(500),runs integer)INSTANTIABLE
NOT FINALREF IS SYSTEM GENERATEDINSTANCE METHOD rating () RETURNS DECIMAL(2,1);CREATE TYPE player AS (
player_name name,
role_played name,
film REF (movie))
INSTANTIABLE
NOT FINAL
REF IS SYSTEM GENERATED;
Capítulo 3: Modelo de Dados Objeto-Relacional 94
Consultas em Tabelas Tipadas Usando REF
CREATE TABLE movies OF movie
(REF IS movie_id SYSTEM GENERATED)
CREATE TABLE actors OF player
(REF IS actor_id SYSTEM GENERATED,
film WITH OPTIONS SCOPE movies)
Coluna
Capítulo 3: Modelo de Dados Objeto-Relacional 95
Consultas em Tabelas Tipadas Usando REF
SELECT film -> runs AS runs
FROM actors
WHERE player_name = ‘Peter Sellers’
AND role_played = ‘Merkin Muffley’;
RUNS
----------
10
20
Campo de Movies
Capítulo 3: Modelo de Dados Objeto-Relacional 96
Consultas em Tabelas Tipadas Usando REF
SELECT film -> rating () AS rating
FROM actors
WHERE player_name = ‘Arnold Schwarzenegger’
AND role_played = ‘Dutch’;
RATING
--------------
2.3
9.0
Método de Movies
Capítulo 3: Modelo de Dados Objeto-Relacional 97
Consultas em Tabelas Tipadas Usando REF-- Recupera a instância completa de movie em uma única coluna
SELECT DEREF (film)
FROM actors
WHERE player_name = ‘RODRIGO SANTORO’
AND role_played = ‘TONHO’
DEREF(TITLE, DESCR, RUNS)
--------------------------------------------------------
MOVIE('ABRIL DESPEDACADO', ‘bla bla', 10)
Capítulo 3: Modelo de Dados Objeto-Relacional 98
Inserção em Tabelas Tipadas Criando uma nova instância de dvd na tabela
short_dvds
INSERT INTO short_dvds (title, desc, runs, stock_number, rental_price)
VALUES (‘The Next Game’, ‘blá blá blá’, 48,
61992, 0.49)
movie_id IS SYSTEM GENERATED Esta linha não é inserida em short_movies, mas consultas a
short_movies que não usarem “ONLY” irão recuperá-la
Short_Movies
Short_DVDs
is-a
Capítulo 3: Modelo de Dados Objeto-Relacional 99
Remoção em Tabelas Tipadas
Removendo linhas (objetos) de todas as tabelas na hierarquia
DELETE FROM short_movies
WHERE title LIKE ‘%dead%’
apaga em short_movies e short_dvds
Short_Movies
Short_DVDs
is-a
Capítulo 3: Modelo de Dados Objeto-Relacional 100
Remoção em Tabelas Tipadas
Removendo linhas (objetos) apenas de uma tabela específica e de suas supertables
DELETE FROM ONLY short_movies
WHERE title = ‘The Lunch Lady’
apaga apenas em short_movies
Short_Movies
Short_DVDs
is-a
Capítulo 3: Modelo de Dados Objeto-Relacional 101
Atualização em Tabelas Tipadas
Atualizando uma linha numa hierarquia
UPDATE short_movies
SET title = ‘The Lunch Lady’
WHERE title = ‘The Cafeteria Lady’
atualiza também short_dvds
Short_Movies
Short_DVDs
is-a
Capítulo 3: Modelo de Dados Objeto-Relacional 102
Atualização em Tabelas Tipadas
Atualizando apenas numa tabela específica e suas supertables
UPDATE ONLY (short_movies)
SET title = ‘The Lunch Lady’
WHERE title = ‘The Cafeteria Lady’
não atualiza em short_dvds
Short_Movies
Short_DVDs
is-a
Capítulo 3: Modelo de Dados Objeto-Relacional 103
Typed Views
Também conhecidas como Referenceable Views ou Object Views
Os dados visíveis na view correspondem a linhas nas tabelas tipadas
Pode-se ter herança simples de views Surgem os conceitos de superview e subview
Capítulo 3: Modelo de Dados Objeto-Relacional 104
Typed Views<view definition> ::=
CREATE VIEW <view name>OF < UDT name>[<subview clause> ][<view element list> ]AS <query expression>[WITH [ <levels clause> CHECK OPTION]
<subview clause>::= UNDER <table name>
<view element list> ::= ( <view element> [{,<view element>} …]
<view element> ::= <self-referencing column specification>| <view column option>
<view column option> ::= <column name> WITH OPTIONS <scope clause>
Capítulo 3: Modelo de Dados Objeto-Relacional 105
Typed Views Exemplos
CREATE VIEW short_movies_with_long_titlesOF movieAS SELECT title, desc, runs
FROM ONLY (short_movies)WHERE char_length (title) > 75;
CREATE VIEW short_DVDs_with_long_titlesOF dvdUNDER short_movies_with_long_titlesAS SELECT title, desc, runs, stock_number, rental_price FROM ONLY (short_dvds)
WHERE char_length (title) > 75
movie
dvd
Short_Movies
Short_DVDs
is-a
Capítulo 3: Modelo de Dados Objeto-Relacional 106
Exercício – Hospital
Criar a seguinte estrutura* de tipos e subtipos:
PESSOA_TYPE
FUNCIONARIO_TYPEPACIENTE_TYPE
MEDICO_TYPE
Nome VARCHAR2(50)
Sexo CHAR(1)
DataNascimento DATE
TipoSanguineo VARCHAR2(3)Login VARCHAR2(8)Senha VARCHAR2(8)Cargo VARCHAR2(20)
CRM VARCHAR2(20)Especialidade VARCHAR2(20) Peso NUMBER
Altura NUMBERObservações VARCHAR2(50)
NOT INSTANTIABLE
Capítulo 3: Modelo de Dados Objeto-Relacional 107
Exercício – Hospital
Criar as seguintes tabelas tipadas FUNCIONARIO_TABLE PACIENTE_TABLE MEDICO_TABLE
Quais as colunas de cada uma das três tabelas tipadas?
Inserir uma linha em cada tabela tipada Consultar a coluna nome na tabela
medico_table. O que acontece?