bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.base de datos...

107
Bases de datos Versión 1.1 Óscar Gómez Curso 2012-2013

Upload: dohanh

Post on 23-Feb-2019

221 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datosVersión 1.1

Óscar Gómez

Curso 2012-2013

Page 2: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.
Page 3: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Índice general

1. Introducción 31.1. Historia de las bases de datos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.2. Historia del software libre GNU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.3. Un repaso al resto del libro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

2. Diseño de bases de datos 52.1. Introducción . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52.2. Modelado de datos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62.3. Modelos E/R . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62.4. Problemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112.5. Transformación de modelos E/R en modelos relacionales . . . . . . . . . . . . . . . . . 222.6. Normalización . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

3. Diseño físico 273.1. Introducción . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273.2. BBDD en Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273.3. Claves ajenas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313.4. MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313.5. Tablespaces y undo files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

4. Consultas SQL 394.1. Introducción . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394.2. La sentencia SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394.3. Condiciones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394.4. Consultas con agregados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414.5. Consultas multitabla . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444.6. Algunos ejercicios resueltos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454.7. Subconsultas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464.8. Actualización y borrado . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

5. Programación 615.1. Introducción . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615.2. Procedimientos almacenados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615.3. Sentencias básicas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

I

Page 4: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

5.4. Funciones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 725.5. Funciones MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 725.6. Solución al examen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

6. Administración 796.1. Introducción . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 796.2. Aspectos básicos de las contraseñas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 796.3. El sistema de privilegios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 806.4. El sistema de concesiones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 826.5. Usuarios con restricciones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 826.6. Vistas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 836.7. Recuperando la clave de root . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 856.8. Copias de seguridad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

7. Access 897.1. Introducción . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 897.2. El entorno . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

8. BBDD Objeto relacionales 918.1. Introducción . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 918.2. Instalación de PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 918.3. Uso de Postgres en una máquina virtual . . . . . . . . . . . . . . . . . . . . . . . . . . 918.4. Funcionamiento básico de Postgres . . . . . . . . . . . . . . . . . . . . . . . . . . . . 928.5. Comandos de Postgres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 928.6. Creación de tipos de datos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 968.7. Soporte documental . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 988.8. Sistemas de información geográfica . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

9. Índices y tablas 103

II

Page 5: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Contents:

Índice general 1

Page 6: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

2 Índice general

Page 7: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 1

Introducción

1.1 Historia de las bases de datos

1.2 Historia del software libre GNU

1.3 Un repaso al resto del libro

3

Page 8: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

4 Capítulo 1. Introducción

Page 9: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 2

Diseño de bases de datos

2.1 Introducción

En general, la construcción de una casa se considera un proyecto de envergadura. Dado que no se puedepermitir que cada uno de los participantes en el proyecto siga su propio ritmo o haga lo que crea con-veniente, se asume que habrá un arquitecto que diseña una estructura y realiza los cálculos necesariospara que todo se construya correctamente. En las bases de datos ocurre lo mismo: determinados nego-cios necesitarán una infraestructura de información muy sofisticada por lo que puede ser convenientedisponer de unos “planos” que permitan la comunicación entre los distintos miembros que participan enun proyecto de desarrollo de un programa informático.

Además, muy a menudo ocurre que transcurrido un tiempo, el usuario del programa desea hacer mejoraso ampliaciones pero por desgracia, la estructura de una base de datos o de un programa no son tan visiblescomo los muros de una casa. La existencia de los planos o diagramas que expliquen como se hizo unabase de datos pueden ser de gran ayuda para tales mejoras. Es de esperar que un cliente con visión defuturo exija una cierta documentación de como se hizo un programa.

Por otro lado, un problema habitual para los desarrolladores informáticos es la necesidad de conocer unpoco el “dominio de conocimiento”. Es decir, si se necesita construir una base de datos que almaceneinformación sobre impuestos, es muy probable que primero se necesite conocer un poco más comofunciona ese mundo. El conocimiento que se obtenga junto a los requisitos que tenga el cliente deberíanplasmarse de alguna forma en un documento.

Al igual que la construcción de una casa requiere la elaboración de planos y cálculos el diseño de unaestructura de almacenamiento de datos requiere que exista un mecanism

Los diagramas como “planos” de una BBDD. Se utilizarán para que despues todo el mundo puedaentender qué hay dentro de la BBDD por si se necesita modificar algo.

Un problema fundamental es el dominio de conocimiento. Como informáticos debemos intentaradquirir el conocimiento del cliente para reflejarlo en una BD. En esencia debemos intentar extraerla información RELEVANTE

Otro problema de gran importancia es la ambigüedad. Se debe concretar al máximo el significadode cada palabra o término específico.

En suma, un informático va a construir un modelo del conocimiento del cliente. La primera labora la hora de construir una BD es modelar la información.

5

Page 10: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Las reuniones, el proceso de desambigüación etc... deben plasmarse en una especificación derequisitos. Un ejemplo de especificación es el estándar IEEE 830, que ofrece una plantilla quepodemos rellenar.

Una vez hecha la especificación, se procederá a la realización del diseño, modelo, plano...

2.2 Modelado de datos

Al hacer un modelo, plano o diagrama de datos, se hacen abstracciones

• Hay que reseñar que no seremos expertos en el campo de aplicación.

• Es muy importante utilizar un lenguaje estándar.

• Los distintos SGBD (Sistemas Gestores de Bases de Datos) son muy distintos entre sí

◦ Por ejemplo, tenemos SGBD monolíticos: se caracterizan por utilizar un solo programade uso y administración de los datos.

◦ También existen los SGBD basados en un modelo cliente/servidor. Los SGBDcliente/servidor hacen que los datos estén gestionados por un solo programa llamadoservidor. A dichos datos pueden acceder montones de clientes.

• Debido a estas diferencias el proceso de crear tablas necesita pasar por varias fases.

◦ Primero se debe hacer el llamado modelo conceptual. Este modelo consiste en hacer losdiagramas que reflejan los requisitos.

◦ Despues haremos el llamado diseño lógico que consiste en crear una estructura de tablasy claves.

◦ Por último se debe hacer el diseño físico que consiste en pasar el diseño lógico a unlenguaje SQL de un SGBD.

2.3 Modelos E/R

El modelo E/R se remonta al año 1970, cuando Peter Chen observó lo complejo que era diseñar una basede datos. Decidió crear un lenguaje común de símbolos que facilitara la comunicación.

2.3.1 Entidades

Se denomina entidad a cualquier elemento sobre el cual se desea almacenar información. Aunque lasentidades suelen corresponderse con los sustantivos, no todos los sustantivos merecen ser entidades queaparezcan en el modelo.

Las entidades pueden ser fuertes o débiles:

Una entidad fuerte es aquella que existe por sí sola.

Una entidad débil es aquella que requiere que otra entidad exista antes que ella.

Supongamos una situación como “empleado realiza viajes”. No se puede almacenar nada sobre un viajesi previamente no existe un empleado que lo realice.

En la debilidad hay dos posibilidades:

6 Capítulo 2. Diseño de bases de datos

Page 11: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Dependencia en existencia: una entidad es débil con dependencia en existencia si antes de poder“existir” o “almacenarse” requiere que otra entidad exista primero.

Dependencia en identificación: la entidad débil no tiene clave propia sino que necesita la de otraentidad.

2.3.2 Ocurrencia de entidades

En general una misma entidad puede ocurrir muchas veces, se dice que una entidad puede tener “variasinstancias”. Una entidad “Coche” puede tener infinitas instancias como “Seat Ibiza CR-2785-X”, “Re-nault 21 8765-CWG”, ...

2.3.3 Interrelaciones

Son asociaciones entre entidades. En general se corresponden con los verbos. Se representan por mediode un rombo con el nombre dentro.

Las interrelaciones pueden ser entre distintos conjuntos de entidades

Interrelación binaria: se da entre dos entidades.

Interrelación ternaria: se da entre tres entidades que participan de forma simultánea en una aso-ciación.

Interrelación n-aria.

Interrelaciones reflexivas: se dan entre una entidad y sí misma. El principal ejemplo se da en lasentencia “unos empleados son jefes de otros”.

2.3.4 Participación o “cardinalidad”

En las bases de datos se debe reflejar el hecho de que las entidades puedan aparecer 0 veces, 1 vez,muchas veces.

En el diagrama, se ponen las cardinalidades junto a la entidad y con el mínimo y el máximo.

Para abreviar,a veces se ponen los máximos encima de la relación y tendremos entonces relaciones comoesta:

“Uno a uno” o 1:1

“Uno a muchos” o 1:n

“Muchos a muchos” o m:n

Ejercicios: hacer el diagrama E/R con entidades, debilidades, interrelaciones y cardinalidades de lossiguientes supuestos.

Hombre se casa con mujer en sociedad monógama

Hombre se casa con mujer en sociedad polígama masculina

Hombre se casa con mujer en sociedad polígama mixta

Pescador pesca pez

Arquitecto diseña casa

2.3. Modelos E/R 7

Page 12: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Piezas forman producto

• Téngase en cuenta que producto no es débil. Esto se debe a que podemos almacenarun producto en la base de datos indicando un hecho:”el producto P1 está hecho de lostornillos T1, T2 y T3”. Este hecho se puede almacenar aunque no tengamos físicamentelos tornillos. A la hora de comprobar la “debilidad” de una entidad se debe ver si pode-mos almacenar los hechos o no, sin pensar en productos o piezas físicos.

Turista visita hotel

Jugador juega equipo

Político gobierna país.

Autor publica libro en una editorial

• Podría verse la solución a esto como tres relaciones

1. Autor escribe libro

2. Libro es editado por editorial

3. Editorial publica libro

• O como una relación ternaria: “Autor publica libro en editorial”

• En resumen, ante las dudas debemos fabricar un diagrama que recoja las posibles asocia-ciones: supongamos que conocemos los siguientes hechos

◦ Cervantes escribe Quijote

◦ Cervantes escribe Novelas Ejemplares

◦ Iván López escribe Bases de datos

◦ M.J Castellanos escribe Bases de datos

◦ Ed. Anagrama publica Quijote

◦ Ed. Anagrama publica Novelas ejemplares

◦ Ed. Garceta publica Bases de datos

◦ Ed. Garceta publica Lazarillo

• Si en las distintas asociaciones encontramos que hay asociaciones rígidas que no puedencambiar, deberemos usar una interrelación ternaria: Cervantes escribe Quijote es un hechoque no puede variar. Es cierto que la otra asociación sí puede cambiar: por ejemplo, mañanaEd. Anagrama publica Bases de datos. Aunque esto es un hecho flexible la relación “Autor-Libro” no puede cambiarse.

2.3.5 Atributos

Se denomina atributos a propiedades de las entidades que tienen la importancia suficiente para almacenardatos sobre ellas y que por tanto aparecerán en el diagrama E/R.

Los atributos suelen tener un dominio: es decir un conjunto de valores del cual toman su información.

Un detalle sutil pero importante es que en ocasiones los atributos no son de las entidades, sino de lasrelaciones. Ej: pensemos en el diseño E/R de un enunciado como este: “un mecanico repara un coche enuna cierta fecha del año”. La información de un conjunto cualquiera de mecánicos podría ser la siguiente

8 Capítulo 2. Diseño de bases de datos

Page 13: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

DNI Nombre Ap1 Ap210 Juan Sanz Alcolea20 Luis Sanz Ruiz30 Angel Diaz Diaz

Es evidente que no podemos poner la fecha de reparación en la entidad Mecánico: un mecánico solopodría reparar coches una vez en su vida, ya cada mecánico solo aparece una vez en la tabla.

Por otro lado, supongamos la siguiente información sobre un conjunto de coches.

Matric. Marca Modelo2211-ZW Seat Ibiza4324-EE Renault 215421-AA VW Bora

La fecha de reparación tampoco es de la entidad Coche: si lo hiciéramos así un coche solo podríarepararse una vez en la vida (recordemos que cada coche solo aparece una vez en la tabla).

A veces, hay atributos que se comportan de una forma especial y esto se recoge en el diseño E/R

Atributos opcionales: pueden aparecer o no.

Atributos monovaluados: solo toman un valor (o como mucho dos, por exclusión).

Atributos compuestos: por ejemplo, el campo “Dirección”.

Atributos derivados: son aquellos que pueden obtenerse o calcularse a partir de otros. Por ejemplo,el campo “Edad” puede obtenerse a partir de “Fecha de nacimiento”.

2.3.6 Entidades débiles

Ya hemos dicho que hay entidades que muestran “debilidad” en base a dos características

Dependencia en existencia: por ejemplo la entidad “Transacción” es débil con respecto a “CuentaCorriente”. En este caso, la interrelación llevará una letra E, para indicar este tipo de dependencia.

Dependencia de identificación: en este caso, la relación utiliza una I para señalar la dependencia.

Ejercicio:¿Qué tipo de relación existe entre estas entidades?

1. Toro y Ganadería: Toro es débil (dependencia en identificación) con respecto a Ganadería.

2. Una empresa tiene un párking y desea almacenar qué empleados aparcan vehículos y los datossobre los mismos.

2.3.7 Ejercicio de modelado

Enunciado

Analiza el siguiente enunciado y transfórmalo en DOS diagramas E/R. ¿Qué características reflejan unou otro modelo?:

Una serie de empresas con CIF, Nombre y Dirección desean ofrecer beneficios a sus clientes. Estosbeneficios tienen un código, una descripción y una limitación. Para obtener dichos beneficios se emitenuna serie de cupones que tienen un código y una fecha de vencimiento

2.3. Modelos E/R 9

Page 14: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Solución

No hay una solución correcta a partir de lo que nos dicen. Podemos pensar en dos posible situaciones

1. Las empresas son libres de ofrecer beneficios, modificarlos y hacer cambios cuando lo deseen.

2. Los beneficios solo los inserta un administrador y las empresas deben ceñirse a la lista de posibil-idades que les ofrecemos

Esto da lugar a que el ejemplo a) sea un modelo en el que se da libertad mientras que el modelo b) seamás rígido, lo que a su vez condiciona el modelo

Podemos por tanto contemplar dos soluciones a partir de las tres entidades Empresa, Beneficio y Cupón

1. Dos relaciones binarias

Relación “Empresa ofrece Beneficio”

Relación “Beneficio Se Plasma En Cupón”

2. Una sola relación ternaria “Empresa con Beneficio con Cupón”

Imaginemos las tres tablas, Empresa, Beneficio y Cupón.

CIF Nombre DireccionE1 ACME C/ GuadianaE2 XYZ Sport C/ Rio

Codigo Desc. Limit.B1 10 % desc. Excepto rebajasB2 5 % desc L a J

Codigo Fecha vencC1 31-12-2011C2 20-12-2011

A la hora de insertar en la tabla Beneficios puede que si damos libertad acaben apareciendo filas iguales

Codigo Desc. Limit.B1 10 % desc. Excepto rebajasB2 5 % desc L a JB3 5 % desc L a JB4 5 % desc L a JB5 15 % desc. Solo S y D

En este caso, B2, B3 y B4 corresponden a los beneficios que ofrecen distintas empresas, dando la casu-alidad de que sus descripciones son iguales. Hay quien considera eso un problema, pero no lo es, sinoque es una consecuencia de la libertad que tienen las empresas para actuar.

2.3.8 Herencia

En ocasiones hay objetos que comparten muchas propiedades y características. Supongamos el ejemplode una base de datos para la DGT. Para cada coche puede haber un dueño, pero este dueño puedeser una persona física o jurídica. Si construyéramos dos entidades separadas “Persona” y “Empresa”descubriríamos que tienen cosas en común.

Para ahorrar esfuerzos se puede poner una sola entidad general que “ofrece” los atributos, y dichosatributos son “heredados” por las entidades hija.

10 Capítulo 2. Diseño de bases de datos

Page 15: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Cuando hay una herencia es posible que una instancia pueda ser de dos entidades a la vez. Por ejemplo,pensemos en las entidades “Personal Contratado” y las entidades “Estudiante” y “Trabajador”. ¿Puedeuna persona ser estudiante y trabajador a la vez? SÍ . Por lo tanto esta herencia no es disjunta, pero la de“Persona” y “Empresa” sí es disjunta.

Las herencias disjuntas se marcan con una “d” en el triángulo.

2.3.9 Relaciones exclusivas

A veces dos entidades pueden ir relacionadas por dos o más conexiones. En determinadas situacionesinteresa restringir el número de conexiones posibles.

Por ejemplo, cuando una entidad “Cliente” se conecta con una entidad “Piso” puede hacerlo por mediode varias relaciones como “Vende”, “Alquila”, “Hace leasing”. Si por ejemplo la ley obligase a que solopodamos tener un tipo de relación, se pondrá un arco en las relaciones.

2.4 Problemas

2.4.1 Problema 1: Comunidades de vecinos

Una firma de abogados dedicada a la administración de fincas desea tener una base de datos para ges-tionar la información de sus clientes.

La firma tiene varios abogados y cada uno de ellos ejerce de administrador de una o más comunidades devecinos, por lo que cobra unos honorarios anuales. Una comunidad es gestionada por un único admin-istrador (nombre, DNI y número de colegiado). Las funciones de un administrador consisten en llevar lacontabilidad de la comunidad, gestionar los recibos y realizar los pagos a las distintas compañías (luz,agua...)

De las empresas que cada comunidad tiene contratadas se guarda su nombre, dirección, teléfono y unapersona de contacto. Además puede interesar tener agrupadas a las compañías por sectores (sector deseguridad, sector de suministros básicos).

De cada comunidad se almacena un código, su nombre, calle, código postal y población. Cada co-munidad consta de una serie de propiedades que pueden ser de tres tipos (vivienda particular, local yoficina). Cada propiedad se caracteriza por un número de portal, planta y letra, un nombre y apellidosdel propietario, un porcentaje de participación en los gastos de la comunidad así como los datos de lacuenta bancaria en la que el propietario desea se le domicilie el pago de los recibos.

Si el propietario no habita en su propiedad se necesitan sus datos (nombre, apellidos, dirección y telé-fono) y los datos de su inquilino (nombre apellidos y teléfono).

Si la vivienda es particular se guarda el número de habitaciones. Si es un local se almacena el tipo delocal y el horario. Si es una oficina se guarda la actividad a la que se dedica.

Cada comunidad tiene un presidente (nombre, apellidos y propiedad de la que son dueños) y variosvocales que tratan con el administrador los asuntos que hay que tratar. Cada comunidad tiene una cuentaen un banco y todo banco tiene un código y un nombre comercial. De las cuentas se almacena el códigode cuenta (que consta de un código de sucursal, dos dígitos de control y un número de cuenta) y unsaldo. Para identificar la cuenta hay que añadir el código del banco al código de cuenta.

Se almacenan dos tipos de apuntes:

2.4. Problemas 11

Page 16: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

El banco emite los recibos, pero el administrador guarda información sobre dichos recibos comoel número de recibo, fecha, importe y si se ha podido cobrar o no. Esta última información senecesitará para llevar un registro de impagados.

En cuanto a los apuntes sobre gastos se tienen los importes que cobran las empresas contratadaspor cada comunidad. Las compañías cobran sus recibos (número, fecha e importe) cargándolo enla cuenta de la comunidad

2.4.2 Solución al problema 1

0. Leer el enunciado completo

1. Leer otra vez examinando cuidadosamente los elementos siguientes

