Introducción a las BD

(1)

Dra. Marta E. Zorrilla Pantaleón

Departamento de Matemáticas, Estadística y Computación

Universidad de Cantabria

Introducción a las BD

(2)

Tabla de contenido

Aplicaciones de BDs.

Concepto de Base de Datos y SGBD.

De los sistemas de ficheros a la BD relacional.

Razones que justifican el uso de BD.

Bases de datos relacionales.

El estándar SQL.

Restricciones de integridad. BD activas.

Transacciones.

(3)

• ¿Qué bases de datos conocéis?

• ¿Con qué aplicaciones de bases de datos trabajáis?

• ¿Qué interfaces presentan?

• Ejemplos:

– Biblioteca

– Gestión académica

– Reservas de hoteles, aviones, …

– Servicios bancarios: tarjetas, cuentas, préstamos, …

– Compras y ventas

– Etc.

• ¿Qué bases de datos conocéis?

• ¿Con qué aplicaciones de bases de datos trabajáis?

• ¿Qué interfaces presentan?

• Ejemplos:

– Biblioteca

– Gestión académica

– Reservas de hoteles, aviones, …

– Servicios bancarios: tarjetas, cuentas, préstamos, …

– Compras y ventas

– Etc.

Aplicaciones de BD Aplicaciones de BD

(4)

Bases de Datos. Finalidad Bases de Datos. Finalidad

Bases de Datos. Finalidad

Base de Datos

Base de Datos:

colección organizada de datos, relativa a un problema concreto,

que puede ser compartida por un conjunto de usuarios/aplicaciones.

CONTROLAR INFORMACIÓN ALMACENAR CONSULTAR ACTUALIZAR DATOS RELACIONES RESTRICCIONES DATOS RELACIONES RESTRICCIONES

Sistema Gestor de Bases de Datos

Sistema Gestor de Bases de Datos:

(5)

• De 1950 a 1960, se desarrollaron las cintas magnéticas para el

almacenamiento de datos. Lectura secuencial. COBOL.

• En la década de los 70, aparición de los discos magnéticos lo que permitió

el acceso directo a los datos. BD jerárquicas y en red.

– Tratamiento de información requería conocer detalles de implementación (bajo nivel)

– Codificación de consultas de forma procedimental

– Codd definió el modelo relacional (1970) Æ modelo teórico bien fundamentado

– .

• De 1950 a 1960, se desarrollaron las cintas magnéticas para el

almacenamiento de datos. Lectura secuencial. COBOL.

• En la década de los 70, aparición de los discos magnéticos lo que permitió

el acceso directo a los datos. BD jerárquicas y en red.

– Tratamiento de información requería conocer detalles de implementación (bajo nivel)

– Codificación de consultas de forma procedimental

– Codd definió el modelo relacional (1970) Æ modelo teórico bien fundamentado

– .

Revisión histórica de los sistemas de Bases de Datos Revisi

Revisióón histn históórica de los sistemas de Bases de Datosrica de los sistemas de Bases de Datos

(6)

• Pero hasta 1980 no aparecieron gestores relacionales comerciales

(Oracle, IBM DB2, Ingres,…) con buen rendimiento y más fáciles

de diseñar y mantener (independencia física y lógica)

– Estudios BD distribuidas y paralelas

– Inicio en BD orientadas a objetos

• Desde 1990:

– BD relacionales orientadas a objetos

– BD dimensionales y OLAP

– XML

– Minería de datos

• Pero hasta 1980 no aparecieron gestores relacionales comerciales

(Oracle, IBM DB2, Ingres,…) con buen rendimiento y más fáciles

de diseñar y mantener (independencia física y lógica)

– Estudios BD distribuidas y paralelas

– Inicio en BD orientadas a objetos

• Desde 1990:

– BD relacionales orientadas a objetos

– BD dimensionales y OLAP

– XML

– Minería de datos

Revisión histórica de los sistemas de Bases de Datos Revisi

(7)

Niveles de abstracción

Niveles de abstracci

Niveles de abstracció

ón

n

Programa 1

Programa 1 Programa 2Programa 2 Programa nPrograma n

