Views in SQL

1
Views in SQL

• View or Virtual Tables in SQL A view in SQL is a
  single table that is derived from other tables
  (could be base table or previously defined views)
• o    A view does not necessarily exist in
  physical form virtual table
• o    Base tables tuples are actually stored in
  the database
•     Restricts possible update operations on
  views
• No limitation on querying a view

2
Views in SQL

• Consider two relations students (sid, name,
  login, age, gpa), enrolled (cid, grade, sid) .
• Suppose that we are often interested in finding
  names and login of students who got a grade B in
  some course with the cid of the course
• Create View B-students (name, login, course)
• As Select s.name, s.sid, e.id
• From Students s, Enrolled e
• Where s.sid e.sid And e.grade B 

3
Views in SQL

• Conceptually whenever B-students is used in a
  query, the view definition is first evaluated to
  obtain the corresponding instance of B-students,
  and then the rest of the query is evaluated
  treating B-students like any other relation
  referred to in the query. 
• Update on view
• View mechanism is to tailor how users see the
  data
• SQL-92 allows update to be specified only on
  views that are defined on a single base table
  using just selection and projection, with no use
  of aggregation operation updateable views

4
Views in SQL

• Why need to restrict view updates 
• SQL-92 view updates are most stringent than
  necessary
• Student (sid, name, login, age, gpa)
• Club (cname, jyear, mname)
• A tuple in club denotes that the student called
  mname has been a member of the club cname since
  the date jyear.

5
Views in SQL

• Why need to restrict view updates 
• Suppose we are often interested in finding the
  name and login of students with a gpa greater
  than 3 who belong to at least one club along with
  the club name and the date they joined the club.
• Create View ActiveStudents (name, login, club,
  since) As
• Select s.name, s.login, c.name, cjyear
• From Students s, Clubs c
• Where s.name c.mname And s.gpa 3

6
Views in SQL
7
Querying views

• If the view did not include a key for the
  underlying table, several rows in the table could
  correspond to a single row in the view (in the
  following example, mname is used instead of sid)
• Now we would like to delete the row (Smith,
  smith_at_ee, Hiking, 1997) from ActiveStudents 
• We must change either
• Students or
• Clubs
• (or both)
• in such a way that evaluating the view definition
  on the modified instance does not produce the row
  (Smith, smith_at_ee, Hiking, 1997).

8
Querying views

• Two ways
• Either
• deleting row (53688, Smith, smith_at_ee, 18, 3.2)
  from Students or
• deleting (Hiking, 1997, Smith) from Clubs.
• Neither is satisfactory.
• Removing from Students also delete tuple Smith_at_ee, Rowing, 1998 from the view
  ActiveStudents. Removing the clubs row also has
  the effect of deleting the row
  smith_at_math, Hiking, 1997 from ActiveStudents.
• Only reasonable solution is to disallow such
  updates on views.

9
Querying views

•  View Implementation
• Efficiently implement a view
• Two main approaches
• Query modification Modify view query into a
  query on the underlying base tables. 
• Inefficient for views defined via complex queries
  
• We need answer very fast /interactive response
  time
• Consider the total number of students for each
  club and the query is
• Select Club, count() Form ActiveStudents Group
  by Club 

10
Querying views

• Select R.Club, count()
• From (Select s.name as Name, s.login as Login,
  c.name as Club, cjyear as Year
• From Students s, Clubs c
• Where s.name c.mname And s.gpa 3) as R
• Group by R.club
• View materialization
• At first, we evaluate the view definition and
  store the result (i.e., physically create a
  temporary view table when the view is first
  queried).
• When a query is now posed on the view, the
  (unmodified) query is executed on the
  pre-computed result

11
Querying views

• Advantage Much faster than query modifications
• Complex view needs not be evaluated when the
  query is computed
• Disadvantage Maintain the consistency of the
  pre-computed view whenever the underlying tables
  are updated
• Incremental update has been developed where it
  is determined what new tuples must be inserted,
  deleted or modified in a materialized view table
  when a change is applied to one of the defining
  base tables

12
Changing the database

• How do we initialize the database? How do we
  update and modify the database state?
• SQL supports an update language for insertions,
  deletions and modifications of tuples.
• INSERT INTO R(A1,,An) VALUES (V1,,Vn)
• DELETE FROM R WHERE
• UPDATE R SET WHERE
  

13
Tuple insertion

• Recall our rock climbing database, with the
  following instance of Routes
• To insert a new tuple into Routes

INSERT INTO Routes(RId, Rname, Grade, Rating,
Height) VALUES (5, Desperation, III,12,600)
14
Tuple insertion, cont.

• Alternatively, we could omit the attributes since
  the order given matches the DDL for Routes

INSERT INTO Routes VALUES (5, Desperation,
III,12,600)
15
Set insertion

• Suppose we had the following relation and wanted
  to add all the routes with rating 8

INSERT INTO HardClimbs(Route,Rating,FeetHigh)
SELECT DISTINCT Rname, Grade, Rating,
Height FROM Routes WHERE rating8
Route Rating FeetHigh SlimyClimb 9
200 The Sluice 8 60 Last Tango 12
100
16
Deletion

• Deletion is set-oriented the only way to delete
  a single tuple is to specify its key.
• Suppose we wanted to get rid of all tuples in
  HardClimbs that are in Routes

DELETE FROM HardClimbs WHERE Route in (SELECT
Name FROM Routes)
17
Modifying tuples

• Non-key values of a relation can be changed using
  UPDATE.
• Suppose we want to increase the age of all
  experienced climbers by 1
• NOTE SQL uses an old-value semantics. New
  values are calculated using the old state, not a
  partially modified state.

UPDATE Climbers SET Age Age1 WHERE Skill
EXP
18
Old-value semantics

• Give a 1000 raise to every employee who earns
  less than their manager.
• Old-value semantics employees 1 and 3 are given
  a raise.
• Otherwise employee 2 will get a raise if they
  are considered after employee 3 receives a raise!

19
Schema modification

• Requirements change over time, so it is useful to
  be able to add/delete columns, drop tables and
  drop views
• DROP TABLE Climbers
• DROP VIEW ExpClimbers
• ALTER TABLE Climbs ADD Weather CHAR(50)
• ALTER TABLE Routes DROP Grade

20
Summary

• Views are useful for frequently executed queries
  and as a layer to shield applications from
  changes in the schema.
• SQL has an update language that allows
  set-oriented updates. Updates (insertions,
  deletions and modifications) change the database
  state.