CC42A: Bases de datos
Auxiliar: Modelo Entidad-Relación y Modelo Relacional

Profesores: Claudio Gutiérrez, Gonzalo Navarro
Prof. Auxiliar: Rodrigo Paredes

P1. Problema del huerto frutícola


Vern Stratton es un horticultor que está en el negocio de las frutas desde hace cincuenta años. Antes que él, su padre y su abuelo fueron dueños de sus huertos y previeron que al menos uno de los nietos lo heredaría. Ellos tienen excelentes registros de datos desde el siglo XIX que podrían constituir la base para un comprensivo sistema de información. Vern está ahora interesado en las respuestas a preguntas como:

La Figura 1 muestra un modelo Entidad/Relación simple que puede utilizarse para responder a estas preguntas.


Figura 1: Modelo Entidad/Relación para el huerto frutícola

Los supuestos considerados son:

  1. El huerto tiene un atributo AREA que lo describe (por ej.: Springtwon, Lee's Valley, etc.)
  2. Cada huerto esta relacionado con los árboles que están en el huerto, por lo que las instancias de ARBOL son árboles físico, no tipos de árboles
  3. Los árboles tienen un año de plantado y muerte, si el árbol aún es productivo el valor del atributo AÑO DE MUERTE es nulo.
  4. Los árboles tienen especies (manzanos, duraznos, etc.) y variedades (manzanas verdes, manzanas rojas, etc.). Un árbol puede tener injertos, por ejemplo un manzano rojo puede tener un injerto de manzana verde.
  5. no verde pero ambas son manzanas, luego produce las dos variedades pero es de una sola especie.

P2. Diseñar un modelo de datos, dado un reporte en papel


Considerando el formulario de la orden de compra de la izquierda (Figura 2), tenemos el modelo de datos de la derecha (Figura 3).
 
Figura 2: Formulario de orden de compra
Figura 3: Modelo Entidad/Relación de una orden de compra

Uno un poco más complicado es el modelo Entidad/Relación (Figura 5) que sale a partir del formulario de la factura  (Figura 4).
 
Figura 4: Formulario de factura Figura 5: Modelo Entidad/Relación de una Factura

P3. Pasar de un modelo Entidad/Relación al Esquema de las Relaciones

 
Ejemplo de como pasar de Entidad/Relación al Esquema de la Relación
Caso
Modelo Entidad/Relación
Esquema de la relación
Entidad con llave PERSONA {#SS, FECHA_NACIMIENTO}
Entidad sin llave VENTA {ID_VENTA, IMPORTE, #PRODUCTO}

Noten que le agregamos el atributo ID_VENTA a la relación!!

Herencia PERSONA {#SS, NOMBRE, DIRECCIÓN}
PERSONA_CASADA {#SS, CONYUGE}
Clave Foránea: #SS referencia a PERSONA

Nota: el resto de los atributos los hereda de persona a través de la clave foránea

Relaciones
uno-a-uno
CLIENTE {ID_CLIENTE, ID_CUENTA}
Clave foránea: ID_CUENTA referencia a CUENTA
CUENTA {ID_CUENTA, ID_CLIENTE}
Clave foránea: ID_CLIENTE referencia a CLIENTE

Noten que se cruzan las llaves primarias

Relaciones
uno-a-muchos
CLIENTE {ID_CLIENTE}
CUENTA {ID_CUENTA, ID_CLIENTE}
Clave foránea: ID_CLIENTE referencia a CLIENTE
Relaciones
muchos-a-muchos
CLIENTE {ID_CLIENTE}
CUENTA {ID_CUENTA}
TIENE_CTA_CTE {ID_CLIENTE, ID_CUENTA}
Clave foránea: ID_CLIENTE referencia a CLIENTE
                      ID_CUENTA referencia a CUENTA
Relaciones
con atributos

Esto es equivalente al rombito con atributos

PRODUCTO {ID_PRODUCTO}
PAÍS {ID_PAÍS}
SE_VENDÍO_EN {ID_PRODUCTO, ID_PAÍS, CANTIDAD}
Claves foráneas: ID_PRODUCTO referencia a PRODUCTO
                         ID_PAÍS referencia a PAÍS

P4. Más problemas de Entidad/Relación a Esquema de relaciones

a)

CLIENTE {ID_CLIENTE, NOMBRE_CLIENTE, DIRECCIÓN}
Nota: puede ser que tenga dos clientes con el mismo nombre, así que agrego un identificador único, ID_CLIENTE, para identificar al cliente.

PROYECTO {ID_PROYECTO, ID_CLIENTE, TITULO, TOTAL, NÚMERO_FACTURA, FECHA_FACTURA}
Claves Foránea: ID_CLIENTE referencia a CLIENTE
Nota: al igual que para CLIENTE, no puedo usar el título de un proyecto como identificador único, así que agrego
ID_PROYECTO como identificador único.

CARGO {ID_CARGO, ID_PROYECTO, CANTIDAD, DESCRIPCIÓN}
Claves Foráneas: ID_PROYECTO referencia a PROYECTO

Ahora las entidades que heredan:
SERVICIO {ID_CARGO, ID_PROYECTO, CONSULTOR}
Claves Foráneas: ID_CARGO, ID_PROYECTO referencia a CARGO

Como CARGO_POR_SUMINISTRO no tiene campos, no merece un esquema de la relación, puesto que con el esquema de CARGO tenemos toda la información que se necesita.
 

b)

TRABAJADOR {ID_TRABAJADOR, NOMBRE, TARIFA_HR, TIPO_DE_OFICIO, ID_SUPV}
Claves Foráneas: TIPO_DE_OFICIO referencia a OFICIO
                           ID_SUPV referencia a TRABAJADOR
ASIGNACIÓN {ID_TRABAJADOR, ID_EDIFICIO, FECHA_INICIO, NÚM_DÍAS}
Claves Foráneas: ID_TRABAJADOR referencia a TRABAJADOR
                           ID_EDIFICIO referencia a EDIFICIO
EDIFICIO {ID_EDIFICIO, DIR_EDIFICIO, TIPO, NIVEL_CALIDAD, CATEGORÍA}
OFICIO {TIPO_DE_OFICIO, PRIMA, HORAS_POR_SEMANA}