Maestría en Bioinformática

Bases de Datos y Sistemas de Información

Diseño Lógico

Ing. Alfonso Vicente, PMPalfonso.vicente@logos.com.uy

Agenda

Diseño lógico

Modelo Relacional

Las 12 reglas de Codd

Esquema e instancia

Más sobre la “C” en ACID

Conceptos

Herramientas

Agenda

Herramientas gratuitasConceptos

Herramientas

Agenda

Diseño lógico

Modelo Relacional

Las 12 reglas de Codd

Esquema e instancia

Más sobre la “C” en ACID

Conceptos

Herramientas

Conceptos

Diseño Lógico

• Es el proceso de definir el esquema lógico de una base de

datos, normalmente después de haber definido el esquema

conceptual

• Más cercano a la implementación que el modelo conceptual

• Aún de alto nivel, sirve para comunicar el diseño y como

guía para la implementación

• Hay herramientas, como brModelo, que nos ayudan a

generar el esquema lógico semi-automáticamente, a partir

del modelo conceptual

Conceptos

Modelo Relacional

• Propuesto por Edgar Codd en 1970 (A Relational Model of

Data for Large Shared Data Banks), es:

• Un lenguaje para realizar el diseño lógico (MER MR)

• Un modelo de datos basado en la lógica de predicados y

la matemática

• Una forma de “ver” el diseño de una base de datos

relacional (MR BD)

• Aplican las extensiones vistas de lógica y matemática

Conceptos

Modelo Relacional

• En la academia:

Dados conjuntos A1, A2, …, An, una relación R n-aria es un

conjunto de n-tuplas tales que R IN (A1 × A2 × … × An)

C = {42881163, 43378842} N = {'Juan', 'Ana', 'Pedro'}

A = {'Pérez', 'Gómez'} T = {099555333, 094111222}

r(P) = { (42881163, 'Juan', 'Gómez', 099555333),

(43378842, 'Ana', 'Gómez', 094111222) }

P es una relación, o más formalmente una “variable de

relación”, y r(P) es una instancia de relación

Conceptos

Modelo Relacional

• En la industria, la relación se puede ver como una tabla

(RELACIÓN = TABLA)

PERSONAS

• Las nombraremos en plural

• Relacional viene de relación entendida de esta forma, no

confundir con relación como “asociación entre entidades” en

el MER

Cedula Nombre Apellido Telefono

42881163 Juan Gómez 099555333

43378842 Ana Gómez 094111222

Conceptos

Modelo Relacional

• SQL propone términos alternativos a los del Modelo

Relacional, y tal vez más intuitivos

• Utilizaremos indistintamente los términos

Lenguaje formal Lenguaje coloquial / SQL

Relación Tabla

Tupla Fila

Atributo Columna

Dominio Tipo

Conceptos

Modelo Relacional

• En una relación (tabla):

• Cada fila representa una n-tupla de R (fila = tupla)

• Las filas no están ordenadas

• Todas las filas son distintas

• Las columnas sí tienen orden

• El significado de cada columna es transmitido

nombrándola con el correspondiente dominio (e.g.

cedula, nombre, apellido, telefono)

• TELEFONO es el conjunto de todos los posibles

teléfonos, o sea un dominio o un tipo de datos

Conceptos

Modelo Relacional

• Superclave

• Dado R(A1, A2, …, AN), se dice que X IN {A1, A2, …, AN}

es superclave en una relación R, si no pueden existir dos

tuplas en r(R) con valores iguales en cada columna de X

PERSONAS(CEDULA, CREDENCIAL, NOMBRE, APELLIDO)

PROYECTOS(NUM_PROYECTO, NOM_PROYECTO)

ASIGNACIONES(CEDULA, NUM_PROYECTO, CARGO)

• ¿Qué superclaves pueden tener las relaciones?

• ¿El conjunto de todas las columnas, es una superclave?

Conceptos

Modelo Relacional

• Clave (o clave candidata)

• Es una superclave minimal, en el sentido que no

contiene propiamente a una superclave

