Modelo de Dados ObjetoRelacional Banco de Dados II

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 l Bancos de Dados Objeto-Relacional l l l Modelo de Dados Objeto-Relacional Linguagem de Consultas Objeto Relacional Visão geral dos conceitos de orientação a objetos disponíveis no ORACLE 11 g 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 l SGBDs Objeto-Relacional combinam os benefícios do modelo Relacional com a capacidade de modelagem do modelo OO l Fornecem suporte para consultas complexas sobre dados complexos l 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 l O modelo de dados OR é uma extensão do modelo Relacional l l 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 l 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 l 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 l Nova Funcionalidade l l Desenvolvimento de aplicações simplificado l l Aumenta indefinidamente o conjunto de tipos e funções fornecidas pelo SGBD Reuso de código Consistência l 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 l O resultado de uma consulta ainda consiste de tabelas l l 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) l É a base para muitos SGBDs OR (Oracle 11 g, Informix Universal Server, IBM’s DB 2 Universal Database, entre outros) l Também está sendo chamada de SQL: 1999 e tem sido caracterizada como “SQL Orientada a Objetos” l SQL: 1999 é muito mais do que SQL-92 incrementada com a tecnologia de OO. Envolve características adicionais que podem ser classificadas em: l Relacionais: novos tipos de dados, novos predicados l 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? l Multi-part standard — ISO/IEC 9075 -n: 1999 l l l Part 1: SQL/Framework Part 2: SQL/Foundation Part 3: SQL/CLI Part 4: SQL/PSM Part 5: SQL/Bindings

Part 1: SQL/Framework l l 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 l Omite host language bindings, dynamic SQL, call interface, e questões semelhantes l SQL tradicional e l SQL orientando a objetos! l Cerca de 1100 páginas l

Part 3: CLI Call-Level Interface l Melhor implementação: ODBC l Alinhado com SQL: 1999 features e ODBC 3. 0 features l Cerca de 400 páginas l

Part 4: SQL/PSM l PSM-96 especificou: l l PSM-99 specifica: l l functions & procedures SQL-server modules computational completeness Análogo a PL/SQL, Transact-SQL, etc. Cerca de 160 páginas

Part 5: SQL/Bindings l l l Embedded SQL (SQL embutido) Dynamic SQL “Direct Invocation” Cerca de 250 páginas Tendência: ser incorporado na Part 2!!!!

Novas partes do SQL l l l Part 7: SQL/Temporal Part 9: SQL/MED (Management of External Data) Part 10: SQL/OLB (Object Linking Binding) l l l JDBC e SQLJ SQL/OLAP SQL/MM: Spatial, Text, Image

Part 2: SQL/Foundation l Estamos interessados neste curso na parte do padrão que trata de Orientação a Objetos: l l l l 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 l l UDT – User Defined Types Evolução dos tipos em SQL: l l 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 l l l 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) l

Tipos definidos pelo usuário l Três tipos de UDT: l l l Distinct types Structured types Reference types

Distinct Types l l Baseados em tipos embutidos CREATE TYPE QI AS INT FINAL l l l 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 l l l 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 l l • “Stored data” Cada atributo pode ser: l l l 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 l l l 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 l l 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 l Procedures l l l Pode ser overloaded: mesmo nome, com números diferentes de parâmetros Tipos de dados argumentos não utilizados para overloading Em qualquer esquema, não ligado a um tipo estruturado.

Procedures, Functions, Methods l Functions: l l l Podem ser overloaded Funções múltiplas com o mesmo nome, mesmo número de parâmetros Distintos pelos tipos de dados 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 l Métodos l l l 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 l l 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 l l 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 l Há dois tipos de métodos: l l Static methods: opera no tipo de dados propriamente dito Instance methods: opera na instância de um tipo.

Declaração de Métodos l Há dois lugares para definição de métodos l l 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), duracao. Minutos int) NOT FINAL METHOD duracao. Em. Horas() RETURNS INTERVAL HOUR(2) TO MINUTE

Exemplo: Método CREATE INSTANCE METHOD duracao. Em. Horas ( ) RETURNS INTERVAL HOUR(2) TO MINUTE FOR Filme RETURN 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)) l

Chamada a Método Recupere a duração em horas e minutos do filme ‘Ghost’ SELECT mt. info. duracao. Em. Horas( ) FROM tb_Filme AS mt WHERE mt. info. titulo = ‘Ghost’

