Práctica+claves+ajenas

=UT 2. Práctica de creación de tablas, claves ajenas y vistas (en Access).= toc

1. Creación de tablas y claves ajenas
En primer lugar debemos crear la base de datos de **proveedores** y **piezas**. Las tablas y nombres de sus columnas son los siguientes:

PROVEEDOR(__pr__, nombre*, estado*, ciudad*) PIEZA(__pz__, nombre*, color*, peso*, ciudad*) SUMINISTRO(__pr__, __pz__, cantidad*)

PROVEEDORES es la tabla de proveedores, con un código (**pr**), el nombre del proveedor (**nombre**), el estado del proveedor (**estado**) y su ciudad (**ciudad**). PIEZAS es la tabla de piezas, con un código de pieza (**pz**), nombre de la pieza (**nombre**), color de la pieza (**color**), peso de la pieza (**peso**) y ciudad de origen de la pieza (**ciudad**). Por último SUMINISTRO es la tabla que describe la cantidad (**cantidad**) de piezas suministradas por cada proveedor. Las columnas PROVEEDOR.estado, PIEZA.peso y SUMINISTRO.cantidad son de tipo entero, el resto cadenas de caracteres. Si quieres fijar longitudes máximas fíjate en las tablas de datos (se muestran más adelante). Especificar también las restricciones de clave primaria que aparece subrayada en la descripción de tablas. Existen dos claves ajenas:

code SUMINISTRO.pr referencia a PROVEEDOR SUMINISTRO.pz referencia a PIEZA code

Al especificar las claves ajenas debemos especificar las reglas de comportamiento de cada clave ajena: propagar, restringir o anular. En nuestro caso elegiremos restringir (opción por defecto). A continuación rellenaremos las tablas de la base de datos. ¿Por qué tabla deberíamos comenzar primero?

Los datos a introducir en PROVEEDOR son:
 * pr || nombre || estado || ciudad ||
 * < S1 ||< Salazar ||< 20 ||< Londres ||
 * < S2 ||< Jaimes ||< 10 ||< París ||
 * < S3 ||< Bernal ||< 30 ||< París ||
 * < S4 ||< Corona ||< 20 ||< Londres ||
 * < S5 ||< Aldana ||< 30 ||< Atenas ||

En PIEZAS:


 * pz || nombre || color || peso || ciudad ||
 * < P1 ||< tuerca ||< verde ||< 12 ||< París ||
 * < P2 ||< perno ||< rojo ||< 17 ||< Londres ||
 * < P3 ||< birlo ||< azul ||< 17 ||< Roma ||
 * < P4 ||< birlo ||< rojo ||< 14 ||< Londres ||
 * < P5 ||< birlo ||< azul ||< 12 ||< París ||
 * < P6 ||< engrane ||< rojo ||< 19 ||< París ||

En SUMINISTRO:

Intenta responder ahora a las siguientes preguntas, primero recapacita sobre ella de forma teórica y luego intenta realizar (si os posible) una prueba en la base de datos.
 * pr || pz || cantidad ||
 * S1 || P1 || 300 ||
 * S1 || P2 || 200 ||
 * S1 || P3 || 400 ||
 * S1 || P4 || 200 ||
 * S1 || P5 || 100 ||
 * S1 || P6 || 100 ||
 * S2 || Pl || 300 ||
 * S2 || P2 || 400 ||
 * S3 || P2 || 200 ||
 * S4 || P2 || 200 ||
 * S4 || P4 || 300 ||
 * S4 || P5 || 400 ||

1.1. Caso 1
Si las reglas de borrado y modificación de ambas claves ajenas son restringir:
 * 1) ¿Se puede borrar la fila de PROVEEDOR que corresponde al proveedor S1?
 * 2) ¿Se puede borrar la fila de PROVEEDOR que corresponde al proveedor S5?
 * 3) En general ¿qué proveedores se pueden borrar?
 * 4) ¿Se puede borrar de SUMINISTRO el envío del proveedor S1 y la pieza P2?
 * 5) En general ¿qué envíos se pueden borrar?
 * 6) ¿Se puede insertar en SUMINISTRO un envío del proveedor S1 y de la pieza P7 con una candad de 300?
 * 7) ¿Se puede insertar en SUMINISTRO un envío del proveedor S1 y de la pieza P6 con una candad de 200?
 * 8) ¿Se puede insertar en SUMINISTRO un envío del proveedor S2 y de la pieza P3 con una cantidad de 400?
 * 9) En general ¿Cuándo se puede insertar envíos en la tabla SUMINISTRO?
 * 10) ¿Se puede borrar la fila de PIEZA que corresponde a la pieza P6?
 * 11) En general ¿qué piezas se pueden borrar?

