2. Restrict views and modifications to that translation to base table modifications is meaningful and unambiguous
Restrictions in SQL standard for updatable views
1. Select(no distinct) on single table T
2. Attributes in table T not in view can be NULL or have default value
3. Subqueries must not refer to T, can refer to other tables
4. No GROUP BY or aggregation
Demo: simple college admissions database
College(cName, state, enrollment)
Student(sID, sName, GPA, sizeHS)
Apply(sID, cName, major, decision)
Example 1
create view CSaccept as
select sID, cName
from Apply
where major = ‘CS’ and decision = ‘Y’;
delete from CSaccept where sID = 123; – works, good
insert into CSaccept values (333, ‘Berkeley’); – works, bad
Example 2
create view CSEE as
select sID, cName, major
from Apply
where major = ‘CS’ or major = ‘EE’;
insert into CSEE values (111, ‘Berkeley’, ‘CS’) works, good
insert into CSEE values (111, ‘Berkeley’, ‘psychology’) works, bad
Example 4 Fix bad inserts
create view CSaccept as
select sID, cName
from Apply
where major = ‘CS’ and decision = ‘Y’
with check option;
insert into CSaccept values (444, ‘Berkeley’); – Error
create view CSEE as
select sID, cName, major
from Apply
where major = ‘CS’ or major = ‘EE’
with check option;
insert into CSEE values (444, ‘Berkeley’, ‘psychology’); – Error
insert into CSEE values (444, ‘Berkeley’, ‘CS’); – Works
Example 5
create view Bio as
select * from Student
where sID in (select sID from Apply where major like’bio%’);
delete from Bio where sName = ‘Bob’; – works, deletes from Student
insert into Bio values (555, ‘Karen’, 3.9, 1000); – works, only adds to Student, not in View
create view Bio2 as
select * from Student
where sID in (select sID from Apply where major like’bio%’)
with check option;
insert into Bio values (555, ‘Karen’, 3.9, 1000); – Error
Check option affects efficiency.
Example 6 Views that can’t be modified
create view HSgpa as
select sizeHS, avg(gpa)
From Sutdent
group by sizeHS;
create view Majors as
select distinct major from Apply;
create view NonUnique as
select * from Student S1
where exists (select * from Student S2
where S1.sID <> S2.sID
and S2.GPA = S1.GPA and S2.sizeHS = S1.sizeHS);
Example 7 View with join
create view Stan(sID, aID, sName, major) as
select Student.sID, Apply.sID, sName, major
from Student, Apply
where Student.sID = Apply.sID and cName = ‘Stanford’;
update Stan set sName = ‘CS major’ where major = ‘CS’
Cannot delete from join view, be careful with insertions.