Banco de Dados Objeto-Relacional

Modelo de Dados
Objeto-Relacional
Banco de Dados II – 2009.2
Bacharelado em Ciência da Computação
UFCG
Prof. Cláudio de Souza Baptista, Ph.D.
Conteúdo

Bancos de Dados Objeto-Relacional






Modelo de Dados Objeto-Relacional
Linguagem de Consultas Objeto Relacional
Visão geral dos conceitos de orientação a
objetos disponíveis no ORACLE 11g
Extensões para criar Objetos Complexos
O impacto da orientação a objetos em consultas
SQL
Integração OO-Relacional através de Visões de
Objeto
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
Banco de Dados Objeto-Relacional
Modelos e Linguagens

O modelo de dados OR é uma extensão do modelo
Relacional


As 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, TADs, métodos e funções como
predicados de busca em uma consulta
Modelo de Dados
Objeto-Relacional
Permite especificar e utilizar tipos abstratos
de dados(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

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 do que
as consultas feitas com junção

Herança é implementada organizando todos os
tipos em hierarquias

Utiliza os construtores set, list, multiset ou array
para organizar coleções de objetos
Benefícios do Modelo de
Dados Objeto-Relacional

Nova Funcionalidade


Desenvolvimento de aplicações
simplificado


Aumenta indefinidamente o conjunto de tipos
e funções fornecidas pelo SGBD
Reuso de código
Consistência

Permite a definição de padrões, código
reusável por todas as aplicações
Linguagem de Consultas para
Bancos de Dados Objeto-Relacional

O resultado de uma consulta ainda consiste
de tabelas


Um SGBD Objeto-Relacional 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
SQL:1999 (SQL-3)

É a base para muitos SGBDs OR (Oracle11g,
Informix Universal Server, IBM’s DB2 Universal
Database, entre outros)

Também está sendo chamada de SQL:1999 e tem
sido caracterizada como “SQL Orientada a Objetos”

SQL:1999 é muito mais do que SQL-92
incrementada com a tecnologia de OO. Envolve
características adicionais que podem ser
classificadas em:
 Relacionais: novos tipos de dados, novos
predicados
 Orientadas a Objetos:tipos de dados definidos
pelo usuário, definição de métodos, uso de
referências
O que tem no SQL:1999?

Multi-part standard — ISO/IEC 9075-n:1999





Part 1: SQL/Framework
Part 2: SQL/Foundation
Part 3: SQL/CLI
Part 4: SQL/PSM
Part 5: SQL/Bindings
Part 1: SQL/Framework




Definições comuns e conceitos
Estrutura do padrão multi-parte
Estrutura para conformidade básica
Possui cerca de 75 páginas
Part 2: SQL/Foundation
O cerne do padrão
 Omite host language bindings, dynamic SQL,
call interface, e questões semelhantes
 SQL tradicional e
 SQL orientando a objetos!
 Cerca de 1100 páginas

Part 3: CLI
Call-Level Interface
 Melhor implementação: ODBC
 Alinhado com SQL:1999 features e ODBC
3.0 features
 Cerca de 400 páginas

Part 4: SQL/PSM

PSM-96 especificou:




PSM-99 specifica:




functions & procedures
SQL-server modules
computational completeness
SQL-server modules
computational completeness
Análogo a PL/SQL, Transact-SQL, etc.
Cerca de 160 páginas
Part 5: SQL/Bindings





Embedded SQL (SQL embutido)
Dynamic SQL
“Direct Invocation”
Cerca de 250 páginas
Tendência: ser incorporado na Part 2!!!!
Novas partes do SQL



Part 7: SQL/Temporal
Part 9: SQL/MED (Management of External
Data)
Part 10: SQL/OLB (Object Linking Binding)



JDBC e SQLJ
SQL/OLAP
SQL/MM: Spatial, Text, Image
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 & comportamento
Encapsulamento: funções & métodos
Observers & mutators
Hierarquias de tipos (herança simples)
User-defined CAST, ordenação
Tabelas tipadas & tipos referência
Tipos de Dados Definidos pelo
Usuário


UDT – User Defined Types
Evolução dos tipos em SQL:




Sempre houve os tipos embutidos (built-in) como:
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 com
semântica limitada!
Houve algumas tentativas de DBMS vendors de
propor soluções para certos tipos de dados
específicos: texto, GIS, imagens e time series
UDT



Entretanto, o que se queria era uma solução
mais genérica que pudesse estender o
SGBD com tipos específicos:
Solução: UDT!!!!
Obs.: o termo User em UDT não quer dizer
usuário final!
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 de um 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)