PERSONAS(CEDULA, CREDENCIAL, NOMBRE, APELLIDO)

PROYECTOS(NUM_PROYECTO, NOM_PROYECTO)

ASIGNACIONES(CEDULA, NUM_PROYECTO, CARGO)

• ¿Qué claves candidatas pueden tener las relaciones?

• Cada relación debe tener al menos una clave candidata

Conceptos

Modelo Relacional

• Clave primaria

• Es una clave candidata (debía haber al menos una) que

elegimos para tratarla especialmente

En la relación PERSONAS:

Claves candidatas: {CEDULA} y {CREDENCIAL}

Clave primaria: {CEDULA}

• Su elección es arbitraria (por ahora)

• Las claves primarias no deben admitir valores NULL

Conceptos

Modelo Relacional

• Clave foránea

• Es una combinación de atributos en una relación cuyos

valores deben coincidir con los valores de una clave

candidata de otra relación

En la relación ASIGNACIONES:

ASIGNACIONES(CEDULA) referencia a PERSONAS(CEDULA)

• Esto se conoce como integridad referencial:

“Si B referencia a A, A debe existir”

Conceptos

Modelo Relacional

• Clave foránea

• Las podemos definir durante el diseño lógico

• El RDBMS se encargará de que siempre se cumpla la

integridad referencial

Conceptos

Las 12 reglas de Codd

• R1: Regla de la información

Toda la información se debe representar en tablas

• R2: Regla de la garantía de acceso

Todo valor escalar debería ser accesible a través del

nombre de la tabla, el nombre de la columna y la clave

primaria de la fila...

...por esto debía existir al menos una clave candidata

Conceptos

Las 12 reglas de Codd

• R3: Tratamiento sistemático de los valores NULL

Un RDBMS debe tener soporte para valores NULL

(desconocidos o que no apliquen), deben ser

independientes del tipo y deben implementarse de una

forma diferente a cualquier valor válido de cualquier tipo

• R4: Catálogo en línea basado en el Modelo Relacional

Una base de datos debe describirse a sí misma

mediante un catálogo basado en el Modelo Relacional,

accesible para los usuarios autorizados

Conceptos

Las 12 reglas de Codd

• R5: Lenguaje de datos completo

Un RDBMS debe tener un lenguaje relacional (como

SQL) que soporte DDL, DML, seguridad y restricciones

de integridad, y transacciones (commit, rollback).

• R6: Actualización de vistas

Todas las vistas que sean teóricamente actualizables

deben ser actualizables en la práctica (se demostró que

esta regla no es decidible: Why Codd's Rule No. 6 Must

be Reformulated)

Conceptos

Las 12 reglas de Codd

• R7: INSERT, UPDATE y DELETE de alto nivel

Un RDBMS debe soportar operaciones INSERT,

UPDATE y DELETE de alto nivel (de conjuntos) para

cualquier conjunto recuperable de datos

• R8: Independencia de la representación física

Los usuarios y aplicaciones son inmunes a los cambios

realizados en la representación física o métodos de

acceso a los datos.

Conceptos

Las 12 reglas de Codd

• R9: Independencia de las modificaciones lógicas

Los usuarios y aplicaciones son inmunes a los cambios

en la estructura lógica de la base (agregado de una

relación, agregado de un atributo a una relación,

modificación del orden de los atributos de una relación).

• R10: Independencia de las restricciones de integridad

Las restricciones de integridad se deben almacenar en el

catálogo, y su modificación no debe afectar a las

aplicaciones existentes.

Conceptos

Las 12 reglas de Codd

• R11: Independencia distribuida

Las aplicaciones deben seguir funcionando bien cuando:

(a) se introduce una versión distribuida del DBMS y (b)

los datos distribuidos existentes son redistribuidos.

• R12: No subversión

No debe haber otra forma de modificar la base que a

través de un lenguaje de múltiples tuplas como SQL (si

la base provee cursores no deben poder usarse para

evitar la integridad o seguridad).

Conceptos

Las 12 reglas de Codd … son 13 !

