PDF de programación - Tema 2. Sistemas gestores de bases de datos

Sistemas de Información II
Tema 2. Sistemas
gestores de bases de
datos

Bibliografía:
Elmasri y Navathe: “Fundamentos de Sistemas de Bases de

Datos”

3ª edición, 2002 (Capítulo 2).

Garcia-Molina, Ullman y Widom: “Database systems: the

complete book”. Prentice-Hall (Capítulo 1).

Carlos Castillo

UPF – 2008

1

Agenda

Objetivos DBMS
Tipos de DBMS
Arquitectura de tres esquemas
Independencia de datos
Resolución de consultas
Transacciones
Interfaces de usuario

2

Objetivos DBMS

Software debe permitir:

Crear bases de datos y esquemas

Data-definition language

Preguntar sobre los datos

Query language

Almacenar los datos de forma persistente
Controlar el acceso de múltiples usuarios

3

Tipos de DBMS

Según modelo de datos

Sistemas gestores de datos relacionales
Sistemas gestores de datos orientados a
objetos
Sistemas gestores de datos objeto-
relacionales

Según número de usuarios

Monousuario
Multiusuario

Según número de sitios

Centralizado
Distribuído

4

Modelo general

Cliente-servidor

Servidor: gestiona la base de datos
Cliente: permite enviar comandos al
servidor

Otras arquitecturas

Múltiples capas para llegar al servidor
Distribuída
Paralela
etc.

5

Arquitectura general

Nivel de vistas

Nivel conceptual

Nivel físico

n
ó
i
c
c
a
r
t
s
b
a

r
o
y
a
M

6

Arquitectura detallada
Usuario 1

Usuario 2

Vista 1
Esquema externo

Vista 2
Esquema externo

Esquema conceptual

Esquema interno

7

Nivel interno

Estructura física de almacenamiento
Todos los detalles de cómo el DBMS
utiliza

el disco duro
la memoria, etc.

Tema principal

El sistema debe ser rápido en responder y
eficiente en el uso de espacio

Administrador de la base de datos

8

Nivel conceptual

Estructura lógica de almacenamiento
Diseño conceptual de la base de datos

Tablas, columnas, etc.

Tema principal

El diseño debe reflejar conceptualmente el
problema a modelar

Administradores de datos

9

Nivel externo

Vistas sobre las tablas
Requiere sólo acceso parcial a los datos
Público objetivo

Desarrolladores de aplicaciones
Usuarios finales
Tema principal

Cada vista debe reflejar adecuadamente la
parte de los datos que interesa a cada uno

10

Definición: Esquema de la

base de datos

Descripción de la estructura de la base de

datos.

11

Definición: Esquema interno

Descripción de la organización física de los
datos: estructuras de datos en disco y rutas

de acceso.

[= descripción de bajo nivel]

12

Ejemplo de esquema interno

[root@machine mysql]# pwd
/var/lib/mysql
[root@machine mysql]# ls -lah
total 405M
drwxr-xr-x 14 mysql mysql 4.0K Dec 18 20:22 .
drwxr-xr-x 28 root root 4.0K Dec 22 2006 ..
drwx------ 2 mysql mysql 4.0K Apr 25 2007 test
drwx------ 2 mysql mysql 4.0K Jun 20 2006 mysql
[root@machine mysql]# ls -lah test/
-rw-rw---- 1 mysql mysql 8.4K 2008-01-07 21:37 comunitat.frm
-rw-rw---- 1 mysql mysql 452 2008-01-07 21:37 comunitat.MYD
-rw-rw---- 1 mysql mysql 1.0K 2008-01-07 22:42 comunitat.MYI
-rw-rw---- 1 mysql mysql 8.7K 2008-01-07 21:37 municipi.frm
-rw-rw---- 1 mysql mysql 379K 2008-01-07 21:37 municipi.MYD
-rw-rw---- 1 mysql mysql 1.0K 2008-01-07 22:42 municipi.MYI

13

MyISAM storage

[root@machine test]# file comunitat.*
comunitat.frm: MySQL table definition file Version 10
comunitat.MYD: DBase 3 data file (130048 records)
comunitat.MYI: MySQL MISAM compressed data file Version 1
[root@machine test]# myisamchk --verbose --description comunitat
MyISAM file: comunitat
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2008-01-07 20:37:40
Status: changed,sorted index pages
Data records: 19 Deleted blocks: 0
Datafile parts: 19 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 3
Datafile length: 452 Keyfile length: 1024
Max datafile length: 281474976710654 Max keyfile length: 17179868159
Recordlength: 57
table description:
Key Start Len Index Type Rec/key Root Blocksize

14

Definición: Esquema

conceptual

Descripción de la estructura lógica de la
base de datos completa (ej.: a través de

diagramas entidad-relación). Se omiten los

detalles del almacenamiento físico.

[= descripción general de alto nivel]

15

Definición: Esquema externo

Descripción de la estructura lógica de una
parte de la base de datos que es de interés

a un grupo particular.

[= descripción específica de alto nivel]

16

Tarea de la base de datos

Mantener la coherencia entre estos
esquemas
Mantener independencia de los datos

17

Definición: Independencia

física de datos

El esquema conceptual no es afectado por

cambios al esquema físico de datos.

18

Definición: Independencia

lógica de datos

Algunos elementos del esquema externo no

son afectados por cambios al esquema

conceptual.

19

Independencia de datos

Independencia lógica de datos

Agregar más columnas no afecta las vistas
Modificar columnas sólo afecta las vistas
que incluyen esas columnas