1.1. Sustantivos: entidades/atributos

1.1.1. Abogado/administrador

1.1.2. Finca/propiedad

1.1.2.1. Num de portal

1.1.2.2. Planta y letra

1.1.2.3. ¿Nombre y ap del propietario?

Esta frase nos dice que el nombre y el apellido del propietario deben estaraqui. Sin embargo, si luego resulta que necesito una entidad Propietario, yano podremos poner a esa entidad estos atributos. Si resulta que luego necesi-tamos esta entidad habremos descubierto que “Propiedad” es débil por iden-tificación.

1.1.2.4. Porcentaje de particip.

1.1.2.5. Datos cuenta bancaria

1.1.3. Comunidad de vecinos

1.1.3.1. Nombre

1.1.3.2. Calle

1.1.3.3. CP

1.1.3.4. Población

1.1.4. Honorarios

1.1.5. Funciones

1.1.6. Pagos

1.1.7. Compañía

1.1.8. Empresa

1.1.8.1. Nombre

1.1.8.2. Dirección

1.1.8.3. Teléfono

1.1.8.4. Persona de contacto

12 Capítulo 2. Diseño de bases de datos

Page 17: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

1.1.9. Sector

1.1.10. Tipos

1.1.10.1. Viv. particular

Num. habitaciones

1.1.10.2. Local

Tipo

Horario

1.1.10.3. Oficina

Actividad

1.1.11. Propietario

1.1.1. Nombre

1.1.2. Apellidos

1.1.3. Dirección

1.1.4. Teléfono

Al leer que un propietario tiene que aparecer como entidad, descubrimos que“Propiedad” va a ser débil, como supusimos.

1.1.12. Inquilino

1.1.12.1. Nombre

1.1.12.2. Apellidos

1.1.13. Presidente

1.1.13.1. Nombre

1.1.13.2. Apellidos

1.1.13.3. Propiedad que poseen

1.1.14. Vocales

1.1.15. Cuenta

1.1.15.1. Código de cuenta

1.1.15.2. DC

1.1.15.3. Número de cuenta

1.1.15.4. Código de banco

Como nos dicen que se necesita el código del banco para poder“identificar” esta entidad, hemos descubierto que esta entidad es dé-bil

1.1.16. Banco

1.1.16.1. Codigo de banco

1.1.16.2. Nombre comercial

2.4. Problemas 13

Page 18: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

1.1.17. Apunte

1.1.17.1. Recibo (cobramos a prop)

Número de recibo

Fecha

Importe

¿Se pagó?

1.1.17.2. Pago (pagamos luz..)

Número de recibo

Importe

Fecha

1.2. Verbos: relaciones

1.2.1 “Abogado” administra “Comunidad”

1.2.2 “Abogado” cobra “Honorarios” (¡¡MAL!!, los honorarios no llegarán a ser enti-dad)

1.2.3 “Comunidad” es gestionada por “Abogado” es lo mismo que “administra”

1.2.4 “Administrador” realiza funciones

1.2.5 “Comunidad” contrata “Empresa”

1.2.6 “Comunidad” consta de “Propiedades”

1.2.7 “Propiedad” paga a través de “Cuenta”

1.2.8 “Comunidad” tiene “Presidente”

1.2.9 “Comunidad” posee “Vocales”

1.2.10 “Comunidad” opera con “Cuenta”

1.2.11 “Banco” emite “Recibo”

2.4.3 Problema 2: Ajedrez

La federación de ajedrez desea disponer de una base de datos de todas las partidas que se celebren bajosu auspicio.

Toda partida viene caracterizada por una fecha de inicio, una duración en horas, los jugadores quese enfrentaron y si eligieron blancas o negras

Dos jugadores pueden enfrentarse solo una vez por día, aunque pueden hacerlo muchas veces endías distintos.

Se anota cada movimiento de la partida: número de movimiento, casilla de origen y casilla dedestino. Si se borra una partida se deben borrar los movimientos

Las partidas no pueden ser aplazadas

De los jugadores se recogen sus nombres, apellidos, dirección postal y electrónica, la federacióna la que pertenecen y el número de federado (que es único en cada federación)

14 Capítulo 2. Diseño de bases de datos

Page 19: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Solo hay una federación por país con un nombre, país al que pertenece, telefono de contacto yfecha de fundación

Una federación no se clausura hasta que no se reasignan sus jugadores a otra federación.

Las partidas pueden darse o no dentro de un torneo. Los torneos tienen un nombre, periodicidad,fecha de creación y son organizados por una federación

En cada edición de un torneo (número ordinal) se registran todos los enfrentamientos y el nombredel ganador. También se anotan la fecha de inicio, la cuantía del premio y el jugador que lo gana

Si desaparece una federación, desaparecen los torneos y por ende, sus ediciones

Aunque un jugador desaparezca, sus partidas no deben desaparecer

2.4.4 Solución al problema 2 (ajedrez)

Entidades

Federación

• Atributo:nombre

• Atributo:país

Partida

• Atributo:fecha de inicio

• Atributo:duración

• Atributo:¿blancas?

Jugador

• Atributo:nombre

• Atributo:apellidos

• Atributo:dirección postal

• Atributo:dirección electrónica

• Atributo:num de federado

Movimiento

• Atributo:número

• Atributo:casilla origen

• Atributo:casilla destino

Torneo: es una entidad débil ya que nos dicen “Si desaparece una federación, desaparecen lostorneos y por ende, sus ediciones”. Por tanto, no puede haber torneo en la BD sin que haya unafederación que lo organice.

• Atributo:nombre

• Atributo:periodicidad

• Atributo:fecha

2.4. Problemas 15

Page 20: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

• Atributo:ganador

• Atributo:fecha inicio

• Atributo:periodicidad

• Atributo:cuantía del premio

Relaciones

Se necesita una relación para conectar los jugadores con partida: “Partida enfrenta jugadores”. Elenfrentamiento tiene atributos

• También podríamos considerar que hay una sola relación reflexiva “Jugador Se Enfrenta ConJugador”. Esta relación tendría atributos como “fecha de inicio”, “duración”, etc...

“Partida Consta De Movimiento”: aquí se descubre que Movimiento es débil, no puede almace-narse un movimiento sin que esté “dentro de una partida”

“Jugador Pertenece A Federación”.

Hay dos posibilidades de relación

• “Federación organiza Torneo que Consta De Partidas”: relación ternaria

• “Federación organiza Torneo y Torneo Consta De Partidas”: dos binarias

• Dado que se permite que las partidas estén o no dentro de un torneo es más apropiados queel modelo permita cierta libertad para lo cual es más apropiado utilizar las dos binarias.

2.4.5 Problema 3: Formación

El departamento de formación de una empresa desea construir una BD para planificar y gestionar laformación de sus empleados. Los supuestos semánticos que deben recogerse son los siguientes:

La empresa organiza cursos internos de formación de los que se desea conocer el código de curso,el nombre, una descripción, el número de horas de duración y el coste del curso.

Un curso puede tener como prerrequisito haber realizado otro(s) previamente, y, a su vez, la real-ización de un curso puede ser prerrequisito de otros. Un curso que es prerrequisito de otro puedeserlo de forma obligatoria u opcional.

Un mismo curso tiene diferentes ediciones, es decir, se imparte en distintos lugares,fechas y condiversos horarios (intensivo, mañana o tarde). En una misma fecha de inicio sólo puede impartirseuna edición de un curso.

Los cursos se imparten por personal de la propia empresa. Un curso puede tener varios docentespero una edición sólo tiene un profesor.

De los empleados se desea almacenar su código de empleado, nombre y apellidos,dirección, telé-fono, NIF, fecha de nacimiento, nacionalidad, sexo, firma y salario,así como si está o no capacitadopara impartir cursos.

Un mismo empleado puede ser docente en una edición de un curso y alumno en otra edición, peronunca puede ser ambas cosas a la vez (en una misma edición de un curso o lo imparte o lo recibe)

16 Capítulo 2. Diseño de bases de datos

Page 21: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

2.4.6 Solución al problema 3 (Formación)

Entidades

Curso

• Código de curso (clave)

• Nombre

• Descripción

• Duración (número de horas)

• coste

Edición (débil con respecto a curso)

• Lugar

• Fecha: clave

• Horario

Empleado

• Nombre

• Ap

• Dirección

• Tlf

• Dni: clave

• Nacionalidad

• Firma

• Sexo

• Salario

• Capacitado

Relaciones

“Curso Es Prerrequisito De Curso”: muchos a muchos

“Curso Celebra Ediciones”: edición es débil con respecto a curso

“Empleado Imparte Edición”.

“Empleado Sigue Edición”

Nos dicen que no se puede hacer las dos cosas a la vez en una edición, o una u otra.

2.4. Problemas 17

Page 22: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

2.4.7 Problema 4: viveros

Se desea diseñar una Base de Datos para gestionar los empleados y productos a la venta de una cadenade viveros dedicados a la venta de diversos productos relacionados con la jardinería. Los supuestos quehay que recoger en la BD son los siguientes:

La cadena de viveros dispone de varios viveros en la provincia de Madrid identificados por uncódigo de tienda y de los que se almacenará un teléfono, una dirección y un responsable que seráuno de los empleados que trabaja en el vivero (es necesario almacenar durante qué períodos detiempo ha sido responsable cada empleado).

Los productos que se venden tienen asignado un código de producto y nos interesa guardar elprecio y el stock que hay de cada producto en cada uno de los viveros y pueden ser de tres tipos:plantas de las que se guardará su nombre, y una breve descripción de los cuidados que requiere;accesorios de jardinería y artículos de decoración.

Estos productos se distribuyen en zonas dentro de cada vivero cada una de ellas identificadas porun nombre dentro de cada vivero (zona exterior regadío, interior climatizada, zona de caja, etc.).Se desea conocer el stock de cada producto de acuerdo a las zonas del vivero.Los empleadosestarán asignados a una determinada zona en un vivero la cual podrá cambiar a lo largo del tiempo(se guardará histórico de ello) y además, los empleados puedenmo verse de un vivero a otro segúnlas necesidades en distintos períodos de tiempo.

De los empleados se quiere conocer su DNI, su nombre y un teléfono de contacto.En cuanto alproceso de venta de los distintos productos, sólo se almacenarán los pedidos que realizan losclientes pertenecientes al Club VIP que es una promoción especial que permite a los clientesobtener descuentos según las cuantías de sus compras. De estos clientes se almacena su DNI, sunombre, dirección, teléfono y la fecha de incorporación al club así como los datos de sus pedidosque incluyen un número de pedido, la fecha de realización, los productos adquiridos junto con lasunidades y el descuento realizado; por último, también se incluye el precio de los portes en casode que se hayan contratado. De cada cliente se almacenarán todos los pedidos que haya realizadohasta la fecha.En cuanto a estos pedidos de clientes pertenecientes al Club VIP interesa tambiénguardar quién fue el empleado que lo gestionó y en qué vivero se realizó el pedido teniendo encuenta que un pedido en un determinado vivero lo gestiona un único empleado

Solución al problema 4 (Viveros)

Entidades:

Empleado

• DNI

• Nombre

• Número de contacto

Producto

• Código

• Precio

• Stock (más tarde se descubre que stock NO ES UN ATRIBUTO DE PRODUCTO sino deuna relación)

Planta es una entidad hija de Producto

18 Capítulo 2. Diseño de bases de datos

Page 23: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

• Nombre

• Cuidados

Accesorio de jardinería (hija de producto)

Artículo

Viveros

• Código

• Teléfono

• Dirección

• ¿Responsable? No, esto es una relación

Zona

• Nombre (clave)

Pedido

• Número: clave

• Fecha

• Unidades, precio de portes y descuento son atributos de una relación cuyo nombre todavíano conocemos.

Cliente: habrá “Normales” y “Club VIP”

• DNI

• Dirección

• Teléfono

• Fecha de incorporación (solamente para el club VIP)

Relaciones:

“Empleado es Responsable De Vivero” tiene unos atributos de fecha de inicio y fin.

“Producto Se Distribuye en Zona”. Esto implica que hay un atributo en esta relacion llamadoStock.

“Empleado Trabaja en Zona”

A la hora de almacenar el hecho de que un pedido consta de varios productos vamos a descubrirque la relación ternaria no es la mejor opción ya que nos obligaría a que en un pedido solo apun-tásemos un producto

VIP1 Ped1 RosasVIP1 Ped1 TijerasVIP1 Ped1 Semillas

Técnicamente, esta relación ternaria es capaz de guardar el hecho de que el cliente VIP1, en su pedido 1compró “Rosas, Tijeras y Semillas”, sin embargo este modelo ternario nos está obligando a repetir datos.Como conclusión la relación “Cliente hace pedido que consta de Artículos” no es la mejor opción, sinoque desglosaremos esto en dos binarias

“Vip Realiza Pedido” (esto implica que Pedido es débil)

2.4. Problemas 19

Page 24: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

“Pedido consta de Productos”.

Hay una relación a cuatro entre “Pedido” “Cliente VIP”, “Vivero” y “Empleado”

Problema

2.4.8 Problema 5: Universidad

El Departamento de Informática de la universidad necesita una base de datos para almacenar la informa-ción concerniente a los proyectos de investigación tanto actuales como pasados en los que trabajan losprofesores y así poder llevar a cabo una gestiónmás eficiente. La información que se desea almacenarcorresponde a los siguientes supuestossemánticos.

En el departamento los profesores participan en proyectos de investigación caracterizados por uncódigo de referencia único, por un nombre, un acrónimo, un presupuesto total, el programa deI+D que lo financia, una fecha de inicio y una fecha de finalización y una breve descripción de losobjetivos del proyecto.

En los proyectos trabajan profesores del departamento durante un período de tiempo, esdecir, unafecha de inicio y una fecha de fin, pudiendo ocurrir que un profesor trabaje en elmismo proyectoen varias épocas (f_ini, f_fin) diferentes.

Un profesor se identifica por su nombre y apellidos y se caracteriza por su despacho y teléfonoy puede trabajar en varios proyectos simultáneamente y en un proyecto de investigación trabajanvarios profesores. De todos los profesores que trabajan en el proyecto hay uno que es el inves-tigador principal deproyecto que interesa conocer. Es importante tener en cuenta que el profesorinvestigador del proyecto nunca varía a lo largo de la vida del proyecto de investigación.

Los profesores pueden ser doctores o no doctores, de tal manera que un profesor nodoctor siem-pre tiene a un único profesor doctor como supervisor en un momento determinado,interesandoalmacenar los supervisores y períodos de tiempo de la supervisión que ha tenido undeterminadoprofesor no doctor.

En relación con la participación de los profesores en proyectosde investigación, el investigadorprincipal de un proyecto siempre tiene que ser un doctor.Por otro lado, los proyectos de investi-gación producen una serie de publicaciones sobrelas que también interesa guardar información.

Una publicación se caracteriza por un número en secuencia dentro de cada proyecto de investi-gación y se guardará el título y los profesores que la han escrito; las publicaciones son de dostipos, publicaciones en congresos y publicaciones en revista; de las primeras se almacenará elnombre del congreso, su tipo (nacional ointernacional), la fecha de inicio y de fin, el lugar de cel-ebración, país y la editorial que hapublicado las actas del congreso (si es que se han publicado);de las publicaciones en revista interesa saber el nombre de la revista, la editorial, el volumen, elnúmero y las páginas de inicio y fin.

No solamente interesa conocer los profesores que han participado en las publicaciones de losproyectos de investigación sino también las líneas de publicación que cubren estas publicaciones.Una línea de investigación se identifica por un código, un nombre (por ejemplo,recuperación deinformación multilingüe, bases de datos espacio-temporales, etc.) y un conjunto de descriptores(por ejemplo, la línea de investigación bases de datos temporales puede tener como descriptoresBases de Datos, SGBD Relacional, Dimensión temporal).

Los profesores tendrán asociados en la BD las líneas de investigación en las quetrabajan inclu-so podría ocurrir que hubiera profesores que no tuvieran ninguna línea asignada.Así, tanto losprofesores doctores como los no doctores pueden escribir publicaciones sobre una o más líneasde investigación y nos interesa saber sobre qué línea de investigación ha escrito un determinado

20 Capítulo 2. Diseño de bases de datos

Page 25: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

profesor en una publicación, teniendo en cuenta que un profesor queparticipa en una publicaciónsólo escribe en el ámbito de una línea de investigación y que una determinada publicación puedecubrir varias líneas de investigación

Solución: entidades

Proyecto

• código de referencia único

• un nombre

• un acrónimo

• un presupuesto total

• el programa de I+D que lo financia

• una fecha de inicio y una fecha de finalización

• y una breve descripción

Profesor

• Nombre y apellidos.

• Despacho y teléfono.

• ¿Investigador principal hijo de Profesor? Es mala idea porque usaríamos una entidad paraalgo que aparecerá una sola vez.

• “Doctor” y “No doctor” sí pueden ser entidades hija.

Publicación.

• Número

• Título

• Profesores que lo escriben (esto será una relación)

• “En congreso” y “En revista” serán entidades hija.

◦ En congreso tiene los atributos: Tipo, nombre, fecha de inicio, de fin, lugar de cele-bración, país y editorial.

◦ En revista tiene los atributos: nombre de revista, el volumen, editorial, páginas de inicioy fin.

Línea

• Código

• Nombre

Descriptores:

• Nombre

2.4. Problemas 21

Page 26: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Solución: relaciones

“Profesor Participa en Proyecto”

“Profesor dirige proyecto”, esta relación contiene el hecho de que un proyecto tiene 1 y solo 1“investigador principal”.

“Doctor supervisa a No Doctor”

“Doctor dirige proyecto”, esta relación sustituye a la anterior “Profesor dirige proyecto”.

“Proyecto produce publicación”. En realidad, más adelante descubriremos que el Profesor debeintervenir en esta relación dando lugar a una relación ternaria “Proyecto produce publicación enla que interviene profesor”

2.5 Transformación de modelos E/R en modelos relacionales

Toda entidad se convierte en tabla.

Toda entidad débil se convierte en tabla. La clave de esta tabla será la mezcla de la clave del débilmás la clave del fuerte.

En una herencia, la entidad padre se convierte de forma normal. Las hijas heredan la clave delpadre (y en las hijas será además clave ajena).

En las relaciones se trabaja de la siguiente forma

• Si la relación es 1:1 ambas entidades se convierten a tablas Y UNA DE ELLAS TOMA LACLAVE DE LA OTRA que actuará solo como clave ajena

• Si la relación es 1:N el que tiene el N toma la clave del que tiene el 1, que actuará comoparte de la clave primaria además de ser clave ajena. Si además la relación tuviera atributos,estos atributos van en el que tiene el N.

• Si la relación es M:N LA RELACIÓN SE CONVIERTE EN TABLA. La clave de esatabla es la mezcla de las claves de los participantes. Todas ellas actúan además como clavesajenas. Si la relación tiene atributos los ponemos en esta tabla.

2.5.1 Transformación del modelo “Universidad”

Se muestran en negrita las claves primarias.

Publicación (Número, Título)

EnCongreso(Número, Título, Tipo, FechaInicio, FechaFin, Lugar, País, Editorial)

• Clave ajena: La pareja (Número,Título) es clave ajena sobre Publicación(Número,título)

EnRevista(Número, Título, Nombre, Volumen, Editorial, PaginaInicio, PaginaFin)