• R0: Un RDBMS sólo debe utilizar las capacidades

relacionales para gestionar la base de datos

• En la práctica, no todas las reglas se respetan

• Oracle no respeta el tratamiento sistemático de los

valores NULL

• Todos los RDBMSs soportan parcialmente la

actualización de vistas, y debido a ello, la independencia

de las actualizaciones lógicas (e.g. sustituir una tabla por

dos más una vista UNION ALL)

Conceptos

Esquema e instancia

• El esquema de la base es el conjunto de las variables de

relación, es decir, la definición de las tablas, incluyendo sus

restricciones (tipo de datos, NOT NULL, PRIMARY KEY,

UNIQUE KEY, FOREIGN KEY, CHECK)

• No debería cambiar muy a menudo, sólo si cambian los

requerimientos

Conceptos

Esquema e instancia

• En un RDBMS, el esquema se define mediante un lenguaje

llamado DDL (Data Definition Language)

Estudiantes (cedula, nombre, apellido, fec_nac)

Estudiantes (

cedula: número,

nombre: texto(20),

apellido: texto(20),

fec_nac: fecha)

CREATE TABLE ESTUDIANTES(

CEDULA NUMBER NOT NULL PRIMARY KEY,

NOMBRE VARCHAR(20) NOT NULL,

APELLIDO VARCHAR(20) NOT NULL,

FEC_NAC DATE);

Conceptos

Esquema e instancia

• La instancia de la base es el conjunto de las relaciones, es

decir, los valores de las variables de relación

• La instancia cambia todo el tiempo

( INSCRIPCIONES )

Conceptos

Esquema e instancia

• En un RDBMS, la instancia se modifica mediante un

lenguaje llamado DML (Data Manipulation Language)

• Altas / inserciones

INSERT INTO ESTUDIANTES ...

• Bajas / eliminaciones

DELETE FROM ESTUDIANTES ...

• Modificaciones

UPDATE ESTUDIANTES ...

Conceptos

Más sobre la “C” en ACID

• Uno de los trabajos de un RDBMS es soportar

transacciones ACID, ahora podemos profundizar un poco

más en la “C” (Consistency)

• Mediante el DDL se definen restricciones de integridad

• El RDBMS debe asegurar que la instancia, en todo

momento, tenga un estado consistente, es decir, que cumpla

con las restricciones... especialmente cuando se ejecutan

operaciones de DML

• ¿Qué puede pasar durante una operación DML?

Conceptos

Más sobre la “C” en ACID

• INSERT

Imagine que se quiere insertar una nueva tupla en

INSCRIPCIONES con un valor de CEDULA que no existe en

la columna CEDULA de ESTUDIANTES; si se permite esta

inserción se viola la restricción de Integridad Referencial.

En ESTUDIANTES se debe impedir que se inserte una

CEDULA repetida, NULL o que no sea un número.

Conceptos

Más sobre la “C” en ACID

• UPDATE

Piense en las consecuencias de modificar el valor de

CEDULA de una tupla en la relación ESTUDIANTES o en la

relación INSCRIPCIONES. ¿Y qué sucede si descubrimos

que una cédula está mal?

• DELETE

¿Qué pasa si se elimina una tupla de ESTUDIANTES cuyo

valor de CEDULA está en INSCRIPCIONES?

Agenda

Herramientas gratuitasConceptos

Herramientas

Conceptos

Herramientas gratuitas

• Hay muchas herramientas gratuitas que permiten realizar

diseño lógico:

• Oracle SQL Developer Data Modeler

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

• MySQL Workbench

http://dev.mysql.com/downloads/workbench

• Open ModelSphere

http://www.modelsphere.org/download_open_modelsphere_m.html

Conceptos

Herramientas gratuitas

• brModelo:

• Permite realizar el diseño lógico desde cero

• Permite realizar el diseño conceptual y generar el diseño

lógico de forma semi-automática

• Reverse Snowflake Joins:

http://snowflakejoins.com/revj

• Permite visualizar consultas SQL en un formato de

diseño lógico (lo veremos más adelante)