Three-level vision of database
physical
conceptual
logical
A view is a query over a relation.
Why use views?
Hide some data from some users
make some queries easier / more natural
modularity of database access
Real applications tend to use lots of views.
Defining and using views
View V = ViewQuery(R1, R…, Rn)
Schema of V is schema of query result
Query Q involving V, conceptually:
V:= ViewQuery(R1, R…, Rn)
Evaluate Q
In reality, Q rewritten to use R1, R…, Rn instead of V
Note: Ri could itself be a view
SQL Syntax
Create View Vname As
or
Create View Vname(A1, A…, An) As
Demo: simple college admissions database
College(cName, state, enrollment)
Student(sID, sName, GPA, sizeHS)
Apply(sID, cName, major, decision)
create view CSaccept as
select sID, cName
from Apply
where major = ‘CS’ and decision = ‘Y’;
Can refer to view as if its a table.
create view CSberk as
select Student.sID, sName, GPA
From Student, CSaccept
where Student.sID = CSaccept.sID and cName = ‘Berkeley’ and sizeHS > 500;
create view Mega as
select College.cName, state, enrollment, Student.sID, sName, GPA, sizeHS, major, decision
from College, Student, Apply
where College.cName = Apply.cName and Student.sID = Apply.sID;