Download Introducción al SQL

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