Tipos definidos pelo usuário

Três tipos de UDT:



Distinct types
Structured types
Reference types
Distinct Types


Baseados em tipos embutidos
CREATE TYPE QI AS INT FINAL





Não pode misturar o tipo fonte e o tipo distinto e
m expressões
DECLARE VARIABLE X INTEGER;
DECLARE VARIABLE Y QI;
...X+Y --INVALID EXPR!
...X+CAST(Y AS INTEGER) --OK
Distinct Types
CREATE TYPE TAMBLUSA AS INT FINAL;
CREATE TYPE NUMSAPATO AS INT FINAL;
CREATE TABLE pessoas (
nome VARCHAR (30),
calca NUMSAPATO,
veste TAMBLUSA,
);
Distinct Types
Uso incorreto:
SELECT nome
FROM Pessoas
WHERE calca > veste;
Distinct Types
Uso correto:
SELECT nome
FROM Pessoas
WHERE CAST(calca TO INTEGER) >
CAST(veste TO INTEGER);
Distinct Types
Uso incorreto:
SELECT nome, calca + 20
FROM Pessoas
WHERE veste > 7;
Distinct Types
Uso correto:
SELECT nome, CAST (calca TO INTEGER)
+ 20
FROM Pessoas
WHERE CAST(Veste TO INTEGER) > 7;
Structured Types





Também conhecidos como “abstract data
types” (Tipos Abstratos de Dados)
Pode conter uma estrutura arbitrariamente
complexa
Análogo a struct na linguagem C
Stored data => state => attributes
Behavior => semantics => methods &
functions & procedures
Atributos


• “Stored data”
Cada atributo pode ser:



Tipo embutido, 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 — not overloadable
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
Procedures, Funções,
Métodos




Conceito genérico: rotina => procedure,
function, method — normalmente “stored”
Procedure: parâmetros de input & output
invocada usando o comando “CALL”
Função: apenas parâmetro de input (output
retornado como valor da função); invocado
usando a notação funcional
Métodos: caso especial de funções
Procedures, Funções,
Métodos

Procedures



Pode ser overloaded: mesmo nome, com
números diferentes de parâmetros
Tipos de dados dos argumentos não
utilizados para overloading
Em qualquer esquema, não ligado a um tipo
estruturado.
Procedures, Functions,
Methods

Functions:





Podem ser overloaded
Funções múltiplas com o mesmo nome, mesmo
número de parâmetros
Distintos pelos tipos de dados dos argumentos
Mas…usado apenas em tempo de compilação não
em tempo de execução
Em qualquer esquema, não ligado a um tipo
estruturado
Procedures, Functions,
Methods

Métodos






Podem ser overloaded
Fortemente acoplado a um único tipo estruturado
Deve estar no mesmo esquema da definição do tipo
First argument implicit, distinguished —
Tipo do argumento é associado ao tipo estruturado
Todos argumentos, exceto o primeiro usado o tipo
declarado para resolução, o primeiro argumento usa
o tipo mais específico do runtime
Notação: ponto (.) vs funcional




Notação de ponto: a.b.c
Notação funcional: c(b(a))
Funções DEVEM usar a notação funcional
Métodos DEVEM usar a notação de ponto


Observer: SELECT EMP.AGE FROM...
Mutator: SET EMP.AGE = 10
Diferenças 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 de residência
Qualquer esquema
Esquema do tipo
associado
Resolução de rotina
Compilation time
Runtime
Tipos de métodos

Há dois tipos de métodos:


Static methods: opera no tipo de dados
propriamente dito
Instance methods: opera na instância de um tipo.
Declaração de Métodos

Há dois lugares para definição de métodos


Declaração: os métodos são declarados (suas
assinaturas) na definição do tipo de dados
Implementação: a implementação dos métodos
ficam 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 como Java e C++
Exemplo: Método
CREATE TYPE Filme AS (
título varchar (100),
descricao varchar (500),
duracaoMinutos int) NOT FINAL
METHOD duracaoEmHoras() RETURNS
INTERVAL HOUR(2) TO MINUTE
Exemplo: Método
CREATE INSTANCE METHOD
duracaoEmHoras ( ) RETURNS INTERVAL
HOUR(2) TO MINUTE FOR Filme
RETURN CAST (CAST (SELF.duracao AS
INTERVAL MINUTE(4)) AS INTERVAL
HOUR(2) TO MINUTE)
Chamada a Métodos
Ex.:
CREATE TABLE tb_Filme (
cod int,
info filme,
aluguel DECIMAL(5,2))

