Download Bases de Datos Obxecto-Relacionais Introducción
Transcript
Bases de Datos Obxecto-Relacionais Miguel Rodríguez Penabad Laboratorio de Bases de Datos Universidade da Coruña Introducción Conocementos previos I Sobre Programación/Deseño Orientado a Obxectos I Especializacións, Herencia, ... Obxectivos - Contidos do tema I Ver os motivos da aparición da Orientación a Obxectos en BDs. I Conocer as extensións de tipos en BDOR I Tipos complexos, coleccións, referencias a obxectos I Entender os mecanismos de herencia en BDOR I Ver a evolución de SQL (DDL, DML) I Discusión: ventaxas e inconvenientes do modelo obxecto-relacional Bases de Datos Obxecto-Relacionais 2 / 35 Orientación a Obxectos en BD - Motivación Situación actual I Existen BDs e aplicacións tradicionais Ex: Aplicacións xestión (nóminas, etc.) I Rexistros cortos, simples, de lonxitude xa I Primeira forma normal I Aparecen novas necesidades de información Melloras no hardware, novas aplicacións e tipos de información I I I I I CAD/CAM (Deseño/Manufactura) Ferramentas CASE (Enxeñaría de Software) OIS (Oce Information System) Sistemas Multimedia GIS (Sistemas de Información Xeográca) Bases de Datos Obxecto-Relacionais 3 / 35 Orientación a Obxectos en BD - Motivación Problemas e solucións I Orientación a Obxectos no desenvolvemento de software I Éxito da Programación OO, Deseño OO, UML, Proceso Unicado I As aplicacións usan obxectos Non hai 1FN, hai referencias/punteiros a obxectos e coleccións I Problema: Desaxuste de impedancias ó tratar de almacenar obxectos en BD relacionais Necesidade de tranducción, aplanar obxectos, ... I Problemas en consultas I Formulación Ex: Imaxes con círculos I Optimización Ex: calcular polígonos que se solapan cun polígono concreto Bases de Datos Obxecto-Relacionais 4 / 35 Orientación a Obxectos en BD - Plantexamentos (i) Linguaxe de programación Orientado a Obxectos Base de Datos Relacional Base de Datos Persistencia Orientada a Obxectos Orientación Base de Datos a Obxectos Obxecto-Relacional Bases de Datos Obxecto-Relacionais 5 / 35 Orientación a Obxectos en BD - Plantexamentos (ii) BD Orientada a Obxectos Linguaxe de programación Orientado a Obxectos Persistencia Base de Datos Orientada a Obxectos I Proposto por ODMG (Object Data Management Group) I Son Linguaxes de Programación Persistentes (Estándar en 2000) I ODL (Object Denition Language) I OML (Object Manipulation Language) I OQL (Object Query Language) I Evolución: OMG (Object Management Group) I ODMG Java binding evoluciona a JDO (Java Data Objects, estándar sen nalizar) I Máis que unha BD é un conxunto de ferramentas para dotar de persistencia ás clases dunha aplicación I Impacto relativamente baixo no mercado Bases de Datos Obxecto-Relacionais 6 / 35 Orientación a Obxectos en BD - Plantexamentos (iii) BD Obxecto-Relacional Base de Datos Relacional Orientación a Obxectos Base de Datos Obxecto-Relacional I Conservar un modelo compatible co Modelo Relacional I Fundamento teórico moi sólido I Máis de 35 anos de investigación I Exténdese para incorporar características desexables da OO I Adoptado por numerosos SXBD Oracle, IBM DB2, Informix Dynamic Server, PostgreSQL I Extensión do SQL para incorporar as novas características I Estándar SQL:1999, revisado en SQL:2003 I Extensión de tipos, obxectos, herencia, ... I Incorpora outras novidades I Consultas recursivas, SQL/XML, ... Bases de Datos Obxecto-Relacionais 7 / 35 Bases de Datos Obxecto-Relacionais I Evolución do SQL I Tipos de datos I Predenidos I Constructores de tipo I Tipos denidos polo usuario I Referencias I Herencia I Táboas de Obxectos I Creación I Consultas I Vistas Bases de Datos Obxecto-Relacionais 8 / 35 Evolución do SQL I SEQUEL (Structured English QUEry Language). Desarrollado en IBM para SYSTEM R. I SQL-86 ou SQL-1. ANSI e ISO en 1986. Revisado en 1989. I SQL-92 ou SQL-2. 1992. I SQL:1999 (inicialmente chamado SQL3 ou SQL-99) Introduce tipos de datos complexos, procedementos almacenados, triggers, ... I SQL:2003. Revisión do SQL:1999, maioritariamente correccións e SQL/XML. Bases de Datos Obxecto-Relacionais 9 / 35 SQL:2003 - Tipos de datos predenidos I Numéricos exactos SMALLINT, INTEGER, BIGINT, NUMERIC[(p[,s])], DECIMAL[(p[,s])] I Numéricos aproximados REAL, DOUBLE PRECISION, FLOAT[(P)] I Cadeas de caracteres CHAR[ACTER][(n)], CHAR[ACTER] VARYING(n) ou VARCHAR(n), CHARACTER LARGE OBJECT[(tamaño)] ou CLOB[(tamaño)] Opcionalmente NATIONAL para internazionalización I Binarios (En SQL:1999 había BIT, BIT VARYING(n), pero foron eliminados en SQL:2003) BINARY LARGE OBJECT[(tamaño)] ou BLOB[(tamaño)] I Datas e horas DATE, TIME [WITH TIME ZONE], TIMESTAMP [WITH TIME ZONE] Tamén inclúe tipos para INTERVALos de tempo I Outros Lóxicos: XML BOOLEAN, Bases de Datos Obxecto-Relacionais con valores TRUE, FALSE, UNKNOWN 10 / 35 SQL:2003 - Tipos de datos predenidos Exemplos 1 2 3 4 5 6 7 8 CREATE TABLE Libro( id CHAR(5), titulo NATIONAL CHARACTER VARYING(100), resumen NCLOB(25K), imaxe BLOB(2M), prezo NUMERIC(6,2) ... ); 9 10 11 12 13 14 15 16 CREATE TABLE ProgramaTV( ... edade_min SMALLINT, hora_ini TIMESTAMP WITH TIME ZONE, telebasura BOOLEAN, ... ); Bases de Datos Obxecto-Relacionais 11 / 35 SQL:2003 - Constructores de Tipos I Filas (rows) I Coleccións I Tipos distinct I Tipos complexos I Referencias CREATE TABLE Empregado( NSS CHAR(9) NOT NULL PRIMARY KEY, Nome VARCHAR(59) NOT NULL, Enderezo ROW( -- Fila Rua VARCHAR(40), Numero SMALLINT, Poboacion VARCHAR(40) ), Salario EUROS, --Distinct Telefono VARCHAR(20) ARRAY[2], --Colección Experiencia ROW( Posto VARCHAR(30), Anos SMALLINT ) MULTISET --Colección de filas ); Bases de Datos Obxecto-Relacionais 12 / 35 SQL:2003 - Constructores de tipos Tipos Fila I Constructor: ROW(<def. campo1>, <def. campo2>, ...) Enderezo ROW( Rua VARCHAR(40), Numero SMALLINT, Poboacion VARCHAR(40)) I Acceso ó tipo ou ós compoñentes UPDATE ... SET Enderezo=ROW('Real',3,'Lugo') SELECT ... WHERE Enderezo.Rua='Real' I Pode anidarse e EnderezoEspana ROW( Provincia VARCHAR(20), EnderezoProv ROW( Rua VARCHAR(40), Numero SMALLINT, Poboacion VARCHAR(40) ) ) ... e.Provincia='Lugo' and e.EnderezoProv.Poboacion='Viveiro' Bases de Datos Obxecto-Relacionais 13 / 35 SQL:2003 - Constructores de tipos Coleccións (i): Arrays <tipo> ARRAY[(n)] I Pérdese a 1a Forma Normal (con calquera tipo de colección) I Coleeción ordenada (referenciada polo índice), admite valores duplicados I Aplicable a calquera tipo excepto ARRAY I Constructor: ARRAY[<lista elementos>] A CHAR(59) ARRAY, Telefonos VARCHAR(20) ARRAY(2) ... set A = ARRAY['papel','pluma'] ... set Telefonos[2] = '666 123 456' --Telefonos[1]: NULL ... WHERE Telefonos[1] IS NULL I Operacións I Número de elementos: CARDINALITY(A) I Concatenación: I ARRAY['papel','pluma'] || ARRAY['lápiz'] UNNEST Bases de Datos Obxecto-Relacionais 14 / 35 SQL:2003 - Constructores de tipos Coleccións (ii): Multisets <tipo> MULTISET (multiconxunto, bolsa) I Colección sen orden nin índice, admite valores duplicados I Constructor: MULTISET[<lista valores>] ou MULTISET(<sentencia select>) ms1=MULTISET[ROW('secretaria',2), ROW('xefa',4)] ms2=MULTISET(select campo1 from taboa1) I Operacións I Número de elementos: CARDINALITY(ms1) SET(ms2) I Transformación a conxunto: I Predicados: I I a MEMBER ms1 IS A MULTISET MULTISET UNNEST OF ms, ms1 SUBMULTISET OF ms2, SET UNION, MULTISET INTERSECTION, EXCEPT, I Funcións de agregado: COLLECT, FUSION, INTERSECTION Bases de Datos Obxecto-Relacionais 15 / 35 SQL:2003 - Constructores de tipos Exemplos de operacións sobre coleccións EMPRE nomemp coddep postos cod Ana David Pedro 10 10 20 MULTISET[programador, analista, xefe] MULTISET[analista] MULTISET[programador, analista] 10 10 -- A/desde 1FN SELECT nomemp, UNNEST postos FROM Empre; -- Agregados SELECT coddep, COLECT(nomemp) FROM Empre GROUP BY coddep; Bases de Datos Obxecto-Relacionais DEPTO nomdep Compras Ventas SELECT nomdep, MULTISET (SELECT nomemp FROM Empre WHERE coddep = cod) FROM Depto; SELECT COLLECT(nomemp), FUSION(postos) AS TodosPostos, INTERSECTION(postos) AS PostosComuns FROM Empre; 16 / 35 SQL:2003 - Tipos denidos polo usuario I Esquema básico CREATE TYPE <nome tipo> AS <definición tipo> <restriccións de instanciación / subclase> I I <nome tipo> é o identicador (único no <definición tipo> especica o tipo I Tipos esquema) distinct I Tipos estructurados I <restriccións de instanciación / subclase> I [NOT] INSTANTIABLE: Especica se haberá valores dese tipo (atributos, las, ...) I [NOT] FINAL: Especica se podemos especializar o tipo (crear subtipos) NOT FINAL obrigatorio para tipos estructurados (permite especialización) FINAL obrigatorio para tipos distinct (non a permite) Bases de Datos Obxecto-Relacionais 17 / 35 SQL:2003 - Tipos DISTINCT I Redenen tipos predenidos Normalmente para evitar inconsistencias semánticas Podemos realizar conversións ( s) I Son tipo FINAL (non poden denirse outros tipos sobre eles) I cast I 1 2 3 4 5 6 CREATE TABLE EMPREGADO( NSS char(9), Sal DECIMAL(7,2), --En EUROS Com DECIMAL(7,2), --En DOLARES .... ); ¾Que obtemos con esta consulta? 1 2 SELECT Sal+Com as SalTotal FROM EMPREGADO; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Bases de Datos Obxecto-Relacionais CREATE CREATE CREATE Sal Com TYPE EUROS AS DECIMAL(7,2) FINAL; TYPE DOLARES AS DECIMAL(7,2) FINAL; TABLE EMPREGADO( EUROS, DOLARES, .... ); CREATE FUNCTION dolar_a_euro(d DOLARES) RETURNS EUROS BEGIN RETURN d/1.1925; END; CREATE CAST(DOLARES AS EUROS) WITH FUNCTION dolar_a_euro(DOLARES); -- Salario total correcto, en euros SELECT Sal+CAST(Com AS EUROS) as SalTotal FROM EMPREGADO; 18 / 35 SQL:2003 - Tipos estructurados I Constructor básico: la (non se usa a palabra ROW) I A partir de tipos predenidos e/ou tipos distinct I Usando constructores (la, colección) I Permiten denir métodos CREATE TYPE Enderezo AS ( Rua VARCHAR(40), Numero SMALLINT, Poboacion VARCHAR(40) ) INSTANTIABLE NOT FINAL; CREATE TYPE Empregado AS ( NSS CHAR(9), Nome VARCHAR(59), Dir Enderezo, Sal EUROS ) INSTANTIABLE NOT FINAL; CREATE TABLE Departamento( Cod NUMERIC(3) PRIMARY KEY, Nome VARCHAR(50), Director Empregado, Emps Empregado MULTISET ); Bases de Datos Obxecto-Relacionais 19 / 35 SQL:2003 - Rutinas I Estándar ISO/IEC 9075: 2003 ISO/IEC 9075-2: SQL/Foundation ISO/IEC 9075-4: Persistent Stored Modules (SQL/PSM) I Permite incluir rutinas na base de datos procedure) I Procedementos ( I Chamada: CALL function) I Funcións ( I Devolven un valor I Chamada en expresións (select, ...) I Métodos en tipos estructurados/obxectos (method) I Poden ser denidas en SQL ou externas Bases de Datos Obxecto-Relacionais 20 / 35 SQL:2003 - Rutinas Exemplos CREATE PROCEDURE inc_salario CREATE FUNCTION fx (p DOUBLE PRECISION) (IN porcentaxe NUMERIC(2)) RETURNS DOUBLE PRECISON BEGIN LANGUAGE C UPDATE Empregado EXTERNAL NAME '/usr/lib/udf/fx.so' set Salario = Salario * PARAMETER STYLE GENERAL (1 + porcentaxe/100.0); DETERMINISTIC END; NO SQL; CALL inc_salario(2); CREATE FUNCTION sal_total(oNSS CHAR(9)) RETURNS EURO; BEGIN DECLARE saltotal EURO; SELECT Salario+Comision INTO saltotal FROM EMP WHERE NSS = oNSS; RETURN saltotal; END; SELECT NSS, Nome, sal_total(NSS) FROM Empregado; Bases de Datos Obxecto-Relacionais 21 / 35 SQL:2003 - Métodos I Función asociada a un tipo estructurado I Parámetro (implícito) SELF I Distintos tipos de métodos I Estáticos (de clase); de instancia I Constructores, observadores, mutadores, ... CREATE TYPE Rectangulo( x1 FLOAT, y1 FLOAT, x2 FLOAT, y2 FLOAT) INSTANTIABLE NOT FINAL INSTANCE METHOD Area() RETURNS FLOAT; -- Metodo de instancia CREATE INSTANCE METHOD Area RETURNS FLOAT FOR Rectangulo BEGIN RETURN ABS(SELF.x2 - SELF.x1) * ABS(SELF.y2 - SELF.y1); END; Bases de Datos Obxecto-Relacionais CREATE TABLE Rects ( Id INTEGER, Rect Rectangulo ); SELECT Id, Rect.Area() FROM Rects; 22 / 35 SQL:2003 - Métodos (ii) Constructores explícitos ou implícitos CREATE METHOD Rectangulo (x1 float, y1 float, x2 float, y2 float) RETURNS Rectangulo FOR Rectangulo BEGIN SELF.x1 = x1; SELF.y1 = y1; SELF.x2 = x2; SELF.y2 = y2; RETURN SELF; END; Observers e Mutators (denidos polo sistema) UPDATE Rects SET Rect = Rect.x1(2) -- Mutador: -- Rect.x1 = 2 WHERE Rect.Id() = 100;-- Observador -- Combina uso de constructor e mutadores INSERT INTO Rects(Id, Rect) VALUES (101, NEW Rectangulo().x1(1).y1(1).x2(5).y2(9)); INSERT INTO Rects(Id, Rect) VALUES (100, NEW Rectangulo(1,1,5,9)); Bases de Datos Obxecto-Relacionais 23 / 35 SQL:2003 - Referencias e Obxectos I Cada tipo de datos estructurado ten asociado un tipo referencia REF(<tipo>) I Pode ser I I I REF FROM(<campo>): usa un (ou máis) dos atributos REF IS SYSTEM GENERATED: xenerada polo sistema. REF USING <tipo de datos>: denida polo usuario do tipo. I Punteiro persistente I Usaranse para integridade referencial ...pero na declaración de tipo non se indica a táboa referenciada, só o tipo. CREATE TYPE Empregado( NSS CHAR(9), Nome VARCHAR(59), ..., Depto REF(Departamento) ) NOT FINAL REF FROM (NSS); Bases de Datos Obxecto-Relacionais CREATE TYPE Departamento( Nome VARCHAR(40), Emps REF(Empregado) MULTISET ) NOT FINAL REF IS SYSTEM GENERATED; 24 / 35 SQL:2003 - Uso dos Tipos estructurados I Como componentes (campos) dun tipo máis complexo I Como atributos dunha táboa I Para crear táboas de obxectos I Unha táboa almacena unha colección de obxectos Object IDentiers) I Uso de OIDs ( I Importante denir REFerencias, que serán system generated, user generated, derived) I Os OIDs ( I O ámbito das referencias (integridade referencial, con opcións) CREATE TABLE Empregados CREATE TABLE Deptos OF Empregado OF Depto (REF IS NSS USER GENERATED, (REF IS oid SYSTEM GENERATED, -- ref is nss derived? Emps WITH OPTIONS SCOPE Empregados); Depto WITH OPTIONS SCOPE Deptos REFERENCES ARE CHECKED ON DELETE RESTRICT); Bases de Datos Obxecto-Relacionais 25 / 35 SQL:2003 - Herencia Herencia de tipos I Tipos de herencia admitida I Herencia simple ( ... UNDER <superclase>) I Subclases disxuntas I Sobrecarga (redenición) de métodos (selección do método adecuado en tempo de execución) CREATE TYPE Rectangulo( x1 FLOAT, y1 FLOAT, x2 FLOAT, y2 FLOAT) INSTANTIABLE NOT FINAL INSTANCE METHOD Area() RETURNS FLOAT; -- Omitimos o codigo de Area() CREATE TYPE cadrado UNDER rectangulo ( tamano FLOAT ) INSTANTIABLE NOT FINAL OVERRIDING METHOD area() RETURNS FLOAT; CREATE METHOD area() FOR cadrado BEGIN RETURN Self.tamano * Self.tamano; END; Bases de Datos Obxecto-Relacionais CREATE TYPE dobrecadrado UNDER rectangulo ( ladocorto FLOAT ) INSTANTIABLE NOT FINAL OVERRIDING METHOD area() RETURNS FLOAT; CREATE METHOD area() FOR cadrado BEGIN RETURN Self.ladocorto * Self.ladocorto * 2; END; 26 / 35 SQL:2003 - Herencia Herencia en táboas (i) Exemplo Modelo conceptual Bases de Datos Obxecto-Relacionais 27 / 35 SQL:2003 - Herencia Herencia en táboas (ii) Exemplo Datos Persoa IDPers Nome 111 Diego 222 Sara 333 Luisa 444 Carlos Asignatura Cód Nome Créd Aula BD2 BD2 9 [3.3, LAB2.2] Alumno IDPers Ano Curso 222 2002 5 444 2006 1 Profesor IDPers Departamento 111 Computación Bases de Datos Obxecto-Relacionais Aula Número Capacidade Asign 3.3 100 [BD2] LAB2.2 30 [BD2] Clase IDPers Ano Curso 111 222 BD2 111 444 BD2 28 / 35 SQL:2003 - Herencia Herencia en táboas (iii) Exemplo Creación de tipos CREATE TYPE Persoa AS( IDPers CHAR(9), Nome VARCHAR(50) ) NOT FINAL REF FROM(IDPers); CREATE TYPE Alumno UNDER Persoa AS( Ano NUMERIC(4), Curso NUMERIC(1), enClases REF(Clase) MULTISET )NOT FINAL; CREATE TYPE Profesor UNDER Persoa AS( Departamento VARCHAR(20), daClase REF(Clase) MULTISET )NOT FINAL; Bases de Datos Obxecto-Relacionais CREATE TYPE Asignatura AS( Codigo CHAR(5), Nome VARCHAR(30), Creditos NUMERIC(2), asAulas REF(Aula) MULTISET, impartida REF(Clase) MULTISET )NOT FINAL REF FROM(Codigo); CREATE TYPE Aula AS( Numero CHAR(9), Capacidade SMALLINT, asAsignaturas REF(Asignatura) MULTISET )NOT FINAL REF FROM(Numero); CREATE TYPE Clase AS( oProfesor REF(Profesor), oAlumno REF(Alumno), aAsignatura REF(Asignatura) )NOT FINAL REF IS SYSTEM GENERATED; 29 / 35 SQL:2003 - Herencia Herencia en táboas (iv) Exemplo Creación de táboas CREATE TABLE Persoas OF Persoa CREATE TABLE Asignaturas OF Asignatura (REF IS IDPers USER GENERATED); (REF IS Codigo USER GENERATED, impartida WITH OPTIONS SCOPE Clases, CREATE TABLE Alumnos asAulas WITH OPTIONS SCOPE Aulas); UNDER Persoas OF Alumno CREATE TABLE Aulas OF Aula (enClase WITH OPTIONS SCOPE (REF IS Numero USER GENERATED, Clases); asAsignaturas WITH OPTIONS SCOPE Asignaturas ); CREATE TABLE Profesores UNDER Persoas CREATE TABLE Clases OF Clase OF Profesor (REF IS oid SYSTEM GENERATED, (daClase WITH OPTIONS SCOPE oProfesor WITH OPTIONS SCOPE Profesores, Clases); oAlumno WITH OPTIONS SCOPE Alumnos, aAsignatura WITH OPTIONS SCOPE Asignaturas); Bases de Datos Obxecto-Relacionais 30 / 35 SQL:2003 - Herencia Herencia en táboas (v) Almacenamento e consultas I Créanse táboas para a clase e as subclases I Unha persoa (non profesor nin alumno) só ten datos en Persoas. I Un alumno ten o IDPers e nome na táboa de ano e curso na táboa Alumnos. Persoas I Un profesor ten o IDPers e nome na táboa departamento na táboa Profesores. e os datos Persoas e o I Unha persoa non pode ser alumno e profesor á vez (SQL:2003 ⇒ especialización disxunta) I A selección pode implicar Joins I Selección en I Selección en Persoas: Alumnos: só datos de persoas Obtén todos os datos (como persoa e como alumno). Implica facer internamente un join entre Persoas e Alumnos Bases de Datos Obxecto-Relacionais 31 / 35 SQL:2003 - Herencia Herencia en táboas (vi) Modicación de datos Inserción: Inserción en subclase ⇒ Inserción automática en superclase. Borrado: Borrado en superclase ⇒ Borrado en todas as subclases, e viceversa. Modicación: Modicación dos atributos visibles por cada táboa. Modicación como cambio de subclase: non se permite. Implicacións ¾Que ocorre se queremos almacenar ... I que Sara non é alumna (quedaría só como persoa)? I que Luisa é profesora? I que Diego pasa de alumno a profesor? Bases de Datos Obxecto-Relacionais 32 / 35 Consultas Modicación de datos I Tipos básicos (e distinct I Tipos la: constructor sobre eles): igual en SQL-92. ROW I Coleccións: I Arrays: con constructor ou elemento a elemento I Multisets: con constructor ou operacións alxebraicas I Referencias: Primeiro obter a referencia do obxecto. Selección de datos Extracto de transparencias creadas por J. R. Ríos Viqueira (USC). Bases de Datos Obxecto-Relacionais 33 / 35 Discusión Ventaxas I Maior capacidade de modelización I Extensibilidade I Novos tipos de datos e novas operacións I Novas consultas I Reusabilidade I Integración relacional-obxectos (Eliminar desadaptación de impedancias) Inconvenientes I Falta de modelo (teórico) básico universal (para OO) I Non é un modelo cerrado I Problemas co tratamendo de referencias I Xestión de herencia defectuosa Bases de Datos Obxecto-Relacionais 34 / 35 Bibliografía Thomas Connolly and Carolyn Begg. Sistemas de Bases de datos: Un enfoque práctico para diseño, implementación y gestión. a edition, 2005. Addison-Wesley, 4 C. J. Date. Introducción a los sistemas de bases de datos. Prentice Hall, 7 a edition, 2001. Andrew Eisenberg, Jim Melton, Krishna Kulkarni, Jan-Eike Michels, and Fred Zemke. SQL:2003 has been published. SIGMOD Rec., 33(1):119126, 2004. P. Gulutzan and T. Pelzer. SQL-99 complete, really. R&D Books, Kansas, 1999. J. Melton and A. R. Simon. SQL:1999 - Understanding relational language components. Morgan Kaufmann, 2002. Bases de Datos Obxecto-Relacionais 35 / 35 SQL:2003 - Consultas Fragmento del original: SISTEMAS DE BASES DE DATOS BASADOS EN OBJETOS Asignatura: Sistemas de Información, Tema 2 José Ramón Ríos Viqueira Curso 2005-2006 Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 1 CONSULTAS Tipos complejos: ROW, ARRAY, MULTISET CREATE TABLE departamento ( id_dep INTEGER PRIMARY KEY, nombre VARCHAR(50), direccion ROW(calle VARHCAR(100), num INTEGER, loc VARCHAR(10), presupuesto DECIMAL(12, 2), ); CREATE TABLE empleado ( id_emp INTEGER PRIMARY KEY, nombre VARCHAR(50), direccion ROW(calle VARHCAR(100), num INTEGER, loc VARCHAR(10), salario DECIMAL(9, 2), hijos VARCHAR(50) ARRAY, cursos ROW(nombre VARCHAR(50), nota DECIMAL(3, 1)) MULTISET dep INTEGER, FOREING KEY dep REFERENCES departamento(id_dep) ); Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 2 1 CONSULTAS Tipos complejos: ROW, ARRAY, MULTISET Desanidar tipos colección Obtener una lista con los hijos de cada empleado SELECT e.nombre, h.hijo FROM empleado AS e, UNNEST (e.hijos) AS h(hijo) Obtener para cada empleado de Santiago la nota media en los cursos que aprobó SELECT e.nombre, AVG(c.nota) FROM empleado AS e, UNNEST (e.cursos) AS c(nota) WHERE e.direccion.loc = 'Santiago' and c.nota >= 5 GROUP BY e.nombre Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 3 CONSULTAS Tipos complejos: ROW, ARRAY, MULTISET Anidar tipos colección Obtener un informe en el que para cada departamento cuyo presupuesto sea mayor que la suma de los sueldos de sus empleados se indique: • Nombre del departamento, • Presupuesto del departamento, • Relación de empleados con el nombre, salario y número de hijos de cada empleado • Suma del salario de todos sus empleados SELECT d.nombre, d.presupuesto, MULTISET(SELECT e2.nombre, e2.salario, count(h.hijo) AS Hijos FROM empleado AS e2, UNNEST (e2.hijos) AS h(hijo) WHERE d.id_dep = e2.dep GROUP BY e2.nombre, e2.salario), SUM(e.salario) AS SalarioTotal FROM departamento AS d, empleado AS e WHERE e.id_dep=e.dep GROUP BY d.nombre, d.presupuesto HAVING d.presupuesto > SUM(e.salario) Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 4 2 CONSULTAS Uso de tipos estructurados: métodos y funciones CREATE TYPE punto UNDER geo ( x FLOAT, y FLOAT) INSTANTIABLE NOT FINAL; CREATE TYPE linea UNDER geo( coords punto ARRAY) INSTANTIABLE NOT FINAL INSTANCE METHOD longitud() RETURNS FLOAT; CREATE FUNCTION distancia (g1 geo, g2 geo) RETURNS FLOAT BEGIN ... END CARRETERAS cod_carrt propietario Estado Trazado CENTROS_SALUD id_cs nombre camas posicion N-651 Estatal Malo L1 1 Viñas 0 P1 A-9 F Estatal Bueno L2 2 San Cide 10 P2 cp-0804 Provincial Regular L3 064-001 Municipal Bueno L4 Valores de tipo Punto Valores de tipo Linea Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 5 CONSULTAS Uso de tipos estructurados: métodos y funciones Longitud total de carreteras municipales en mal estado SELECT sum(trazado.longitud) FROM carreteras WHERE propietario = 'Municipal' and estado = 'Malo' Número total de camas de los centros de salud que se encuentran a menos de 1 km de un tramo de carretera con longitud mayor de 2 km y estado bueno SELECT sum(camas) FROM carreteras AS c, centros_salud AS cs WHERE c.estado = 'bueno' and c.longitud < 2000 and distancia(c.trazado, cs.posicion) < 1000 Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 6 3 CONSULTAS Uso de tipos estructurados: Herencia 1, 'Tambre' CREATE TABLE rios AS ( id_rio INTEGER PRIMARY KEY, nombre VARHCAR(50), geom geo) 2, 'Tambre' Para cada río, devolver la longitud total de las partes que son de tipo línea SELECT nombre, SUM(TREAT(geom AS linea).longitud) FROM rios WHERE geom IS OF (linea) GROUP BY nombre 3, 'Tambre' Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 7 CONSULTAS Referencias CREATE TYPE Empleado AS ( nombre VARCHAR (50), salario_base DECIMAL (9, 2), complementos DECIMAL(9, 2), dep REF(Departamento)) NOT FINAL REF IS SYSTEM GENERATED INSTANCE METHOD salario() RETURNS DECIMAL(9, 2); CREATE TABLE Empleados OF Empleado (REF IS oid SYSTEM GENERATED, dept WITH OPTIONS SCOPE Departamentos); CREATE TYPE Departamento AS ( nombre VARCHAR (50), dir DIRECCION, emps REF(Empleado) MULTISET, director REF(Empleado)) NOT FINAL REF IS SYSTEM GENERATED; CREATE TABLE Departamentos OF Departamento (REF IS oid SYSTEM GENERATED, emps WITH OPTIONS SCOPE Empleados, director WITH OPTIONS SCOPE Empleados); Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 8 4 CONSULTAS Referencias Dereferenciación de atributos Para cada departamento de santiago, obtén su nombre y el nombre de sus empleados SELECT e.nombre, e.dept->nombre FROM Empleados e WHERE e.dep->dir.loc = 'Santiago' SELECT e.nombre, DEREF(e.dept).nombre FROM Empleados e WHERE DEREF(e.dept).dir.loc = 'Santiago' SELECT e.nombre, (SELECT d.nombre FROM departamentos d WHERE d.oid = e.dep) FROM Empleados e WHERE (SELECT dir.loc FROM departamentos d WHERE d.oid = e.dep) = 'Santiago' SELECT e.nombre, d.nombre FROM Empleados e LEFT JOIN Departamentos d ON (e.dep = d.oid) WHERE d.dir = 'Santiago' Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 9 CONSULTAS Referencias Dereferenciación de métodos Para cada departamento obtener la suma de los salarios de sus empleados SELECT d.nombre, SUM(e.ptr->salario) FROM Departamentos d, UNNEST(d.emps) AS e(ptr) GROUP BY d.nombre Los métodos no se puede aplicar directamente a las tuplas Nombre y salario de cada empleado SELECT e.nombre, e.salario FROM Empleados e ERROR: e no es de tipo empleado, ya que tiene un atributo oid SELECT e.nombre, DEREF(e.oid).salario FROM Empleados e Curso 2005/2006 CORRECTO J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 10 5 CONSULTAS Herencia de tipos CREATE TYPE rectangulo AS ( x1 FLOAT, y1 FLOAT, x2 FLOAT, y2 FLOAT) NOT FINAL INSTANCE METHOD area() RETURNS FLOAT; CREATE TABLE rectangulos AS ( id_rect INTEGER PRIMARY KEY, rec rectangulo) CREATE TYPE cuadrado UNDER rectangulo (tamano FLOAT) NOT FINAL OVERRIDING METHOD area() RETURNS FLOAT; Obten el área de cada cuadrado de la tabla rectángulos SELECT r.id_rect, r.rec.area FROM rectangulos WHERE r.rec IS OF (cuadrado) Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 11 CONSULTAS Herencia de tablas Una selección en una supertabla, devuelve los atributos de esa supertabla para todas las tuplas de esa supertabla y de todas sus subtablas. CREATE TABLE EMPLEADOS OF empleado (REF IS id_emp SYSTEM GENERATED, dep WITH OPTIONS SCOPE DEPARTAMENTOS); CREATE TABLE ADMINISTRADORES OF administrador UNDER EMPLEADOS; CREATE TABLE PROGRAMADORES OF programador UNDER EMPLEADOS (proys WITH OPTIONS SCOPE PROYECTOS); Selecciona todos los empleados, de cualquier tipo SELECT * FROM Empleados Selecciona el nombre y el lenguaje que domina cada programador SELECT p.nombre, p.lenguaje FROM Programadores Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 12 6 CONSULTAS Herencia de tablas Selección de las túplas de un sólo tipo ONLY • Selecciona todos los empleados, que no son ni programadores ni administradores SELECT * FROM ONLY (Empleados) SELECT * FROM Empleados WHERE DEREF(oid) IS OF (Empleado) Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 13 CONSULTAS Definición de vistas Posibilidad de definir vistas con tipo Definición de jerarquías de vistas Las referencias entre tablas base pueden transformarse en referencias entre vistas Vistas para el departamento de ventas Tipos CREATE TYPE empVentas AS ( nombre VARCHAR(50), salario DECIMAL(6, 2)) NOT FINAL; CREATE TYPE admVentas UNDER empVentas ( sistema VARCHAR(20)) NOT FINAL; CREATE TYPE progVentas UNDER empVentas ( lenguaje VARCHAR(20), proys REF(proyecto) MULTISET ) NOT FINAL; CREATE TYPE proyVentas AS ( duracion INTEGER, presup DECIMAL(9, 2), progs REF(programador) MULTISET) NOT FINAL; Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 14 7 CONSULTAS Vistas para el departamento de ventas Vistas CREATE VIEW vistaEmpVentas OF empVentas REF IS oid SYSTEM GENERATED AS (SELECT nombre, salario FROM ONLY (Empleados) WHERE dep->nombre = 'Ventas'); CREATE VIEW vistaProgVentas OF progVentas UNDER vistaEmpVentas (proys WITH OPTIONS SCOPE vistaProyVentas) AS (SELECT lenguaje, proys FROM ONLY(Programadores) WHERE dep->nombre = 'Ventas'); CREATE VIEW vistaAdmVentas OF admVentas UNDER vistaEmpVentas AS (SELECT sistema FROM ONLY(Administradores) WHERE dep->nombre = 'Ventas'); CREATE VIEW vistaProyVentas OF proyVentas REF IS oid SYSTEM GENERATED (progs WITH OPTIONS SCOPE vistaProgVentas) AS (SELECT duracion, presup, progs FROM Proyectos WHERE dep->nombre = 'Ventas'); Curso 2005/2006 J.R.R.V. – SI – Sistemas de bases de datos objeto-relacionales 15 8