Bases de Dades ORACLE

Unitat: Administració remota d'objectes de la BD amb l'Schema Manager

PRÀCTICA

En aquesta pràctica partirem del disseny ja realitzat d'una base de dades relacional que representa una empresa comercial i implantarem en Oracle els objectes (taules, índexs, vistes, etc.). El disseny és una versió reduïda de la suministrada com a exemple en Microsoft Access, anomenada Neptuno.mdb .

1. Disseny de la base de dades

Vista general de les taules i interrelacions de la base de dades.

En la següent imatge es mostren les taules de la BD i les interrelacions entre elles (totes del tipus 1-N). Els camps de cada taula són els originals de Neptuno.mdb. En la nostre pràctica treballarem amb totes les taules però només amb un subconjunt dels seus camps.

Descripció detallada de cada taula

Taula: Categorías
Activitat: nul.la o quasi nul.la.
Descripció dels camps:
IdCategoría: autonuméric, clau primària. NombreCategoria: texte (15), no null, indexat sense duplicats. Descripción: texte (60). Imagen: imatge descriptiva.

Taula: Clients
Activitat: insercions i eliminacions freqüents.
Descripció dels camps:
IdCliente: texte (5), clau primària. NombreCompañía: text(40), no null, indexat amb duplicats. NombreContacto: texte(30), no nul. CargoContacto: text(30), no nul. Dirección: text(60), no nul. Ciudad: text(15), no nul, indexat amb duplicats. Región: text(15), no nul, indexat amb duplicats. CódPostal: text(10), no nul, indexat amb duplicats. País:text(15), no nul.Teléfono: text(24), no nul. Fax: text(24) no nul.

Taula: Compañías de envíos
Activitat: nul.la o quasi nul.la.
Descripció dels camps:
IdCompañíaEnvios: autonumèric, camp clau. NombreCompañía: text(40), no nul. Teléfono: text(24), no nul.

Taula: Detalles de Pedidos
Activitat: Insercions freqüents. Eliminacions nul.les..
Descripció dels camps:
IdPedido: numèric(6). IdProducto: numèric(6) . PrecioUnidad: numéric(6) no nul. Cantidad: numéric positiu no nul. Descuento: numéric(2) amb 1 decimal.
Els camps IdPedido i IdProducto conjuntament formen la clau primària.

Taula: Empleados
Activitat: insercions i eliminacions poc freqüents.
Descripció dels camps:
IdEmpleado: autonumèric, clau primària. Apellidos: text(30). Nombre: text (20). Cargo: text(30). Tratamiento: text (30). Dirección: text (60). Ciudad: text (30). CódPostal: text (5). TelDomicilio: text (9). Tots els camps són obligatoris.

Taula: Productos
Activitat: insercions i eliminacions nul.les o quasi nul.les..
Descripció dels camps:
IdProducto: autonumèric, clau primària. NombreProducto: text (40). IdProveedor: enter(10), indexat amb duplicats. IdCategoria: enter(10), indexat amb duplicats. CantidadPorUnidad: enter(6). PrecioUnidad: enter(6). UnidadesEnExistencia: enter(6). UnidadesEnPedido: enter(6). Excepte els camps indexats i el NombreProducto, la resta de camps no són obligatoris.

Taula: Pedidos
Activitat: Insercions freqüents. Eliminacions nul.les.
Descripció dels camps:
IdPedido: autonumèric (clau primària). IdCliente: text(5), indexat sense duplicats. IdEmpleado: enter(10), indexat amb duplicats. FechaPedido: data. FormaEnvío: enter(10). Tots els camps són obligatoris.

Taula: Proveedores
Activitat: insercions i eliminacions nul.les o quasi nul.les.
IdProveedor: autonumèric, clau primària. NombreCompañía: text(40). NombreContacto: text(30). CargoContacto: text(30). Dirección: text(40). Tots els camps són obligatoris.

2. ACTIVITATS DE LA PRÀCTICA

2.1 Crear les taules en Oracle segons les especificacions del disseny, tenint especial cura en la definició de claus primàris (primary key) i camps d'interrelació entre taules (foreign key) per evitar problemes posteriors.

2.2 Definiu un cluster que comprengui el camp IdPedido de la taula Pedidos i tots els camps de la taula Detalles_de_Pedidos doncs aquestes dues taules quasi sempre es consultaran simultàniament.

2.3 Introduïr algunes dades en les taules, com a mínim tres files per taula. Haureu de tenir en compte que les dades verifiquin les restriccions d'integritat que heu imposat en l'apartat anterior.

2.4 Definir una instantània (snapshot) de refresc manual anomenada Venedors sobre una de les taules més consultades: Empleados. Incloure tots els camps.

2.5 Definir les següents vistes:

Productes_del_Proveidor: mostra els noms dels productes que comercialitza l'empresa junt amb els noms dels seus proveidors. Contindrà la següent sentència:

Select NombreCompañía, NombreProducto From Productos, Proveedores
Where Productos.IdProveedor = Proveedores.IdProveedor
Com ja sabeu, per interrelacionar dues taules (o més) en SQL cal indicar els camps que han de coincidir (la clau primària i la foreign key) en la clàusula Where.
Productes_i_Categoríes: Mostra una llista de nom de productes, preu i el seu nom de categoría interrelacionant les taules Productos i Categorías.
Productes_econòmics: fa un Select sobre la vista Productes_i_Categorías seleccionant aquells productes amb un preu igual o inferior a 1000.
Vendes_dels_Empleats: mostra un llista d'empleats i el total d'articles que han vengut. La sentència Select compendrà tres taules interrelacionades: Empleados, Pedidos i Detalles_Pedidos i contindrà clàusules d'agrupació i de totals per obtenir la suma del camp Cantidad. Utilitzeu la instantània Venedors en lloc de la taula Empleados per augmentar la velocitat d'accés.

2.6 Definir el següents sinònims:

Productes: sinònim de la vista Productes_del_Proveidor.
Vendes: sinònim de la vista Vendes_dels_Empleats.
Comandes: sinònim de la taula Detalles_de_Pedidos.
Doneu drets de Select per a tothom sobre la vista Comandes.

2.5 Definiu un disparador (trigger) que s'activi amb qualsevol operació d'inserció, esborrat o modificació sobre la taula Empleados de forma que aparegui un missatge prou visible advertint que s'ha de refrescar la instantània Venedors doncs la taula Empleados ha canviat. Per exemple:

*********************************************
*** Ull: has de actualitzar la instantània Venedors ***
*********************************************