Sistema Gestor de Bases de Datos (SGBD) Nivel Lógico Nivel Interno Vista A

La finalidad de trabajar con técnicas de BD es disfrutar de

una visión abstracta de los datos que facilite el desarrollo y

uso de aplicaciones.

(8)

Bases de Datos. Justificación Bases de Datos.

Bases de Datos. JustificaciJustificacióónn

•

• Optimización en la gestión de la información.

Flexibilidad de adaptación a cada problema.

Control de la integridad de los datos.

Garantía sobre la consistencia de la información.

Facilidad de acceso concurrente.

Independencia física y lógica de los datos.

(9)

BD Relacional I

NOMBRE PROFESION LOCALIDAD Pedro Luis María Ana profesor estudiante estudiante estudiante Santander Santander Las Palmas Madrid

Personal

NOMBRE LOCALIDAD Luis María Ana Santander Las Palmas Madrid

SELECT NOMBRE, LOCALIDAD FROM Personal

WHERE PROFESION = ”estudiante”

Los datos se conciben agrupados en forma de tablas Cada fila establece una relación entre un conjunto de valores

(10)

BD Relacional II

ENTIDAD NOMBRE POBLACION DIRECCION 0893 Santander 0059 Popular 3428 Bilbao Vizcaya 5632 Banesto BANCOS OFICINAS 0893 001 Madrid Castellana, 73

3428 022 Las Palmas Triana, 21

0893 022 Gáldar R. Moreno, 3

5632 213 Oviedo Uría, 43

0893 300 Barcelona Diagonal, 435

• Toda tabla tiene una columna o conjunto de columnas

que permiten identificar cada una de sus filas; éstas

componen la llamada clave principal de la tabla.

• Los valores de la clave principal no se pueden repetir.

• Unas tablas se refieren a otras

mediante vínculos de tipo

jerárquico.

• Este vínculo de referencia

entre dos tablas se establece

mediante columnas de idénticos

tipos de datos en las dos tablas.

• La referencia de una fila de una tabla a otra de la otra

tabla se produce cuando ambas tienen el mismo valor.

(11)

Tipos de datos

Cadena de caracteres (character string).

Numérico (numeric).

Fecha y hora (datetime).

Objeto grande (large object, LOB).

Tipos definidos por el usuario.

Cada carácter requiere un byte para su almacenamiento.

Enteros: Cortos (ocupan 2 bytes).

Largos (ocupan 4 bytes).

Decimales: definidos por su precisión y escala.

Notación científica: Simple precisión (ocupan 4 bytes).

Doble precisión (ocupan 8 bytes).

Diferentes opciones según nivel de precisión.

(12)

Índices

Í

Índices

ndices

Búsquedas más ágiles ….

pero supone una sobrecarga en actualizaciones

Restricción de unicidad

No permite repeticiones del valor en la columna

o columnas afectadas por el índice

Índices únicos:

PRIMARY KEY, UNIQUE, CREATE UNIQUE INDEX

Índices con duplicados:

(13)

Índices

Í

Índices

ndices

Cod_art Descripción WRD ACC Access-97 Word-97 EXC ACC Access-2002 Excel-2000 ... ... ARTÍCULOS Versión 97 97 2000 2002 ... Unidades 50 4 ... LÍNEAS_DE_PEDIDO Num_ped 742 742 742 ... Cod_art ACC EXC WRD ACC ... Versión 2002 2000 97 2002 ... Num_lin 1 2 3 1 ... 30 849 30 Cod_art WRD ACC EXC ACC ... Versión 97 97 2000 2002 ... Índice de unicidad Cod_art WRD ACC EXC ACC ... Versión 97 2002 2000 2002 ...

Índice con repeticiones

(14)

El problema del diseño I El problema del dise

El problema del diseñño Io I

PROPIETARIOS: DNI LOCALES: CODIGO

NOMBRE UBICACION

DIRECCION SUPERFICIE

PROPIETARIOS: DNI LOCALES: CODIGO

NOMBRE UBICACION

DIRECCION SUPERFICIE

Ejemplo aclaratorio

Repetición de información

Posibilidad de contradicciones en los datos

Problemas en inserciones

Pérdida de información al borrar