Chamada a Método
Recupere a duração em horas e minutos do
filme ‘Ghost’
SELECT mt.info.duracaoEmHoras( )
FROM tb_Filme AS mt
WHERE mt.info.titulo = ‘Ghost’
Encapsulamento

Considere:


CREATE TYPE rational AS ( numerator
INTEGER, denominator INTEGER )
Funções implícitas:


CREATE FUNCTION numerator (rational)
RETURNS INTEGER
CREATE FUNCTION numerator (rational,
INTEGER) RETURNS rational
Construtores

Nenhum “new object” é criado, ao invés usase da seguinte forma:
DECLARE VARIABLE ratvar rational;
SET ratvar = rational(5,7);
INSERT INTO table1 (ratcol)
VALUES (rational(13,131));
Construtores

Construtor default gerado pelo sistema:
CREATE FUNCTION rational() RETURNS rational

Overloadable: qualquer número de
construtores definidos pelo usuário:


CREATE FUNCTION rational(numer,denom)
RETURNS rational
CREATE FUNCTION rational(denom)
RETURNS rational
Exemplo
// Ponto em coordenadas polares que pode retornar coordenadas cartesianas
CREATE TYPE ponto AS (
rho REAL,
theta REAL, … ) NOT FINAL
METHOD x_coord ( ) RETURNS REAL
METHOD y_coord ( ) RETURNS REAL
CREATE INSTANCE METHOD x_coord ( )
RETUNRS REAL FOR ponto
RETURN cos(SELF*theta)*SELF*rho
CREATE INSTANCE METHOD y_coord( ) RETURNS REAL FOR ponto
RETURN sin(SELF.theta)*SELF.rho
Sintaxe Tipo estruturado
CREATE TYPE name
[ UNDER supertype-name ]
AS ( attrib-name type,... )
[ [ NOT ] INSTANTIABLE ]
[ NOT ] FINAL
[ REF ref-options ]
[ method-spec,... ]
Sintaxe Tipo estruturado

REF ref-options =>
User-defined:
REF USING predefined-type

[ ref-cast-option ]
Derived:
REF ( attrib-name, ... )
 System-generated:
REF IS SYSTEM GENERATED

Sintaxe Tipo estruturado


method-spec =>
Método original:
[ INSTANCE | STATIC ] METHOD name ( paramname type,... ) RETURNS type

Método sobrescrito:
OVERRIDING original-method
Remoção de um UDT
DROP TYPE nome <CASCADE|RESCRICT>
Alterando um UDT
<alter type> ::= ALTER TYPE <nome> <action>
<action> ::=
ADD ATTRIBUTE <definition> |
DROP ATTRIBUTE <nome> RESTRICT |
ADD <method specification> |
DROP <method specification>
Herança de Tipos


Permite a
especialização dos
tipos existentes
“Subtype” &
“Supertype”
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
Herança

Obs.:



Pode-se definir UDT contendo atributos cujos
tipos são supertipos do tipo definido!
Não pode definir UDT cujos atributos contenham
o tipo do próprio tipo que está sendo definido
(recursivo)
Não pode definir UDT contendo atributos com
tipos de subtipos que ainda vão ser definidos!
Herança: Substitutability




Na herança usa-se o conceito de “most
specific type” numa hierarquia de tipos
O princípio da Substitutability diz respeito a
habilidade de prover uma instância de um
subtipo em qualquer local onde uma
instância de um supertipo é esperada.
Ex.: DECLARE p Pessoa;
SET p = NEW Empregado (…);
OBS.: o inverso não é válido!
Herança
CREATE TYPE emp UNDER person
( salary DECIMAL(6,2),
dept department )
METHOD give_raise(...)...,
OVERRIDING METHOD address(...)...;
Herança
Tipo declarado:
CREATE TYPE department (
dept_name CHARACTER(30),
manager employee, ...)
 Tipo mais específico

DECLARE VARIABLE x department;
SET x.manager = executive('Oliveira',...);
UDT: Valores e não instâncias!


