VISTES

Amb les vistes, treballarem amb els esquemes externs.

Per a crear una vista cal fer servir la sentència CREATE VIEW

ESTRUCTURA

CREATE VIEW nom_vista [(llista_columnes)] AS (consulta) [WITH CHECK OPTION];

Les vistes no existeixen realment com un conjunt de valors emmagatzemats a la base de dades, sinó que són taules fictícies, anomenades derivades (no materialitzades). Es construeixen a partir de taules reals (materialitzades) emmagatzemades a la base de dades.

Per a esborrar una vista cal fer servir la sentència DROP VIEW, que presenta el format:

DROP VIEW nom_vista {RESTRICT|CASCADE};

Les vistes poden ser actualitzables o no.

AVANTATGES

• Independència
• Simplificació
• Millora de les dades i de les aplicacions de l’ús per a l’usuari de la seguretat
• Integritat de les dades
• Rendiment

DESAVENTATGES

• Restriccions d’actualitzacions
• Restriccions d’estructura; alguns SGBD no permeten construir una vista a partir d’una consulta qualsevol.

 ACTUALITZACIÓ DE LES VISTES

Les vistes sempre es poden consultar, però no sempre es poden actualitza...

Una vista no és actualitzable quan:

• Hi ha una clau primària, atribut not null o UNIQUE d’alguna taula que no intervé en la vista generada.
• Generalment les vistes definides sobre més d’una taula (Join)
• Les vistes que inclouen clàusules DISTINCT, HAVING, GROUP BY o funcions d’agregació (AVG, MIN,..),

Els disparadors permeten efectuar modificacions sobre taules que NO són actualitzables.

Creem una vista sobre la base de dades Empresa_a que ens doni per a cada client el número de projectes que té encarregats el client:

CREATE VIEW projectes_per_client (codi_cli, nombre_projectes) AS (

   SELECT c.codi_cli, COUNT(*)

   FROM projectes p, clients c

   WHERE p.codi_client = c.codi_cli

   GROUP BY c.codi_cli

);

La clàusula WITH CHECK OPTION assegura que mai podrem actuar sobre parts d’una taula que no estan a la vista.

ACTIVITATS

Descarregar script exposicions.

1. Donada la vista següent:
   CREATE VIEW AdrecesFotografs AS SELECT Nom, Adreca, Pais FROM Fotografs;
   INSERT INTO AdrecesFotografs VALUES ('Jack Shephard','St. Sebastian hospital, Los Angeles', 'EEUU');
   
   Explica breument quin seria l'efecte sobre la vista anterior i la taula Fotografs si executem la sentència SQL següent:
   INSERT INTO AdrecesFotografs VALUES ('Jack Shephard','St. Sebastian hospital, Los Angeles', 'EEUU');
   Que actualitzaria la view AdrecesFotografs i Fotografs però en fotografs faltaría la PRIMARY KEY.
   
   Canviaria alguna cosa si la vista tingués la clàusula WITH CHECK OPTION?
   En aquest cas no succeeix res diferent perque no hi ha un WHERE, i no afegeixes res que no estigui a la llista, si tinguesim alguna cosa de fora de la llista, no ho permetria.
   
2. Crear una vista anomenada FotografsDeRenom que obtingui totes les dades de la taula Fotografs i que a més, per a cada fotògraf, ens doni el preu mig que van pagar els visitants per entrar a les exposicions on aquest fotògraf ha exposat, i que el llistat surti ordenat pel preu mig de forma descendent. És actualitzable aquesta vista? Per què?
   CREATE VIEW FotografsDeRenom AS
       SELECT f.passaport, f.nom, f.adreca, f.telefon, f.pais, f.classificacio_fotograf, avg(e.preu) as 'preu mig'
       FROM Fotografs f, Exposicions e, Exposen ex
       WHERE f.passaport=ex.passaport AND e.codi_exposicio=ex.codi_exposicio
       GROUP BY (*)
       ORDER BY preu_mig DESC;
   
   No es actualitzable perque te un GROUP BY i es una vista definida sobre més d'una taula.

Activitat vistes: Manipulació de la base de dades Exposicions

Descarregar script exposicions.

1. Crear una vista d'aquelles exposicions que s'han portat a terme entre el 3-6-2010 i el 15-11-2010. Mostrar totes les dades de les exposicions i dels seus participants, i que tinguin un preu superior a 9€.
   DROP VIEW Exposicions_estiu;
   
   CREATE VIEW Exposicions_estiu AS
       SELECT ex.*, f.* FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       WHERE (ex.data_inici BETWEEN '2010-6-3' AND '2010-11-15') AND (ex.data_final BETWEEN '2010-6-3' AND '2010-11-15') AND ex.preu>9
       GROUP BY codi_exposicio;
   
   select * FROM Exposicions_estiu;
   
   No es actualitzable, perque utilitza JOIN's + GROUP BY
   