Repetición de información

Posibilidad de contradicciones en los datos

Problemas en inserciones

Pérdida de información al borrar

Problemas del diseño

Primera alternativa

DNI NOMBRE DIRECCION CODIGO UBICACION SUPERFICIE

(15)

El problema del diseño II El problema del dise

El problema del diseñño IIo II

Pérdida de dependencias funcionales

Problemas del diseño

Segunda alternativa

DNI NOMBRE DIRECCION

Propietarios

CODIGO UBICACION SUPERFICIE

(16)

El problema del diseño III El problema del dise

El problema del diseñño IIIo III

Sólo un propietario para cada local

Problemas del diseño

Tercera alternativa

Propietarios

CODIGO UBICACION SUPERFICIE DNI

(17)

El problema del diseño IV El problema del dise

El problema del diseñño IVo IV

La referencia entre tablas siempre es una relación “de 1 a n” o “de n a 1”

Si se desea que un propietario pueda tener varios locales y, al mismo tiempo, que un

local pueda se de varios propietarios, la relación es simétrica, es “de n a n” y no

puede ser resuelta con sólo dos tablas. Para conseguirlo, es necesario introducir una

tabla auxiliar que tenga relaciones de “de n a 1” con las de propietarios y locales.

Un propietario puede tener varios locales (n) mientras

que un local sólo puede ser de un propietario (1).

Tercera alternativa

Propietarios

CODIGO UBICACION SUPERFICIE DNI

Locales

Cuarta alternativa

CODIGO UBICACION SUPERFICIE CODIGO

DNI

Propietarios

(18)

Sistemas distribuidos

Para distribuir los datos de una tabla entre varias sedes, hay varias alternativas:

Réplica. Se conservan varias copias idénticas de una misma tabla en diferentes sedes.

Fragmentación. La tabla se divide en varios fragmentos que se guardan en

emplazamientos diferentes.

La fragmentación puede ser horizontal, cuando se distribuyen filas; vertical, si son

columnas las que se reparten; o mixta.

Réplica y fragmentación. La tabla se divide en varios fragmentos.

El sistema conserva varias réplicas de estos fragmentos en diferentes sedes.

Los sistemas centralizados realizan todas sus operaciones en un único

sistema informático.

La distribución entre varias sedes permite que los datos residan donde se han

generado o donde son más necesarios.

En el sistema centralizado los datos tienen una sola ubicación y en el distribuido, residen

en varios emplazamientos.

(19)

Lenguajes de BD

SQL

, lenguaje declarativo cuya base se encuentra en el álgebra

relacional (lenguaje procedimiental). Comercial. Estándar.

QBE

, lenguaje gráfico. Introducido en algunos gestores.

Datalog

, a nivel investigador, no comercial. Prolog.

Ambos se apoyan en dos lenguajes de consulta

formales basados en lógica matemática:

• el cálculo relacional de tuplas y de dominios

(20)

Lenguajes de BD: QBE

(21)

El lenguaje SQL

SQL

(Structured Query Language)

SQL

(Structured Query Language)

Lenguaje declarativo de acceso a los datos.

Estándar para las bases de datos relacionales.

Incluye la capacidad de actuar tanto sobre la estructura de la base

de datos como sobre sus propios datos.

Desarrollado en el San José Research Center (IBM)

Fue utilizado por primera vez en 1970.

En 1986: ANSI (American National Standards Institute) e

ISO (International Standards Organization)

(22)

SQL-92 y SQL-99

SQL

SQL-

-92 y SQL

92 y SQL-

-99

99

SQL-92 incorpora:

• Nuevos operadores relacionales: OUTER JOIN y JOIN • SQL dinámico

• El parámetro SQLSTATE para gestión de errores • Cursores de desplazamiento (scroll cursor).

• Modo de acceso (lectura o lectura/escritura) y nivel de aislamiento de las transacciones.

• Definir dominios (CREATE DOMAIN).

En la actualidad, se trabaja con el SQL:1999 (parte del SQL3). Las características más relevantes son:

• Nuevos tipos de datos: LOB, BOOLEAN, ROW, ARRAY, DISTINCT. • Posibilidad de definir nuevos tipos de datos por parte del usuario. • Disparadores (triggers), vistas actualizables

