Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Views, Automatic View Modifications

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.

December 3, 2011 | Comments Off on Intro to Databases: Views, Automatic View Modifications | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: