Bases de Datos y SQL

1. Seminario Unigis 21 de Enero 2000 Bases de Datos y SQL Michael Gould

2. Índice • Introducción: Bases de datos • Modelo relacional • SQL • Repaso de comandos principales • Lenguaje de definición de datos (DDL) • Lenguaje de manipulación (DML) • Demostraciones • Extensiones de SQL para el mundo SIG • Problemas con el modelo relacional

3. ¿Porque las bases de datos? • Parece obvio hoy en día • Tradicionalmente sistemas trabajaban a base de ficheros sueltos, y procedimientos sobre ellos • sistemas a medida de cada aplicación (pág. 2-9) • Bdatos: separación de datos e su implementación (hardware/software) • Independencia • Protección (permite sistema multiusuario) • Flexibilidad (conectar la bdatos a todo) • Eficiencia (minimiza duplicidad de datos) • Integridad (minimiza errores lógicos)

4. Papel de BBDD en los SIG • Típicamente mucha énfasis en cursos de SIG en la parte cartográfica • digitalización, depuración, conversión de mapas digitales... • enfoque geométrico • “ y se puede enlazar atributos a cada elemento geográfico...línea, polígono etc.” • típico ejemplo: segmento de calle (línea) con 6 atributos: longitud, anchura, 4 números de policía • La parte cartográfica es más visual, interesante (transparencias)

5. Papel de BBDD (2) • La creación de la base de datos SIG supone la recogida de datos carto(geo)gráficos y atributos • Ocupa gran parte del tiempo/presupuesto • Durante la explotación de un SIG, a largo plazo, la actualización cartográfica juega un papel trivial • Explotación del SIG sinónimo con consultar ...a la base de datos (transparencias) • la geometría se mantiene relativamente fija, los atributos no • el SGBDR permite combinaciones de consultas casi sin limite • limitación: del diseño de la base de datos • esta en vuestros manos

6. Papel de BBDD (3) • Un experto en BBDD puede determinar el éxito de (o salvar) un proyecto SIG; un cartógrafo no • UNIGIS ofrece dos asignaturas (módulos) dedicadas a las BBDD • Aconsejables los dos módulos • Y si puede ser, un curso de Oracle después de Unigis • Si no puede ser, utilización de MS-Access (en adición a Quasar) para el primer módulo

7. Modelos de bases de datos • Modelo jerárquico • estructura de árbol: relaciones 1:muchos • requiere duplicación de datos • Modelo en red • permiten mejor relación entre los datos • todo conectado a todo • muy utilizado en aplicaciones COBOL (empresarial) • Dibujos en la página 2-30 • Modelo relacional • modelo dominante hoy en día

8. Modelo relacional • Dr Edgar (Ted) Codd, de la IBM • 1970 “A relational model of data for large shared data banks” Communications of the ACM 13(6). • Modelo muy simple, flexible hasta cierto punto • Todo en tablas, con columnas y filas • Operaciones para crear, borrar, modificar tablas • Otras operaciones (álgebra relacional) para manipular (consultar) estas tablas... • El modelo se caracteriza por tres elementos

9. Características del modelo • Elemento estructural: forma de guardar datos • todo en tablas, y nada más que tablas • sin duplicar registros (filas, tuplas) • campos (columnas) con nombres únicos • entradas en un campo de solo un tipo • numérico (entero, real..), texto, fecha, etc. • todas las entradas serán datos atómicos • orden de filas/columnas no importa • valores nulos soportados (<> 0) • claves para crear relaciones (solo una es clave primaria)

10. Características (2) • Elemento de manipulación: que se puede hacer • Entrada: una o mas tablas • Salida: una tabla nueva • Codd define álgebra y cálculo relacional (el usuario no los vea) • En la práctica, solo son 3 operadores fundamentales: • SELECT: especificar “criterios de búsqueda” y crear una nueva tabla con solo los datos que buscábamos • PROJECT: copia un subconjunto de campos a una tabla nueva • JOIN: “pega” dos tablas para crear una nueva • Select y Join: operaciones críticas en el SIG vectorial