Os tipos estruturados possuem valores e não
instâncias!!!!!
Razão: não há OID, que só ocorrerá no
repositório que são as tabelas tipadas (vistas
mais à frente!)
UDT: Valores e não instâncias!
Ex.:
BEGIN
DECLARE v1, v2 Filme;
SET v1 = NEW Filme (‘Shrek’, ‘blá blá blá’,
118);
SET v2 = v1;
SET v1 = v1.duracao(135);
END
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ó podem ser invocados através da keyword
NEW
Tais construtores devem ter SELF como
retorno.
User Defined
Constructors(Inicializadores)
Ex.:
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;

User Defined
Constructors(Inicializadores)
BEGIN
DECLARE f filme;
SET f =
NEW filme (‘Shrek’, ‘blá blá blá’, 135);
…
END;
User Defined
Constructors(Inicializadores)
Exemplo anterior usando o construtor definido
pelo sistema
BEGIN
DECLARE f filme;
SET f = NEW filme
SET f.titulo= ‘Shrek’;
SET f.duracao = 135;
END; -- decrição contém valor null
Inserindo dados
INSERT INTO tb_Filmes 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
Inserindo dados
Mesma inserção anterior usando uma variável
INSERT INTO tb_Filmes VALUES(
‘1203’, -- código do filme
f, -- instância de Filme
2.99); -- preço aluguel
Atualizando dados
1. Atualizando o valor do aluguel de um filme
particular
UPDATE tb_Filme
SET aluguel = 1.99
WHERE codigo = 1234;
Atualizando dados
2. Atualizando um filme sem todos os dados
UPDATE tb_Filme
SET info = NEW Filme (‘’, ‘’, 228)
WHERE codigo = 1234;
Atualizando dados
UPDATE tb_Filme
SET info = NEW Filme (info.titulo,
info.descricao, 228)
WHERE codigo = 1234;
Atualizando dados
Mesmo que o exemplo anterior, de forma mais
simples:
UPDATE tb_Filme
SET info = info.descricao(113)
WHERE codigo = 1234;
Atualizando dados
Mesmo que o exemplo anterior, de forma mais
simples:
UPDATE tb_Filme
SET info.descricao= 113
WHERE codigo = 1234;
Recuperando Dados
SELECT mt.info.descricao
INTO :variavel_host
FROM tb_filmes
WHERE mt.codigo = 1234;
User Defined CAST (1)
Ex.:
CREATE FUNCTION filme_to_char(meuFilme Filme)
RETURNS CHAR(610)
BEGIN
DECLARE n CHAR(100), d CHAR(500),
t CHAR(10), retorno CHAR(610);
SET n = meuFilme.titulo;
SET d = meuFilme.descricao;
SET t = CAST( meuFilme.duracao AS CHAR(10));
RETURN n || d || t;
END
User Defined CAST (2)
CREATE CAST (filme AS CHAR(610))
WITH filme_to_char;
SELECT CAST (filme AS CHAR(610))
INTO :variavel_Filme
FROM tb_Filmes
WHERE cod = 1234;
Comparação de UDT
Há a comparação default de UDT, que 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
Comparação de UDT

Comparação STATE


Você pode instruir o sistema para fazer comparações
baseadas em valores de atributos.
Usa expressão de igualdade, retornando um Boolean.
É a comparação default, campo a campo.
Ex.
CREATE ORDERING FOR Filme
EQUALS ONLY BY STATE;

Comparação de UDT

Comparação MAP


Mapear dois tipos estruturados para algum tipo embutido
do SQL, e dai fazer a comparação com o resultado deste
mapeamento.
Este mapeamento é feito com uma map function, que é
invocada automaticamente
Ex.
CREATE ORDERING FOR Filme
ORDER FULL BY MAP WITH
FUNCTION filme_mapping(filme);

Comparação de UDT
Comparação MAP (cont.)
CREATE FUNCTION filmeMapping( f filme)
RETURNS INTEGER
RETURN length(f.titulo)+f.duracao;

Comparação de UDT

Comparação Relative

Comparação usando o resultado retornado por
uma relative function, que retorna:




-1: o primeiro tipo é menor do que o segundo
0: os dois tipos são iguais
+1: o primeiro tipo tem valor maior do que o segundo
Pode-se especificar para o supertipo e os
subtipos herdam
Comparação de UDT