• Cursores (punteros) sensitivos. Queries recursivos. • Definición de roles de usuario

(23)

SQL 2003

Foundation Included in SQL/Foundation, so this part no longer exists.

Part 8 - SQL/Objects

Extensions to SQL to deal with time-oriented data types.

SQL specialization of the X-Open XA specification. Project has been canceled.

Embedded SQL.

Persistent Stored Modules: Stored routines, external routines, and procedural language extensions to SQL.

Call Level Interface: Corresponds to ODBC.

Online Analytical Processing: Amendment describing functions and operations useful for analytical processing.

SQK Data definition and data maniputlation syntax and semantics, including SQL embedded in non-object programming languages.

Structure of the standard and relationship between various parts. Common definitions and concepts. Conformance requirements

statement. Explanation Postposed Part 7 -SQL/Temporal Canceled Part 6 -SQL/Transaction Foundation Part 5 -SQL/Bindings Completed Part 4 - SQL/PSM Completed Part 3 - SQL/CLI SQL/OLAP Completed Part 2 -SQL/Foundation Completed Part 1 -SQL/Framework State Part

(24)

SQL 2003

SQL and XML

Java Routines and Types: Routines using the Java™ Programming Language (Persistent Stored SQLJ)

Replication facilities for SQL. The goal is to define syntax and semantics to allow definition of replication schemes and rules, including rules for resolution of conflicts.

Information and Definition Schemas.

INFORMATION_SCHEMA (85 vistas)

Object Language Bindings: Specifies the syntax and semantics of embedding SQL in Java™.

Management of External Data: Adds syntax and definitions to SQL/Foundation to allow SQL access to non-SQL data sources (files). Explanation Completed Part 14 - SQL/XML Completed Part 13 - SQL/JRT Canceled Part 12 -SQL/Replication Completed Part 11 -SQL/Schemata Completed Part 10 - SQL/OLB Completed Part 9 - SQL/MED State Part

SQL/MM: especificación de tipos de datos abstractos (aprob. 2006 ) Part 1: Framework Part 5: Still image

Part 2: Full Text Part 6: Data mining Part 3: Spatial

SQL/MM: especificación de tipos de datos abstractos (aprob. 2006 )

(25)

SQL:2003

• Nuevos tipos de datos:

MULTISET, BIGINT y XML

• Columnas calculadas en tablas (valores escalares)

• Funciones escalares y que devuelven tablas

• Creación de tablas: LIKE , AS

• MERGE: permite la combinación de operaciones de inserción y

actualización en una sola instrucción

• Generadores de secuencia.

(26)

El lenguaje SQL: manipulación

El lenguaje SQL: manipulaci

ón

ó

n

INSERT INTO PROPIETARIOS (DNI, NOMBRE, DIRECCION) VALUES (‘13234567R‘, ‘Sanz, Luis‘, ‘Gran Vía 26‘)

PROPIETARIOS DNI NOMBRE DIRECCION LOCALES CODIGO DNI UBICACION SUPERFICIE

Insertar una nueva fila en la tabla

PROPIETARIOS

SELECT CODIGO, UBICACION, NOMBRE, DIRECCION FROM LOCALES, PROPIETARIOS

WHERE LOCALES.DNI = PROPIETARIOS.DNI AND SUPERFICIE > 200

SELECT CODIGO, UBICACION, NOMBRE, DIRECCION FROM LOCALES, PROPIETARIOS

WHERE LOCALES.DNI = PROPIETARIOS.DNI AND

SUPERFICIE > 200

Encontrar los locales con superficie mayor que 200 y su propietario

CODIGO UBICACION NOMBRE DIRECCION

L-31 Alta 236 Sanz, Luis Gran Vía 26 L-234 Bailén 46 Laso, Ana Isabel II 38 L-9 Cuesta 2 Sanz , Luis Gran Vía 26 L-302 Becedo 10 Fe, Pedro

Resultado

UPDATE PROPIETARIOS SET DIRECCION =‘Alta 87’ WHERE DNI = ‘20333444F‘

Modificar la dirección del propietario cuyo D.N.I. es 20333444F