11. Características (3) • Elemento de integridad: control lógico • Integridad de entidades • garantiza que los campos clave tengan datos (no nulos) y que si existe un registro se puede localizar • Integridad referencial • mantiene intactas relaciones (referencias) de clave a clave • no puedes borrar un registro al que depende otra tabla • los dos campos clave deben ser del mismo tipo

12. Grado = 5 Cardinalidad = 2155

15. ¿Como manipular los datos/tablas? • Structured Query Language, SQL • Viene de Sequel (IBM, 1974), todavía se pronuncia “siquel”, aunque oficialmente es “S.Q.L.” • Un estándar ANSI, ISO pero... • Los fabricantes han creado sus propias versiones no exactamente estándares... • PL/SQL de Oracle <> SQL de MS Access (Jet) • Muchos SIG utilizan ficheros DBF o MDB, que los manipulan sin los gestores dBase o Access • Ningún fabricante soporta el 100% del estándar

16. SQL y el modelo relacional • SQL no forma parte del modelo relacional • Query-By-Example (QBE), otros lenguajes de consulta pueden aplicarse también al modelo • SQL ha sido aceptado como el lenguaje de facto • SQL aceptado por Codd, con matices • Sirve como lenguaje completo: de definición (DDL) y de manipulación (DML) de datos según el modelo relacional • Tiene una estructura “pseudo inglésa” • Se utiliza como lingua franca entre sistemas

17. Repaso de comandos SQL • DDL: • CREATE <tabla> • DROP <tabla> • DML: • SELECT <columna(s) de datos> • FROM <tabla(s)> • WHERE <condición lógica>

18. Ejemplos del sintaxis SQL create table zona ( IdZona smallint not null unique, NomZona char(30) not null unique, Superf smallint, IdOfCD smallint not null ); create table tipo ( IdTipo smallint not null unique, DescTipo char(30) not null unique );

19. Mas ejemplos... SELECT DISTINCT NomCons FROM ofarea,relacion,ofcd,zona,parcela,construc WHERE NomAr=’Central’ AND ofarea.IdAr=relacion.IdAr AND relacion.IdOfCD=zona.IdOfCD AND zona.IdZona=parcela.IdZona AND parcela.IdCons=construc.IdCons;

20. Mas ejemplos... SELECT NomAr,AVG(Superf),SUM(Superf) FROM ofarea,relacion,zona WHERE ofarea.IdAr=relacion.IdAr AND relacion.IdOfCD=zona.IdOfCD GROUP BY NomAr;

22. Relaciones • Son BBDD relacionales, ¿no? • Dividimos los datos entre varias tablas (específicas) para minimizar la duplicación de datos, y también las dependencias entre campos • proceso conocido como normalización (sección 4.1.3) • Hay relaciones de 3 tipos entre atributos • 1:1, una persona tiene un DNI • 1:M, una persona tiene muchos amigos • M:N, una tienda tiene muchos clientes, cada uno de los cuales es cliente de muchas tiendas

23. Relaciones (2) • El modelo relacional no permite relaciones M:N, por eso a veces hay que crear nuevas tablas (auxiliares) como “puentes” entre una tabla y otras • Ejemplo de la Videoteca: • tabla “clientes” (cada cliente es único) • tabla “películas” (cada película es única) • Problema: ¿Como modelar el caso en que una película esta en manos de muchos clientes, y que cada cliente puede haber alquilado muchas películas? • Solución: nueva tabla “movimientos”, con campos en común con “clientes” y “películas”

24. Claves • Para enlazar tablas mediante un campo en común • Claves primarias (campo único), como DNI en la tabla “clientes” • Claves externas (foráneas), como DNI en la tabla “movimientos” • Ejemplo de Neptuno en Access

25. Diseño de la Base de Datos • Cuales son las entidades (y sus atributos) de importancia • Cuales son las relaciones entre ellas • Creación de modelos E-A-R tratada en detalle en la sección 4 del libro Unigis • Luego diseñar una bdatos física de acuerdo con el modelo • Este diseño no es una tarea trivial • La explotación del SIG (consultas posibles) se basa en este diseño !! • Rediseñar una base de datos a posteriori MUY caro !!