Comparação Relative
Ex.:
CREATE ORDERING FOR Filme
ORDER FULL BY RELATIVE WITH
FUNCTION filme_comp(filme, filme);

CREATE FUNCTION filme_comp (f1 filme, f2 filme)
RETURNS INTEGER
IF ( … ) – condição da aplicação
THEN RETURN -1
ELSEIF ( …)
THEN RETURN 0
ELSE RETURN 1
END IF;
Comparação de UDT

Remover um User Defined Comparison:
DROP ORDERING FOR <nome do tipo>
Type predicate
Usado para se saber o tipo de um
determinado variável.
 Ex.: determinar de o que está armazenado
na coluna pes é um empregado
pes IS OF (Empregado)

pes IS OF (ONLY PESSOA) – sem subtipo
Segurança de UDT




Em SQL:1999 não há public, protected, private
A restrição de acesso é via privilégios
Tais privilégios são dados com comando
GRANT e podem ter WITH GRANT OPTION
Privilégios:



USAGE: permite o tipo ser usado (colunas, rotinas)
UNDER: permite subtipos do tipo em questão serem
definidos
EXECUTE: permite usuários executarem um dado
método
Segurança de UDT

Exemplo:
GRANT EXECUTE ON INSTANCE METHOD
ano_lancamento( ) FOR filme TO PUBLIC;
Tabelas tipadas
Instâncias do tipo são linhas de uma tabela
 Comporta-se como objetos:
CREATE TABLE rationals OF rational REF
IS id_col ref-option
 Cria uma tabela base com uma coluna por
atributo, mais uma coluna “self-referencing”
 Esta coluna “self-referencing” contém em
cada linha um valor que identifica
univocamente a linha

Tabelas tipadas
Ex. 2:
CREATE TABLE movie (
title varchar (100).
desc
varchar(500),
runs integer)
NOT FINAL

Tabelas tipadas





Uma tabela tipada é uma tabela ordinária!
Pode-se inserir, remover, alterar e consultar as
linhas da tabela usando SQL
Pode-se definir constraints e triggers
Obs.: o uso de NEW visto anteriormente, não causa
uma instância ser armazenada numa tabela.
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!)
Tabelas Tipadas

Colunas “Self-referencing”:



Espécie de OID
Seu valor é único globalmente, ou seja, não
haverá duas linhas numa tabela com valor igual
da coluna “self-referencing”
As aplicações podem usar os valore 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
Hierarquias de Tabelas



Pode-se definir uma tabela com sendo subtabela de uma outra, chamada super-tabela
Só pode criar hierarquias de tabelas tipadas
Deve haver correspondência às hierarquias de
tipo



Supertable deve ser “of” supertype
Subtable deve ser “of” subtype
Ou seja os relacionamentos entre as subtabelas
e respectivos subtipos devem ser 1 para 1.
Hierarquias de Tabelas
Entretanto, a hierarquia completa de tabelas
não precisa ter um relacionamento com a
hierarquia completa de tipos
Movie
 Ex. Hierarchia
de tableas Movie

VHS Tape
Dolby Digital
Sound
DVD
DTS Sound
BlueRay
Hierarquias de Tabelas

Hierarquia de Tabelas para Apenas DVD
DVD
Dolby Digital
Sound
DTS Sound
Hierarquias de Tabelas

Hierarquia de Tabelas sem diferenciação de
som
Movie
VHS Tape
DVD
BlueRay
Hierarquias de Tabelas

Hierarquia de Tabelas Inválida!
Movie
VHS Tape
Dolby Digital
Sound
BlueRay
DTS Sound
Definindo Tabelas Tipadas
<table definition> :: =
CREATE TABLE <table name>
OF <UDT name>
[<subtable clause>]
[ <table element list> ]
<subtable clause> ::=
UNDER <supertable clause>
<supertable clause> ::= <table name>
Definindo Tabelas Tipadas
<table element list> ::=
( <table element> [ {, <table element>} … ] )
<table element> ::= <table constraint definition> |
<self-referencing column specification>
| < column options >
Definindo Tabelas Tipadas




OBS: Pode-se especificar o nome de uma tabela
de uma única super-tabela (herança simples)
O nome da tabela na cláusula da sub-tabela
dever ser um nome de uma tabela tipada!
Os nomes do atributos da tabela não podem ser
iguais aos herdados
Não se pode definir colunas arbitrárias (for a do
UDT) dentro de uma tabela tipada, apenas as que
compõem o UDT + self-referencing column
Definindo Tabelas Tipadas