Encapsulamento l Considere: l l CREATE TYPE rational AS ( numerator INTEGER, denominator INTEGER ) Funções implícitas: l l CREATE FUNCTION numerator (rational) RETURNS INTEGER CREATE FUNCTION numerator (rational, INTEGER) RETURNS rational

Construtores l Nenhum “new object” é criado, ao invés usase da seguinte forma: DECLARE VARIABLE ratvar rational; SET ratvar = rational(5, 7); INSERT INTO table 1 (ratcol) VALUES (rational(13, 131));

Construtores l Construtor default gerado pelo sistema: CREATE FUNCTION rational() RETURNS rational l Overloadable: qualquer número de construtores definidos pelo usuário: l l 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 l REF ref-options => User-defined: REF USING predefined-type l [ ref-cast-option ] Derived: REF ( attrib-name, . . . ) l System-generated: REF IS SYSTEM GENERATED l

Sintaxe Tipo estruturado l l method-spec => Método original: [ INSTANCE | STATIC ] METHOD name ( paramname type, . . . ) RETURNS type l 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> : : = ADD ATTRIBUTE <definition> | DROP ATTRIBUTE <nome> RESTRICT | ADD <method specification> | DROP <method specification>

Herança de Tipos l l Permite a especialização dos tipos existentes “Subtype” & “Supertype”

Herança l l l Subtype herda tudo do supertype SQL: 1999 dá suporte apenas a herança simples Na definição de subtype: l l l Novos atributos podem ser adicionados Novos métodos podem ser adicionados Métodos podem ser sobrescritos

Herança l Obs. : l l l 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 l l 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, . . . ) l Tipo mais específico l DECLARE VARIABLE x department; SET x. manager = executive('Oliveira', . . . );

UDT: Valores e não instâncias! l l 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 v 1, v 2 Filme; SET v 1 = NEW Filme (‘Shrek’, ‘blá blá’, 118); SET v 2 = v 1; SET v 1 = v 1. duracao(135); END

User Defined Constructors(Inicializadores) l l 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; l

User Defined Constructors(Inicializadores) BEGIN DECLARE f filme; SET f = NEW filme (‘Shrek’, ‘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á’, 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(meu. Filme) RETURNS CHAR(610) BEGIN DECLARE n CHAR(100), d CHAR(500), t CHAR(10), retorno CHAR(610); SET n = meu. Filme. titulo; SET d = meu. Filme. descricao; SET t = CAST( meu. Filme. 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 l Pode-se também criar uma semântica de comparação para o UDT l Usa-se o comando: CREATE ORDERING FOR <nome do tipo> EQUALS ONLY BY <categoria> | ORDER FULL by <categoria> l <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 l Comparação STATE l l 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; l

Comparação de UDT l Comparação MAP l l 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); l

Comparação de UDT Comparação MAP (cont. ) CREATE FUNCTION filme. Mapping( f filme) RETURNS INTEGER RETURN length(f. titulo)+f. duracao; l

Comparação de UDT l Comparação Relative l Comparação usando o resultado retornado por uma relative function, que retorna: l l -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 l Comparação Relative Ex. : CREATE ORDERING FOR Filme ORDER FULL BY RELATIVE WITH FUNCTION filme_comp(filme, filme); l CREATE FUNCTION filme_comp (f 1 filme, f 2 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 l Remover um User Defined Comparison: DROP ORDERING FOR <nome do tipo>

Type predicate Usado para se saber o tipo de um determinado variável. l Ex. : determinar de o que está armazenado na coluna pes é um empregado pes IS OF (Empregado) l pes IS OF (ONLY PESSOA) – sem subtipo

Segurança de UDT l l Em SQL: 1999 não há public, protected, private A restrição de acesso é via privilégios Tais privilégios são dados comando GRANT e podem ter WITH GRANT OPTION Privilégios: l l l 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 l Exemplo: GRANT EXECUTE ON INSTANCE METHOD ano_lancamento( ) FOR filme TO PUBLIC;

Tabelas tipadas Instâncias do tipo são linhas de uma tabela l Comporta-se como objetos: CREATE TABLE rationals OF rational REF IS id_col ref-option l Cria uma tabela base com uma coluna por atributo, mais uma coluna “self-referencing” l Esta coluna “self-referencing” contém em cada linha um valor que identifica univocamente a linha l

Tabelas tipadas Ex. 2: CREATE TABLE movie ( title varchar (100). desc varchar(500), runs integer) NOT FINAL l

Tabelas tipadas l l l 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? l l 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 l Colunas “Self-referencing”: l l l 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. l Por isso, alguns chamam typed tables de referenced tables

Hierarquias de Tabelas l l l 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 l l l 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 l Ex. Hierarchia de tableas Movie l VHS Tape Dolby Digital Sound DVD DTS Sound Blue. Ray

Hierarquias de Tabelas l Hierarquia de Tabelas para Apenas DVD Dolby Digital Sound DTS Sound

Hierarquias de Tabelas l Hierarquia de Tabelas sem diferenciação de som Movie VHS Tape DVD Blue. Ray

Hierarquias de Tabelas l Hierarquia de Tabelas Inválida! Movie VHS Tape Dolby Digital Sound Blue. Ray DTS Sound

Definindo Tabelas Tipadas <table definition> : : = CREATE TABLE <table name> OF <UDT name> [<subtable clause>] [ <table element list> ] <subtable clause> : : = UNDER <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 l l 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 l Obs. : l l 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> : : = SYSTEM GENERATED | USER GENERATED | DERIVED

Definindo Tabelas Tipadas <column option> : : = <column name> WITH OPTIONS <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 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 l l 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>] l <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%’ l

Consultas em Tabelas Tipadas Recupere filmes de curtíssima duração: SELECT title, runs FROM short_movies WHERE runs < 60 l

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 l

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, 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á’, 48, 61992, 0. 49) l