• Clave ajena: La pareja (Número,Título) es clave ajena sobre Publicación(Número,título)

Línea (CódigoLínea, Nombre)

Descriptor (Nombre)

EntraEn(CódigoLínea, NombreDescriptor)

• Clave ajena: CódigoLínea es clave ajena sobre Línea(Código)

22 Capítulo 2. Diseño de bases de datos

Page 27: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

• Clave ajena: NombreDescriptor es clave ajena sobre Descriptor(Nombre)

Cubre(Numero, Título, CodigoLinea)

• Clave ajena: La pareja (Número,Título) es clave ajena sobre Publicación(Número,título)

• Clave ajena: El atributo CodigoLinea es clave ajena sobre Linea(Codigo)

Profesor (Nombre, apellidos, Despacho, Tlf)

Doctor (Nombre, apellidos)

NoDoctor (Nombre, apellidos, Nombre, apellidos)

• Clave ajena: La segunda pareja (nombre,apellidos) es clave ajena sobre Doctor(Nombre,apellidos). Solo es clave ajena por ser una relación 1:n

Produce (Nombre, apellidos, número, título, código)

• Clave ajena: La pareja (nombre,apellidos) es clave ajena sobre Profesor(Nombre, apellidos).

• Clave ajena: La pareja (número, título) es clave ajena sobre Publicación (número, título)

• Clave ajena: El atributo Código es clave ajena sobre Línea(Código)

Proyecto (CódigoProyecto, Nombre, Acrónimo, Presupuesto, programa, FechaInicio, Fechafin,Descripción, Nombre, apellidos)

• Clave ajena: La pareja (nombre,apellidos) es clave ajena sobre Doctor(Nombre, apellidos).Solo es clave ajena por ser una relación 1:n. Por favor, téngase en cuenta que Doctor es laentidad que intervenía en la relación Y NO LA ENTIDAD PROFESOR.

Participa (Nombre, apellidos, CodigoProyecto)

• Clave ajena: La pareja (nombre,apellidos) es clave ajena sobre Profesor(Nombre, apellidos).

• Clave ajena: El atributo CódigoProyecto es clave ajena sobre Proyecto(CodigoProyecto)

2.5.2 Transformación del modelo “Ajedrez”

Partida (CódigoPartida, FechaInicio, Duración)

Movimiento (CodigoPartida, Numero, CasillaOrigen, CasillaDestino)

• Clave ajena: CodigoPartida es clave ajena sobre Partida(CodigoPartida)

• Inciso: Según la regla de transformación 1:n, codigo partida debe ser ajena. Sin embargo unaregla anterior nos ha dicho que debe formar parte de la clave primaria.

Federacion(Nombre, Pais)

Jugador (Nombre, apellidos, NumeroDeFederado, DireccionPostal, DireccionElectronica, Nom-breFederacion)

• NombreFederacion es clave ajena sobre Federacion(Nombre)

Enfrenta es una relación m:n. Se convierte en tabla. En-frenta(CodigoPartida,Nombre,Ap,Fecha,Vencedor, NombreBlancas, ApBlancas)

• CodigoPartida es ajena sobre Partidas(CodigoPartida)

• La pareja (Nombre, Apellidos) es clave ajena sobre Jugador(Nombre, Apellidos)

• La pareja (NombreBlancas,ApellidoBlancas) es ajena sobre Jugador(Nombre,Apellidos)

2.5. Transformación de modelos E/R en modelos relacionales 23

Page 28: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

• A tener en cuenta: esta tabla ilustra que un diseño mejor habría sido tener dos relacionescomo “Se enfrenta con blancas” y “Se enfrenta con negras”. Aún así, habría que programar

Torneo (NombreTorneo, NombreFed, Periodicidad,Cuantia)

• NombreFed es clave ajena sobre Federación(Nombre)

Edición (Numero, NombreTorneo,NombreFed, Nombre,Apellidos)

• La pareja (NombreTorneo,NombreFed) es clave ajena sobre Tor-neo(NombreTorneo,NombreFed)

• La pareja (Nombre,Apellidos) es clave ajena sobre Jugador(Nombre,Apellidos)

En este punto debemos reformar la tabla partida y dejarla así

• Partida (CódigoPartida, FechaInicio, Duración, NumeroEdicion, NombreTorneo, Nom-breFederacion)

◦ El trío (NumEdicion, NombreTorneo, NombreFederacion) es clave ajena sobre Edi-cion(NumEdicion,NombreTorneo,NombreFederacion)

2.5.3 Transformación del modelo “Fincas”

Resuelto en la pizarra

2.6 Normalización

Cuando se elabora un diseño de tablas, puede ocurrir que aún siga existiendo mucha redundancia.

Al diseñar tablas se debe examinar la redundancia que pueda existir en ellas. Estas redundancias suelenser visibles a simple vista: Dado un CP se puede deducir la Población. Estas redundancias adoptan unnombre muy concreto: “Dependencias funcionales” o tambien “implicaciones”.

A veces, las dependencias funcionales implican varios campos en conjunto. Supongamos una tabla comoesta

Jugador (Nombre,ap, país)

Cuando en una dependencia hay varios atributos se le llama “dependencia funcional completa”.

Para eliminar una dependencia crearemos una tabla separada de la tabla inicial.

En la nueva tabla pondremos estos campos: el implicante y los implicados

La clave de la nueva tabla es el implicante (o los implicantes, si hay varios)

En la tabla vieja se borran todos los implicados y el implicante se convierte en clave ajena sobrela nueva tabla.

Recordemos un par de conceptos

Clave primaria: es un atributo (o pareja, o trío) que sabemos que no se va a repetir.

Clave candidata: es un atributo (o pareja) que PODRÍA ACTUAR COMO PRIMARIA pero nolo hemos elegido.

Transitividad: supone que si “A implica B” y “B implica C”, automáticamente sabremos que “Aimplica C”.

24 Capítulo 2. Diseño de bases de datos

Page 29: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

2.6.1 Primera forma normal (1FN)

Una tabla está en 1FN si en cada atributo se prohíbe insertar más de un valor.

2.6.2 Segunda forma normal (2FN)

Una tabla está en segunda forma normal si todos los campos tienen dependencia completa respecto a laclave.

Supongamos una base de datos con una tabla como esta

Compras(CodigoProveedor, CodigoProducto, NombreProducto, Cantidad).

En esta tabla es fácil observar que hay una dependencia funcional pero no completa con respecto a laclave (CodigoProveedorProducto) sino parcial, de la forma “CodigoProducto implica Nombre”.

2.6.3 Tercera forma normal

Una tabla está en 3FN si está en 2FN y además no hay dependencias transitivas.

2.6. Normalización 25

Page 30: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

26 Capítulo 2. Diseño de bases de datos

Page 31: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 3

Diseño físico

3.1 Introducción

En general hemos seguido un proceso lógico en la creación de bases de datos.

Tema 1: posibilidades

Tema 2: Diseño

• Análisis

• Diseño E/R

• Pasar a un diseño relacional

• Normalización.

3.2 BBDD en Access

3.2.1 Interfaz “Vista de tabla”

Access crea automáticamente un campo llamado ID que actuará como clave primaria. Este campo IDusará un tipo llamado “Autonumérico”

Dicho campo es un número entero

Se incrementa automáticamente cuando pasamos a otra fila.

3.2.2 Tipos de datos

Access maneja los siguientes tipos de datos

Texto

Acepta letras, números y símbolos pero solo acepta conjuntos de longitud pequeña (255 símbolos máx-imo)

27

Page 32: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Memo

Acepta textos largos.

Fecha

Permite manejar datos relacionados con las fechas y con sus intervalos.

Numéricos

Se reservan para datos para los cuales se deseen hacer operaciones matemáticas.

Los tipos numéricos pueden concretarse aún más:

Byte: acepta números en el rango 0-255

Enteros cortos: números entre -32767 y 32768

Entero largo: a nivel interno usa 32 bits (-2^32 hasta 2^32)

Decimal: acepta decimales al nivel que le indiquemos

Simple: acepta negativos y decimales de precisión variable

Doble: acepta negativos y decimales muchísimo más grandes y con mucha más precisión decimal

En general la regla es: “cuanto más corto es el campo más rápido se procesa pero menos precisiónnos ofrece”

Sí/No

Reservado para valores lógicos (True/False)

3.2.3 Formato de datos

Se puede controlar la apariencia que tendrán los datos en pantalla, por ejemplo, una fecha puedemostrarse como 13-2-85 o 13/2/1985

Formato de la fecha

En el formato de la fecha se deben usar tres símbolos básicos para manejar el día, el mes y el año,queson respectivamente d, m y a

d-m-aa: Escribe algo como “1-3-81”

d-m-aaaa: Escribe algo como “1-3-1981”

d-mmm-aaaa: Escribe algo como “1-Mar-1981”

dd-mmm-aaaa: Escribe la fecha “01-Mar-1981”

dd-mm-aaaa: Escribe la fecha “01-03-1981”

dd/mm/aaaa: Escribe la fecha “01/03/1981”

dd/mmmm/aaaa: Escribe la fecha “01/Marzo/1981”

28 Capítulo 3. Diseño físico

Page 33: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

hh:nn:ss dd/mmmm/aaaa: Escribe la fecha “01/Marzo/1981”

Formato de cadenas

El único símbolo de interés es @

@@-@@ Coge los dos primeros símbolos y los separa de los dos segundos por un guión.

“Desde “@@” hacia “@@

Formato de números

Para modificar la presentación de los números se utiliza el símbolo 0.

000,00: “Extraer un número de 3 cifras, si no lo hay se rellena con ceros, y despues extraer dosdecimales. Mostrar eso en pantalla”

3.2.4 Máscaras de entrada

Hemos dicho que el formato cambia la forma de mostrar las cosas en pantalla (pero no cambia comose almacenan por dentro). Sin embargo a veces es importante intentar evitar que se almacenen datosincorrectos.

Para indicar a Access las máscaras de entrada, usaremos estos códigos

0: Es obligatorio insertar una cifra en esa posición

L: Es obligatorio insertar una letra en esa posición

9: Es optativo insertar algo, pero si se hace que sea una cifra

?: Es optativo insertar algo, pero si se hace que sea una letra

A: Es obligatorio insertar algo aquí y puede ser una letra o un número.

a: Es optativo insertar algo aquí y puede ser una letra o un número.

Ejercicio: ¿como sería la máscara que permite a la gente insertar su DNI con o sin letra?

Solución: 90000000?

Ejercicio: ¿como sería la máscara de entrada de un campo login que puede tener 3-4 letras seguidas de4-5 números?

Solución: la solución sería ?LLL00009 (se recomienda dejar los elementos optativos en las “esquinas”).

Ejercicio: ¿como sería la máscara de entrada de un campo password, donde se obliga a que la clave tengaentre 8 y 12 símbolos que pueden ser letras o números?

Solución: AAAAAAAAaaaa

3.2.5 Título

El título es lo que se mostrará en los formularios. Se puede dejar en blanco.

3.2. BBDD en Access 29

Page 34: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

3.2.6 Valor predeterminado

Es un valor que Access puede insertar automáticamente para casos de tablas donde la mayor parte deregistros tienen el mismo valor.

3.2.7 Regla de validación

Es más potente que una máscara de entrada, ya que permite indicar condiciones que los datos debencumplir antes de ser insertados

3.2.8 Texto de validación

Es el texto que aparecerá cuando la regla no se cumple.

3.2.9 Requerido

Nos indica si es obligatorio que haya dentro del campo o si por el contrario puede estar vacío.

3.2.10 Indexado

Indica si el campo deberá estar en un índice de la base de datos.

Un índice es un archivo de la base de datos que el programa puede utilizar para hacer búsquedas másdeprisa. No fabricaremos un índice para todos los campos ya que el proceso puede ser lento y ocuparmucho espacio.

Puede ser la primera y mejor opción cuando los usuarios se quejan de que las búsquedas son lentas.

3.2.11 Compresión Unicode

Permite seguir utilizando un solo byte por símbolo a pesar de que utilicemos Unicode (que utiliza de 2a 4 bytes por símbolo)

3.2.12 Modo IME

IME significa Input Method Editor, y especifica los diversos sistemas existentes para introducir símbo-los. Es relevante solamente en casos como el uso de lenguajes asiáticos y similares.

3.2.13 Oraciones IME

Permite controlar como Access va a procesar las frases en otros idiomas.

3.2.14 Etiquetas inteligentes

Permite que Access procese de una forma especial diversos campos. Para ello debemos saber comofunciona el vocabulario XML de Microsoft

30 Capítulo 3. Diseño físico

Page 35: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

3.2.15 Alineación

Controla como aparecerá el texto: alineado a la derecha, a la izquierda, distribuido (justificado).

Los puntos del 11 al 15 no entran en examen.

3.3 Claves ajenas

Access denomina a este concepto “Relaciones”. Para indicar que un campo es clave ajena sobre otro, sedeberá acudir a este menú y arrastrar la clave ajena sobre la primaria.

Se denomina integridad referencial a la capacidad del SGBD de obligar a que los datos de una tablaexistan previamente en otra.

Cuando se conectan campos y la clave ajena es autonumérica se está cometiendo un error, ya que Accessintentaría cambiar los valores sin ceñirse a la clave primaria.

Al crear una clave ajena se pueden hacer cumplir dos condiciones extrar

1. Actualización en cascada: si alguien cambia la clave primaria, el que se marcó como clave ajenacambiará automáticamente

2. Borrado en cascada: si alguien borra el registro de la clave primaria, los registros asociados en laclave ajena se borrarán también. No siempre es necesario hacer esto.

3.4 MySQL

3.4.1 Comandos básicos

Mostrar las bases de datos en el servidor: show databases;

Utilizar una BD a partir de un instante dado: use cdcol;

Mostrar las tablas de una base de datos: show tables;

Mostrar las descripciones detalladas de las tablas: desc user;

3.4.2 Creación de bases de datos y tablas

El comando para crear una base de datos: create database <nombre>. Sin embargo la creación de unatabla es un proceso complejo.

create table publicacion(

numero int,titulo char(60)

);

create table <nombre>(

<campo1> <tipo de datos> ,<campo2> <tipo de datos> ,...

3.3. Claves ajenas 31

Page 36: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

<campo20> <tipo de datos>);

3.4.3 Tipos de datos en MySQL

Los tipos pueden ser

Numéricos: pueden ser unsigned o no

• Enteros: no aceptan decimales

◦ TINYINT: -128,+127 O 0-255

◦ SMALLINT: -32768,32767 O 0-65535

◦ INT -2100 millones, 2100 millones o 0-4200 millones

◦ BIGINT: -2^63, 2^63 o 0-2^64

• Decimales: sí aceptan parte fraccionarios

◦ DECIMAL (c, d): la c indica el total de cifras, y la d indica cuantas de ellas serándecimales. Así, DECIMAL (10,2) nos da un número con 10 cifras en total y dos deellas decimales, es decir, que tenemos 8 cifras para la parte entera.

◦ FLOAT: hasta 8 decimales

◦ DOUBLE: hasta 12 decimales

Cadenas

• CHAR(x): la x indica la longitud máxima de la cadena. Reserva el espacio aunque no seutilice. Son más rápidos de procesar pero gastan más espacio.

• VARCHAR(x): la x indica un máximo que no se puede sobrepasar pero si no se usa esemáximo el resto del espacio está libre. Son más lentos de procesar pero ahorran espacio.

Fechas

• DATE: almacena fechas en formato ‘aaaa-mm-dd’

• TIME: almacena tiempos en formato ‘hh:mm:ss:fraccion’

• DATETIME: almacena fechas junto con las horas en formato predefinido ‘aa-mm-ddhh:mm:ss’

• TIMESTAMP: es una marca de tiempo gestionada por el SGBD y no se puede modificardirectamente.

BLOB: Binary Large OBjects.

3.4.4 Claves en MySQL

En MySQL se pueden definir indicaciones de como se va a comportar un campo:

Clave primaria: es un conjunto de campos cuyo valor no se puede repetir. Además, las clavesprimarias suponen crear automáticamente un índice.

32 Capítulo 3. Diseño físico

Page 37: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

use ... ;drop table empleado;create table empleado(

dni char(10),nombre varchar(60),apellidos varchar(120),primary key (dni)

);

insert into empleado values(

"5678123W", "Juan", "Ramirez Diaz");

insert into empleado values(

"1234567Z", "Angel", "Sanchez Gomez");

insert into empleado values("5678123W", "Tomas", "Perez Perez");

select * from empleado;

Una alternativa sería utilizar como clave primaria un conjunto de campos

create table empleado(

dni char(10),nombre varchar(60),apellidos varchar(120),primary key (nombre, apellidos)

);

insert into empleado values(

"10X", "Juan", "Gomez Gomez");

insert into empleado values(

"10X", "Angel", "Gomez Gomez");

insert into empleado values(

"20Z", "Juan", "Gomez Gomez");

Clave ajena

create table empleado(

dni char(10),

3.4. MySQL 33

Page 38: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

nombre varchar(60),apellidos varchar(120),primary key (nombre, apellidos)

);create table sueldos(

dni char(10),sueldo decimal(6,2),primary key (dni),foreign key (dni) references empleado (dni)

);

insert into sueldos values ("99Q", 950.45);

insert into sueldos values ("1234567Z", 950.45);insert into sueldos values ("5678123W", 1430.91);

3.4.5 Resolución del modelo de tablas “Universidad”

create table publicacion(

titulo varchar(100),numero smallint,primary key (titulo, numero)

);

create table encongreso(

titulo varchar(100),numero smallint,fechainicio date,fechafin date,primary key (titulo, numero),foreign key (titulo, numero)

referencespublicacion (titulo,numero)

);

create table enrevista(titulo varchar(100),numero smallint,nombre varchar(50),volumen smallint,editorial varchar(30),paginainicio smallint,paginafinal smallint,primary key (titulo,numero),foreign key (titulo,numero)

references publicacion(titulo,numero));

create table lineas(

codigolinea smallint unsigned,nombre varchar(200),

34 Capítulo 3. Diseño físico

Page 39: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

primary key (codigolinea));

create table descriptor(

nombre varchar(220),primary key (nombre)

);

/* Ojo: esta definición estaría malporque los tipos de datos no coinciden.

-O codigolinea arriba es un int, o aquilo cambiamos para que sea smallint unsigned-O nombre lo ponemos aqui como varchar(200)o arriba ponemos varchar(80)*/

create table entraen(

codigolinea int,nombredescriptor varchar(80),primary key (codigolinea, nombredescriptor),

);

create table entraen(

codigolinea smallint unsigned,nombredescriptor varchar(220),primary key (codigolinea, nombredescriptor),foreign key(codigolinea)

references lineas(codigolinea),foreign key (nombredescriptor)

references descriptor(nombre));

create table cubre(

numero smallint,titulo varchar(100),codigolinea smallint unsigned,primary key (numero, titulo, codigolinea),foreign key (titulo, numero)

references publicacion(titulo, numero),foreign key (codigolinea)

references lineas(codigolinea));

create table profesor(

nombre varchar(80),apellidos varchar(140),despacho smallint unsigned,tlf varchar(25),primary key (nombre, apellidos)

);

create table doctor

3.4. MySQL 35

Page 40: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

