Bases de Datos y SQL - PowerPoint

Document Sample
Bases de Datos y SQL - PowerPoint Powered By Docstoc
					                  Seminario Unigis
                  21 de Enero 2000




Bases de Datos y SQL



             Michael Gould
                    Í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
¿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)
    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)
          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
         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
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
          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
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)
            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
            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
Cardinalidad = 2155   Grado = 5
       ¿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
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
Repaso de comandos SQL
   DDL:
     – CREATE <tabla>
     – DROP <tabla>
   DML:
     – SELECT <columna(s) de datos>
     – FROM <tabla(s)>
     – WHERE <condición lógica>
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
  );
       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;
   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;
FROM
Datum INNER JOIN (Map_projection INNER JOIN (Height_reference_system INNER JOIN
(((Keyword INNER JOIN ((Ellipsoid INNER JOIN (Geographic_area INNER JOIN
(Standard_product INNER JOIN (Scale INNER JOIN ((Organisation INNER JOIN Contact
ON Organisation.organisationID = Contact.organisationID) INNER JOIN (Dataset
INNER JOIN (((((((((((Application_schema INNER JOIN Contact_Role_of_contact ON
Application_schema.datasetID = Contact_Role_of_contact.datasetID) INNER JOIN
Dataset_Keyword ON Application_schema.datasetID = Dataset_Keyword.datasetID)
INNER JOIN Dataset_quality ON Application_schema.datasetID =
Dataset_quality.datasetID) INNER JOIN Distribution ON
Application_schema.datasetID = Distribution.datasetID) INNER JOIN Extent ON
Application_schema.datasetID = Extent.datasetID) INNER JOIN
Extent_Geographic_area ON (Extent.datasetID = Extent_Geographic_area.datasetID)
AND (Application_schema.datasetID = Extent_Geographic_area.datasetID)) INNER JOIN
Object_type ON Application_schema.datasetID = Object_type.datasetID) INNER JOIN
Organisation_Role_of_organisation ON Application_schema.datasetID =
Organisation_Role_of_organisation.datasetID) INNER JOIN Role_of_contact ON
(Role_of_contact.roleID = Contact_Role_of_contact.roleID) AND
(Organisation_Role_of_organisation.roleID = Role_of_contact.roleID)) INNER JOIN
Role_of_organisation ON (Role_of_organisation.roleID =
Organisation_Role_of_organisation.roleID) AND (Contact_Role_of_contact.roleID =
Role_of_organisation.roleID)) INNER JOIN Spatial_reference_system ON
Application_schema.datasetID = Spatial_reference_system.datasetID) ON
(Dataset.datasetID = Dataset_quality.datasetID) AND (Dataset.datasetID =
Dataset_Keyword.datasetID) AND (Dataset.datasetID =
Contact_Role_of_contact.datasetID) AND (Dataset.datasetID =
Organisation_Role_of_organisation.datasetID) AND (Dataset.datasetID =
Object_type.datasetID) AND (Dataset.datasetID = Extent.datasetID) AND
(Dataset.datasetID = Application_schema.datasetID) AND (Dataset.datasetID =
Distribution.datasetID) AND (Dataset.datasetID =
Spatial_reference_system.datasetID)) ON (Contact.contactID =
Contact_Role_of_contact.contactID) AND (Organisation.organisationID =
Organisation_Role_of_organisation.organisationID)) ON Scale.scaleID =
Dataset.scaleID) ON Standard_product.standard_productID =
Dataset.standard_productID) ON Geographic_area.geographic_areaID =
Extent_Geographic_area.geographic_areaID) ON Ellipsoid.ellipsoidID =
Spatial_reference_system.ellipsoidID) INNER JOIN Attribute_type ON
Object_type.object_typeID = Attribute_type.object_typeID) ON Keyword.keywordID =
Dataset_Keyword.keywordID) INNER JOIN Association_type ON
Object_type.object_typeID = Association_type.from_object_typeID) INNER JOIN
Object_Structure_primitive ON Object_type.object_typeID =
Object_Structure_primitive.object_typeID) ON
Height_reference_system.height_reference_systemID =
Spatial_reference_system.height_reference_systemID) ON
Map_projection.map_projectionID = Spatial_reference_system.map_projectionID) ON
Datum.datumID = Spatial_reference_system.datumID

WHERE
                 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
             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”
                    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
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 !!
      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”
        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
     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
     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...
    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...
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.
 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’;
 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’;
     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
      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
                       Seminario Unigis
                       21 de Enero 2000




Gracias por vuestra atención.

        gould@uji.es