Independencia física de datos

Si el DBMS modifica su organización
interna de ficheros, no pasa nada con el
esquema conceptual (sucede cuando hay
un cambio de versión, o al migrar a otro
DBMS)

20

Consultas a bases de datos

21

Lenguajes

Según área

Vistas, definición de datos, manipulación
de datos, etc..

Según nivel

Alto nivel: no procedimientos
Bajo nivel: procedimientos

22

Lenguajes para interactuar

con bases de datos

Lenguaje para definir vistas

Ej.: lenguaje de reportes

Lenguaje para definir datos

CREATE ...

Lenguaje para definir almacenamiento

Para escoger un cierto esquema interno
Lenguaje para manipulación de datos

SELECT, UPDATE, ...

23

Lenguajes en la práctica

Lenguaje para definir vistas

SQL
O algún lenguaje propietario para reportes

Lenguaje para definir datos

SQL

Lenguaje para definir almacenamiento

Extensiones propietarias de SQL

Lenguaje para manipulación de datos

SQL

24

Dialectos de SQL -- Limit

MYSQL, PostgreSQL

ORACLE

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS
rownumber, columns
  FROM tablename
)
WHERE rownumber <= n

Fuente: http://troels.arvin.dk/db/rdbms/

25

Dialectos de SQL -- Limit/skip

MYSQL, PostgreSQL

ORACLE

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n OFFSET skip

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS
rownumber, columns
  FROM tablename
)
WHERE rownumber > skip and rownumber <= (n+skip)

26

Dialectos de SQL - Multi-insert

INSERT INTO tablename
VALUES (0,'foo') , (1,'bar') , (2,'baz');

Sí:

PostgreSQL
MySQL
DB2

No:

MSSQL
Oracle

27

Dialectos de SQL (cid:19)



Autoincrement (Oracle)

CREATE TABLE mytable (
  mytable_id INTEGER PRIMARY KEY,
  ... -- (other columns)
);
 
CREATE SEQUENCE mytable_seq;
 
CREATE TRIGGER mytable_seq_trigger
BEFORE INSERT ON mytable FOR EACH ROW
BEGIN
  IF (:new.mytable_id IS NULL) THEN
    SELECT mytable_seq.nextval INTO :new.mytable_id
    FROM DUAL;
  END IF;
END;

28

Dialectos de SQL (cid:19)
Autoincrement



MySQL
  CREATE TABLE tablename (
    columnname INTEGER AUTO_INCREMENT PRIMARY KEY
    ...
  )
DB2 / STANDARD
CREATE TABLE tablename (
  tablename_id INTEGER GENERATED BY DEFAULT AS IDENTITY
  ...
)

PostgreSQL
CREATE TABLE tablename (
  tablename_id SERIAL,
  ...
)

29

DBMS con procedimientos

(ejemplo de PL/SQL)

DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;

30

Interfaces de usuario

Intérprete de comandos
Formularios
Interfaces gráficas

Interfaces parametrizadas
Web

Interfaces en lenguaje natural

31

MySQLadmin (Web)

32

phpMyAdmin

33

34

35

MySQLadmin (cliente)

36

Resolución de consultas

37

Usuarios, aplicaciones

Resolución de
consultas

Intérprete
consultas

Gestor de
transacciones

Solución de la
consulta

Registro y
recuperación

Administrador
almacenamiento

Buffers o
almacenamiento
temporal

Almacenamiento
Ficheros o Log

38

Ejecución de una consulta

Consulta en SQL -> compilador
Plan de ejecución de la consulta

Se puede recuperar usando “explain
QUERY”

Ejemplo explain en postgres:

EXPLAIN SELECT * FROM agenda;
Seq scan on agenda (cost=5, rows=5)
EXPLAIN SELECT * FROM agenda
WHERE nacimiento > 1985;
Idx scan on agenda (cost=2, rows=5)
39

Ejemplo en MS SQL Server
SELECT * FROM Employee e, Person c
WHERE e.ContactID = c.ContactID ORDER BY c.LastName

Sort(ORDER BY:([c].[LastName] ASC))
|-Nested Loops(Inner Join, OUTER REFERENCES:([e].[ContactID], [Expr1004])
WITH UNORDERED PREFETCH)
|--Clust Index Scan(OBJECT:([Employee].[PK_Employee_EmployeeID] AS [e]))
|--Clust Index Seek(OBJECT:([Person].[PK_Contact_ContactID] AS [c]),
SEEK:([c].[ContactID]=[Employee].[ContactID] as [e].[ContactID])
ORDERED FORWARD)

http://en.wikipedia.org/wiki/Query_plan

40

Planificador de consultas

Buscar el mejor orden
Puede ser difícil en consultas complejas
Ej.: nombre de las sucursales en Madrid en
que trabajen empleados de más de 64
años
Conviene primero filtrar lo más restrictivo
Pasar pocos datos de una etapa a otra

Recorrer columnas indexadas primero
Optimización de consultas

41

Consultas y buffers

Procesador de consultas

Solicita fragmentos al administrador de
buffers
Fragmentos son índices, filas, etc.

Administrador de buffers

Usar la memoria apropiadamente
Procesamiento de transacciones

Control de concurrencia

Hasta qué punto podemos admitir dos usuarios
haciendo algo al mismo tiempo

Registro y recuperación en caso de fallas

42

Transacciones

Ejemplo: mover dinero de una cuenta
bancaria a otra, ¿se quita a un