2. Crear una vista que mostri el passaport, el nom i el país dels fotògrafs que participen a alguna exposició amb preu superior o igual a la mitjana de totes les exposicions.
   
   DROP VIEW Participants_de_preu_elevat;
   
   CREATE VIEW Participants_de_preu_elevat AS
       SELECT f.passaport, f.nom, f.pais FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       WHERE ex.preu>=(select avg(ex2.preu) FROM Exposicions ex2)
       GROUP BY passaport;
      
   select * FROM Participants_de_preu_elevat;
   
   No es actualitzable, perque utilitza JOIN's + GROUP BY
3. Crear una vista que contingui els codis d'exposició de les exposicions fetes al 2010, on els fotografs siguin d'Anglaterra. Cal mostrar els codis d'exposició, la data i nom i cognom dels fotografs.
   DROP VIEW Expos2010;
   
   CREATE VIEW Expos2010 AS
       SELECT ex.codi_exposicio, ex.data_inici, ex.data_final, f.nom FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       WHERE 2010=YEAR(data_inici) AND 2010=YEAR(data_final) AND f.pais="Anglaterra";
      
   select * FROM Expos2010;
   
   No es actualitzable, perque utilitza JOIN's.
4. Crea una vista on a partir de les exposicions fetes al 2010 els fotografs participants hi hagin exposat més de 2 fotografies. Mostra nom i cognom del fotograf, exposició (codi i nom) i número de fotografies.
   
   DROP VIEW Expos2010mes2f;
   
   CREATE VIEW Expos2010mes2f AS
       SELECT f.nom, ex.codi_exposicio, ex.titol, e.num_fotos FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       WHERE YEAR(data_inici)=2010 AND e.num_fotos>2;
     
   select * FROM Expos2010mes2f;
   
   No es actualitzable, perque utilitza JOIN's
5. Inventa una vista que sigui actualitzable i una altre que no ho sigui, a partir de la BD Exposicions.
1. Volem crear una vista actualitzable per a que el personal pugui afegir el passaport, el nom i l'adreça dels fotografs que entrin.
   DROP VIEW VistaActualitzable;
   
   CREATE VIEW VistaActualitzable AS
       SELECT passaport,nom,adreca FROM Fotografs
       WITH CHECK OPTION;
      
   select * FROM VistaActualitzable;
   
2. Volem crear una vista que mostri el nom dels fotografs amb el titol de les exposicions.
   DROP VIEW VistaNoActualitzable;
   
   CREATE VIEW VistaNoActualitzable AS
       SELECT f.nom, ex.titol FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       GROUP BY f.nom asc,ex.titol
       WITH CHECK OPTION;
      
   select * FROM VistaNoActualitzable;

EXERCICIS VISTES SOBRE LA BD MATRICULES

Descarregar script matricules.

Per a cada exercici argumenta si és o no actualitzable, és a dir, si admet o no operacions d'inserció, d'esborrat i de modificació.

1. Crear una vista que recuperi el dni, el nom i l'any d'inici de tots els estudiants que porten més de cinc anys a l'escola.
   DROP VIEW Alumnes_antics;
   
   CREATE VIEW Alumnes_antics AS
       SELECT e.DNI, e.nom, m.Any FROM ESTUDIANT e NATURAL JOIN MATRICULES m
       WHERE (YEAR(CURDATE())-(m.Any))>5
       GROUP BY DNI;
   
   select * FROM Alumnes_antics;
   
   No es actualitzable perquè utilitza JOIN i GROUP BY.
2. Creeu una vista que recuperi el codi de l'assignatura, el nom, el responsable, l'àrea, i el número d'estudiants que l'estan cursant, per a totes les assignatures de l'àrea "M".
   CREATE VIEW Estudiants_cursen_M AS
       SELECT e.codi_assignatura, e.nom, e.responsable, e.area, count(c.dni) FROM Assignatures e NATURAL JOIN Cursen c
       WHERE e.area="M"
       GROUP BY c.codi_assignatura;
   
   select * FROM Estudiants_cursen_M;
   
   No es actualitzable, com a l'exercici anterior, l'utilització de JOIN fa que no ho sigui i el GROUP BY.
3. Crea una vista que mostri el número de matriculacions de cada assignatura cada any, concretament ha de mostrar l'any, el nom de l'assignatura i el número de matriculacions. Cal ordenar els resultats per any i número de semestre de forma descendent.
   CREATE VIEW N_MATRICULACIONS AS
       SELECT m.any, a.nom, count(m.codi_matricula) FROM MATRICULES m NATURAL JOIN ASSIGNATURA a JOIN ESTUDIANT e ON m.codi_matricula=e.codi_matricula
       GROUP BY a.nom, m.any, m.numero
       ORDER BY m.any desc,m.numero desc
   ;
   
   select * FROM N_MATRICULACIONS;
   No es actualitzable, com als exercicis anteriors, l'utilització de JOIN i GROUP BY.