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.