diseño lógico - pedeciba• es el proceso de definir el esquema lógico de una base de datos,...
TRANSCRIPT
![Page 1: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/1.jpg)
Maestría en Bioinformática
Bases de Datos y Sistemas de Información
Diseño Lógico
Ing. Alfonso Vicente, [email protected]
![Page 2: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/2.jpg)
Agenda
Diseño lógico
Modelo Relacional
Las 12 reglas de Codd
Esquema e instancia
Más sobre la “C” en ACID
Conceptos
Herramientas
![Page 3: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/3.jpg)
Agenda
Herramientas gratuitasConceptos
Herramientas
![Page 4: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/4.jpg)
Agenda
Diseño lógico
Modelo Relacional
Las 12 reglas de Codd
Esquema e instancia
Más sobre la “C” en ACID
Conceptos
Herramientas
![Page 5: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/5.jpg)
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
![Page 6: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/6.jpg)
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
![Page 7: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/7.jpg)
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
![Page 8: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/8.jpg)
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
![Page 9: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/9.jpg)
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
![Page 10: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/10.jpg)
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
![Page 11: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/11.jpg)
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?
![Page 12: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/12.jpg)
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
![Page 13: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/13.jpg)
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
![Page 14: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/14.jpg)
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”
![Page 15: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/15.jpg)
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
![Page 16: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/16.jpg)
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
![Page 17: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/17.jpg)
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
![Page 18: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/18.jpg)
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)
![Page 19: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/19.jpg)
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.
![Page 20: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/20.jpg)
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.
![Page 21: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/21.jpg)
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).
![Page 22: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/22.jpg)
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)
![Page 23: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/23.jpg)
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
![Page 24: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/24.jpg)
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);
![Page 25: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/25.jpg)
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 )
![Page 26: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/26.jpg)
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 ...
![Page 27: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/27.jpg)
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?
![Page 28: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/28.jpg)
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.
![Page 29: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/29.jpg)
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?
![Page 30: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/30.jpg)
Agenda
Herramientas gratuitasConceptos
Herramientas
![Page 31: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/31.jpg)
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
![Page 32: Diseño Lógico - PEDECIBA• 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](https://reader035.vdocumento.com/reader035/viewer/2022062917/5ece00cde612f130492ec986/html5/thumbnails/32.jpg)
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)