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;