Remoção em Tabelas Tipadas l Removendo de todas as tabelas na hierarquia DELETE from short_movies WHERE title LIKE ‘%dead%’

Remoção em Tabelas Tipadas l 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 l Atualizando uma linha numa hierarquia UPDATE short_movies SET title = ‘The Lunch Lady’ WHERE title = ‘The Cafeteria Lady’

Atualização em Tabelas Tipadas l 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 l l l 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> : : = <column name> WITH OPTIONS <scope clause>

Typed Views l Obs. : l WITH CHECK OPTION l 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 l 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 l l l 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 l Falamos em aula anterior que existem três tipos de rotinas em SQL: l l l 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 l l 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 l Razões para considerar o uso de rotinas externas: l l 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 l Desvantagens de Rotinas Externas: l l l 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 l Envolve um número de passos: l l Análise sintática Execução runtime l l Overloading Overrriding

Rotinas SQL l Potencial das Rotinas SQL: l Triggers podem invocar procedimentos que: l l l Enviam email Imprimem documentos Ativa um equipamento robótico para recuperar um determinado dado

Invocação de Procedures l l 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 l Algoritmo: l l l 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/mail. Package/send 1’ 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/mail. Package/send 2’ EXTERNAL SECURITY DEFINER

Ex. De Criação e invocação de procedure CALL sendmail (‘customer@muvid. com’, ‘Exciting sale for web customers only!’, 1, : mail. Status); CALL sendmail (‘customer@muvid. com’, ‘Exciting sale for web customers only!’, : mail. Status);

Invocação de Função l l 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 l Algoritmo l l l 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 l Chamada a métodos difere de chamada à funções: l l 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ção. Horas( ) FROM TB_Filme mt WHERE mt. info. titulo = ‘The Matrix’ l

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 (SELF. runs AS INTERVAL MINUTE(4) ) + INTERVAL ‘ 10’ MINUTE(4) AS INTERVAL HOUR(2) TO MINUTE);

Exemplo de overriding DECLARE dvd 1, dvd 2 dvd; DECLARE len 1, len 2, len 3, len 4 INTERVAL HOUR TO MINUTE; DECLARE lang 1, lang 3 BOOLEAN; SET dvd 1 = NEW dvd (‘Seven’, ‘… a serial killer …’, 123, 299, 1. 89, ‘bla’); SET dvd 2 = NEW dolby_dvd (‘Seven’, ‘… a serial killer …’, 123, 299, 1. 89, ‘bla’, TRUE); SET len 1= dvd 1. lenght_interval; -- invoca método de dvd SET len 2 = dvd 2. lenght_interval; -- invoca método overriden de dolby_dvd SET len 3 = (dvd 1 AS dvd). lenght_interval; -- igual a len 1 SET len 4 = (dvd 2 as dvd). lenght_interval; -- invoca método de dvd SET lang 1 = dvd 1. alt_language; -- erro SET lang 2 = (dvd 1 AS dolby_dvd). alt_language; -- erro dvd 1 ÍS NOT dolby_dvd