PDF de programación - Diseño y Administración de Bases de Datos

Diseño y Administración
de Bases de Datos
Cuadernos de Prácticas de Laboratorio

Colección manuales uex - 100

Manuel
Barrena García

100

DISEÑO Y ADMINISTRACIÓN

DE BASES DE DATOS

MANUALES UEX

100

MANUEL BARRENA GARCÍA

DISEÑO Y ADMINISTRACIÓN

DE BASES DE DATOS

Cuadernos de Prácticas de Laboratorio

2015

Cualquier forma de reproducción, distribución, comunicación pública o transformación de esta obra solo
puede ser realizada con la autorización de sus titulares, salvo excepción prevista por la ley. Diríjase a
CEDRO (Centro Español de Derechos Reprográficos, www.cedro.org) si necesita fotocopiar o escanear
algún fragmento de esta obra.

© Manuel Barrena García, para esta edición.
© Universidad de Extremadura, para esta edición.

Edita:

Universidad de Extremadura. Servicio de Publicaciones
C/ Caldereros, 2 - Planta 2ª. 10071 Cáceres (España)
Tel. 927 257 041 ; Fax 927 257 046
E-mail: [email protected]
http://www.unex.es/publicaciones

ISSN 1135-870-X
ISBN de méritos 978-84-606-9523-3

Impreso en España - Printed in Spain
Maquetación, fotomecánica e impresión: Dosgraphic, s.l. – 914 786 125

ÍN
DI
CE

ÍNDICE GENERAL

LABORATORIO 1. DISEÑO LÓGICO IMDB

¿Qué es IMDB?

1.
2. Análisis y colección de requisitos
3. Modelo conceptual
4. Descripción de entidades

y asociaciones
Ejercicios

5.

LABORATORIO 2. MATERIALIZACIÓN
DE IMDB

1.

Instalación y primeros pasos
con MySql

2. Creación de la base de datos

myimdb
Ejercicios

3.

LABORATORIO 3. CONSULTAS
SOBRE IMDB

Introducción

1.
2. Consultando el diccionario
3. Consultas de agrupación

y funciones agregadas
Uso de subconsultas

4.
5. Consultas mediante join
6.

Ejercicios Finales. Consultas
sobre IMDB

LABORATORIO 4. CONSTRUCCIÓN
DE ÍNDICES SOBRE IMDB

Introducción
El coste de una consulta simple

1.
2.
3. Cómo elegir un índice
4.

La importancia del acceso
a la tabla base
Ejercicios finales

5.

9

9
10
10

11
18

19

19

20
26

27

27
27

30
31
34

37

43

43
45
46

50
53

ÍN
DI
CE



LABORATORIO 5. OPTIMIZACIÓN
DE CONSULTAS

Introducción
Ejecución de JOINs en Mysql

1.
2.
3. Optimización del JOIN

mediante índices
Ejercicios finales

4.



LABORATORIO 6. LA SENTENCIA EXPLAIN

Introducción a EXPLAIN
El campo ID
El campo SELECT_TYPE
El campo TABLE
El campo TYPE

1.
2.
3.
4.
5.
6. KEY y ROWS
7. POSSIBLE_KEYS, KEY_LEN y REF
8.

El campo EXTRA



LABORATORIO 7. PRACTICANDO
LA OPTIMIZACIÓN DE CONSULTAS EN IMDB

1.
2.
3.

Introducción
Informes de rendimiento
Ejercicios de optimización

55

55
56

56
62

63

63
64
65
68
69
73
76
78

81

81
82
85

LABORATORIO 1.
DISEÑO LÓGICO IMDB

Objetivos. Tras cubrir esta sesión práctica, el alumno/a ha adquirido destrezas para
abordar el diseño conceptual de una base de datos y definir su modelo lógico a partir del
análisis previo de la información relacionada.

1. ¿QUÉ ES IMDB?

IMDB es el acrónimo de Internet Movie Data Base, la mayor plataforma en Internet que
aglutina información sobre obras cinematográficas en distintos formatos (cine, televisión, video-
juegos, etc.). La página web www.imdb.com, de acceso público, proporciona información
sobre la práctica totalidad de obras cinematográficas a disposición del público en general. Esta
plataforma se sostiene sobre una enorme base de datos que la propia plataforma hace parcial-
mente pública para un uso no comercial.

La principal razón para el uso de la base de datos IMDB en este laboratorio es servir como
herramienta experimental para conseguir muchas de las competencias específicas establecidas
en la asignatura DABD. El uso de IMDB nos permitirá abordar en profundidad la mayor parte
de la temática relativa al diseño y la administración de las bases de datos, comenzando por
el diseño conceptual, avanzando hacia los aspectos de diseño lógico y físico de las bases de
datos y finalizando aspectos de vital importancia en las tareas de administración de las bases
de datos.

El objetivo de la parte de laboratorio de esta asignatura será el diseño y administración de
una base de datos que sustente de modo eficiente y por supuesto efectivo el acceso por parte
del usuario a una plataforma similar a www.imdb.com.

X
E
U

S
E
L
A
U
N
A
M

9

2. ANÁLISIS Y COLECCIÓN DE REQUISITOS