Obs.:


NÃO é permitido especificar PRIMARY KEY
constraint num subtabela.
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)
Definindo Tabelas Tipadas
<self-referencing column specification> ::=
REF IS <column name> <reference
generation>
<reference generation> ::=
SYSTEM GENERATED
| USER GENERATED
| DERIVED
Definindo Tabelas Tipadas
<column option> ::=
<column name> WITH OPTIONS <column
option list>
<column option list> ::=
[<scope clause>]
[<default clause>]
[<column constraint definition> …]
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.
Definindo Tabelas Tipadas
CREATE TYPE movie AS (
title varchar(100),
desc
varchar(500),
runs
integer)
INSTANTIABLE
NOT FINAL
REF IS SYSTEM GENERATED
Definindo Tabelas Tipadas
CREATE TYPE dvd UNDER movie AS (
stock_number INTEGER,
renta
varchar(500),
extra_features feature_desc)
INSTANTIABLE
NOT FINAL
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) )
Definindo Tabelas Tipadas
CREATE TABLE short_dvds OF dvd
UNDER short_movies
( rental_price WITH OPTIONS
CONSTRAINT short_dvds_check_price
CHECK (price < 1.99) )
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)
<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
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 types




Permite uma variável referenciar uma outra
Apenas instâncias de tipos estruturados podem ser
referenciados
Referencia um valor de uma row/instance
explicitamente representado numa linha
Sintaxe:
<reference type> ::=
REF ( <UDT> ) [<scope clause>]
<scope clause> ::= SCOPE <table name>
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
Default: NO ACTION
Consultas em Tabelas Tipadas
Recupere os DVD’s “Rose”:
SELECT title, runs
FROM short_dvds
WHERE title LIKE ‘%Rose%’

Consultas em Tabelas Tipadas
Recupere filmes de curtíssima duração:
SELECT title, runs
FROM short_movies
WHERE runs < 60

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

Consultas em Tabelas Tipadas
Usando REFs
CREATE TYPE movie AS (
title varchar(100),
desc varchar(500),
runs integer)
INSTANTIABLE
NOT FINAL
REF IS SYSTEM GENERATED
INSTANCE METHOD rating () RETURNS
DECIMAL(2,1)
Consultas em Tabelas Tipadas
Usando REFs
CREATE TYPE player AS (
player_name name,
role_played name,
film REF (movie) )
INSTANTIABLE
NOT FINAL
REF IS SYSTEM GENERATED
Consultas em Tabelas Tipadas
Usando REFs
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)
Consultas em Tabelas Tipadas
Usando REFs
SELECT film -> runs
FROM actors
WHERE player_name = ‘Peter Sellers’
AND role_played = ‘Merkin Muffley’
Consultas em Tabelas Tipadas
Usando REFs
SELECT film -> ratings ( )
FROM actors
WHERE player_name = ‘Peter Sellers’
AND role_played = ‘Merkin Muffley’
Consultas em Tabelas Tipadas
Usando REFs
SELECT DEREF (film)
FROM actors
WHERE player_name = ‘Peter Sellers’
AND role_played = ‘Merkin Muffley’
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)

Remoção em Tabelas Tipadas

Removendo de todas as tabelas na
hierarquia
DELETE from short_movies
WHERE title LIKE ‘%dead%’
Remoção em Tabelas Tipadas

Removendo apenas de uma tabela
específica e suas supertables
DELETE from ONLY short_movies
WHERE title = ‘The Lunch Lady’
Atualização em Tabelas Tipadas

Atualizando uma linha numa hierarquia
UPDATE short_movies
SET title = ‘The Lunch Lady’
WHERE title = ‘The Cafeteria Lady’
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’
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: superview &
subview
Typed Views
<view definition> ::=
CREATE VIEW <table 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>
Typed Views

Obs.:

WITH CHECK OPTION

Usada para Views que podem ser atualizadas: update, delete e insert.
Exemplo:
CREATE VIEW cheap_movies AS
SELECT title, price
FROM movies
WHERE price < 1
WITH CHECK OPTION;
Assim, o seguinte Update seria rejeitado:
UPDATE cheap_movies
SET price = 15
WHERE title = ‘The Wedding Singer’;
Typed Views
Exemplo:
CREATE VIEW short_movies_with_long_titles
OF movie (REF IS movie_id DERIVED)
AS SELECT title, desc, runs
FROM ONLY (short_movies)
WHERE char_length (title) > 75