(nombre varchar(80),apellidos varchar(140),primary key (nombre, apellidos),foreign key (nombre, apellidos)

references profesor(nombre, apellidos));

create table nodoctor(

nombre_nodoctor varchar(80),apellidos_nodoctor varchar(140),nombre_doctor varchar(80),apellidos_doctor varchar(140),primary key (nombre_nodoctor, apellidos_nodoctor),foreign key (nombre_nodoctor, apellidos_nodoctor)

references profesor(nombre, apellidos),foreign key (nombre_doctor, apellidos_doctor)

references doctor(nombre, apellidos));

create table produce(

nombre varchar(80),apellidos varchar(140),titulo varchar(100),numero smallint,codigo smallint unsigned,primary key(nombre, apellidos, titulo, numero, codigo),

foreign key (nombre, apellidos)references profesor(nombre, apellidos),

foreign key (titulo, numero)references publicacion(titulo, numero),

foreign key (codigo)references lineas(codigolinea)

);

create table proyecto(

codigo int,nombre varchar(150),acronimo varchar(15),primary key (codigo)

);

create table participa(

nombre varchar(80),apellidos varchar(140),codigoproyecto int,primary key(nombre,apellidos,codigoproyecto),

foreign key (codigoproyecto)references proyecto(codigo),

foreign key (nombre,apellidos)references profesor(nombre,apellidos)

36 Capítulo 3. Diseño físico

Page 41: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

);

3.4.6 Resolución del modelo “Ajedrez”

create database ajedrez;use ajedrez;

create table partida(

codigo int,fechainicio date,duracion_minutos smallint unsigned,primary key (codigo)

);

create table movimiento(

codigopartida int,numero int,casillaorigen char(2),casilladestino char(2),primary key (codigopartida, numero),foreign key (codigopartida)

references partida(codigo));

create index ind_casilla_origenon movimiento(casillaorigen);

create table federacion(

nombre varchar(120),pais varchar(80),primary key (nombre)

) tablespace ts_disco_lento;

create table jugador(

nombre varchar(80),apellidos varchar(140),email varchar(120),dir_postal varchar(200),num_federado int,nombre_federacion varchar(120),primary key (nombre,apellidos),foreign key (nombre_federacion)

references federacion(nombre)) tablespace ts_disco_lento;

3.5 Tablespaces y undo files

Los “undo files” son archivos utilizados por el SGBD para anotar los hechos que van teniendo lugar(por ejemplo, al sacar dinero de un cajero). Si algo va mal, el SGBD puede utilizar dichos ficheros para

3.5. Tablespaces y undo files 37

Page 42: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

deshacer operaciones por completo.

Para crear un log en la base de datos que se almacene en un archivo “logs” en un directorio cualquierapodemos hacer lo siguiente. Además, es obligatorio elegir un sistema interno a utilizar al almacenar losdatos. Hoy en día, el “engine” InnoDB es el más actual.

create logfile group logs_ajedrezadd undofile "D:\oscar\logs"engine=innodb;

create tablespace ts_disco_lentoadd datafile "d:\oscar\lento"use logfile group logs_ajedrezengine=InnoDB;

38 Capítulo 3. Diseño físico

Page 43: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 4

Consultas SQL

4.1 Introducción

Las consultas, las sentencias SELECT se apoyan sobre dos conceptos matemáticos denominados

Algebra relacional

Cálculo relacional

4.2 La sentencia SELECT

La sentencia SELECT permite extraer datos de las tablas en base a condiciones muy diversas.

La operación más básica que podemos hacer es extraer todo lo que hay en una tabla.

select * from proveedores;select * from partes;select * from proyectos;select * from suministra;

4.3 Condiciones

Muy a menudo no necesitaremos mostrar todos los datos de la tabla, sino solo algunos que se especifi-carán mediante condiciones.

Si por ejemplo, deseáramos mostrar solo los proveedores cuya ciudad sede está en París haríamos algocomo esto.

select * from proveedoreswhere ciudad="Paris";

Otra posible consulta sería “mostrar todos los proveedores cuyo estado tiene el codigo 10”

select * from proveedoreswhere estado=10;

39

Page 44: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Las condiciones pueden ser muy complejas, y se pueden construir utilizando los operadores AND y OR.

Por ejemplo, “mostrar todos los proveedores cuya ciudad es Londres y su estado es 10”.

select * from proveedoreswhere ciudad="Londres"and estado=10;

Mostrar las partes cuyo peso es mayor de 15.

select * from parteswhere peso>19;

No es obligatorio mostrar todos los campos, se pueden mostrar solamente algunos de ellos indicando sunombre. Por ejemplo, “mostrar el nombre de proveedor y la ciudad de los proveedores cuyo estado sea20”.

select nombreprov, ciudadfrom proveedoreswhere estado=20;

¿Qué mostrará la siguiente consulta?

select nombreprovfrom proveedoreswhere estado<=10

andestado>=20;

La respuesta es que no se muestra nada. La condición está mal escrita ya que no puede haber un númeroque sea menor de 10 y a la vez mayor de 20.

Mostrar los nombres de proveedores cuyo estado sea 10 y su ciudad Paris o Londres.

Un primer intento sería este .. code-block:: mysql

select nombreprov from proveedores where estado=10 and (ciudad=”Paris” or ciu-dad=”Londres”);

Sin embargo, no funciona correctamente. La propia pregunta es ambigua

Una posibilidad es que la pregunta fuera así “Mostrar los nombres de proveedores cuyo estado sea10 y (su ciudad Paris o Londres).”

La otra posibilidad es que la pregunta fuera así “Mostrar los nombres de proveedores cuyo (estadosea 10 y su ciudad Paris) o Londres.”

Es importante recordar que cuando en una condición hay tres o más elementos de comparación puedeque sea necesario utilizar paréntesis.

Mostrar las partes rojas o verdes que pesen 17 o más.

Esta pregunta debe aclararse antes de resolverse.

Mostrar las partes siempre que pesen 17 o más y que luego cumplan una de estas dos: tener colorrojo o tener color verde.

“Mostrar las partes (rojas o verdes) que pesen 17 o más.”

Mostrar las partes que siendo verdes pesen 17 o más o si no que simplemente sean rojas.

40 Capítulo 4. Consultas SQL

Page 45: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

“Mostrar las partes rojas o (verdes que pesen 17 o más).”

Normalmente, se suele asumir la primera pregunta, que se resuelve así

select * from parteswhere peso>=17and(color="Rojo" or color="Verde");

select * from parteswhere(color="Rojo" or color="Verde")and peso>=17;

4.4 Consultas con agregados

Se denomina agregado a alguna función de tipo estadístico aplicada a un subconjunto de los datos deuna tabla.

4.4.1 Recuento

La función COUNT nos dice cuantas filas cumplen una cierta condición. No es obligatorio poner dichacondición.

select count(*) from partes;

Si deseáramos una condición como por ejemplo “hacer el recuento de partes cuyo color sea Azul”

select count(*) from parteswhere color="Azul";

¿Cuantas partes hay que no sean rojas?

select count(*) from parteswhere color<>"Rojo";

¿Cuantos proveedores hay cuya ciudad sea Londres?

select count(*)from proveedoreswhere ciudad="Londres";

¿Cuantas partes hay que sean rojas y pesen más de 16?

select count(*) from parteswhere peso>16

and color="Rojo";

¿Cuantos proyectos hay en Madrid?

select count(*) from proyectoswhere ciudad="Madrid";

4.4. Consultas con agregados 41

Page 46: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

4.4.2 Promedio

Esta función calcula la media aritmética de las filas que cumplan una cierta condición. Tampoco esobligatorio poner la condición. La función promedio en SQL es AVG(*)

¿Cual es el peso medio de las partes?

select avg(peso) from partes;

¿Cual es la media de peso de la partes azules?

select avg(peso) from parteswhere color="Azul";

4.4.3 Máximos y mínimos

Son operaciones que nos devuelven el valor más grande o más pequeño de entre los que cumplan unacondición.

Estas funciones en SQL son

MAX(campo-numérico)

MIN(campo-numérico)

¿Cual es peso más grande de alguna parte?

select max(peso) from partes;

¿Cual es el peso más pequeño de alguna parte?

4.4.4 Sumas

La operación SUM(campo-numérico) efectúa la suma de ese campo para las filas que cumplan una ciertacondición.

¿Cuantas partes en total ha suministrado el proveedor v1?

select sum(cantidad)from suministrawhere numprov="v1";

¿Cuantas partes ha recibido el proyecto y1?

select sum(cantidad)from suministrawhere numproyecto="y1";

¿Cuantas partes p2 ha suministrado el proveedor v2?

select sum(cantidad)from suministrawhere numparte="p2"

and numprov="v2";

42 Capítulo 4. Consultas SQL

Page 47: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

4.4.5 Múltiples agregados

A veces no se hará una sola operación matemática sino muchas. En ese caso es muy importante indicara SQL en base a qué debe hacer los resultados, o lo que es lo mismo como agrupar antes de hacer lasoperaciones.

En SQL se indicarán los grupos sobre los cuales se va a hacer cada operación mediante la cláusulaGROUP BY (campo). Además, al hacer un “group by” es obligatorio también seleccionar el campo porel que se hacen los grupos.

La respuesta a una pregunta como:”¿Cual es la media de peso de las partes por color?”

select avg(peso), colorfrom partesgroup by (color);

¿Cual es la media de los pesos en función del nombre de parte?

select avg(peso), nombrepartefrom partesgroup by (nombreparte);

¿Cuantos proveedores hay en cada ciudad?

select count(*), ciudadfrom proveedoresgroup by (ciudad);

¿Cual es la media de suministros por cada proveedor?

select avg(cantidad), numprovfrom suministragroup by (numprov);

¿Cual es la media de pesos de las piezas rojas o verdes?

select avg(peso), colorfrom parteswhere color="Rojo"

or color="Verde"group by (color);

4.4.6 Condiciones de los agregados

Los agregados pueden llevar sus propias condiciones:

NO VAN CON EL WHERE

Van por separado utilizando HAVING

El HAVING debe ir despues del GROUP BY

“Mostrar cuantos proveedores hay por ciudad, pero solo cuando haya dos o más”

La primera parte de este ejercicio es igual que el anterior. Sacamos cuantos proveedores hay por ciudad

select count(*), ciudadfrom proveedoresgroup by (ciudad);

4.4. Consultas con agregados 43

Page 48: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Si ahora deseamos mostrar solo aquellos cuyo recuento sea mayor o igual que 2 debemos añadir unacláusula HAVING como esta

select count(*), ciudadfrom proveedoresgroup by (ciudad)having count(*)>=2;

El WHERE es una condición que se aplica antes de hacer los cálculos. Sin embargo, si una vez hechoslos cálculos no deseamos mostrarlos todos deberemos utilizar el HAVING.

“Mostrar cuantos tornillos hay en total”

Al hacer esta consulta se pueden cometer varios errores, como por ejemplo este, que muestra todas laspartes

select count(*), nombreparte from partesgroup by nombreparte;

Esto no es exactamente un error, sino más bien una trampa: se hizo el recuento a mano y se hizo trampa

Error: En este caso se ha confundido el where con el having

select count(*), nombreparte from partesgroup by nombreparte having nombreparte="Tornillo";

Pregunta: ¿Podríamos quitar el group by? Respuesta: aunque en este caso sí podríamos no se debe hacer.Cuando nos pidan una operación matemática por grupos, debemos poner group by

select count(*), nombreparte from parteswhere nombreparte="Tornillo"group by nombreparte

4.5 Consultas multitabla

En ocasiones la información que nos pidan puede que esté dispersa por distintas tablas. SQL ofrece unmecanismo para “conectar” tablas y así poder hacer las comparaciones que nos pidan.

Por ejemplo, si nos piden el nombre de las partes suministradas en una cantidad >=500 descubriremosque

El nombreparte está en la tabla partes

La cantidad esta en la tabla suministra

Las tablas partes y suministra tienen un campo en común, el campo numparte

Utilizando una cláusula denominada “inner join” SQL puede establecer las correspondencias correctasentre dos o más tablas.

Cruce de datos entre las tablas partes y suministra basándonos en que el campo numparte de suministradebe ser igual que el numparte de suministra

De aqui sacamos solamente los campos que nos piden

44 Capítulo 4. Consultas SQL

Page 49: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

4.6 Algunos ejercicios resueltos

1. Deseamos saber los numeros de proveedor que realizan suministros

select numprov from suministra;

2. Deseamos saber los numeros de proveedor que realizan suministros pero sin que se muestrenrepetidos

select distinct numprov from suministra;

3. Sumar las cantidades que se han suministrado (¿cuantas piezas se han suministrado?)

select sum(cantidad) from suministra;

4. Se desea ver la suma de las distintas cantidades de partes suministradas

select sum(cantidad),numparte from suministragroup by numparte;

5. Se desea saber las cantidades totales que ha suministrado cada proveedor

select sum(cantidad),numprov from suministragroup by numprov;

6. Cantidades totales suministradas por v1 y v4

select sum(cantidad), numprov from suministrawhere numprov=’v1’ or numprov=’v4’group by (numprov);

7. Mostrar los numeros de parte suministrados en una cantidad total mayor o igual que 1000

select sum(cantidad), numpartefrom suministragroup by (numparte)having sum(cantidad)>=1000;

8. Mostrar la suma de las partes suministradas por v1, v2, o v3 en una cantidad mayor de 550

select sum(cantidad),numpartefrom suministrawhere numprov=’v1’ or numprov=’v2’ or numprov=’v3’group by (numparte)having sum(cantidad)>550;

9. Mostrar cuantos proveedores hay en Londres

select count(*) from proveedoreswhere ciudad=’Londres’;

10. ¿Cuantas partes rojas hay?

select count(*) from parteswhere color="Rojo";

11. ¿Qué colores están repetidos en las partes?

4.6. Algunos ejercicios resueltos 45

Page 50: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

select count(*),color from partesgroup by colorhaving count(*)>=2;

4.7 Subconsultas

Al hacer consultas hemos observado que hay cláusulas que permiten establecer condiciones.

Al hacer las condiciones es posible que necesitemos hacer una “subpregunta” y que la sentencia SE-LECT quede algo así

select ....from ...where campo>(select max(cantidad) from suministra)

Supongamos una pregunta como la siguiente: “¿Cuales son los nombres de parte que pesan lo mismoque la parte más pesada?”

Podemos sacar el peso maximo con esta consulta

select max(peso) from partes;

select nombreparte from parteswhere peso>=(select max(peso) from partes);

¿Qué nombres de parte pesan más que la media?

Se saca la mediaselect avg(peso) from partes;select *

from parteswhere peso>=(select avg(peso) from partes);

Dentro de las subconsultas, aparte de las comparaciones típicas como >, >=, <>, <=, <, etc... existenotros elementos para hacer comparaciones

EXISTS: nos dará las filas donde exista alguna fila que cumpla la condición

ALL: la condición deben cumplirla todas las filas

12. ¿Qué nombres de parte corresponden a una pieza azul o almacenada en París?

select nombreparte from parteswhere color="Azul" or ciudad="Paris"

13. ¿Qué colores tienen las distintas partes que no sean tornillos?

select * from parteswhere nombreparte<>"Tornillo";

14. ¿Cuantos proveedores hay que no tengan su almacén en Atenas?

select count(*) from proveedoreswhere ciudad<>"Atenas";

15. ¿Qué nombres de parte se suministran en una cantidad mayor o igual de 400?

46 Capítulo 4. Consultas SQL

Page 51: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

select nombreparte,cantidadfrom partes inner join suministraon partes.numparte=suministra.numpartewhere cantidad>=400;

16. ¿Qué proveedores suministran partes en una cantidad <300?

Por un lado, el nombre de proveedor está en la tabla proveedores, que podemos ver haciendo esta con-sulta

select * from proveedores;

numprov nombreprov estado ciudadv1 Smith 20 Londresv2 Jones 10 Parisv3 Blake 30 Parisv4 Clarke 20 Londresv5 Adams 30 Atenas

Sin embargo, las cantidades de suministro están en la tabla suministra que podemos ver ejecutando estaconsulta

select * from suministra;

numprov numparte numproyecto cantidadv1 p1 y1 200v1 p1 y4 700v2 p3 y1 400v2 p3 y2 200v2 p3 y3 300v2 p3 y4 500v2 p3 y5 600v2 p3 y6 400v2 p3 y7 600v2 p5 y2 100v3 p3 y1 200v3 p4 y2 500v4 p6 y3 300v4 p6 y7 300v5 p1 y4 100v5 p2 y2 200v5 p2 y4 100v5 p3 y4 200v5 p4 y4 800v5 p5 y4 400v5 p5 y5 500v5 p6 y2 200v5 p6 y4 500

Como la información está dispersada, necesitamos cruzar las tablas con un inner join teniendo en cuentaque tienen un campo igual, en este caso el campo numprov. Podemos “hacer el cruce” haciendo un innerjoin con esta consulta

select * from proveedores inner join suministraon proveedores.numprov=suministra.numprov;

4.7. Subconsultas 47

Page 52: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Cuyo resultado es que se cruzan los datos correctamente y se obtiene una tabla como esta

numprov numparte numproyecto cantidad numprov nombreprov estado ciudadv1 p1 y1 200 v1 Smith 20 Londresv1 p1 y4 700 v1 Smith 20 Londresv2 p3 y1 400 v2 Jones 10 Parisv2 p3 y2 200 v2 Jones 10 Parisv2 p3 y3 300 v2 Jones 10 Parisv2 p3 y4 500 v2 Jones 10 Parisv2 p3 y5 600 v2 Jones 10 Parisv2 p3 y6 400 v2 Jones 10 Parisv2 p3 y7 600 v2 Jones 10 Parisv2 p5 y2 100 v2 Jones 10 Parisv3 p3 y1 200 v3 Blake 30 Parisv3 p4 y2 500 v3 Blake 30 Parisv4 p6 y3 300 v4 Clarke 20 Londresv4 p6 y7 300 v4 Clarke 20 Londresv5 p1 y4 100 v5 Adams 30 Atenasv5 p2 y2 200 v5 Adams 30 Atenasv5 p2 y4 100 v5 Adams 30 Atenasv5 p3 y4 200 v5 Adams 30 Atenasv5 p4 y4 800 v5 Adams 30 Atenasv5 p5 y4 400 v5 Adams 30 Atenasv5 p5 y5 500 v5 Adams 30 Atenasv5 p6 y2 200 v5 Adams 30 Atenasv5 p6 y4 500 v5 Adams 30 Atenas

Si ponemos la condición que nos falta el ejercicio se resuelve correctamente

select nombreprov,cantidadfrom proveedores inner join suministraon proveedores.numprov=suministra.numprov

where cantidad<300order by nombreprov desc;

17. ¿Cuantos proveedores suministran partes en una cantidad <300?

Para orientarnos, extraemos las filas enlas que la cantidad es menor de 300

select numprov from suministra where cantidad<300;

Si hacemos el recuento, observaremos que hay9 filas, pero proveedores reales solo están v1, v2, v3 y v5.

select count(numprov)from suministrawhere cantidad<300;

Si ponemos distinct, no nos repetirá los proveedores

select distinct numprovfrom suministrawhere cantidad<300;

Por tanto la solución pasa por hacer el recuento

48 Capítulo 4. Consultas SQL

Page 53: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

de los distintos numprov

