With views, we will work with external schemas.

To create a view, you need to use the CREATE VIEW statement

STRUCTURECREATE VIEW view_name [(column_list)] AS (query) [WITH CHECK OPTION];

Views do not actually exist as a set of stored values in the database; instead, they are fictitious tables, called derived (non-materialized) tables. They are constructed from real (materialized) tables stored in the database.

To delete a view, you need to use the DROP VIEW statement, which has the following format:

DROP VIEW view_name {RESTRICT|CASCADE};

Views can be updatable or non-updatable.

ADVANTAGES

  • Independence
  • Simplification
  • Improvement of data and application usage from the user's perspective regarding security
  • Data integrity
  • Performance

DISADVANTAGES

  • Update restrictions
  • Structure restrictions; some DBMS do not allow building a view from just any query.

UPDATING VIEWS

Views can always be queried, but they cannot always be updated...

A view is not updatable when:

  • There is a primary key, NOT NULL attribute, or UNIQUE constraint of a table that is not involved in the generated view.
  • Generally, views defined on more than one table (Join)
  • Views that include DISTINCT, HAVING, GROUP BY clauses or aggregate functions (AVG, MIN, ..)

Triggers allow modifications to be performed on tables that are NOT updatable.

We create a view on the Empresa_a database that gives us, for each client, the number of projects commissioned by the 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

);

The WITH CHECK OPTION clause ensures that we will never be able to act on parts of a table that are not in the view.

ACTIVITIES

Download script exposicions.

  1. Given the following view: CREATE VIEW AdrecesFotografs AS SELECT Nom, Adreca, Pais FROM Fotografs; INSERT INTO AdrecesFotografs VALUES ('Jack Shephard','St. Sebastian hospital, Los Angeles', 'EEUU'); Briefly explain what would be the effect on the previous view and the Fotografs table if we execute the following SQL statement: INSERT INTO AdrecesFotografs VALUES ('Jack Shephard','St. Sebastian hospital, Los Angeles', 'EEUU'); It would update the AdrecesFotografs view and Fotografs, but in Fotografs the PRIMARY KEY would be missing. Would anything change if the view had the WITH CHECK OPTION clause? In this case nothing different happens because there is no WHERE clause, and you do not add anything that is not in the list; if we had something outside the list, it would not allow it.
  1. Create a view called FotografsDeRenom that retrieves all data from the Fotografs table and also, for each photographer, gives us the average price visitors paid to enter the exhibitions where this photographer has exhibited, and that the list comes out sorted by average price in descending order. Is this view updatable? Why? 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; It is not updatable because it has a GROUP BY and it is a view defined on more than one table.

Views activity: Manipulation of the Exposicions database

Download script exposicions.

  1. Create a view of those exhibitions that were carried out between 3-6-2010 and 15-11-2010. Show all data of the exhibitions and their participants, and that have a price higher than 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; It is not updatable, because it uses JOINs + GROUP BY
  1. Create a view that shows the passport, name, and country of photographers who participate in any exhibition with a price greater than or equal to the average of all exhibitions. 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; It is not updatable, because it uses JOINs + GROUP BY
  1. Create a view containing the exhibition codes of exhibitions held in 2010, where the photographers are from England. The exhibition codes, date, and first and last names of the photographers must be displayed. 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; It is not updatable, because it uses JOINs.
  1. Create a view where, based on exhibitions held in 2010, participating photographers have exhibited more than 2 photographs. Show the photographer's first and last name, exhibition (code and name), and number of photographs. 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; It is not updatable, because it uses JOINs
  1. Invent a view that is updatable and another one that is not, based on the Exposicions DB.
  1. We want to create an updatable view so that staff can add the passport, name, and address of photographers who enter. DROP VIEW VistaActualitzable; CREATE VIEW VistaActualitzable AS SELECT passaport,nom,adreca FROM Fotografs WITH CHECK OPTION; select * FROM VistaActualitzable;
  1. We want to create a view that shows the names of the photographers along with the titles of the exhibitions. 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;

EXERCISES ON VIEWS OVER THE MATRICULES DB

Download script matricules.

For each exercise, argue whether or not it is updatable, that is, whether or not it allows insertion, deletion, and modification operations.

  1. Create a view that retrieves the DNI, name, and start year of all students who have been at the school for more than five years. 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; It is not updatable because it uses JOIN and GROUP BY.
  1. Create a view that retrieves the subject code, name, person in charge, area, and number of students taking it, for all subjects in area "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; It is not updatable; as in the previous exercise, the use of JOIN and GROUP BY makes it non-updatable.
  1. Create a view that shows the number of enrollments for each subject every year; specifically, it must show the year, the name of the subject, and the number of enrollments. Sort the results by year and semester number in descending order. 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; It is not updatable, as in the previous exercises, due to the use of JOIN and GROUP BY.