26. SQL en el ámbito SIG • Se utiliza (SQL es un estándar de facto) • Cuando sabes SQL, sabes el 30% de cualquier SIG vectorial • Pero no es lenguaje óptimo para representar las relaciones espaciales (basadas en la geometría) • cerca de, pasando por, intersección con, dentro de • Y no permite interacción multimodal • ¿Cuáles son las carreteras que pasan por este <señalizando con ratón> barrio? • En general: SQL es para tablas de texto • “SQL sirve para modelar como la gente utiliza tablas”

27. Problemas con SQL • Normalmente el SIG maneja datos alfanuméricos (en tablas relacionales) y gráficos (en ficheros propietarios)... • Ejemplos de Arc/INFO, MapInfo • SQL no ofrece herramientas para la parte gráfica • No es eficiente guardar miles (millones) de coords x,y,z en columnas largas • Para representar un polígono hace falta crear por lo menos 5 tablas y sus relaciones correspondientes • Demasiado complicado y lento

28. Problemas con SQL (2) • ¿Como optimizar almacenamiento de datos espaciales? • Puedes ordenar un campo y definir un índice (que siempre es unidimensional) sobre este campo • Contra la norma de Codd, que el orden no importa • Y ¿qué campo vas a elegir? Sólo coord-x, sólo coord-y... • En general, lectura de tablas relacionales es muy lenta (olvídalo para dibujar elementos geográficos) • Indices bidimensionales • Quadtree, KDB-tree (van Oosterom) • Decomposición/indexación regular en 2-D del espacio

29. Problemas con SQL (3) • No se puede definir “tipos de datos abstractos” • Modelo relacional define CHAR, ENTERO, REAL, FECHA, etc. • Sería útil poder definir tipos geométricos por ejemplo: • línea, nodo, rectángulo... • Reconocida hace 20 años la necesidad de extensiones especiales al SQL para servir los campos SIG, CAD, diseño...

30. Solución 1: Pseudo-SQL • Ejemplo de MapInfo • Han definido extensiones para “objetos geográficos” • aquí objeto = entidad (no es OO) • obj contiene otro obj, tiene intersección con, esta completamente dentro... • Gestor de base de datos hecho por MapInfo, que entiende estas extensiones, y que trabaja con ficheros DBF • No cumple con muchas normas del ANSI SQL • Pero funciona...

32. Solución 2: “SQL espacial” • Bundock y otros (Smallworld) • Herring y otros (Intergraph>>Oracle) • van Oosterom (en libro anexo Unigis) • SQL-3 algo más flexible, permite algo de OO • SQL-MM (multimedia) • Oracle Spatial (todos los datos en tablas relacionales) • Basado en un nuevo modelo objeto-relacional • Soporta algo de SQL, algo de conceptos OO, programación desde Java, C++, etc.

33. Ejemplos, Oracle Spatial ¿Cuales son los parques con ríos? SELECT parks.name FROM parks, rivers WHERE sdo_geom.relate(parks.geometry, rivers.geometry, ’OVERLAPBDYINTERSECT’) = ’OVERLAPBDYINTERSECT’;

34. Otro de Oracle Spatial Parques por donde pasa la carretera I-93 SELECT Parks.Name FROM Parks, Roads WHERE MDSYS.SDO_RELATE(Parks.Geometry, Roads.Geometry, ’MASK=ANYINTERACT’) = ’TRUE’ AND Roads.Name = ’I-93’;

35. BBDD objeto-relacional • En su infancia • Oracle liderando el campo, empujando fuerte • tiene grupo de 200 trabajando en temas especiales • BD relacionales poseen una masa crítica sustancial • Todos los sistemas utilizan indexación 2-D (o n-D) para mejorar rendimiento • tema tratado en otro modulo de Unigis

36. El futuro de las BBDD • Ya que vivimos en tiempo de Internet, nadie sabe • ¿Será la propia Internet (web) nuestra base de datos? • Todo distribuido • Todo conectado • Faltan nuevos índices, buscadores • Es una base de datos con dominio abierto • crece al ritmo de 100.000 páginas (recursos) al día • no es posible la consulta “Dame un listado de todos los recursos sobre tal tema” • Otros tiempos, otros SIGs

37. Seminario Unigis 21 de Enero 2000 Gracias por vuestra atención.gould@uji.es