select count(distinct numprov)from suministrawhere cantidad<300;

18. ¿Qué nombre tienen las partes suministradas en una cantidad total de 550 o más?

select sum(cantidad), partes.numparte,nombrepartefrom suministrainner join parteson suministra.numparte=partes.numpartegroup by (partes.numparte)having sum(cantidad)>=550;

19. ¿Qué nombres de parte suministran los distintos proveedores?

select nombreprov, nombrepartefrom proveedores inner join suministra

on suministra.numprov=proveedores.numprovinner join partes

on partes.numparte=suministra.numparte;

20. Queremos saber los proveedores que están ubicados en el mismo sitio que alguna parte.

Examinemos la tabla partes

numparte nombreparte color peso ciudadp1 Tuerca Rojo 12 Londresp2 Perno Verde 17 Parisp3 Tornillo Azul 17 Romap4 Tornillo Rojo 14 Londresp5 Leva Azul 12 Parisp6 Engranaje Rojo 19 Londres

Examinemos la tabla proveedores

numprov nombreprov estado ciudadv1 Smith 20 Londresv2 Jones 10 Parisv3 Blake 30 Parisv4 Clarke 20 Londresv5 Adams 30 Atenas

Se puede comprobar que no nos piden para nada datos de la tabla suministra. Lo único que se necesitaes emparejar las filas donde las ciudades sean iguales.

select nombreprov,nombreparte,partes.ciudadfrom partesinner join proveedores

on partes.ciudad=proveedores.ciudad;

21. ¿Como se llaman las piezas que se suministran en la cantidad máxima?

select nombreparte,max(cantidad) from suministrainner join parteson suministra.numparte=partes.numparte

where cantidad=(select max(cantidad) from suministra);

4.7. Subconsultas 49

Page 54: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

22. ¿Que numeros de proveedores suministran por encima de la media de suministros?

select numprov, cantidadfrom suministrawhere cantidad >

(select avg(cantidad) from suministra

);

Cuidado: si nos dijeran "suministran una cantidadtotal que esté por encima de la media", deberíamossumar las cantidades por proveedor y compararlas con la media.

select sum(cantidad), numprovfrom suministragroup by numprovhaving sum(cantidad)>(

select avg(cantidad) from suministra);

23. Números de parte suministradas en una cantidad media mayor de 450

select avg(cantidad),numpartefrom suministragroup by numpartehaving avg(cantidad)>450;

24. ¿Cual es el peso medio de las partes?

select avg(peso) from partes;

25. Obtener el nombre de los provedores, el nombre de parte que suministran y la cantidad en quesuministran

select nombreprov, nombreparte,cantidadfrom proveedores

inner join suministraonsuministra.numprov=proveedores.numprovinner join partesonsuministra.numparte=partes.numparte

26. Obtener las parejas de proveedores que NO están en la misma ciudad

selectprov1.numprov, prov1.ciudad,prov2.numprov, prov2.ciudadfrom proveedores as prov1

inner join proveedores as prov2where prov1.ciudad<>prov2.ciudad;

27. Mostrar suministros donde el proveedor y la parte hayan resultado ser de una ciudad distinta

select suministra.numprov, suministra.numparte,proveedores.ciudad, partes.ciudad

from suministrainner join proveedores

50 Capítulo 4. Consultas SQL

Page 55: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

on proveedores.numprov=suministra.numprovinner join parteson partes.numparte=suministra.numpartewhereproveedores.ciudad<>partes.ciudad;

28. ¿Cuantos proveedores suministran partes rojas o que pesen 12 gramos o más?

Trozo 1: partes rojas

select numparte from partes where color="Rojo"

Trozo 2: partes que pesan 12 o mas

select numparte from partes where peso>12;

Partes que cumplen alguna de las dos cosas

Los proveedores que hacen suministros están en el campo numprov de la tabla suministra

select numprov from suministra;

29. ¿Cuantas piezas se han suministrado de cada ciudad de piezas?

select sum(cantidad),ciudad from partesinner join suministra

on suministra.numparte=partes.numpartegroup by (ciudad);

30. ¿De qué ciudad de proveedor ha salido la cantidad más grande de suministros?.

Esta pregunta podría entenderse de dos formas “Obtener la cantidad más grande suministrada y la ciudaddel proveedor correspondiente”

En primer lugar necesitamos cruzar “proveedores” y suministra

4.7. Subconsultas 51

Page 56: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

v1 p1 y1 200 v1 Smith 20 Londresv1 p1 y4 700 v1 Smith 20 Londresv2 p3 y1 400 v2 Jones 10 Parisv2 p3 y2 200 v2 Jones 10 Parisv2 p3 y3 300 v2 Jones 10 Parisv2 p3 y4 500 v2 Jones 10 Parisv2 p3 y5 600 v2 Jones 10 Parisv2 p3 y6 400 v2 Jones 10 Parisv2 p3 y7 600 v2 Jones 10 Parisv2 p5 y2 100 v2 Jones 10 Parisv3 p3 y1 200 v3 Blake 30 Parisv3 p4 y2 500 v3 Blake 30 Parisv4 p6 y3 300 v4 Clarke 20 Londresv4 p6 y7 300 v4 Clarke 20 Londresv5 p1 y4 100 v5 Adams 30 Atenasv5 p2 y2 200 v5 Adams 30 Atenasv5 p2 y4 100 v5 Adams 30 Atenasv5 p3 y4 200 v5 Adams 30 Atenasv5 p4 y4 800 v5 Adams 30 Atenasv5 p5 y4 400 v5 Adams 30 Atenasv5 p5 y5 500 v5 Adams 30 Atenasv5 p6 y2 200 v5 Adams 30 Atenasv5 p6 y4 500 v5 Adams 30 Atenas

Una primera aproximación sería esta consulta

select max(cantidad),ciudad fromsuministra inner join proveedoresonsuministra.numprov=proveedores.numprov;

Sin embargo esto no funciona porque cualquier operación de agregado no involucra a ninguna fila. Dehecho esa consulta nos devuelve un máximo correcto pero no nos devuelve la ciudad asociada, sino laprimera que encuentra.

Hay que encontrar otra forma de expresar esta consulta

select ciudadfrom proveedores inner join suministraonsuministra.numprov=proveedores.numprovwhere cantidad=(

select max(cantidad) from suministra);

La otra forma de entender la pregunta sería

“Sumar las cantidades por proveedor y decir la ciudad del proveedor con la cantidad máxima”

Una primera aproximación sería esta

select ciudad fromsuministra inner join proveedoresonsuministra.numprov=proveedores.numprovgroup by (proveedores.numprov)

52 Capítulo 4. Consultas SQL

Page 57: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

having sum(cantidad)>=ALL(

select sum(cantidad)from suministragroup by (numprov)

);

31. ¿Qué ciudad de parte tiene la mayor media de suministros?

Cruzamos suministra y partes

select * from suministrainner join partesonsuministra.numparte=partes.numparte

Por otra lado, sacamos la media por partes de la tabla suministra

select avg(cantidad) from suministragroup by numparte;

Si mezclamos la primera parte con la segunda

select ciudad from suministrainner join partesonsuministra.numparte=partes.numpartegroup by partes.numparte

having avg(cantidad)>=ALL(

select avg(cantidad) from suministragroup by numparte

);

32.Obtener todos los detalles de todos los proyectos

select nombreprov, nombreparte,nombreproyecto,cantidadfrom suministrainner join proyectosonsuministra.numproyecto=proyectos.numproyectoinner join partesonsuministra.numparte=partes.numparteinner join proveedoresonsuministra.numprov=proveedores.numprov;

33. Obtener todos los detalles de todos los proyectos ubicados en Londres.

select nombreprov, nombreparte,nombreproyecto,cantidadfrom suministrainner join proyectosonsuministra.numproyecto=proyectos.numproyecto

4.7. Subconsultas 53

Page 58: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

inner join partesonsuministra.numparte=partes.numparteinner join proveedoresonsuministra.numprov=proveedores.numprovwhere proyectos.ciudad="Londres";

34. Obtener los códigos de proveedor que suministran al proyecto Y1.

select numprov from suministrawhere numproyecto=’y1’;

35. Obtener los datos de los proyectos que usan partes en cantidades comprendidas entre 300 y 750.

select distinct proyectos.*from suministrainner join proyectosonsuministra.numproyecto=proyectos.numproyectowhere( cantidad>=300 ) and ( cantidad<=750 )order by proyectos.numproyecto;

36. Obtener las combinaciones posibles parte-color

Este ejercicio requiere tener dos copias de la misma tabla. Será necesario utilizar los alias para combi-nar la tabla partes consigo misma.

select distinct p1.nombreparte, p2.colorfrom partes as p1inner joinpartes as p2;

37. Obtener los colores de las partes que se han suministrado por V1.

select partes.color from suministrainner joinparteson partes.numparte=suministra.numpartewhere numprov=’v1’;

38. Obtener la cantidad total de partes P1 usadas por parte de V1.

Datos del proveedor v1

select * from suministrawhere numprov=’v1’;

Suma de todas las partes de v1

select sum(cantidad)from suministrawhere

numprov=’v1’andnumparte=’p1’;

54 Capítulo 4. Consultas SQL

Page 59: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

39. Obtener las parejas de nombres de ciudad tales que un proveedor ubicado en la primera ciudadsuministra a algún proyecto ubicado en la segunda ciudad.

select proveedores.ciudad, proyectos.ciudadfrom suministrainner join proveedores

onsuministra.numprov=proveedores.numprov

inner join proyectosonsuministra.numproyecto=proyectos.numproyecto;

40. Obtener los códigos de parte suministrados a los proyectos ubicados en la misma ciudad delproveedor.

select suministra.numpartefrom suministrainner join proyectos

onsuministra.numproyecto=proyectos.numproyecto

inner join proveedoresonsuministra.numprov=proveedores.numprov

whereproveedores.ciudad=proyectos.ciudad;

41. Obtener los códigos de proyecto a los que suministra un proveedor que no está en la misma ciudad.

select suministra.numproyectofrom suministrainner join proveedores

onproveedores.numprov=suministra.numprov

inner join proyectosonproyectos.numproyecto=suministra.numproyecto

whereproyectos.ciudad<>proveedores.ciudad;

42. Obtener las parejas de partes suministradas por el mismo proveedor.

select distinct s1.numparte,s2.numpartefrom suministra as s1inner join suministra as s2ons1.numprov=s2.numprov;

43. Obtener los códigos de parte suministrados a cualquier proyecto que esté ubicado en Londres

select numparte from suministrainner join proyectos

onsuministra.numproyecto=proyectos.numproyecto

whereproyectos.ciudad="Londres";

44. Obtener los códigos de proyecto que usan al menos una parte suministrada por el proveedor V1

4.7. Subconsultas 55

Page 60: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

En primer lugar seleccionamos las partes suministradas por v1

select numparte from suministrawhere numprov=’v1’;

En segundo lugar queremos saber los proyectos cuyas partes sean alguna de las que hemos extraídoantes.

select numproyecto from suministrawhere numparte in(

select numparte from suministrawhere numprov=’v1’

);

45. Obtener los códigos de proveedor que suministran al menos una parte roja.

Seleccionamos las partes cuyo color es rojo

select numparte from partes where color="Rojo";

Ahora, en la tabla suministra sacamos las filas donde la parte sea alguna de las extraídas antes

select numprov from suministrawhere numparte in(

select numparte from parteswhere color="Rojo"

);

Una solución distinta

select numprov from suministrainner join parteson suministra.numparte=partes.numpartewhere partes.color="Rojo";

46. Obtener los códigos de proyecto cuya ciudad es la primera en la lista de ciudades.

Seleccionamos la ciudad más pequeña

select min(ciudad) from proyectos;

select * from proyectoswhere ciudad in(

select min(ciudad) from proyectos);

Otra forma de hacerlo con inner join’s sería la siguiente

select p1.numproyecto,p1.ciudad from proyectos as p1inner join proyectos as p2onp1.numproyecto=p2.numproyectowhere p1.ciudad=(

select min(ciudad) from proyectos);

56 Capítulo 4. Consultas SQL

Page 61: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

47. Obtener los códigos de proyecto a los que se suministra la parte P1 en una cantidad promedioigual o superior a la cantidad más grande que se suministra al proyecto Y1.

Extraemos la cantidad más grande del proyecto “y1”

select max(cantidad) from suministrawhere numproyecto="y1";

Al haber una condición para el promedio deberemos poner algo como esto

select numproyecto,avg(cantidad) from suministrawhere numparte=’p1’group by numproyecto

having avg(cantidad)>=(

select max(cantidad) from suministrawhere numproyecto="y1"

);

48. Obtener los códigos de proveedor de los que suministran la parte P1 a algún proyecto en unacantidad superior a la cantidad promedio de la parte P1 para ese proyecto.

Podemos empezar intentando sacar la media de partes p1 para cada proyecto

select numproyecto, avg(cantidad) from suministrawhere numparte=’p1’group by numproyecto;

Extraemos las filas de la tabla suministra donde la parte sea p1

select numprov from suministra as s1where numparte=’p1’and cantidad >(

select avg(cantidad) from suministra as s2where numparte=’p1’ands2.numproyecto=s1.numproyectogroup by numproyecto

);

49. Obtener los códigos de proyecto a los que ningún proveedor de Londres suministra una parte roja.

Primero averiguamos los proveedores de Londres que suministran partes rojas.

select suministra.numprovfrom suministrainner join parteson partes.numparte=suministra.numparteinner join proveedoreson proveedores.numprov=suministra.numprovwhereproveedores.ciudad="Londres"andpartes.color="Rojo";

Ahora examinamos la tabla suministra y comprobamos que el proveedor no esté en el conjunto devueltopor la consulta anterior

4.7. Subconsultas 57

Page 62: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

select * from suministrawhere numprovnot in(

select suministra.numprovfrom suministrainner join parteson partes.numparte=suministra.numparteinner join proveedoreson proveedores.numprov=suministra.numprovwhereproveedores.ciudad="Londres"andpartes.color="Rojo"

);

50. ¿Hay algún proveedor que suministre la misma parte a TODOS los proyectos?

Para conseguir la solución a este problema se deben utilizar algunas características de los cuantifi-cadores.

Replanteamos la pregunta

“se desea saber los proveedores donde para todos los proyectos existe una misma parte suministrada”

o más desarrollado

“proveedores (de la tabla suministra) donde para todo proyecto (de la tabla proyectos) existe un sumin-istro donde el codigo de proveedor es dicho proveedor y la parte es la misma parte que mirábamos ensuministra”

select numprov from suministra as s1where not exists(

select numproyecto from proyectoswhere numproyecto not in(

select numproyecto from suministra as s2wheres1.numparte=s2.numparteands1.numprov=s2.numprov

)

);

51. Obtener los códigos de proyecto que usan todas las partes suministradas por el proveedor v1

“Obtener los códigos de proyecto donde para toda parte de la tabla suministra existe una parte sumin-istrada por v1”

Obtener el conjunto de parte suministradas por v1.

select numparte from suministrawhere numprov=’v1’;

Las filas de la tabla suministra donde para toda parte de v1 existe alguna fila asociada

58 Capítulo 4. Consultas SQL

Page 63: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

select numprov from suministra as s1where not exists(

select numparte from suministra as s2where numprov=’v1’and not exists(

select numparte,numproyectofrom suministra as s3wheres2.numparte=s3.numparteand s1.numproyecto=s3.numproyecto

));

52. (Para nota) Obtener los pares de proveedores V1 y V2 que suministren EXACTAMENTE el mis-mo conjunto de partes.

La pregunta podría replantearse como

“obtener parejas de proveedores donde para toda parte de un proveedor de la primera tabla existe otroproveedor distinto tal que la parte es la misma que la parte del primero”.

4.8 Actualización y borrado

Una vez que se han insertado datos, estos no son inmutables. Pueden cambiarse valores de las filas oincluso pueden borrarse las filas.

Estas operaciones se hacen con las sentencias UPDATE y DELETE.

Para cambiar valores se hace lo siguiente

UPDATE <TABLA> SET <CAMPO>=VALOR WHERE <CONDICION>

Por ejemplo, si quisiéramos hacer que en la tabla partes se cambiara la ciudad Londres por Madridharíamos algo como esto

UPDATE partes SET ciudad="Madrid"WHERE ciudad="Londres";

Poner en la tabla partes el peso a 30 en todas las partes cuyo peso sea mayor que 16;

update partes set peso=30where peso>16;

Poner a “Amarillo” el color de las partes de Paris

update partes set color="Amarillo"where ciudad="Paris";

Para borrar datos

DELETE FROM <TABLA> WHERE <CONDICION>

Borrar todas las partes cuyo peso es 12

delete from partes where peso=12;

4.8. Actualización y borrado 59

Page 64: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

60 Capítulo 4. Consultas SQL

Page 65: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 5

Programación

5.1 Introducción

Los elementos de los lenguajes de programación son muy similares entre sí y el cambio de lenguajesolamente supone la modificación de ciertos hábitos.

En el entorno de las bases de datos se pueden encontrar programas que actúen mediante dos mecanismosdistintos. Estos dos mecanismos se definen como

Procedimientos almacenados: residen en el propio servidor de bases de datos.

Programas externos: utilizando algún mecanismo los programas externos se comunican con elservidor para intercambiar datos mediante un lenguaje de programación cualquiera.

Entre los mecanismos de comunicación más utilizados encontramos ODBC (Open DataBases Commu-nication). Este estándar especifica claramente a servidores y cliente como tienen que dar o pedir datos.

Una versión modificada de ODBC es JDBC que ha modernizado el estándar pero solo sirve para pro-gramas Java.

5.2 Procedimientos almacenados

5.2.1 Carga de programas

Para cargar código almacenado en un fichero SQL externo se utiliza lo siguiente

\. d:\oscar\t5\prueba1.sql

5.2.2 Variables

Una variable es una posición de memoria con nombre. Normalmente las variables conllevan un tipo querestringe lo que podemos almacenar en ella.

En MySQL las variables se declaran con la palabra clave DECLARE.

Los tipos utilizables con las variables son los mismos que tiene MySQL.

61

Page 66: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

5.2.3 Declaración de procedimientos

Los procedimientos en MySQL actúan igual que las funciones en Java con las diferencias en la sintaxis.

El primer problema que aparece en los procedimientos MySQL viene dado por el hecho de que losprocedimientos a veces interfieren con el delimitador ”;”. Para evitarlo, debemos avisar a MySQL deque durante un tiempo cambiaremos de delimitador, para ello se usa la siguiente sentencia

Dentro de “código” podrán ir sentencias que terminarán de forma normal, con el punto y coma ”;”

El problema viene dado porque el intérprete de MySQL intenta procesar todo lo que haya hasta llegara un punto y coma. Si no cambiásemos el delimitador, se ejecutaría la definición del procedimiento sinhaber encontrado un “end” y todo fallaría.

Ejercicio: crear un procedimiento que acepte una cantidad c y que seleccione todos los suministros cuyocampo cantidad sea mayor que c

delimiter //

create procedure mayores (cant int)begin

select * from suministrawhere cantidad>cant;

end

//

delimiter ;

Ejercicio: crear un programa que acepte un color co y una ciudad ci y que busque todas las partes cuyocolor sea ese co pasado y la ciudad ese ci pasado. Co y ci son parámetros y NO LLEVAN COMILLAS