DELETE FROM LOCALES WHERE CODIGO = ‘L-234‘

Borrar el local de código L-234

(27)

CREATE DOMAIN Estatura DEC(3,2)

CONSTRAINT Valor_estatura CHECK (Estatura > 1,75)

Mediante la instrucción CREATE DOMAIN (SQL-99) se pueden definir un tipo de dato de usuario a partir de un tipo de dato estándar (no la incluyen todos los gestores)

Otras restricciones I

CREATE TABLE JUGADORES

(DNI CHAR(10) NOT NULL, NOMBRE CHAR(25) NOT NULL, DIRECCION CHAR(30) NOT NULL, TELEFONO CHAR(15),

SEXO CHAR(1) CHECK ( SEXO in (“M”, “F”) NOT NULL, FE_ALTA DATE DEFAULT today NOT NULL, ESTATURA DEC(3,2)

CONSTRAINT Valor_estatura CHECK (ESTATURA > 1,75) PRIMARY KEY ( DNI ));

CREATE TABLE JUGADORES

(DNI CHAR(10) NOT NULL,

NOMBRE CHAR(25) NOT NULL,

DIRECCION CHAR(30) NOT NULL,

TELEFONO CHAR(15),

SEXO CHAR(1) CHECK ( SEXO in (“M”, “F”) NOT NULL,

FE_ALTA DATE DEFAULT today NOT NULL,

ESTATURA DEC(3,2)

CONSTRAINT Valor_estatura CHECK (ESTATURA > 1,75) PRIMARY KEY ( DNI ));

Otras restricciones:

Otras restricciones

Otras restricciones:

- Valores requeridos

- Dominio de valores:

* atributo

* relación

Ejemplo: para cada fila de la tabla LOCALES, los valores de DNI_propietario y DNI_arrendatario no pueden ser iguales.

Codigo DNI_propietario DNI_arrendatario Ubicacion Superficie DNI Nombre Direccion PERSONAS LOCALES X

ALTER TABLE LOCALES WITH NOCHECK ADD

(28)

Otras restricciones II

Asertos y disparadores

CREATE ASSERTION restriccion_suma CHECK

(not exists (select * from sucursal

where (select sum(importe) from prestamo

where prestamo.nombreSucursal= sucursal.nombreSucursal) >= (select sum(saldo) from cuenta

where cuenta.nombreSucursal = sucursal.nombreSucursal)))

Mediante la instrucción CREATE ASSERTION (SQL-99) se puede expresar una condición que la base de datos debe satisfacer siempre (no la incluyen todos los gestores)

Los triggers (disparadores) son procesos predefinidos que entran en acción en respuesta a eventos específicos de manipulación de datos (insert, update, delete).

Son más flexibles que los asertos para expresar restricciones semánticas. Generalmente se utilizan para:

• recoger restricciones complejas • automatizar procesos

• anotar acciones (log)

(29)

Otras restricciones II

Ejemplo: para cada fila de la tabla LOCALES, los valores de DNI_propietario y DNI_arrendatario no pueden ser iguales.

Codigo DNI_propietario DNI_arrendatario Ubicacion Superficie DNI Nombre Direccion PERSONAS LOCALES X

Reglas de negocio

ALTER TABLE LOCALES WITH NOCHECK ADD

CONSTRAINT CK_locales CHECK (DNI_propietario <> DNI_arrendatario)

Los triggers son procesos predefinidos que entran en acción en respuesta a eventos específicos de manipulación de datos.

Codigo DNI_propietario DNI_arrendatario Ubicacion Superficie

L-31 L-234 L-9 L-302 Alta 236 Bailén 46 Cuesta 2 Becedo 10 220 350 280 255 50501502 40401402 30301302 40401402 60601602 50501502 70701702 60601602

(30)

CREATE TRIGGER CTRL_locales ON LOCALES FOR INSERT, UPDATE AS DECLARE @num int

SELECT @num=count(*) FROM inserted I WHERE I.dni_propietario=I.dni_arrendatario

IF (@num>0) BEGIN

RAISERROR ('El DNI del propietario no puede coincidir con el DNI del Arrendatario.', 16, 1) goto on_error END GoTo fin on_error: ROLLBACK TRANSACTION fin:

CREATE TRIGGER CTRL_locales ON LOCALES FOR INSERT, UPDATE AS DECLARE @num int

SELECT @num=count(*) FROM inserted I WHERE I.dni_propietario=I.dni_arrendatario

IF (@num>0) BEGIN

RAISERROR ('El DNI del propietario no puede coincidir con el DNI del Arrendatario.', 16, 1) goto on_error END GoTo fin on_error: ROLLBACK TRANSACTION fin:

Codigo DNI_propietario DNI_arrendatario Ubicacion Superficie

L-234 L-302 Bailén 46 Becedo 10 350 255 60601602 60601602 50501502 60601602 inserted UPDATE LOCALES SET DNI_propietario = '60601602' WHERE DNI_propietario = '40401402' UPDATE LOCALES SET DNI_propietario = '60601602' WHERE DNI_propietario = '40401402'

Ejemplo de trigger

Ejemplo de

(31)

create trigger descubierto after update on cuenta

referencing new row as nueva_fila

for each row

when nueva_fila.saldo < 0

begin atomic

insert into prestamo values

(nueva_fila.numero_cuenta, nueva_fila.nombre_sucursal,-

nueva_fila.saldo);

insert into prestatario

(select nombre_cliente, numero_cuenta

from impositor

where nueva_fila.numero_cuenta = impositor.numero_cuenta);

update cuenta set saldo = 0

where cuenta.numero_cuenta = nueva_fila.numero_cuenta

end

create trigger descubierto after update on cuenta

referencing new row as nueva_fila

for each row

when nueva_fila.saldo < 0

begin atomic

insert into prestamo values

(nueva_fila.numero_cuenta, nueva_fila.nombre_sucursal,-

nueva_fila.saldo);

insert into prestatario

(select nombre_cliente, numero_cuenta

from impositor

where nueva_fila.numero_cuenta = impositor.numero_cuenta);

update cuenta set saldo = 0

where cuenta.numero_cuenta = nueva_fila.numero_cuenta

end

Si un saldo queda negativo, se crea un préstamo por el importe del

descubierto. El nº de préstamo toma el valor del nº de cuenta

Si un saldo queda negativo, se crea un préstamo por el importe del

descubierto. El nº de préstamo toma el valor del nº de cuenta

Trigger en SQL:99

Trigger

(32)

Transacciones I

BEGIN WORK

INSERT INTO BANCOS( ENTIDAD, NOMBRE ) VALUES ( “3322”, ”BSCH” )

UPDATE OFICINAS

SET ENTIDAD = “3322” WHERE ENTIDAD = “0893” DELETE FROM BANCOS

WHERE ENTIDAD = “0893” Si no ha habido ningún error

COMMIT WORK

Y si ha habido algún error ROLLBACK WORK

Transacción:

conjunto de

operaciones de manipulación de

datos que deben ser consideradas

como una unidad.

ENTIDAD NOMBRE

ENTIDAD CODIGO_OFICINA POBLACION DIRECCION 0893 Santander 0059 Popular 3428 Bilbao Vizcaya 5632 Banesto BANCOS OFICINAS 0893 001 Madrid Castellana, 73 3428 022 Las Palmas Triana, 21 0893 022 Gáldar R. Moreno, 3 5632 213 Oviedo Uría, 43 0893 300 Barcelona Diagonal, 435

Ejemplo:

Eliminar el Banco Santander

de la base de datos y asignar todas sus

oficinas a una nueva entidad bancaria, el

BSCH, cuyo código de entidad es el 3322

Propiedades:

ATOMICIDAD: todo o nada

CONSISTENCIA: coherencia de los datos

AISLAMIENTO: serialización de transacciones DURABILIDAD: los cambios son permanentes

(33)

Transacciones II

Inicio de transacción

K = 1

Bloqueo de los datos afectados por la instrucción K

Apunte de la instrucción en el dispositivo LOG ¿Se puede ejecutar

la instrucción K?

¿K = n? K = K+1

Desbloqueo de datos

Ejecución de las instrucciones del dispositivo LOG

(34)