1.2. Caso 2
Vuelve a dejar las tablas en su estado original. Cambia las reglas de comportamiento de SUMINISTRO.pr a propagar:
 * 1) ¿Se puede borrar la fila de PROVEEDOR que corresponde al proveedor S1?
 * 2) ¿Qué ha pasado con los envíos del proveedor S1?
 * 3) ¿Se puede borrar la fila de PROVEEDOR que corresponde al proveedor S5?
 * 4) En general ¿qué proveedores se pueden borrar?
 * 5) ¿Se puede borrar de SUMINISTRO el envío del proveedor S2 y la pieza P2?
 * 6) En general ¿qué envíos se pueden borrar?
 * 7) ¿Se puede borrar la fila de PIEZA que corresponde a la pieza P6?
 * 8) ¿Se puede borrar la fila de PIEZA que corresponde a la pieza P2?
 * 9) En general ¿qué piezas se puede borrar?

1.3. Caso 3
Vuelve a dejar las tablas en su estado original. Cambia las reglas de comportamiento de SUMINISTRO.pz a propagar:
 * 1) ¿Se puede borrar la fila de PROVEEDOR que corresponde al proveedor S1?
 * 2) ¿Qué ha pasado con los envíos del proveedor S1?
 * 3) ¿Se puede borrar la fila de PROVEEDOR que corresponde al proveedor S5?
 * 4) En general ¿qué proveedores se pueden borrar?
 * 5) ¿Se puede borrar de SUMINISTRO el envío del proveedor S2 y la pieza P2?
 * 6) En general ¿qué envíos se pueden borrar?
 * 7) ¿Se puede borrar la fila de PIEZA que corresponde a la pieza P6?
 * 8) ¿Se puede borrar la fila de PIEZA que corresponde a la pieza P2?
 * 9) ¿Qué ha pasado con los envíos de la pieza P2?
 * 10) En general ¿qué piezas se pueden borrar?

1.4. Caso 4
Vuelve a dejar las tablas en su estado original. Cambia las reglas de comportamiento de ambas claves, de modo que para el borrado escojas restringir y para la modificación escojas propagar:
 * 1) ¿Se puede borrar la fila de PROVEEDOR que corresponde al proveedor S1?
 * 2) ¿Se puede borrar la fila de PROVEEDOR que corresponde al proveedor S5?
 * 3) En general ¿qué proveedores se pueden borrar?
 * 4) ¿Se puede modificar en PROVEEDOR el código del proveedor S1 y cambiarlo por S6?
 * 5) ¿Qué ha pasado con los envíos del proveedor S1?
 * 6) ¿Se puede modificar en SUMINISTRO el envío del proveedor S3, poniendo que es el proveedor S4?
 * 7) ¿Se puede modificar en SUMINISTRO el envío anterior, poniendo que es el proveedor S5?
 * 8) ¿Se puede modificar en SUMINISTRO el envío anterior, poniendo que es el proveedor S7?
 * 9) ¿Cuándo se puede modificar el código de un proveedor en PROVEEDOR?
 * 10) ¿Cuándo se puede modificar el código de un proveedor en un envío de SUMINISTRO?
 * 11) En general ¿qué piezas se pueden borrar?
 * 12) ¿Cuándo se puede modificar el código de una pieza en PIEZA?
 * 13) En general ¿qué envíos se pueden borrar?
 * 14) ¿Cuándo se puede modificar el código de una pieza en un envío de SUMINISTRO?

2. Vistas
Las vistas son útiles porque:
 * Proporcionan seguridad ocultando partes de la base de datos a ciertos usuarios.
 * Permiten que los usuarios accedan a los datos en el formato que ellos desean o necesitan.
 * Se pueden simplificar operaciones sobre las relaciones base que son complejas.
 * Se puede utilizar una vista para ofrecer un esquema externo a un usuario de modo que éste lo encuentre “familiar”.
 * Los atributos se pueden renombrar.
 * Se pude cambiar el orden en que se visualizan las columnas.
 * Se pueden hacer restricciones para que sólo se vea un subconjunto horizontal de la relación.
 * Proporcionan independencia de datos a nivel lógico:
 * Si se añade un atributo a una relación, los usuarios no se percatan de su existencia si subsistemas no lo incluyen.
 * Si una relación existente se reorganiza o se divide en varias relaciones, se pueden crear vistas para que los usuarios la sigan viendo como al principio.
 * Si una relación existente se reorganiza o se divide en varias relaciones, se pueden crear vistas para que los usuarios la sigan viendo como al principio.