delimiter //create procedure Color_parte

(color_pasado varchar(20),ciudad_pasada varchar(20) )

beginselect * from partes

where color=color_pasadoandciudad=ciudad_pasada;

end//

delimiter ;

Ejercicio: crear un procedimiento que permita sumar la cantidad de partes suministradas cuyo color seael mismo que un cierto color pasado

drop procedure suministradas_color;

delimiter //

create procedure suministradas_color( color_pasado varchar(20) )

beginselect sum(cantidad)

62 Capítulo 5. Programación

Page 67: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

from suministrainner join partesonsuministra.numparte=partes.numpartewhere color=color_pasado;

end

//

delimiter ;

call suministradas_color("Rojo");call suministradas_color("Gris");

Ejercicio: crear un procedimiento que reciba dos cosas

Color_pasado

Nombre_prov_pasado

El procedimiento deberá decirnos cuantas partes de ese color suministra ese nombre de proveedor.

Ejercicio: crear un procedimiento que reciba dos cosas

Nombre_parte_pasada

Cantidad_pasada

El procedimiento deberá decirnos cuantas partes con ese nombre han sido suministradas en una cantidadmayor o igual que la cantidad pasada

drop procedure mayores_que;delimiter //create procedure mayores_que(

nombre_parte_pasada varchar(11),cantidad_pasada int)

beginselect * from suministra inner join partes

on suministra.numparte=partes.numpartewhere

nombreparte=nombre_parte_pasadaandcantidad>cantidad_pasada;

end

//delimiter ;

5.3 Sentencias básicas

5.3.1 Decisiones con IF

La sintaxis de un IF es prácticamente igual a la de Java

5.3. Sentencias básicas 63

Page 68: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

IF (<comparacion>) THENBEGIN

<sentencias>ENDELSE

BEGINEND

END IF;

Ejemplo: crear un procedimiento que extraiga las filas de la tabla suministra que cumplan cierta condi-cion sobre la cantidad. Si el usuario pasa un simbolo “>” el procedimiento nos devuelve las filas mayoresque cierta cantidad. Si no pasa un “<” nos devuelve las filas con una cantidad menor que la pasada

drop procedure selector_v2;

delimiter //

create procedure selector_v2( operacion char(1), cantidad_pasada int)begin

if (operacion="<") thenbegin

select * from suministra wherecantidad<cantidad_pasada;

end;else

beginselect * from suministra where

cantidad>cantidad_pasada;end;

end if;end;

//delimiter ;

Ejercicio:crear un procedimiento que dados los parámetros siguientes compruebe cuantas partes con uncierto nombre tienen una cantidad menor o mayor que una cierta cantidad pasada

nombre_parte_pasado

cantidad_pasada

operacion

drop procedure selector_v3;delimiter //

create procedure selector_v3( nombre_parte_pasada varchar(11),cantidad_pasada int,operacion varchar(1) )

beginif (operacion=">") then

beginselect * from suministra inner join

partes on

64 Capítulo 5. Programación

Page 69: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

suministra.numparte=partes.numpartewherenombreparte=nombre_parte_pasada

andcantidad>cantidad_pasada;

end;else

beginselect * from suministra inner join

partes onsuministra.numparte=partes.numpartewherenombreparte=nombre_parte_pasada

andcantidad<cantidad_pasada;

end;end if;

end;

//delimiter ;

Ejercicio: ampliar el programa anterior para que soporte búsquedas utilizando los comparadores “<”,“>”, “>=”, “<=”, “=” y “<>”.

5.3.2 5.3.2 Decisiones múltiples con CASE..WHEN

El programa anterior puede resolverse utilizando sentencias if-then-else anidadas, sin embargo es pocopráctico y difícil de ampliar.

La sintaxis de la sentencia case es la siguiente

CASE <variable>WHEN ">" THEN

BEGINEND;

WHEN ">=" THENBEGINEND;

WHEN "<" THENBEGINEND;

...ELSE

BEGIN

END;END CASE;

Basándonos en esta sintaxis, una posible solución al problema anterior sería esta

drop procedure selector_v4;delimiter //create procedure selector_v4

( nombre_parte_pasada varchar(11),cantidad_pasada int,operacion varchar(1) )

5.3. Sentencias básicas 65

Page 70: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

begincase operacion

when ">" thenbeginselect * from suministrainner join partes

onsuministra.numparte=partes.numpartewherenombreparte=nombre_parte_pasadaand cantidad>cantidad_pasada;

end;when ">=" then

beginselect * from suministra

inner join partesonsuministra.numparte=partes.numpartewherenombreparte=nombre_parte_pasadaand cantidad>=cantidad_pasada;

end;when "<>" then

beginselect * from suministra

inner join partesonsuministra.numparte=partes.numpartewherenombreparte=nombre_parte_pasadaand cantidad<>cantidad_pasada;

end;when "<" then

beginselect * from suministra

inner join partesonsuministra.numparte=partes.numpartewherenombreparte=nombre_parte_pasadaand cantidad<cantidad_pasada;

end;when "<=" then

beginselect * from suministra

inner join partesonsuministra.numparte=partes.numpartewherenombreparte=nombre_parte_pasadaand cantidad<=cantidad_pasada;

end;when "=" then

beginselect * from suministra

inner join parteson

66 Capítulo 5. Programación

Page 71: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

suministra.numparte=partes.numpartewherenombreparte=nombre_parte_pasadaand cantidad=cantidad_pasada;

end;else

beginselect ("Operacion no reconocida");

end;end case;

end;//delimiter ;

Ejercicio: crear un programa que acepte un nombre de parte,luego dos ciudades C1 y C2 y luego uncomparador que puede ser “AND” o “OR”. El programa debe decirnos las partes que pertenecen a lasdos ciudades o a una de ellas.

drop procedure comparador;delimiter //

create procedure comparador( nombre_parte_pasada varchar(11),

c1_pasada varchar(15), c2_pasada varchar(15),operador varchar(3) )

begin

case operadorwhen "AND" then

beginselect * from partes as p1, partes as p2where p1.nombreparte=nombre_parte_pasadaandp2.nombreparte=nombre_parte_pasadaand (p1.ciudad=c1_pasadaand p2.ciudad=c2_pasada);

end;when "OR" then

beginselect * from partes as p1, partes as p2where p1.nombreparte=nombre_parte_pasadaandp2.nombreparte=nombre_parte_pasadaand (p1.ciudad=c1_pasadaor p2.ciudad=c2_pasada);

end;else

beginselect ("Escriba OR o AND (mayúsculas)");

end;end case;

end

//

delimiter ;

5.3. Sentencias básicas 67

Page 72: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

5.3.3 Bucles con WHILE

Utilizando la sentencia WHILE podemos repetir una tarea que se repita un cierto número de veces pormedio de una variable.

La sintaxis de WHILE es la siguiente

set variable_control=0;WHILE (<condicion>) DO

...<sentencias>..set variable_control=variable_control+1;

END WHILE;

Ejercicio resuelto: hacer un programa MySQL que imprima los 10 primeros números pares.

drop procedure pares;delimiter //

create procedure pares()begin

declare contador int;set contador=0;while (contador<=20) do

select (contador);set contador=contador+2;

end while;end;

//delimiter ;

5.3.4 Cursores

Un cursor es un puntero que apunta a la primera fila del resultado de una consulta. Podemos ir ha-ciendo avanzar el puntero y procesar cada fila por separado. Podemos dejar de procesar filas cuando lodeseemos, sin que sea obligatorio examinar todos los resultados.

No se puede declarar variables nuevas despues de un cursor. Es decir, esto no es válido

declare un_cursor cursor for...declare contador int;

Esto sí es válido

declare contador int;declare un_cursor cursor for...

Un ejemplo de cursor sería el siguiente

DECLARE nombre_cursor CURSOR FOR SELECT * FROM .... ;

Para empezar a visitar la primera fila se debe hacer lo siguiente.

No debemos olvidar CERRAR el cursor

68 Capítulo 5. Programación

Page 73: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

CLOSE nombre_cursor;

Ejercicio: extraer los proveedores que han recibido las 5 cantidades más grandes de suministros.

drop procedure mejores_clientes;delimiter //

create procedure mejores_clientes()begin

declare contador int;declare num_prov_sacado varchar(5);declare cantidad_sacada int;declare cur1 cursor for

select numprov, cantidad from suministraorder by cantidad desc;

open cur1;set contador=0;while (contador<5) do

fetch cur1 into num_prov_sacado, cantidad_sacada;select (num_prov_sacado);select (cantidad_sacada);set contador=contador+1;

end while;close cur1;

end;

//delimiter ;

Ejercicio: hacer un programa que saque las 2 partes de más peso de la tabla partes.

La consulta para este ejercicio sería:

select * from partes order by peso desc;

Y el procedimiento sería

drop procedure mayores_pesos;delimiter //create procedure mayores_pesos()begin

declare contador int;declare peso_extraido int;declare ciudad_extraida varchar(15);declare cur1 cursor for

select ciudad,peso from partes order by peso desc;open cur1;set contador=0;while (contador<2) do

beginfetch cur1 into ciudad_extraida, peso_extraido;select (ciudad_extraida),(peso_extraido);set contador=contador+1;

end;end while;close cur1;

5.3. Sentencias básicas 69

Page 74: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

end;

//delimiter ;

Ejercicio: sumar las cinco cantidades más bajas de la tabla suministra.

La consulta sería ésta:

select * from suministra order by cantidad;

Y la solución sería

drop procedure suma_filas;delimiter //create procedure suma_filas()begin

declare contador int;declare suma int;declare cantidad_extraida int;declare cur1 cursor for

select cantidad from suministraorder by cantidad asc;

open cur1;set suma=0;set contador=0;while contador<5 do

fetch cur1 into cantidad_extraida;set suma=suma+cantidad_extraida;set contador=contador+1;

end while;close cur1;select suma;

end;//delimiter ;

5.3.5 Parámetros de salida

Si un procedimiento tiene que devolver cosas puede hacerlo utilizando parámetros en la cabecera quehayan sido etiquetados como “out”.

drop procedure suma_filas;delimiter //create procedure suma_filas(out suma_devuelta int)begin

declare contador int;declare suma int;declare cantidad_extraida int;declare cur1 cursor for

select cantidad from suministraorder by cantidad asc;

open cur1;set suma=0;set contador=0;while contador<5 do

fetch cur1 into cantidad_extraida;

70 Capítulo 5. Programación

Page 75: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

set suma=suma+cantidad_extraida;set contador=contador+1;

end while;close cur1;set suma_devuelta=suma;

end;//delimiter ;

Ejercicio: realizar un procedimiento que devuelva en una variable la ciudad del proveedor con unacantidad total (sumar) de suministros mayor.

La consulta sería algo así

select sum(cantidad),suministra.numprov,ciudad from suministrainner join proveedoreson suministra.numprov=proveedores.numprovgroup by numprovhaving sum(cantidad)>=ALL

(select sum(cantidad)

from suministragroup by numprov

);

El procedimiento sería así:

drop procedure mayor_suministro;delimiter //

create procedure mayor_suministro(out ciudad_devuelta varchar(11) )

begindeclare c_devuelta varchar(11);declare suma int;declare num_proveedor varchar(2);declare cur1 cursor for

select sum(cantidad),suministra.numprov,ciudad from suministrainner join proveedoreson suministra.numprov=proveedores.numprovgroup by numprovhaving sum(cantidad)>=ALL

(select sum(cantidad)

from suministragroup by numprov

);

open cur1;fetch cur1 into suma, num_proveedor,c_devuelta;set ciudad_devuelta=c_devuelta;close cur1;

end;

//

5.3. Sentencias básicas 71

Page 76: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

delimiter ;

5.4 Funciones

La sintaxis de una función es la siguiente

delimiter //

create function <nombre> (parametros) returns stringbeginend;

//

delimiter ;

Ejercicio: construir una función que devuelva la suma total de cantidades de la tabla suministra

La consulta sería así:

select sum(cantidad) from suministra;

Y la función

drop function suma_suministra;delimiter //create function suma_suministra() returns intbegin

declare suma int;declare cur1 cursor for

select sum(cantidad) from suministra;

open cur1;fetch cur1 into suma;close cur1;

return suma;end;

//delimiter ;

set @s=suma_suministra();

5.5 Funciones MySQL

La primera función de utilidad es la función CONCAT. Este trozo de código une dos cadenas en una sola

set @cadena1=CONCAT(apellido, nombre);

Hacer una función que devuelva el nombre de la pieza y el color en una sola cadena. Se debe devolverla pieza con un peso mayor.

72 Capítulo 5. Programación

Page 77: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

La solución sería:

delimiter //create function pieza_mayor_peso() returns varchar(25)

begindeclare cadena varchar(25);declare nombre_pieza varchar(10);declare color_pieza varchar(10);declare cur1 cursor for

select nombreparte, color from parteswhere peso=( select max(peso) from partes );

open cur1;fetch cur1 into nombre_pieza, color_pieza;set cadena=CONCAT(nombre_pieza, ":", color_pieza);return cadena;close cur1;

end;

//

delimiter ;

Hay una pareja de funciones llamadas LEFT y RIGHT, que nos permiten extraer trozos de las cadenas.

Por ejemplo, la función LEFT(peso, 1) nos devuelve la primera letra del campo peso.

Ejercicio: construir una función que nos devuelva la pieza de mayor peso con la letra inicial del colorentre paréntesis (es decir, se debe devolver algo como “Engranaje (R)”)

Para resolver este ejercicio era necesario utilizar la función LEFT (cadena, longitud). Esta función nosdevuelve un trozo de la cadena que mide lo que diga el parámetro longitud.

Existe una variante que extrae trozos desde el lado derecho y que se llama RIGHT (cadena, longitud).

drop procedure pieza_pesada_inicial;delimiter //

create procedure pieza_pesada_inicial()begin

declare parte_extraida varchar(15);declare color_extraido varchar(15);declare cadena varchar(30);declare inicial varchar(1);declare cur1 cursor for

select nombreparte, color from parteswhere peso=( select max(peso) from partes );

open cur1;

fetch cur1 into parte_extraida, color_extraido;set inicial=LEFT(color_extraido, 1);set cadena=CONCAT(parte_extraida, " (",

inicial, ")" );select (cadena);close cur1;

end;

5.5. Funciones MySQL 73

Page 78: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

//delimiter ;

Ejercicio: construir una función que nos devuelva el codigo de proyecto con una cantidad de sumin-istros menor. Por problemas de integración con otra base de datos necesitamos que se devuelva todo enmayúsculas.

Para resolver esto existe una función que convierte una cadena a su equivalente en mayúsculas.

drop function proveedor_minimo;delimiter //

create function proveedor_minimo () returns varchar(3)begin

declare num_prov_extraido varchar(3);declare cur1 cursor for

select numprov from suministrawhere cantidad=(

select min(cantidad)from suministra

);open cur1;fetch cur1 into num_prov_extraido;set num_prov_extraido=UPPER(num_prov_extraido);close cur1;return num_prov_extraido;

end;

//delimiter ;

Ejercicio: crear un procedimiento que acepte una cadena como parámetro. El procedimiento deberámostrar todas las partes de la tabla partes cuyo nombre contenga la subcadena pasada como parámetro.Es decir, podemos hacer algo como ésto

CALL buscar("To");

En general, la búsqueda de secuencias, cadenas o patrones es algo muy común pero muy sencillo deresolver con algunas funciones.

La función INSTR() permite comprobar si una cierta subcadena está dentro de una cadena.

Existe un operador llamado LIKE que permite hacer búsquedas “difusas”.

Por ejemplo, se puede hacer una búsqueda como esta

select * from parteswhere nombreparte LIKE "uer%";

El programa resuelto quedaría así

drop procedure buscar;

delimiter //

create procedure buscar (subcadena varchar(15) )begin

declare patron varchar(17);

74 Capítulo 5. Programación

Page 79: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

set patron=CONCAT("%", subcadena, "%");select * from partes

where nombreparte like patron;

end;

//

delimiter ;

Ejercicio: se desea crear un procedimiento que permita marcar como obsoletas ciertas partes de la tablapartes. Para ello, el usuario ejecutará un procedimiento como este

call marcar_obsoleta("To");

Este procedimiento CAMBIARÁ los nombres de parte de la tabla partes poniéndoles un asterisco alprincipio. Para resolverlo será necesario manejar correctamente la condición de NOT FOUND para uncursor. Además, necesitamos un procedimiento principal que construya el patrón y que luego llame alprocedimiento auxiliar que actualiza las filas.

drop procedure marcar_obsoletas;drop procedure actualizar;delimiter //

create procedure actualizar (patron varchar(15) )begin

declare terminar int;declare nombre_extraido varchar(35);declare nuevo_nombre varchar(38);declare cur1 cursor for

select nombreparte from parteswhere nombreparte like patron;

declare continue handlerfor not foundset terminar=true;

open cur1;set terminar=FALSE;while (terminar!=TRUE) do

fetch cur1 into nombre_extraido;select (nombre_extraido);set nuevo_nombre=CONCAT("* ", nombre_extraido);update partes set nombreparte=nuevo_nombre

where nombreparte=nombre_extraido;end while;close cur1;

end;

#Este procedimiento fabrica el patron y despues#llama a actualizarcreate procedure marcar_obsoletas(subcadena varchar(15))begin

declare patron varchar(17);set patron=concat("%", subcadena, "%");call actualizar(patron);

5.5. Funciones MySQL 75

Page 80: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

end;

//

delimiter ;

Ejercicio: crear un procedimiento que indique la desviación media de los pesos de las partes.

drop procedure desviaciones_medias;delimiter //

create procedure desviaciones_medias()begin

declare terminado boolean;declare media decimal(8,2);declare peso_extraido decimal(8,2);declare desv decimal(8,2);declare cur1 cursor for

select avg(peso) from partes;declare cur2 cursor for

select peso from partes;declare continue handler for not found

set terminado=true;open cur1;open cur2;fetch cur1 into media;fetch cur2 into peso_extraido;set terminado=false;while not terminado do

# Se calcula la desviacionset desv= abs ( peso_extraido-media );select desv;fetch cur2 into peso_extraido;

end while;close cur1;close cur2;

end;

//delimiter ;

Aparte de la función ABS, que calcula el valor absoluto se pueden encontrar otras funciones de utilidadcomo las siguientes:

CEIL (valor): Redondear el valor hacia arriba.

FLOOR (valor): Redondea hacia abajo.

ROUND (valor, decimales): si hacemos ROUND (3.6576, 2) nos devolvería el valor 3.66

5.6 Solución al examen

Cálculo de la mediana de un conjunto de valores

76 Capítulo 5. Programación

Page 81: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

drop procedure mediana;delimiter //

create procedure mediana (out mediana int)begin

declare cuantos int;declare peso_extraido int;declare contador int;

declare cur1 cursor forselect count(*) from partes;

declare cur2 cursor forselect peso from partes order by peso asc;

open cur1;fetch cur1 into cuantos;close cur1;open cur2;set cuantos=ceil(cuantos/2);set contador=0;while (contador<cuantos) do

fetch cur2 into peso_extraido;set contador=contador+1;

end while;

set mediana=peso_extraido;close cur2;

end;

//delimiter ;

Crear una función que nos devuelva “sí” si hay la misma cantidad de partes rojas que de proyectos deAtenas. Si no es así debe devolver “no”.

