3. El lenguaje SQL

3. El lenguaje SQL Carlos 25 November, 2009 - 12:17

Una vez terminado el modelo relacional, decidimos completar la documentación que veníamos realizando con las sentencias SQL correspondientes. Así, veremos en qué se concretará el modelo relacional.

Como aún no sabemos en qué sistema gestor de base de datos vamos a implantar la solución, decidimos simplemente anotar las sentencias según el estándar SQL92, y, posteriormente, ya examinaremos las particularidades del sistema gestor escogido para adaptarlas.

 

3.1. Sentencias de definición

3.1. Sentencias de definición Carlos 25 November, 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 < fechainicio),
   CHECK (fechainicio < 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>
     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)

 

 

 

 

3.2. Sentencias SQL de manipulación

3.2. Sentencias SQL de manipulación
SQL
Dataprix 26 November, 2009 - 14:59

A continuación, decidimos indicar algunas sentencias de  manipulación corrientes para completar la documentación. De esta manera, cuando empecemos el desarrollo, tendremos mucho más claras estas operaciones sobre la base de datos:

•    Nuevo cliente:   
INSERT INTO CLIENTE VALUES (‘Juan Pérez’,’42389338A’,’912223354’,’juanperez@gmail.com’);

•    Nueva petición:
INSERT INTO PETICION VALUES (5,’42389338A’, ‘No le arranca el ordenador’, ’Nueva’,CURRENT_TIMESTAMP,NULL,NULL,NULL);

•    Cambio de estado de la petición, añadimos una nota y un material:
UPDATE PETICION SET estado=‘Aceptada’ WHERE referencia=5;
INSERT INTO NOTA_PETICION VALUES (5,’Parece un problema del disco duro. Vamos examinarlo más a fondo.’,CURRENT_TIMESTAMP,’35485411G’);
INSERT INTO MATERIAL_PETICION VALUES (‘Disco duro 20Gb’,5,250.00,1);

•    Materiales solicitados en la petición 5:
SELECT nombrematerial, cantidad, precio FROM MATERIAL_PETICION WHERE peticion=5

•    Número de peticiones abiertas del cliente ‘42389338A’:
SELECT COUNT(*) FROM PETICION WHERE cliente=‘42389338A’ AND estado NOT IN (‘Resuelta’, ’Cerrada’);

La creación de vistas del apartado anterior nos ha mostrado también algunas consultas complejas que repetimos a continuación:

•    Peticiones abiertas:
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:
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;

Finalmente, vamos a practicar con las consultas que realizamos en álgebra relacional en el apartado anterior:

•    Obtención de una petición junto con los datos del cliente:
R:= PETICION [cliente=nif] CLIENTE
SELECT * FROM PETICION JOIN CLIENTE ON PETICION.cliente=CLIENTE.nif;

•    Obtención de una petición con todas sus notas:
NP(peticionnota,nota,fechanota,empleado):=NOTA_PETICION (peticion,nota,fecha,empleado) R:=PETICION[referencia=peticionnota]NP
SELECT PETICION.*, peticion AS peticionnota, nota, fecha as fechanota, empleado
FROM PETICION JOIN NOTA_PETICION ON referencia=peticionnota;

•    Obtención de los datos de todos los empleados que han participado en la petición 5:
NP:=NOTA_PETICION[peticion=5]
RA:=EMPLEADO[nif=empleado]NP
R:=RA[nombre,nif]
SELECT E.nombre, E.nif FROM EMPLEADO E, NOTA_PETICION N WHERE
E.nif=NOTA_PETICION.empleado
AND NOTA_PETICION.peticion=5;