Según el estándar SQL, hay ciertas condiciones bajo las cuales se determina si se permite realizar una actualización sobre una vista:
 * Se permiten las actualizaciones de vistas que se definen mediante una consulta simple sobre una sola relación base y que contienen la clave primaria de la relación base.
 * No se permiten las actualizaciones de vistas que se definen sobre varias relaciones base.
 * No se permiten las actualizaciones de vistas definidas con operaciones de agrupamiento (GROUP BY).No se permiten las actualizaciones de vistas definidas con operaciones de conjuntos.

En SQL las vistas se crean mediante la sentencia CREATE VIEW. En Access las vistas se crean como consultas. Como en los casos anteriores necesitamos la base de datos inicial, sobre la que crearemos algunas vistas.

2.1. Crear una vista que corresponda a la siguiente relación:
Lista_Proveedores(pr,nombre,ciudad)
 * 1) ¿Qué sucede si se trata de insertar en la vista el proveedor S6 que se llama Vidal y que está en Atenas? Observa la tabla base sobre la que se ha definido la vista.
 * 2) ¿La vista es actualizable?
 * 3) Cambia la definición de la tabla PROVEEDOR, especificando que la columna estado no admite nulos (es requerida) y que no tiene valor por defecto. ¿La vista es actualizable?

2.2. Crea ahora una nueva vista como la del apartado anterior pero sin el código de proveedor:

 * 1) ¿Qué sucede si se trata de insertar un nuevo proveedor que se llama GIL y que está en Paris?
 * 2) ¿Qué sucede si se cambia la ciudad del proveedor Jaimes y pasa de ser París a ser Roma?
 * 3) ¿La vista es actualizable?

2.3. Crea una vista que corresponda a la siguiente relación:
Provisiones(pz,pz_nombre,pr,pr_nombre)
 * 1) ¿Qué sucede si se trata de insertar en la vista en envío de 300 unidades del proveedor S7, que se llama Monfort y la pieza P7 cuyo nombre es tornillo? Observa las tablas base sobre las que se ha definido la vista.
 * 2) ¿La vista es actualizable?
 * 3) ¿Debería serlo según el estándar?
 * 4) ¿Qué sucede si sobre la vista se trata de cambiar el envío de S3 para convertirlo en un envio de S2?
 * 5) ¿Qué sucede si sobre la vista se cambia el nombre del proveedor S1 por Marcos en el envío de P1?
 * 6) ¿Qué sucede si se trata de insertar en la vista un envío de 100 unidades del proveedor S2, se pone como nombre Prades, y la pieza es P8, con nombre clavo?
 * 7) ¿Será actualizable la vista si se elimina de su definición la columna pz?

2.4. Crea una vista que muestre, para cada proveedor, su código, su nombre, su estado, su ciudad y la cantidad total correspondiente a todos sus envíos:

 * 1) ¿Se puede insertar en la vista un nuevo proveedor S8, cuyo nombre es Valero, su estado es 30, su ciudad es Roma y que no envía ninguna pieza (su cantidad total será cero... ¿o nula?).
 * 2) ¿La vista es actualizable?

2.5. Crea una nueva vista que corresponda a la siguiente relación, que muestra solo las piezas rojas o azules:
Piezas_rojasazules(pz,nombre) Si no puedes realizar la
 * 1) ¿Se puede insertar en la vista una nueva pieza P9 con nombre tornillo?
 * 2) ¿La vista es actualizable?

2.6. Crea una vista que obtiene el mismo resultado que la anterior:
Piezas_rojasazules(pz,nombre) En este caso la relación no debe utilizar operaciones de conjuntos _(unión)
 * 1) ¿Se puede insertar en la vista una nueva pieza P9 con nombre tornillo?
 * 2) Si la vista sólo muestra piezas rojas o azules, y se ha insertado en ella la pieza P9 ¿de qué color es esta nueva pieza? Consulta la tabla de las piezas.
 * 3) Vuelve a consultar la vista ¿aparece la pieza P9?
 * 4) ¿La vista es actualizable? ¿Debería serlo?.