drop function cuantos;

delimiter //

create function cuantos() returns varchar(2)begin

declare cuantos_rojos, cuantos_atenas int;

declare cur1 cursor forselect count(*) from partes where color="Rojo";

declare cur2 cursor forselect count(*) from proyectos

where ciudad="Atenas";open cur1;open cur2;

fetch cur1 into cuantos_rojos;fetch cur2 into cuantos_atenas;

if (cuantos_rojos=cuantos_atenas) thenreturn "SI";

5.6. Solución al examen 77

Page 82: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

elsereturn "NO";

end if;close cur1;close cur2;

end;

//

delimiter ;

1 drop procedure insertar_madrid;2

3 delimiter //4

5 create procedure insertar_madrid(6 codigo_proyecto varchar(11),7 nombre_proyecto varchar(11) )8

9 begin10 insert into proyectos11 values (codigo_proyecto,nombre_proyecto,"Madrid");12 end;13

14 //15

16 delimiter ;

78 Capítulo 5. Programación

Page 83: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 6

Administración

6.1 Introducción

La administración de un SGBD suele referirse a un SGBD corporativo.

El administrador de bases de datos o DBA es el responsable de todo lo concerniente a los datos. Puedehaber muchas personas que trabajen con los datos: programadores SQL, programadores Java, progra-madores web, etc...

Los SGBD corporativos, como MySQL ofrecen muchas posibilidades, casi tantas como un sistemaoperativo.

Un problema fundamental en la administración de un SGBD pasa es asegurar el uso correcto de lospermisos: en líneas generales, los permisos deberían restringirse al mínimo. Además, para obtener lamáxima seguridad deberíamos hacer otras tareas como las siguientes:

Se debe comprobar también la seguridad del resto del equipo: seguridad física, seguridad delsistema operativo, etc...

No compartir la clave de root.

Dar solamente los permisos mínimos necesarios, aunque sea más trabajoso.

No se debe trabajar con claves sin cifrar.

Se deben utilizar cortafuegos.

6.2 Aspectos básicos de las contraseñas

6.2.1 La clave de root

Cuando nos conectamos a un servidor hacemos esto

mysql -u root -p

lo que es potencialmente inseguro, ya que es posible capturar tramas en redes y obtener contraseñas.

Si se desea acceder a otro host distinto (tal vez un remoto) se debe hacer lo siguiente:

79

Page 84: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

mysql -u root -p -h 10.9.0.91

6.2.2 Las claves de usuarios no root

Es posible conectar con un servidor MySQL de varias formas

mysql -u pepito --password=1234mysql -u pepito -p

La segunda es preferible, aunque más incómoda.

6.2.3 Hashes

Un hash es un cálculo que se hace sobre una secuencia de datos que se utiliza para asegurar la integridadde la secuencia de datos.

6.2.4 Cambios entre versiones

En MySQL 4.1 se cambió el sistema. Los nuevos hashes se almacenan con un asterisco delante.

6.2.5 Asegurando contra atacantes

En sistemas UNIX MySQL no debe ejecutarse con el usuario root.

Al crear usuarios debe tenerse mucho cuidado al usar comodines.

No debe permitirse a los usuarios crear ficheros.

No debe haber usuarios sin clave.

6.3 El sistema de privilegios

MySQL tiene un sistema interno que controla quien puede hacer qué. Se debe tener en cuenta queMySQL tiene dos barreras

1. La primera barrera se da en la conexión: se debe disponer de un usuario y de una clave.

2. La segunda barrera se comprueba cada vez que el usuario escribe una sentencia.

Se debe tener cuidado porque a veces los cambios en los permisos no se ejecutan inmediatamente. Siobservamos que esto está ocurriendo podemos lanzar el comando siguiente:

FLUSH PRIVILEGES;

Para ver los permisos concedidos al usuario que utilizamos podemos lanzar este comando

show grants;

A partir de ahora debemos recordar que un usuario no es solamente un login, sino también un sitio desdeel que se conecta. Estos dos usuario NO SON IGUALES

80 Capítulo 6. Administración

Page 85: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

’pepito’@’localhost’’pepito’@’10.9.0.0/255.255.0.0’;’pepito’@’sede-a.empresaacme.com’;

Los permisos pueden ser de tres tipos:

Administrativos: controlan la forma en que MySQL se comporta.

De base de datos: afectan a todas las tablas de una BD.

De tabla: controlan los accesos a solo una de las tablas de una BD.

Los permisos de MySQL están controlados por medio de una BD especial llamada “mysql”.

La tabla user almacena los privilegios asociados a los usuarios

select user, host,password from user;

Se puede observar que hay dos usuarios llamados root@localhost y [email protected]. Son usuarios dis-tintos y ambos tienen la clave vacía. No deben tenerse SGBD con la clave de root vacía.

Además la instalación por defecto también crea un usuario vacío y sin contraseña que permite conectarsesin clave.

Para borrar un usuario se usa el comando DROP USER

DROP USER ’’@’localhost’;

Si un cambio no tiene lugar es posible que los cambios no se hayan volcado en el sistema de MySQL.Podemos forzar a que los cambios tengan efecto usando este comando

FLUSH PRIVILEGES;

Se debe tener en cuenta que para poder conectar a una base de datos remota debemos disponer delusuario con el host correspondiente.

¿Qué ocurre si aparentemente podemos entrar como dos posible usuarios distintos?. Supongamos quenos conectamos desde la IP 10.9.0.200 con el usuario root y en MySQL hay dos usuarios (el % significa“cualquier cosa”).

usuario host passwordroot 10.9.0.200 sesamoroot % 12341234

La regla es que cuando un usuario se conecta y se le pueden aplicar dos o más reglas se utiliza siemprela más restrictiva.

Para crear un usuario debemos especificar su login y su host. El host podría ser ‘ %’.

create user ’dam1’@’10.9.0.0/255.255.0.0’identified by ’sesamo’;

create user ’dam1’@’10.9.0.254’identified by ’12341234’;

create user ’dam1’@’%’identified by ’6789’;

En ocasiones un usuario puede haber olvidado su clave o simplemente se desea renovar la misma poruna política de seguridad.

6.3. El sistema de privilegios 81

Page 86: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

set password for ’dam1’@’10.9.0.0/255.255.0.0’ = PASSWORD(’12345678’);

ESTO ES UN ERROR

set password for ’dam1’@’10.9.0.0/255.255.0.0’ = ’12345678’;

6.4 El sistema de concesiones

Para permitir a un usuario lanzar comandos se debe utilizar el comando siguiente

GRANT <PERMISOS>ON <BASE_DE_DATOS>.<COLUMNA1>,

<BASE_DE_DATOS>.<COLUMNA2>,..<BASE_DE_DATOS>.<COLUMNAN>,proyectos.**.*

TO ’dam1’@’10.9.0.0/255.255.0.0’;

Un ejemplo sería este

grant insert,select on proyectos.partesto ’dam1’@’10.9.0.0/255.255.0.0’;

flush privileges;

revoke insert,select on proyectos.partesfrom ’dam1’@’10.9.0.0/255.255.0.0’;

flush privileges;

Aparte de los permisos SELECT, INSERT, UPDATE Y DELETE, CREATE, CREATE INDEX, CRE-ATE VIEW

Ejercicio: diseñar un esquema de seguridad para dar respuesta a las siguientes necesidades de acceso adatos.

Se necesita dar acceso a dos usuarios que puedan seleccionar datos de cualquier tabla que existaen la base de datos proyectos.

De uno de los usuarios sabemos su IP (preguntar al compañero). El otro usuario puede provenirde cualquier punto de la red 10.9.xxx.xxx.

Existe un usuario admin que puede provenir de cualquier punto de la red 10.9 y que puede hacerlotodo con cualquier tabla de la base de datos proyectos.

Extra: se desea que el usuario admin tenga a su vez permisos para conceder permisos.

6.5 Usuarios con restricciones

Al crear un usuario puede ser interesante limitar la cantidad de actividad que dicho usuario ejecuta porhora.

Al crear los usuarios con CREATE USER se pueden indicar algunas limitaciones como las siguientes

82 Capítulo 6. Administración

Page 87: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

create user ’admin’@’10.9.0.0/255.255.0.0’identified by ’1234’

grant select on proyectos.*to ’admin’@’10.9.0.0/255.255.0.0’

with max_queries_per_hour 8with max_connections_per_hour 3with max_updates_per_hour 2;

El parámetro max_queries_per_hour se refiere a máximo de comandos generales, mientras quemax_update_per_hour se refiere específicamente a consultas que involucren cambios.

6.6 Vistas

Una vista es un trozo extraído de una tabla en base a una consulta. Las vistas pueden utilizarse pararestringir aún más la cantidad de información que pueden ver los usuarios.

Antes de crear una vista se debe tener muy clara la consulta que se va a aplicar. Por ejemplo, si deseamostener por separado los datos de Paris sin incluir el estado podríamos crear una vista como esta:

CREATE VIEW PROV_PARIS ASSELECT NUMPROV,NOMBREPROV,CIUDAD

FROM PROVEEDORESWHERE CIUDAD="Paris";

CREATE USER ’usu_paris’@’10.9.0.0/255.255.0.0’IDENTIFIED BY ’paris’;

CREATE USER ’usu_paris’@’localhost’IDENTIFIED BY ’paris’;

GRANT SELECT ON PROYECTOS.PROV_PARISTO ’usu_paris’@’10.9.0.0/255.255.0.0’;

GRANT SELECT ON PROYECTOS.PROV_PARISTO ’usu_paris’@’localhost’;

Ejercicio: los administradores de la base de datos de proyectos necesitan establecer una configuraciónde seguridad de acuerdo a las siguientes caracteristicas

Existe un usuario admin que puede conectarse desde cualquier punto de la red 10.9 que puedehacer lo siguiente

• Insertar, seleccionar, actualizar y borrar datos de cualquier tabla

• Puede crear también vistas nuevas

Existe un usuario jefe_londres que puede hacer cualquier cosa con los datos de cualquier tabladonde la ciudad sea Londres. Puede conectarse desde cualquier sitio.

Existe un usuario usu_continental que puede insertar y seleccionar datos en tablas donde la ciudadsea Atenas o Paris. Puede conectarse desde cualquier sitio.

Existe un usuario pruebas que puede hacer consultas a cualquier tabla. Solo puede hacerlo el quetenga cierta IP (la del compañero de al lado)

Solución:

6.6. Vistas 83

Page 88: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

drop user ’admin’@’10.9.0.0/255.255.0.0’;

create user ’admin’@’10.9.0.0/255.255.0.0’identified by ’1234’;

grant insert,select,update,delete, create viewon proyectos.* to ’admin’@’10.9.0.0/255.255.0.0’;

Para el segundo apartado hay que permitir a ‘jefe_londres’ que manipule las tablas donde la ciudad seaLondres. Las tablas que cumplen este requisito son proveedores, partes y proyectos.

Este requisito obliga a crear tres vistas separadas para cada una de las tres tablas.

create view partes_londres asselect * from partes

where ciudad=’Londres’;

create view proveedores_londres asselect * from proveedores

where ciudad=’Londres’;

create view proyectos_londres asselect * from proyectos

where ciudad=’Londres’;

create user ’jefe_londres’@’%’identified by ’londres’;

grant all privileges on proyectos.partes_londres,proyectos.proveedores_londres,proyectos.proyectos_londres

to ’jefe_londres’@’%’;

Para el tercer apartado

create user ’jefe_continental’@’%’identified by ’continental’;

create view partes_continentales asselect * from partes where

ciudad=’Atenas’ or ciudad=’Paris’;

create view proveedores_continentales asselect * from proveedores where

ciudad=’Atenas’ or ciudad=’Paris’;

create view proyectos_continentales asselect * from proyectos where

ciudad=’Atenas’ or ciudad=’Paris’;

grant insert,select onproyectos.proyectos_continentales,proyectos.partes_continentales,proyectos.proveedores.continentalesto’jefe_continental’@’%’;

create user ’pruebas’@’10.9.0.99’

84 Capítulo 6. Administración

Page 89: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

identified by ’prueba’;

grant select on proyectos.* to’pruebas’@’10.9.0.99’

6.7 Recuperando la clave de root

Para recuperar la clave de root de MySQL se necesitará la clave de “Administrador” de Windowso la clave de “root” de Linux.

Puede ser necesario manejar varias consolas, al menos dos.

En el modo especial que vamos a utilizar NO SE PUEDEN USAR LOS COMANDOS SET PASS-WORD. Sin embargo la sentencia UPDATE sigue funcionando, por lo que podremos usarla pararestaurar a una nueva clave conocida.

El proceso tiene varios pasos

1. Arrancar una consola e ir al directorio de MySQL. Se debe re-arrancar el mysqld con una opciónespecial para no utilizar los permisos. Para parar el proceso puede ser necesario parar el proceso“mysqld.exe”

mysqld --skip-grant-tables

Con esto el servidor de bases de datos arranca, ignorará todas las claves y permisos y se quedará ensegundo plano.

2. En otra consola, volvemos a conectar con mysql, pero ahora no hará falta poner clave a root.

mysql -u rootmysql> use mysql;mysql> select user,host,password from user;mysql> update user set password=PASSWORD("1234")

where user=’root’ and host=’localhost’;

3. Una vez hecho el update, podemos parar el proceso mysqld y volver a arrancarlo normalmentedisponiendo de la clave de root recuperada.

6.8 Copias de seguridad

6.8.1 Introducción

Las copias de seguridad permiten a un administrador recuperar los datos en casos de desastre informáti-co. En líneas generales cualquier desastre conllevará cierta pérdida de datos.

Existen diversos tipos de copia de seguridad:

Físicas: copiar los directorios de la base de datos. La ventaja es que es muy fácil de hacer. Elinconveniente es que MySQL no reconoce directorios que no estén gestionados por él.

Lógicas: son copias procesadas y generadas por el propio SGBD. Son un poco más difíciles dehacer (hay que aprender comandos) pero son muy fáciles de restaurar. Si hay muchos datos enmuchas tablas, las lógicas pueden llegar a ser muy lentas.

6.7. Recuperando la clave de root 85

Page 90: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Otra posibilidad es distinguir las copias de seguridad que se hacen “en caliente” o “en frío”.

“En frío”: el servidor SE PARA y nadie puede registrar datos nuevos o borrar datos viejos.

“En caliente”: el servidor no se para, aún a riesgo de que haya datos que no entren en esta copiade seguridad y se queden para la siguiente.

6.8.2 El comando mysqldump

El comando mysqldump es el más utilizado para hacer copias de seguridad lógicas en caliente. Estecomando se conectará al servidor de bases de datos y volcará los datos que pidamos a un fichero SQL.

Las opciones que maneja el programa son las siguientes

–single-transaction: con este parámetro hacemos que nuestra copia se procese como un único granconjunto de operaciones de lectura de datos. Esto permite asegurar que no hay solapamientos enoperaciones de cambios en los valores de los datos.

-u root -p: No siempre hace falta ser el administrador. Si existe un usuario‘dam1’@‘10.9.0.0/255.255.0.0‘ y dicho usuario tiene permisos de lectura en una base dedatos también los tendrá para hacer la copia de seguridad.

mysqldump -u root -p --single-transaction proyectos > copia1.sql

Es posible hacer copias de seguridad solo de ciertas tablas. Esto permite ahorrar tiempo en la copia yademás perturbar menos la carga de trabajo del SGBD.

Si por ejemplo, deseásemos copiar solo la tabla partes, basta con indicar su nombre detrás del nombrede la base de datos

mysqldump -u root -p --single-transaction proyectos partes > copia2.sql

6.8.3 Los “outfiles”

Los outfiles son archivos donde se pueden dejar los resultados de consultas. Proporcionan mucha versa-tilidad y flexibilidad, pero se deben tener los permisos correspondientes por parte del administrador delsistema operativo.

Por ejemplo, para hacer una copia de seguridad de los proveedores de Londres haríamos esto

mysql -u root -pmysql> use proyectos;mysql> select * into outfile ’prov_londres.txt"

from proveedores where ciudad=’Londres’;

Se podría poner una ruta como ‘C:prov_londres.txt’. Si no lo ponemos el archivo se crear en mysqldat-aproyectos

Supongamos que se pierden los datos de los proveedores de Londres

mysql> delete from proveedores where ciudad=’Londres’;

Para recargar datos guardados en un outfile se usaría algo como esto

mysql> load data infile ’prov_londres.txt’into table proveedores;

86 Capítulo 6. Administración

Page 91: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

6.8.4 Copias de seguridad incrementales

Las copias de seguridad completas son muy fáciles de restaurar. Sin embargo, si una BD es grande serámuy lenta de hacer, interrumpirá el trabajo del SGBD y el espacio en disco se agotará rápidamente.

Las copias de seguridad incrementales permiten almacenar solamente los cambios hechos desde uncierto momento. Por ejemplo, se podría hacer una copia completa el día 1 de cada mes y luego al finalde cada día, una incremental.

Si hubiera un fallo, por ejemplo, el día 17, se necesitarán aplicar las copias en orden empezando por lacompleta seguida de los 16 incrementos. Esto significa que la restauración de copias incrementales esmás pesada de hacer que las completas.

Para poder hacer copias incrementales, MySQL debe haber sido arrancado con la opción correcta.

mysqld --log-binmysql -u root -pmysql> update...#Esto obliga a volcar los nuevos logs que#se hayan podido generarmysqladmin -u root -p flush-logs

Usando los comandos mysqlbinlog podemos volcar cualquier archivo de log en un fichero mysql.

6.8. Copias de seguridad 87

Page 92: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

88 Capítulo 6. Administración

Page 93: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 7

Access

7.1 Introducción

Access es un SGBD “ofimático”. Una característica fundamental de su uso es que es monousuario.

Si se desea un SGBD multiusuario de MS, se necesitará utilizar SQL Server (Express)

Access ofrece un entorno de desarrollo integrado, lo que supone una ventaja en cuanto a la comparacióncon otros productos.

Como curiosidad histórica, Access proviene de un conjunto de bibliotecas llamados JetDB.

Access respeta el estándar ODBC que permite “compartir” bases de datos para que sean consultadas porprogramas que se conecten desde un sitio remoto.

7.2 El entorno

Al arrancar Access se ofrecen un conjunto de plantillas que contienen BD “prefabricadas” con tablasya creadas junto con las claves y las relaciones entre dichas tablas. Esto permite acelerar el tiempo dedesarrollo en ciertos casos, sin embargo en otros se deberá asumir el control y crear las tablas, claves yrelaciones desde cero.

Al crear una base de datos en blanco, Access ofrece un diseño inicial de la tabla en la que ya hay uncampo llamado “Id” de tipo “autonumérico”.

Un tipo autonumérico es un número que se incremente en uno de forma automática.

Se pueden quitar y poner columnas desde este entorno inicial así como indicar los tipos de los datos deforma sencilla. Se pueden marcar varios campos como “único”, es decir sus valores no se pueden repetir.Si un campo tiene la marca “se requiere” es que es obligatorio rellenarlo con algo.

Cuando un número se manipula para que muestre más o menos decimales no se va a modificar el númeroalmacenado.

Access permite indicar que una columna se va a utilizar a menudo para hacer búsquedas. Si algunosvalores se usan mucho la inserción será más cómoda.