La fase de diseño de una base de datos se debe sustentar en un análisis previo de la
realidad de la empresa u organización que desea o precisa la utilización de un SGBD. Como
sabemos, este paso previo al diseño conlleva una serie de tareas definidas por un método
riguroso que facilita la comprensión de la realidad que rodea al sistema. El primer paso en
nuestro análisis consistiría en llevar a cabo una exploración detallada a los contenidos de la
plataforma www.imdb.com. Este recorrido nos debe aportar una amplia perspectiva sobre los
datos que necesitaremos almacenar y los tipos de operaciones a los que ha de enfrentarse
nuestra base de datos.

La información contenida en la plataforma web IMDB es tan extensa que nos llevaría
mucho más tiempo del que disponemos para finalmente poder plantear un diseño conceptual
acorde al análisis realizado. Para la realización de nuestras prácticas reduciremos pues el
alcance de nuestra base de datos a una porción manejable con la que podamos trabajar aún
de modo realista.

3. MODELO CONCEPTUAL

Figura 1. Diagrama E/R para la base de datos IMDB.

La figura 1 presenta una versión simplificada del diagrama E/R para IMDB.

X
E
U

S
E
L
A
U
N
A
M

10

MANUEL BARRENA GARCÍA4. DESCRIPCIÓN DE ENTIDADES Y ASOCIACIONES

Describimos en este apartado la colección de entidades y asociaciones que constituyen el

modelo conceptual de IMDB.

TiTle

Almacena los títulos de todas las obras del catálogo, ya sean películas, episodios, series,
programas, reportajes, etc. Además del título guarda datos que facilitan adquirir información
adicional sobre la obra, tal como qué clase de obra es, su año de producción, si es un capítulo
a qué serie pertenece, etc.

• id: int(11). Identificador. Autoincrementado.
• title: text. Título de la obra (película, serie, capítulo…).
• imdb_index: varchar(12). Se usa para diferenciar dos obras producidas el mismo año que

comparten el mismo título. Utiliza códigos I, II, III, IV, etc.

• kind_id: int(11). Código que indica el tipo de obra en cuestión. Almacena los códigos de
diferentes tipos de obra, por ejemplo (7, episode) indicando que se trata de un episodio
perteneciente a una serie.

• production_year: int(11). Año de producción de la obra.
• imdb_id: int(11). Se utiliza para acceder directamente a un registro desde la web de IMDB.
Por ejemplo, si el imdb_id para un nombre es 104, entonces se puede acceder a la direc-
ción http://www.imdb.com/name/nm0000104/ para recuperar la página del actor Antonio
Banderas. Sin embargo en nuestra base de datos no se importan los valores de imdb_id,
son todos nulos.

• phonetic_code: varchar(5). Código SOUNDEX del título. Este código se utiliza para repre-
sentar cómo suena una palabra, de modo que todas las palabras que suenan similarmente
comparten el mismo código, haciendo más sencilla la búsqueda de homónimos.

• episode_of_id: int(11). Se utiliza en obras que son episodios pertenecientes a una serie.
Almacena el identificador de la tabla title para el cual la obra en cuestión es un episodio.
Sólo tiene un valor no nulo cuando el valor de kind_id = 7. Para cualquier otro valor de
kind_id, episode_of_id es NULO.

• season_nr: int(11). Se utiliza en obras que son episodios pertenecientes a una serie. Como
las series pueden tener varias temporadas, registra la temporada en cuestión. Cuando
kind_id <> 7, este valor es NULL.

• episode_nr: int(11). Se utiliza en obras que son episodios pertenecientes a una serie. Guarda

el número del episodio correspondiente.

• series_years:varchar(49). Se utiliza en obras que son o bien episodios (kind_id=7) o bien

tv-series (kind_id=2). Muestra el rango de años de la serie.

• md5sum: varchar(32). Hash correspondiente al título de la obra.

X
E
U

S
E
L
A
U
N
A
M

11

DISEÑO Y ADMINISTRACIÓN DE BASES DE DATOSAkA_TiTle

Representa una asociación entre obras, para identificar las obras por los diferentes títulos
que les han puesto en otros países o ubicaciones. Registra prácticamente la misma información
que Title, añadiendo obviamente el atributo movie_id que le permite conectarse con la obra
original en cuestión.

• id: int(11). Identificador. Autoincrementado.
• movie_id: int(11). Identificador de la obra original (película, serie, capítulo…).
• title: text. Título alternativo de la obra (película, serie, capítulo…).
• imdb_index: varchar(12). Se usa para diferenciar dos obras producidas el mismo año que

comparten el mismo título. Utiliza códigos I, II, III, IV, etc.

El resto de los atributos tiene el mismo significado que para la entidad Title.

Movie_link

Tiene como propósito registrar las relaciones que existen entre dos obras. Por ejemplo la
película Rocky II es continuación de Rocky, por lo que existe una entrada en esta tabla que
conecta sus movie_id’s e indica el significado de esa conexión.

• id: int(11). Identificador de la conexión. Autoincrementado.
• movie_id: int(11). Identificador de la obra que se conecta con otra.
• linked_movie_id: int(11). Identificador de la obra conectada.
• link_type_id: int(11) Código que indica el tipo de conexión que existe entre la obra movie_id

y la obra linked_movie_id. Estos códigos se almacenan en el diccionario link_type.

link_Type

Actúa como diccionario (o lista de valores) para traducir los códigos de los diferentes
posibles conexiones que existen entre dos obras. Por ejemplo, el código 1 representa que una
película sigue a otra, el código 3 que es un remake, etc.

• id: int(11). Código del tipo de conexión.
• lin