Título
DISEÑO FÍSICO DE UNA BASE DE DATOS ORACLE
Finalidad Autor
Mostrar el proceso de diseño físico de una base de datos Oracle para el caso de estudio FILMES
Eduardo Encalada [email protected] Revisión
Feb-2020
Escenario
Deseamos realizar el diseño físico para la base de datos FILMES, cuyos insumos los podemos encontrar en el Anexo 1 (diseño lógico, transacciones de datos, SGBD elegido y proyección de ítems a almacenar) y en el Anexo 2 (análisis transaccional), al final de este documento.
Introducción
El diseño físico tiene como objetivo definir las especificaciones de implementación que conlleven a la materialización de la base de datos.
Para realizar el diseño físico se requiere como insumos:
El diseño lógico
Las transacciones de datos
La selección del SGBD a usar
La proyección de la cantidad de ítems a almacenar
El análisis transaccional
Y el proceso de diseño físico implica 4 pasos:
1. Traducir el modelo lógico a lenguaje de base de datos 2. Identificar y definir índices
3. Definir la organización física de los datos 4. Definir especificaciones de seguridad
Traducción del modelo lógico
Implica traducir el modelo de datos resultante del diseño lógico, al lenguaje del SGBD seleccionado (conocido como DDL en bases de datos relacionales). Se requiere por tanto conocimiento del lenguaje y de la organización y estructuración lógica que maneja el SGBD:
Estructuras de almacenamiento y organización de los datos. Ej: TABLAS, INDICES
Restricciones de integridad: Primary Key, Unique, Foreign Key, NOT NULL, etc.
Se parte del modelo lógico (diagramas y diccionario de datos), y a partir de ello se realiza lo siguiente:
Evaluar posibles limitaciones de implementación en el SGBD elegido.
Traducir relaciones base
Traducir integridad referencial
Traducir otras restricciones de integridad
Resolver campos derivados
En Oracle y en la mayoría de SGBD la actualización de campos calculados debe realizarse a través de triggers o directamente desde la aplicación
Casi todos los SGBD tienen limitaciones para implementar la multiplicidad máxima del extremo muchos en las asociaciones uno a muchos.
Normalmente las restricciones de integridad que los SGBD relacionales permiten implementar mediante DDL son:
Llaves primarias (constraint PRIMARY KEY)
Llaves foráneas (constraint FOREIGN KEY)
Llaves alternas o campos únicos (constraint UNIQUE)
Campos obligatorios (constraint NOT NULL)
Restricciones específicas de dominio (constraint CHECK)
Para las restricciones que no permita implementar el SGBD con DDL, existen dos alternativas:
1. Mediante triggers
2. En la aplicación de usuario final Script DDL caso de estudio "Filmes"
El script completo está disponible AQUÍ.
A efectos de aclarar algunos aspectos presentamos aquí una parte de script que corresponde a la implementación de la tabla PELICULAS.
En la primera sentencia creamos la tabla con todos sus campos, las restricciones que hemos definido allí son:
Restricción de tipo: los tipos de dato.
Restricción de obligatoriedad: NOT NULL. En este caso tal como consta en el diseño lógico, el id, el nombre, el tipo de guion, el año y la sinopsis son campos obligatorios.
Lo siguiente es definir llave primaria, y llaves foráneas. En este caso lo hacemos luego de creada la tabla, mediante un ALTER TABLE ... ADD CONSTRAINT. Obviamente también se las puede implementar dentro del CREATE TABLE, en ese caso solo tomar en cuenta que, para el caso de la llave foránea, previamente debe haberse creado la tabla referenciada, PAISES en este caso.
Con lo anterior habremos definido la estructura y restricciones que constan en el diagrama relacional. Lo siguiente es definir el resto de restricciones del modelo. Primero las restricciones de dominio específico, que en lo que respecta a PELICULAS son las siguientes:
peliculas.tipo_guion: Solo los valores: "Original", "Adaptado"
peliculas.anio: Debe ser mayor o igual a 1900
peliculas.fecha_estreno: Debe ser mayor o igual a 01-ene-1900
peliculas.duracion_minutos: Debe ser mayor a 30
Ese tipo de restricciones se implementan usando un constraint tipo CHECK, el cual permite establecer una condición de validación para el campo.
Finalmente hay dos restricciones más que tienen que ver con películas:
1. En el mismo año, no puede haber dos películas con el mismo nombre 2. Cada película debe tener al menos un género
La primera, implica que los valores de los campos peliculas.nombre_original más peliculas.anio, juntos, no pueden duplicarse. Para ello creamos una restricción de tipo UNIQUE, la cual permite definir claves alternativas, en ese caso para la combinación de esos dos atributos.
En lo que respecta a la segunda restricción "Cada película debe tener al menos un género", se debe tomar en cuenta que los géneros de las películas se registran en la tabla PELICULAS_GENEROS que está vinculada a la tabla PELICULAS mediante llave foránea. Significa que, se podrá asociar un género a una película solo luego de haber agregado la película en PELICULAS: Por lo tanto, no es posible controlar esa restricción al momento de crear la película. Esta restricción deberá controlarla la aplicación.
Los campos derivados tampoco pueden implementarse mediante DDL de Oracle, ya que implica un cálculo con base en atributos de otras tablas. No es simplemente el validar que se cumpla una condición.
Índices
Los índices son muy importantes a efectos de optimizar el rendimiento de la base de datos en operaciones de consulta. Por lo tanto, es muy importante en este punto identificar aquellas columnas para las que sería conveniente crear un índice.
Conceptualmente hay dos tipos de índices:
Índices primarios:
Índices secundarios:
Los índices primarios se refieren a aquellos que se definen para llaves primarias y llaves foráneas. Y hay que partir asumiendo que tanto llaves primarias, como llaves foráneas siempre deberían tener un índice, de hecho, la mayoría de SGBD al definirlas como tales les crean un índice de forma automática.
Lo que debemos buscar son índices secundarios, es decir, para columnas que no sean ni PK ni FK.
¿Como identificar las columnas candidatas para crear índices secundarios?
Las columnas candidatas son aquellas que frecuentemente son usadas para combinar (JOIN), filtrar (WHERE), ordenar (ORDER BY) y agrupar (GROUP BY). Sobre todo, filtrar, ordenar y agrupar; la combinación normalmente viene dada por las columnas PK y FK que como se dijo, debemos asumir que siempre tendrán índice.
Si se tienen ya las consultas SQL que se van a realizar es más fácil: aquellas columnas que aparecen en la condición de selección de la cláusula WHERE, o por la cual se ordenan los resultados, o que se usan para agrupar los datos, son las columnas candidatas.
Por ejemplo, consideremos la transacción "Listar las películas según el género" en nuestro caso de estudio FILMES:
Como se ve son 5 columnas usadas a efectos combinar (peliculas.id_pelicula y peliculas_generos.idpelicula), filtrar (peliculas_generos.genero), y ordenar (peliculas.anio y peliculas.nombre_original). En este caso no existen agrupamientos (GROUP BY), si lo existiese también se debería tomar en cuenta.
De dichas columnas se excluyen como ya se dijo PKs y FKs que corresponde a índices primarios que siempre deberían estar indexadas.
Columnas candidatas a indexar en caso de estudio FILMES
Con base en el análisis de las transacciones de datos del caso FILMES, las columnas candidatas para crear índices secundarios, serían:
1. personas.apellidos
2. peliculas.nombre_original 3. peliculas.anio
4. personajes.personaje
5. peliculas_directores.tipo_direccion 6. estudios.nombre_estudio
7. peliculas_generos.genero 8. adaptaciones.idioma 9. paises.nombre_pais
DDL creación de índices
Recomendaciones
En principio se crearían índices para todas las columnas candidatas.
Posteriormente durante la afinación y monitoreo de la base de datos se evaluará si hay algún índice que está de más, o si falta alguno.
Cuando las tablas son muy pequeñas en cuanto a volumen de datos, se recomienda no crear índices (Por ejemplo, Países en nuestro caso).
Cuando son columnas tipo cadena de gran longitud tampoco es recomendable indexar
En bases de datos transaccionales (OLTP) se debe reducir el uso de índices al mínimo necesario.
Sobre todo, en columnas de tablas que se actualizan con mucha frecuencia.
No olvidar que los índices aceleran las consultas, pero ralentizan las actualizaciones
Organización física
Cálculo de espacio de almacenamiento en disco requerido
La principal estructura lógica de almacenamiento son las tablas, y por lo tanto es el principal elemento a tomar en cuenta en la proyección del espacio en disco.
Para determinar el espacio en disco que a ocupar por las tablas se toma en cuenta:
Tamaño de registro de cada tabla
Proyección de filas a almacenar en cada tabla en un mínimo de 5 años
El tamaño de registro de una tabla se estima con base en los tipos de datos. En el caso de Oracle por ejemplo el tamaño según cada tipo de dato, son los siguientes:
Todo registro tiene una dirección interna llamada ROWID que también ocupa espacio y por lo tanto debe ser tomado en cuenta.
Con base en lo anterior, para nuestro caso de estudio los tamaños de registro de cada tabla serían los siguientes:
Relaciones Tamaño aproximado de
cada registro (en Bytes) Observación
Personas 573
Actores 54
Directores 54
Guionistas 32
Peliculas 6380 Asumimos que la sinopsis ocupa hast 4000
caracteres
Personajes 214
Peliculas_Directores 91
Películas_Guionistas 76
Estudios 72
Peliculas_Estudios 76
Peliculas_Generos 62
Adaptaciones 316
Paises 132
Lo siguiente es determinar el número de registros (filas) que se van a almacenar con una proyección de 5 años. Luego, para calcular el tamaño de cada tabla se multiplica el número de ítems que se prevé almacenar en la tabla por el tamaño de cada registro.
Consideraciones adicionales:
Si una tabla tiene asociado archivos digitales a las tuplas (jpg, pdf, doc ...), se debe incluir en el tamaño del registro.
Dependiendo del SGBD se deberá prever espacio para otros archivos que son de datos (Ej:
REDOLOGS en Oracle)
Seguridad
Considerando los siguientes requerimientos de disponibilidad para el caso de estudio "Filmes":
Se requiere disponibilidad 24x7x365
Tiempo de recuperación ante fallo: máximo 1 hora
Y con la finalidad de garantizar la seguridad de la base de datos en sus 3 ámbitos:
Confidencialidad de la información
Integridad de los datos
Disponibilidad
Se establecen las siguientes medidas orientadas a garantizar la seguridad de la base de datos "Filmes:
Implementar servidor de respaldo.
Implementar replicación síncrona (en tiempo real) entre base de datos maestro y esclavo
Contar con redundancia eléctrica.
Implementar arreglos de discos en servidores (RAID).
Las aplicaciones accesibles desde la extranet solo pueden consultar datos.
La conexión hacia la base de datos solo estará habilitada para equipos que estén en la red interna de la empresa (intranet).
Las aplicaciones dirigidas a público en general solo pueden implicar consulta de datos.
El acceso a los datos desde sistemas externos se permitirá solo a través del uso de web services.
La información correspondiente a: email de la persona, salario, y los montos de financiamiento, no serán de acceso público.
Los salarios deben almacenarse en formato encriptado.
No se deben permitir modificaciones directas en la base de datos (activar log de auditoría).
Cada aplicación tendrá su propio usuario de base de datos.
Las consultas de datos se realizarán a través de vistas.
.-
Anexo 1
Caso de estudio "Filmes"
Descripción
Se desea implementar un base de datos para almacenar información referente a producciones cinematográficas, donde se pueda llevar el registro de películas, sus directores, estudios que las producen, guionistas y su reparto, considerando además que:
Cada película es producida por uno o varios estudios.
A cada película la protagonizan uno o más actores. Y cada actor puede interpretar uno o más personajes.
Un actor puede participar en una o más películas.
Una película puede tener uno o más directores.
Tomar en cuenta que una persona puede ser director y también actor, en una misma película o en distintas.
Para identificar la información a incluir se tomó con referencia:
http://www.filmaffinity.com/es
http://www.imdb.com/
Lo que se busca es disponer de un completo catálogo de películas, que sea accesible de forma pública para que cualquier persona a través la web pueda buscar películas y ver su ficha informativa.
Luego del estudio preliminar, en el cual se definieron entre otros los alcances, las vistas de usuario, los requerimientos de datos y las transacciones de datos, se procedió primero a realizar el diseño conceptual (modelo E-R), y luego el diseño lógico. Para el diseño lógico se decidió trabajar con el modelo de base de datos relacional
Para el caso de estudio descrito, se detalla a continuación:
Diseño lógico
Transacciones de datos
SGBD elegido
Consideraciones para el cálculo del espacio en disco
Diseño lógico
Diagrama relacional
P = Primary Key, F = Foreign Key, * = Campo obligatorio Restricciones adicionales de dominio
Tabla Campo Restricción de Dominio Adicional
peliculas tipo_guion Solo los valores: "Original", "Adaptado"
peliculas anio Debe ser mayor o igual a 1900 peliculas fecha_estreno Debe ser mayor o igual a 01-ene-1900 peliculas duracion_minutos Debe ser mayor a 30
adaptaciones idioma Solo los valores: "ES","EN","FR","PG"
adaptaciones doblaje Solo los valores "SI", "NO"
personas genero Solo valores "M", "F"
personajes tipo_interpretacion Solo los valores: "Actuación”, “Voz"
peliculas_directores salario No puede ser negativo peliculas_guionistas salario No puede ser negativo peliculas_estudios financiamiento No puede ser negativo
Otras restricciones de integridad
1. actores.nro_peliculas, es un campo calculado, es igual a la sumatoria de películas en las que el actor forma parte del reparto
2. directores.nro_peliculas, es un campo calculado, es igual a la sumatoria de películas en las que el actor forma parte del reparto
3. Cada película debe tener al menos un género
4. En el mismo año, no puede haber dos películas con el mismo nombre
Transacciones de datos Nro Freq
mensual Tipo Transacción Descripción 1 200 Actualización Registrar una nueva
película Incluidos géneros, adaptaciones, estudios que la producen, actores que participan etc.
2 4 Actualización Gestionar actores Agregar actores o modificar información de actores 3 2 Actualización Gestionar directores Agregar directores o modificar información de directores 4 3 Actualización Gestionar guionistas Agregar guionistas o modificar información de guionistas 5 20 Actualización Actualizar Información
de Películas Modificar alguna información de películas ya registradas 6 35000 Consulta Mostrar ficha
informativa de una película
Visualizar ficha completa de una película.
7 3000 Consulta Mostrar Personajes
Interpretados por un actor
Para un actor dado, ver las películas en las que ha participado y los personajes que ha interpretado
8 100 Consulta Obtener número de
películas dirigidas por un director
9 15000 Consulta Listar películas adaptadas a un idioma (ej. ES)
Buscar y listar nombre adaptado, año y país de películas adaptadas a un determinado idioma
10 30000 Consulta Listar Películas según el
género Buscar y listar películas según el género 11 50 Consulta Listar Películas con
mayor presupuesto Listar el top ten de las películas más costosas 12 800 Consulta Listar directores mejor
pagados Listar el top ten de las(los) directoras(es) mejor pagados
*En este caso las operaciones de consulta se asumen que son consultas que puede realizar el público en general
SGBD elegido
Oracle Express Edition
Consideraciones para el cálculo de espacio en disco
Número de ítems que se proyecta almacenar a 5 años o 3000 actores, 600 directores y 1000 guionistas o 200 países
o 500 estudios
o 50000 películas, y por cada una un promedio de 15 personajes, 3 directores, 2 guionistas, 2 estudios, 2 géneros y 5 adaptaciones
Anexo 2
Análisis transaccional para el caso de estudio "Filmes"
Análisis de resultados
Se trata de una base de datos predominantemente de consulta
Tablas más accedidas:
o PELICULAS
o PELICULAS_GENEROS o ADAPTACIONES
Tablas más consultadas o PELICULAS
o PELICULAS_GENEROS o ADAPTACIONES
En cuanto a actualizaciones la incidencia no es alta, y no existen tablas donde predomine esta operación.
Transacciones críticas:
o Mostrar ficha informativa de una película o Registrar una nueva película
o Actualizar Información de Películas