El botón “Relaciones” permite establecer relaciones de clave ajena entre las tablas de una base de datos.

89

Page 94: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

7.2.1 Ejercicio

Utilizando el entorno de Access, crear la estructura de proveedores, partes y proyectos utilizada en eltema 4, en el archivo SQL “BD Proveedores, partes y proyectos”.

90 Capítulo 7. Access

Page 95: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 8

BBDD Objeto relacionales

8.1 Introducción

Un objeto es una representación de una entidad física que consta de atributos y métodos.

La herencia es la capacidad de compartir automáticamente atributos y/o métodos.

En BBDD una relación es lo mismo que una tabla. El nombre “relación” proviene del conceptomatemático de relación.

Las bases de datos objeto-relacionales son aquellas capaces de manipular relaciones utilizando concep-tos relacionados con la programación orientada a objetos.

Aparte de todo esto, las bases de datos objeto-relacionales suelen tener otras capacidades interesantes,como la manipulación de datos espaciales, textuales e incluso representaciones en XML.

XML es un formato de intercambio de datos basado en marcas.

En este tema veremos como utilizar PostgreSQL, que es un sistema de BBDD objeto-relacional queofrece las capacidades que hemos comentado.

8.2 Instalación de PostgreSQL

Durante la instalación de Postgres podremos elegir los directorios de instalación del programa y de datos.Se puede obtener una pequeña mejora en la eficiencia almacenando los datos en un disco duro distinto.

Por otro lado, en la instalación se nos pedirá la clave de administración del usuario principal (llamadopostgres).

8.3 Uso de Postgres en una máquina virtual

Las máquinas virtuales tienen diversos modos de funcionamiento que condicionan lo que tenemos quehacer para que alguien pueda acceder a nuestro Postgres (o MySQL) dentro de Virtual BOX

NAT: El anfitrión actúa de “router”

Red Interna: la comunicación solo se da entre VM

91

Page 96: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Puente: El SO virtualizado es una máquina más.

Solo host: la comunicación solo se da entre anfitrión y virtualizado.

8.3.1 Acceso a Postgres con VM en modo puente

Como la máquina virtual es una más de la red, bastará con configurar el SO virtualizado para que alguienpueda acceder a la IP del SO virtualizado y conectar con el puerto 5432 que es el de Postgres.

8.3.2 Acceso a Postgres con VM en modo NAT

Este modo ofrece una mayor seguridad, ya que VirtualBox actúa como un router con su propio cortafue-gos.

Para configurar el acceso a Postgres dentro de VirtualBox hay que configurar correctamente el envío depuertos.

El objetivo final es que cuando alguien conecte con la IP de nuestro SO virtualizado, VirtualBox autoriceesa entrada de tráfico. El proceso es el mismo que se conoce como “abrir puertos”.

Se debe autorizar el tráfico para el puerto TCP 5432 y reenviarlo a la IP del SO virtualizado sin ponernada en la IP del SO anfitrión (lo que indica que nos da igual la IP de origen que inicia la conexión).

8.3.3 Acceso a Postgres en modo “Solo host”

En este caso, se debe configurar el SO anfitrión para que reenvíe el tráfico hacia el SO virtualizado. Esdecir, el SO anfitrión debe tener capacidades de enrutamiento.

8.3.4 Acceso a Postgres en modo red interna

No funcionará si alguien se conecta desde otra máquina física distinta. El modo red interna es para hacerexperimentos y el tráfico generado nunca sale de la máquina.

8.4 Funcionamiento básico de Postgres

El funcionamiento es muy similar a MySQL. Hay un programa servidor que espera peticiones y nosconectamos a él con un programa cliente.

Un cliente puede ser gráfico o textual. En cualquiera de los dos casos, las operaciones que se puedenhacer son las mismas.

Al usar el interfaz gráfico podemos hacer distintas operaciones de creación con el botón derecho delratón mientras que en el interfaz textual deberemos lanzar comandos.

8.5 Comandos de Postgres

Postgres respeta el estándar ANSI SQL, al igual que MySQL, por lo que los comandos son iguales.

92 Capítulo 8. BBDD Objeto relacionales

Page 97: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

8.5.1 Creación de tablas

CREATE TABLE <nombre>(

<nombre_de_campo> <tipo>,<nombre_de_campo> <tipo>,<nombre_de_campo> <tipo>,<nombre_de_campo> <tipo>

);

Ejemplo:

CREATE TABLE partes(

id integer primary key,nombre varchar(20),color varchar(8),peso numeric(3,1),ciudad varchar(15)

);

Se pueden poner también claves ajenas, de la misma forma que se podía en MySQL.

CREATE TABLE propietarios(

id integer primary key,nombre varchar(20),id_parte integer,foreign key (id_parte)

references partes(id));

Al tener las tablas vacías, si intentásemos insertar este valor, veremos que no se puede. Si el propietariocon el código 3, llamado Juan posee la parte 1, deberíamos escribir lo siguiente:

INSERT INTO propietarios VALUES (3, ’Juan’, 1 );

Para que esto funcione, primero deberíamos hacer algo como esto

INSERT INTO partes VALUES (1,’Tornillo’, ’Rojo’, 12.3, ’Londres’ );

Para ver las tablas que hay se puede usar el pseudocomando d. Para ver la descripción de una tablaconcreta se puede usar d <nombre_de_tabla>

Herencia

La herencia permite que una tabla herede automáticamente los campos y restricciones de otra tabla a laque se llama tabla padre.

Por ejemplo, supongamos que en una empresa hay empleados con id y sueldo, pero despues hay variostipos de empleado, como investigador, becario y administrativo.

Creación de la tabla padre:

CREATE TABLE empleados(

id integer primary key,

8.5. Comandos de Postgres 93

Page 98: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

nombre varchar(50),sueldo numeric(6,2)

);

Creación de la tabla hija investigadores

CREATE TABLE investigadores(

nombre_proyecto varchar(30)) INHERITS (empleados);

Ejercicio: insertar el investigador con id 5, con nombre ‘Tomas’, sueldo 3500 y nombre_proyecto ‘Con-sola’.

Respuesta: El código SQL es el siguiente:

INSERT INTO investigadores VALUES (5,’Tomas’, 3500, ’Consola’);

Si ahora consultamos las dos tablas veremos que el valor SE INSERTA EN LAS DOS TABLAS

Podemos repetir la inserción de valores, las veces que deseemos, y siempre se propagarán los valores

INSERT INTO investigadores VALUES ( 7,’Julian’, 5100, ’Antena’ );

INSERT INTO investigadores VALUES ( 42, ’Andres’, 2400, ’Disco’ );

Ejercicio: Construir las tablas Becarios y Administrativos que heredan de Empleados. La tabla Becariostiene los campos duracion_contrato (integer) y la Administrativos tiene los campos categoria varchar(10)y titulacion varchar(15). Insertar valores en las dos tablas.

CREATE TABLE becarios ( duracion_contrato integer )inherits (empleados);

INSERT INTO becarios VALUES ( 4, ’Luis’, 1900, 9 );

CREATE TABLE administrativos(

categoria varchar(10),titulacion varchar(15)

) INHERITS (empleados);

INSERT INTO administrativos VALUES(

13, ’Angel’, 2450,’B1’, ’Tec. Sup. DAM’

);

Ejercicio: Borrar el empleado cuyo código es el número 7. Examinar las tablas investigadores y emplea-dos para ver qué ha ocurrido.

delete from empleadoswhere id=7;

Ejercicio: Borrar el investigador cuyo código es el número 42. Examinar las tablas investigadores yempleados para ver qué ha ocurrido.

94 Capítulo 8. BBDD Objeto relacionales

Page 99: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

delete from investigadoreswhere id=42;

Ejercicio: La empresa en la que trabajamos ha decidido crear dos categorías adicionales de empleados.Los becarios repartidores y los becarios auxiliares.

La tabla Repartidores tiene un solo campo llamado km y que es de tipo numeric (6,1). La tabla aux-iliares tiene dos campos denominados horas_extra, de tipo integer, y el campo tipo_acceso, que es unvarchar(30).

Insertar valores en Repartidores y Auxiliares y comprobar qué ocurre en las diversas tablas.

CREATE TABLE repartidores(

km numeric(6,1)) INHERITS (becarios);

CREATE TABLE auxiliares(

horas_extra integer,tipo_acceso varchar(30)

) INHERITS (becarios);

Un repartidor tiene un id (22), un nombre (Ramon), un sueldo (870), una duración de contrato (6 meses),y un kilometraje 1450.

INSERT INTO repartidores VALUES(

22, ’Ramon’, 870, 6, 1450);

Un auxiliar tiene un id (33), un nombre (Ricardo), un sueldo (760),una duración de contrato (18 meses),unas horas extra (85), y un tipo de acceso (Convenio)

INSERT INTO auxiliares VALUES(

33, ’Ricardo’, 760, 18,85, ’Convenio’

);

Conclusión: la herencia funciona como un árbol de tablas, en las cuales el SGBD propaga correctamentetodos los datos hacia las tablas padre. Además, al construir las tablas no es necesario construir ningunaclave ajena ni especificar borrados o actualizaciones en cascada.

Tipos de herencia

En Postgres, la herencia no es absoluta. Es decir, en nuestro ejemplo de empleados, becarios y adminis-trativos, podemos tener datos que aparezcan en una tabla, pero no en las tablas hija.

En el ejemplo que hemos visto, la pregunta sería ¿puede haber alguien que esté simplemente en la tablaempleados, sin estar en ninguna de las hijas?. SÍ

Probemos por ejemplo este SQL.

INSERT INTO empleados VALUES(

8.5. Comandos de Postgres 95

Page 100: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

5, ’Alicia’, 1950);

Cuando en una tabla hay datos suyos y datos de otras tablas que heredan, la selección de datos puederequerir comandos especiales.

¿Cómo se pueden recuperar simplemente las personas que son empleados normales, sin tener en cuentabecarios o investigadores?.

Para recuperar tales datos se debe hacer lo siguiente

select * from only empleados;

En resumen: si se desea recuperar los datos de una tabla sin incluir los datos de sus posibles tablas hija,se debe hacer un select * from only <nombre_de_la_tabla>

8.6 Creación de tipos de datos

Los lenguajes de programación ofrecen soporte para crear nuestros propios tipos de datos (en Java, sellaman “clases”). PostgreSQL ofrece soporte para crear nuestros propios tipos o clases.

Una vez creado un tipo, podemos crear tablas cuyos campos sean del tipo que hemos creado.

Para crear un tipo de datos, se debe hacer lo siguiente

CREATE TYPE Rectangulo AS(

la_base int,altura int

);

Y para usar ese tipo en un campo, se usa de la misma forma que cualquier otro tipo.

CREATE TABLE rectangulos(

id int primary key,r Rectangulo

);

¿Como se insertan datos en estas tablas?. La inserción de tipos creados por nosotros requiere usar ‘(<datos> )’.

INSERT INTO rectangulos VALUES(

1, ’(2, 12)’);

INSERT INTO rectangulos VALUES(

2, ’(6, 2)’);

INSERT INTO rectangulos VALUES(

3, ’(2, 12)’);

96 Capítulo 8. BBDD Objeto relacionales

Page 101: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

Ejercicio: crear una tabla que pueda almacenar el tipo de datos Punto3D. Un punto en 3D tiene trescoordenadas x, y, z que pueden ser números con hasta 2 decimales. Insertar algunos valores en la tabla.

CREATE TYPE Punto3D AS(

x decimal(8,2),y decimal(8,2),z decimal(8,2)

);

CREATE TABLE puntos(

id integer primary key,punto Punto3D

);

insert into puntos values(

1,’(2.25, 6.57, 8.96 )’

);

insert into puntos values(

2,’(12.25, 9.7, 5.6 )’

);insert into puntos values(

3,’(9.65, 8.7, 3.6 )’

);

Un detalle importante es que cuando los valores sean cadenas NO SE DEBEN DEJAR ESPACIOSENTRE LAS COMAS.

Ejercicio: crear un tipo de datos Linea que tenga un campo m (que es la pendiente de la recta) de tipodecimal(8,2), un campo k que es la constante a sumar, de tipo decimal(8,2) y un campo orientacion quees varchar(5). Crear una tabla que permita almacenar valores e insertar 3.

CREATE TYPE Linea AS(

m decimal(8,2),k decimal(8,2),orientacion varchar(5)

);

CREATE TABLE lineas(

id integer primary key,l Linea

);

Cuidado: al insertar información de los varchar, se deben evitar los espacios entre comas.

INSERT INTO lineas VALUES(

1, ’(3,2,N-S)’

8.6. Creación de tipos de datos 97

Page 102: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

);

INSERT INTO lineas VALUES(

2, ’(2.41,-7.22,SE-NO)’);

INSERT INTO lineas VALUES(

3, ’(-4.46,-12.33,NE-SO)’);

8.7 Soporte documental

El procesamiento de documentos involucra resolver una serie de problemas

Stop words: artículos, preposiciones. En general, palabras que no aportan (casi) nada al discurso.

Relevancia: es una medida de como de bueno es un documento en relación a una búsqueda.

Rendimiento: es la capacidad de un programa para ser eficiente en las búsquedas de texto.

Postgres ofrece soporte documental permitiendo almacenar campos de tipo text.

Para poder hacer búsquedas hay que conocer algunas funciones de Postgres.

La función to_tsvector coge un campo y lo procesa de forma eficiente para poder buscar en él.

La función to_tsquery coge una cadena y la procesa como si fuera una búsqueda.

El operador que intenta asociar una búsqueda con un texto es el @@.

Una prueba muy simple

select to_tsvector(’Postgresql es unsistema gestor de base de datos’)@@to_tsquery (’mysql’);

select to_tsvector(’Postgresql es unsistema gestor de base de datos’)@@to_tsquery (’mysql & postgresql’);

select to_tsvector(’Postgresql es unsistema gestor de base de datos’)@@to_tsquery (’mysql | postgresql’);

¿Qué capítulo es el que habla de algo virtual? ¿y de VirtualBox?

select id from capituloswhere

to_tsvector(texto)@@

98 Capítulo 8. BBDD Objeto relacionales

Page 103: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

to_tsquery(’virtual’);

select id from capituloswhereto_tsvector(texto)@@to_tsquery(’VirtualBox | (Virtual & Box)’);

Ejercicio: ¿Qué número de capítulo es el que habla de algún cliente de Postgres?

select id from capituloswhereto_tsvector(texto)@@to_tsquery(’cliente’);

Ejecuta estas dos consultas y comprueba el resultado

select to_tsvector(’Postgres es un cliente de BD orientado a objetos con una gran capacidad de extension’);

select to_tsvector(’spanish’, ’Postgres es un cliente de BD orientado a objetos con una gran capacidad de extension’);

Si indicamos el idioma del texto, Postgres utilizará una base de datos interna para eliminar las stop-wordsadecuadas para ese idioma.

¿Qué ocurre si una misma palabra buscada aparece en dos o más documentos?.

8.8 Sistemas de información geográfica

Son programas especializados en el procesamiento de información geográfica. Esta información puedeprocesarse en dos formas

2 Dimensiones: es un mecanismo más rápido pero más impreciso

3 Dimensiones: se obtiene más precisión pero suele requerir más operaciones, lo que lo hace máslento.

Los SIG ofrecen (a veces) soporte para el cálculo de rutas, lo que es un problema de difícil solucióncuando hay muchos pueblos/ciudades y muchas rutas.

8.8.1 Uso de PostGIS

Distintos usuarios pueden haber utilizado distintos sistemas de referencia. Si se proporciona al GIS lasdiferencias entre los dos puntos (0,0) el GIS puede calcular automáticamente los movimiento entre dosmapas que hayan usado distintos Sistemas de Referencia.

En un SIG tenemos tres elementos principales para almacenar información geográfica

Puntos

Líneas

Polígonos

Para crear una base de datos, podemos hacer lo siguiente desde la línea de comandos o desde el pgAdmin.

8.8. Sistemas de información geográfica 99

Page 104: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

create database espanya;

Esto indica que queremos que nuestra BD actúe como una extensión PostGIS, es decir como una basede datos SIG

create extension postgis;

Creamos una tabla de puntos

create table puntos(

id integer primary key,punto geometry(POINT)

);

E insertamos algunos valores

insert into puntos values(

1,ST_GeomFromText(’POINT(5 4)’)

);

insert into puntos values(

2,ST_GeomFromText(’POINT(6 5.2)’)

);

insert into puntos values(

3,ST_GeomFromText(’POINT(9 7)’)

);

Creamos ahora una tabla de líneas

create table lineas(

id integer primary key,linea geometry(LINESTRING)

);

Insertamos algunos valores

insert into lineas values(

10,ST_GeomFromText(

’LINESTRING(5 4, 7 5, 9 7)’)

);

insert into lineas values(

20,ST_GeomFromText

100 Capítulo 8. BBDD Objeto relacionales

Page 105: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

(’LINESTRING(4 1, 4 4,5 7)’

));

insert into lineas values(

30,ST_GeomFromText(

’LINESTRING(6 2, 6 5, 4 7)’)

);

Se puede calcular la longitud de una ruta utilizando la función ST_Length.

Ejercicio: crear una consulta select que nos dé la carretera de mayor longitud.

select id, ST_Length(linea)from lineaswhere ST_Length(linea)=(

select max(ST_Length(linea))from lineas

);

Aparte de calcular longitudes, es posible comprobar otras cuestiones de interés geográfico.

¿Está una cierta ciudad en una cierta ruta?. Para averiguar esto, tenemos la función ST_Within. Estafunción devuelve ‘f’ si no se cumple o ‘t’ en el caso de que sí se cumpla.

Ejercicio: listar los codigos de punto que están dentro de alguna ruta de la tabla lineas.

PostGIS (que es como se llama la extensión que hemos instalado, también soporta la definición de áreas).Para ello, hay que crear tablas que almacenen tipos POLYGON.

create table parcelas(

id integer primary key,parcela geometry(POLYGON)

);

insert into parcelas values(

1,ST_GeomFromText(

’POLYGON((

0 0,0 1000,100 1000,100 0,0 0

))’)

);

8.8. Sistemas de información geográfica 101

Page 106: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

Bases de datos, Versión 1.1

insert into parcelas values(

2,ST_GeomFromText(

’POLYGON((

100.1 0,100.1 1000,200 1000,200 0,100.1 0

))’)

);

select id, ST_AsText(parcela) from parcelas;

insert into parcelas values(

3,ST_GeomFromText(

’POLYGON((

199.5 1000,300 1000,300 0,199.5 0,199.5 1000

))’

));

Ejercicio: Utilizando la función ST_Intersects averiguar qué parcelas tienen intersecciones con otrasparcelas.

select p1.id, p2.idfrom parcelas as p1,parcelas as p2where ST_Intersects(

p1.parcela, p2.parcela)=’t’ and p1.id<>p2.id;

Se puede calcular el area de un polígono cualquiera por irregular que sea o independientemente de loshuecos son ST_Area

select id, ST_Area(parcela) from parcelas;

102 Capítulo 8. BBDD Objeto relacionales

Page 107: Bases de datos - ecaths1.s3.amazonaws.comecaths1.s3.amazonaws.com/bduagrm/752643670.Base de Datos con...Índice general 1. Introducción 3 1.1. Historia de las bases de datos.

CAPÍTULO 9

Índices y tablas

genindex

modindex

search

103