3.1. Sentencias de definición

3.1. Sentencias de definición Carlos 25 Noviembre, 2009 - 12:50

• Creación de la base de datos

Atención

En algunos casos es conveniente la definición de dominios para facilitar el trabajo posterior de mantenimiento de la coherencia de la base de datos. No es acon- sejable definir dominios para cada dominio relacional, pero sí en los casos en que una columna puede tomar una serie de valores determinados.

CREATE SCHEMA GESTION_PETICIONES;

• Definición de dominios

CREATE DOMAIN dom_estados AS CHAR (20) 
    CONSTRAINT estados_validos
    CHECK (VALUE IN (`Nueva´,´Se necesitan más datos´,´Aceptada´, ´Confirmada´, ´Resuelta´,`Cerrada´))
   DEFAULT `Nueva´;

• Creación de las tablas

Atención

Aquí deberemos tener en cuenta las reglas de integridad, ya que habrá que explicitar la política escogida como restricción.

CREATE TABLE PETICION ( referencia INTEGER NOT NULL,    
   cliente INTEGER NOT NULL,
   resumen CHARACTER VARYING (2048), 
   estado dom_estados NOT NULL, 
   fecharecepcion TIMESTAMP NOT NULL,
   fechainicio TIMESTAMP, fechafin IMESTAMP,         
   tiempoempleado TIME, PRIMARY KEY (referencia),  
   FOREIGN KEY cliente REFERENCES CLIENTE(nif)
     ON DELETE CASCADE 
     ON UPDATE CASCADE,
   CHECK (fecharecepcion &lt; fechainicio), 
   CHECK (fechainicio &lt; fechafin) );

CREATE TABLE NOTA_PETICION ( peticion INTEGER NOT NULL, 
   nota CHARACTER VARYING (64000),
   fecha TIMESTAMP NOT NULL, 
   empleado CHARACTER (9),
   FOREIGN KEY (peticion) REFERENCES PETICION(referencia) 
     ON DELETE NO ACTION&gt;
     ON UPDATE CASCADE,
   FOREIGN KEY (empleado) REFERENCES EMPLEADO(nif)  
     ON DELETE NO ACTION
     ON UPDATE CASCADE );

CREATE TABLE MATERIAL_PETICION ( nombrematerial CHARACTER VARYING (100) NOT NULL,
   peticion INTEGER NOT NULL, 
   precio DECIMAL(8,2), 
   cantidad INTEGER,
   FOREIGN KEY (peticion) REFERENCES PETICION(referencia) 
     ON DELETE NO ACTION
     ON UPDATE CASCADE );

CREATE TABLE CLIENTE ( nombre CHARACTER VARYING (100) NOT NULL,
   nif CHARACTER (9) NOT NULL, 
   telefono CHARACTER (15), 
   email CHARACTER (50), 
   PRIMARY KEY (nif) );

CREATE TABLE EMPLEADO (nombre CHARACTER VARYING (100) NOT NULL,
   nif CHARACTER (9) NOT NULL, 
   PRIMARY KEY (nif) );

• Creación de vistas

Función de vistas

Las vistas agilizarán las consultas que prevemos que van a ser mas frecuentes.

– Peticiones pendientes:

CREATE VIEW peticiones_pendientes (referencia, nombre_cliente, resumen, estado, duracion, fecharecepcion) AS (
SELECT P.referencia, C.nombre, P.resumen, P.estado,(P.fechainicio	P.fecharecepcion), P.fecharecepcion
FROM PETICION P JOIN CLIENTE C ON P.cliente = C.nif
WHERE estado NOT IN (`Resuelta´,´Cerrada´) ORDER BY fecharecepcion )

– Tiempo y precio de los materiales empleados para las peticiones terminadas en el mes en curso:

CREATE VIEW peticiones_terminadas (referencia, nombre_cliente,	resumen, tiempo_empleado, importe_materiales) AS (
   SELECT P.referencia, C.nombre, P.resumen, P.tiempoempleado, SUM(M.precio) 
   FROM PETICION P, CLIENTE C, MATERIAL_PETICION M
   WHERE P.cliente=C.nif AND M.peticion=P.referencia AND estado=`Resuelta´ 
   GROUP BY P.referencia)