V := ViewQuery(R1, R2, ..., Rn); Evaluate Q;
CREATE VIEW ViewName AS Queryor to rename attributes:
CREATE VIEW ViewName(A1, A2, ..., Am) AS Query
Student(ID, name, address, GPA, SAT) Campus(location, enrollment, rank) Apply(ID, location, date, major, decision)Example: Applicants to Santa Cruz
CREATE VIEW ApplySC AS SELECT ID, date, major FROM Apply WHERE location = 'SC'Can use in query:
SELECT Student.ID, name, major FROM Student, ApplySC WHERE Student.ID = ApplySC.ID AND (GPA > 3.7 OR SAT > 1400)rewritten to:
SELECT Student.ID, name, major FROM Student, (SELECT ID, date, major FROM Apply WHERE location = 'SC') as ApplySC WHERE Student.ID = ApplySC.ID AND (GPA > 3.7 OR SAT > 1400)or in "flattened" form:
SELECT Student.ID, name, major FROM Student, Apply WHERE Student.ID = Apply.ID AND (GPA > 3.7 OR SAT > 1400) AND location = 'SC'Or could make query into another view:
CREATE VIEW HighSC AS SELECT Student.ID, name, date, major FROM Student, ApplySC WHERE Student.ID = ApplySC.ID AND (GPA > 3.7 OR SAT > 1400)Another example: Students who applied to more than one campus
CREATE VIEW MultiApply AS SELECT DISTINCT Student.* FROM Student as S, Apply as A1, Apply as A2 WHERE S.ID = A1.ID AND S.ID = A2.ID AND A1.location <> A2.locationCan make entire join into a view ("universal relation"):
CREATE VIEW AllDB AS SELECT Student.ID, name, address, GPA, SAT, Campus.location, enrollment, rank, date, major, decision FROM Student, Campus, Apply WHERE Student.ID = Apply.ID AND Apply.location = Campus.locationNo more joins in queries!
Now can write:
SELECT * FROM AllDB WHERE GPA < 3.5 AND rank > 3 AND major = 'psychology'rewritten to:
SELECT Student.ID, name, address, GPA, SAT, Campus.location, enrollment, rank, date, major, decision FROM Student, Campus, Apply WHERE Student.ID = Apply.ID AND Apply.location = Campus.location AND GPA < 3.5 AND rank > 3 AND major = 'psychology'
DELETE FROM ApplySC WHERE date < 1/1/03translated to:
DELETE FROM Apply WHERE date < 1/1/03 AND location = 'SC'Another example:
CREATE VIEW CsEe AS SELECT ID, location, major FROM Apply WHERE major = 'CS' OR major = 'EE'Insertion into this view:
INSERT INTO CsEe VALUES (123, 'Berkeley', 'CS')translated to:
INSERT INTO Apply VALUES (123, 'Berkeley', NULL, 'CS', NULL)Note:
Insertion into ApplySC view:
INSERT INTO ApplySC VALUES (123, 4/15/03, 'economics')translated to:
INSERT INTO Apply VALUES (123, NULL, 4/15/03, 'economics', NULL)Note:
INSERT INTO HighSC VALUES (123, 'Mary', 4/15/03, 'CS')
Question: Translated to?
Example:
CREATE VIEW AvgSAT(a) AS SELECT avg(SAT) FROM Student; UPDATE AvgSAT SET a = 1125;
Question: Translated to?
Recall example: Students who applied to more than one campus
CREATE MATERIALIZED VIEW MultiApply AS SELECT DISTINCT Student.* FROM Student as S, Apply as A1, Apply as A2 WHERE S.ID = A1.ID AND S.ID = A2.ID AND A1.location <> A2.locationQuestion: What modifications to regular relations might invalidate current contents of MultiApply?
Even though materialized views are stored as tables, updates to materialized views incur same ambiguity problems as updates to virtual views.
Question: Why not just update materialized view table?
Question: How does one decide whether to use a virtual or a materialized view?