michael gould bases de datos y sql seminario unigis 21 de enero 2000
TRANSCRIPT
Michael Gould
Bases de Datos y SQL
Seminario Unigis
21 de Enero 2000
ÍndiceIntroducción: Bases de datosModelo relacionalSQL
Repaso de comandos principalesLenguaje de definición de datos (DDL)Lenguaje de manipulación (DML)
DemostracionesExtensiones de SQL para el mundo SIGProblemas con el modelo relacional
¿Porque las bases de datos?Parece obvio hoy en díaTradicionalmente sistemas trabajaban a base
de ficheros sueltos, y procedimientos sobre ellossistemas 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áficadigitalizació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íaLa 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 atributosOcupa gran parte del tiempo/presupuestoDurante la explotación de un SIG, a largo plazo, la
actualización cartográfica juega un papel trivialExplotació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 datosesta en vuestros manos
Papel de BBDD (3)Un experto en BBDD puede determinar el éxito
de (o salvar) un proyecto SIG; un cartógrafo noUNIGIS ofrece dos asignaturas (módulos)
dedicadas a las BBDDAconsejables los dos módulosY si puede ser, un curso de Oracle después de
UnigisSi no puede ser, utilización de MS-Access (en
adición a Quasar) para el primer módulo
Modelos de bases de datosModelo jerárquico
estructura de árbol: relaciones 1:muchosrequiere duplicación de datos
Modelo en redpermiten mejor relación entre los datostodo conectado a todomuy utilizado en aplicaciones COBOL (empresarial)
Dibujos en la página 2-30Modelo relacional
modelo dominante hoy en día
Modelo relacionalDr Edgar (Ted) Codd, de la IBM1970 “A relational model of data for large
shared data banks” Communications of the ACM 13(6).
Modelo muy simple, flexible hasta cierto puntoTodo en tablas, con columnas y filasOperaciones para crear, borrar, modificar
tablasOtras operaciones (álgebra relacional) para
manipular (consultar) estas tablas...El modelo se caracteriza por tres elementos
Características del modeloElemento estructural: forma de guardar datos
todo en tablas, y nada más que tablassin duplicar registros (filas, tuplas)campos (columnas) con nombres únicosentradas en un campo de solo un tipo
numérico (entero, real..), texto, fecha, etc.todas las entradas serán datos atómicosorden de filas/columnas no importavalores 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 tablasSalida: una tabla nuevaCodd 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 nuevaSelect y Join: operaciones críticas en el SIG vectorial
Características (3)Elemento de integridad: control lógico
Integridad de entidadesgarantiza que los campos clave tengan datos (no nulos) y
que si existe un registro se puede localizarIntegridad 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
Grado = 5C
ardi
nali
dad
= 2
155
¿Como manipular los datos/tablas?Structured Query Language, SQLViene 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 AccessNingún fabricante soporta el 100% del estándar
SQL y el modelo relacionalSQL no forma parte del modelo relacionalQuery-By-Example (QBE), otros lenguajes de
consulta pueden aplicarse también al modeloSQL ha sido aceptado como el lenguaje de
factoSQL aceptado por Codd, con maticesSirve 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 SQLDDL:
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 NomConsFROM
ofarea,relacion,ofcd,zona,parcela,construc
WHERE NomAr=’Central’AND ofarea.IdAr=relacion.IdArAND relacion.IdOfCD=zona.IdOfCDAND zona.IdZona=parcela.IdZonaAND parcela.IdCons=construc.IdCons;
Mas ejemplos...
SELECT SELECT NomAr,AVG(Superf),SUM(Superf)NomAr,AVG(Superf),SUM(Superf)FROM ofarea,relacion,zonaFROM ofarea,relacion,zonaWHERE ofarea.IdAr=relacion.IdArWHERE ofarea.IdAr=relacion.IdArAND relacion.IdOfCD=zona.IdOfCDAND relacion.IdOfCD=zona.IdOfCDGROUP BY NomAr;GROUP BY NomAr;
FROMDatum 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 ContactON Organisation.organisationID = Contact.organisationID) INNER JOIN (DatasetINNER JOIN (((((((((((Application_schema INNER JOIN Contact_Role_of_contact ONApplication_schema.datasetID = Contact_Role_of_contact.datasetID) INNER JOINDataset_Keyword ON Application_schema.datasetID = Dataset_Keyword.datasetID)INNER JOIN Dataset_quality ON Application_schema.datasetID =Dataset_quality.datasetID) INNER JOIN Distribution ONApplication_schema.datasetID = Distribution.datasetID) INNER JOIN Extent ONApplication_schema.datasetID = Extent.datasetID) INNER JOINExtent_Geographic_area ON (Extent.datasetID = Extent_Geographic_area.datasetID)AND (Application_schema.datasetID = Extent_Geographic_area.datasetID)) INNER JOINObject_type ON Application_schema.datasetID = Object_type.datasetID) INNER JOINOrganisation_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 JOINRole_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 ONApplication_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 ONObject_type.object_typeID = Attribute_type.object_typeID) ON Keyword.keywordID =Dataset_Keyword.keywordID) INNER JOIN Association_type ONObject_type.object_typeID = Association_type.from_object_typeID) INNER JOINObject_Structure_primitive ON Object_type.object_typeID =Object_Structure_primitive.object_typeID) ONHeight_reference_system.height_reference_systemID =Spatial_reference_system.height_reference_systemID) ONMap_projection.map_projectionID = Spatial_reference_system.map_projectionID) ONDatum.datumID = Spatial_reference_system.datumID
WHERE
RelacionesSon BBDD relacionales, ¿no?Dividimos los datos entre varias tablas
(específicas) para minimizar la duplicación de datos, y también las dependencias entre camposproceso conocido como normalización (sección 4.1.3)
Hay relaciones de 3 tipos entre atributos1:1, una persona tiene un DNI1:M, una persona tiene muchos amigosM: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”
ClavesPara enlazar tablas mediante un campo en
comúnClaves 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 DatosCuales son las entidades (y sus atributos) de
importanciaCuales son las relaciones entre ellasCreación de modelos E-A-R tratada en detalle en
la sección 4 del libro UnigisLuego diseñar una bdatos física de acuerdo con
el modeloEste diseño no es una tarea trivialLa 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 SIGSe utiliza (SQL es un estándar de facto)
Cuando sabes SQL, sabes el 30% de cualquier SIG vectorialPero no es lenguaje óptimo para representar las
relaciones espaciales (basadas en la geometría)cerca de, pasando por, intersección con, dentro
deY 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 SQLNormalmente el SIG maneja datos
alfanuméricos (en tablas relacionales) y gráficos (en ficheros propietarios)...
Ejemplos de Arc/INFO, MapInfoSQL no ofrece herramientas para la parte
gráficaNo es eficiente guardar miles (millones) de coords
x,y,z en columnas largasPara representar un polígono hace falta crear por lo
menos 5 tablas y sus relaciones correspondientesDemasiado 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 campoContra la norma de Codd, que el orden no importaY ¿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-SQLEjemplo 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 SQLPero 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 OOSQL-MM (multimedia)Oracle Spatial (todos los datos en tablas
relacionales)Basado en un nuevo modelo objeto-relacionalSoporta 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 WHEREsdo_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 WHEREMDSYS.SDO_RELATE(Parks.Geometry,
Roads.Geometry,’MASK=ANYINTERACT’) = ’TRUE’AND Roads.Name = ’I-93’;
BBDD objeto-relacionalEn su infanciaOracle liderando el campo, empujando fuerte
tiene grupo de 200 trabajando en temas especialesBD relacionales poseen una masa crítica
sustancialTodos los sistemas utilizan indexación 2-D (o n-
D) para mejorar rendimientotema tratado en otro modulo de Unigis
El futuro de las BBDDYa que vivimos en tiempo de Internet, nadie
sabe¿Será la propia Internet (web) nuestra base de
datos?Todo distribuidoTodo conectado
Faltan nuevos índices, buscadoresEs una base de datos con dominio abierto
crece al ritmo de 100.000 páginas (recursos) al díano es posible la consulta “Dame un listado de todos
los recursos sobre tal tema”Otros tiempos, otros SIGs