CREATE VIEW short_DVDs_with_long_titles
OF dvd
UNDER short_movies_with_long_titles
AS SELECT title, desc, runs, stock_number, rental_price
FROM ONLY (short_dvds)
WHERE char_length(title) > 75
Tabelas Tipadas e Privilégios





Privilégio USAGE: é requerido para se poder usar
um tipo numa aplicação ou BD.
Se está invocando um método num valor de TAD
que é armazenado numa coluna de um tipo de uma
tabela ordinária (não tipada), é necessário ter o
privilégio SELECT
Se o método é um mutator, deve também ter o
privilégio UPDATE
Também é necessário ter o privilégio EXECUTE em
todos os métodos invocados
Privilégio UNDER: para permitir especificar subtabelas, sub-tipos ou sub-views.
Rotinas SQL

Falamos em aula anterior que existem três
tipos de rotinas em SQL:



Procedures: chamadas através de CALL e com
parâmetroe IN, OUT e INOUT; ligadas a um
esquema
Funções: chamadas diretamente através de
notação funcional f(a), com parâmetros de IN e
retorno, e ligadas a um esquema
Métodos: chamados diretamente através de
notação de ponto f.a, com parâmetros de entrada
e retorno, ligados a um UDT
Rotinas SQL




Uma rotina SQL é uma rotina que é invocada
através de um comando SQL.
Pode ser escrita em SQL ou em outra
linguagem, neste caso chamado de external
routine
O padrão SQL:1999 provê especificação
para 9 LP: Java, C, Ada, Cobol, Fortran, M
(antiga Mumps), Pascal e PL/I.
Uma rotina externa é armazenada num
arquivo fora do SGBD.
Rotinas SQL

Razões para considerar o uso de rotinas externas:




Pode-se já ter um número de rotinas escritas em alguma
LP. Por exemplo, um pacote estatístico
Poucos SGBD implementam SQL/PSM, usando, ao invés,
uma linguagem proprietária, por exemplo, Oracle PL-SQL,
MS Transact-SQL que não são interoperáveis
Também pode-se requerer uma certa funcionalidade que é
computacionalmente intensiva,. Por exemplo, análise de
séries temporais diárias, semanais, mensais, e semestrais
com relatórios de vendas de DVDs.
Rotinas externas são geralmente capazes de acessar
serviços providos de baixo nível do SO, como sistemas de
arquivo, gerência de memória, etc.
Rotinas SQL

Desvantagens de Rotinas Externas:



Impedance mismatch (menor em Java)
A criação de novas sessões para execução de
rotinas externas pode ser custoso, impactando no
desempenho (context switching overhead)
Treinamento em dois ambientes: SQL e outra LP
Invocação de Rotinas

Envolve um número de passos:


Análise sintática
Execução runtime


Overloading
Overrriding
Rotinas SQL

Potencial das Rotinas SQL:

Triggers podem invocar procedimentos que:



Enviam email
Imprimem documentos
Ativa um equipamento robótico para recuperar um
determinado dado
Invocação de Procedures




São chamadas através do comando SQL:
CALL
Parâmetros são passados e retornados
Podem ser overloaded, mas não overriden
Estranhamente não é permitido, por
exemplo: transmit (integer) e transmit (real),
Invocação de Procedures

Algoritmo:





Todas as rotinas do nome invocado são identificadas.
Todas as rotinas contidas em esquemas que não são
parte do corrente SQL-path são eliminadas
Todas as rotinas restantes que são funções ou métodos
são eliminados
Os procedures restantes para os quais o invocador não
possui o privilégio EXECUTE são eliminados
Todos procedimentos cujos número de parâmetros não é
igual ao número de argumentos na chamada CALL são
eliminados
Se existem duas ou mais procedures restantes, então o
algoritmo seleciona aquela que está contida no esquema
que apareçe mais cedo no SQL-path.
Ex. de Criação e invocação de
procedure
CREATE PROCEDURE sendmail (
IN to varchar(32),
IN subj varchar(100),
IN prio
smallint,
OUT status integer)
LANGUAGE C
PARAMETER STYLE GENERAL
SPECIFIC sendmail_c
DETERMINISTIC
NO SQL
DYNAMIC RESULT SET 0
EXTERNAL NAME ‘usr/musicshop/bin/mailPackage/send1’
EXTERNAL SECURITY DEFINER
Ex. De Criação e invocação de
procedure
CREATE PROCEDURE sendmail (
IN to varchar(32),
IN subj varchar(100),
OUT status integer)
LANGUAGE C
EXTERNAL NAME
‘usr/musicshop/bin/mailPackage/send2’
EXTERNAL SECURITY DEFINER
Ex. De Criação e invocação de
procedure
CALL sendmail (‘[email protected]’,
‘Exciting sale for web customers only!’, 1,
:mailStatus);
CALL sendmail (‘[email protected]’,
‘Exciting sale for web customers only!’,
:mailStatus);
Invocação de Função


São um pouco mais complexas do que
invocações de procedures
Quando existem várias funções com o
mesmo nome, SQL usa vários critérios para
escolher a função que irá escolher para ser
executada.
Invocação de Função

Algoritmo





Todas as rotinas com o nome são identificadas. As que
estiverem em esquemas que não são parte do corrente SQLpath são eliminadas
Todas as rotinas remanescentes que são procedures ou
métodos são eliminados
As funções restantes, cujo número de parâmetros não é igual ao
número de argumentos na função invocada são eliminadas
Para cada função restante, o tipo de dados de cada parâmetro é
checado contra o tipo do respectivo argumento; caso não casem
a função é eliminada
Se existem duas ou mais funções restantes para executar, então
o algoritmo seleciona aquela que aparece mais cedo no SQLpath
Ex. de Função
CREATE FUNCTION ratio (
IN title_param VARCHAR(100),
IN length_param INTEGER)
RETURNS REAL
LANGUAGE SQL;
Exemplo de invocação de
função
SELECT mt.info.titulo, mt.info.length,
ratio (mt.info.titulo, mt.info.length)
FROM TB_Filme as mt
WHERE mt.info.titulo = ‘TITANIC’
OBS.: notem o uso da notação funcional na
chamada da função ratio( ) e da notação de
ponto no observer titulo( )
Invocação de Método de
Instância

Chamada a métodos difere de chamada à
funções:


Métodos podem ser overloadable e overriden
O SQL-path não é usado para resolução de
método
Invocação de Método de
Instância
Exemplo
SELECT mt.info.duraçãoHoras( )
FROM TB_Filme mt
WHERE mt.info.titulo = ‘The Matrix’

Exemplo de overriding
CREATE TYPE dvd UNDER movie AS (
stock_number integer,
rental_price decimal(5,2),
extra_features feature)
INSTANTIABLE
NOT FINAL;
CREATE TYPE dolby_dvd UNDER dvd AS (
alt_language BOOLEAN)
INSTANTIABLE
NOT FINAL;
OVERRIDING METHOD length_interval ( )
RETURNS INTERVAL HOUR(2) TO MINUTE;
CREATE INSTANCE METHOD length_interval( )
RETURNS INTERVAL HOUR(2) TO MINUTE FOR dolby_dvd
RETURN CAST (CAST (SELF.runs AS INTERVAL MINUTE(4) ) +
INTERVAL ‘10’ MINUTE(4)
AS INTERVAL HOUR(2) TO MINUTE);
Exemplo de overriding
DECLARE dvd1, dvd2 dvd;
DECLARE len1, len2, len3, len4 INTERVAL HOUR TO MINUTE;
DECLARE lang1, lang3 BOOLEAN;
SET dvd1 = NEW dvd (‘Seven’, ‘… a serial killer …’, 123, 299, 1.89, ‘bla’);
SET dvd2 = NEW dolby_dvd (‘Seven’, ‘… a serial killer …’, 123, 299, 1.89, ‘bla’, TRUE);
SET len1= dvd1.lenght_interval; -- invoca método de dvd
SET len2 = dvd2.lenght_interval; -- invoca método overriden de dolby_dvd
SET len3 = (dvd1 AS dvd).lenght_interval; -- igual a len1
SET len4 = (dvd2 as dvd).lenght_interval; -- invoca método de dvd
SET lang1 = dvd1.alt_language; -- erro
SET lang2 = (dvd1 AS dolby_dvd).alt_language; -- erro dvd1 ÍS NOT dolby_dvd