CPS 430/542 Lecture notes: Views

---

Three types of relations

---

• stored relation (as known as a table or base relation)
• virtual relation (as known as a view)
• temporary results (i.e., subqueries in FROM or WHERE clause)

---

Relation with a view

---

• views are the relational database analog of iTunes playlists
• tuples in view are not physically stored (other than in the base relation), only logically stored
• no duplication of data
• 3-tier ANSI sparc DB architecture
• why use views? for same reasons as playlists; also, for security purposes (e.g., to protect ssn's)

Students(ssn, name, major, gpa)

CREATE VIEW CPSstudents AS
   (SELECT name, gpa
    FROM Students
    WHERE major = 'CPS');

• can be defined over multiple relations (including other views)
• can be queried (or used in queries) just like a base relation
• can be used, for other operations, just like a base relation with some caveats

---

Processing queries involving views

---

• translated to query with only base relations by the query processor

SELECT name
FROM CPSstudents
WHERE gpa >= 3.0;

= 

SELECT name
FROM (SELECT name, gpa
      FROM Students
      WHERE major = 'CPS')
WHERE gpa >= 3.0;

=

SELECT name
FROM Students
WHERE major = 'CPS' AND gpa >= 3.0;

• query optimization and rewrite rules
• relational algebra query trees (triangles)

---

Can we insert into a view?

---

• often the answer is `no'
• for a view to be updatable, it must have been defined
  • with no more than one relation (or updatable view) in the FROM clause
  • without a subquery in the WHERE clause
  • without a DISTINCT in the SELECT clause
  • with attributes sufficient to insert tuple properly into the base relation
• if these conditions are met, the view is updatable
• tuples inserted into base relations are padded with NULL's
• same rules apply for

  INSERT INTO
  DELETE FROM
  UPDATE
  DROP VIEW
  

  • if base relation DROPped, all views defined through it are now broken (akin to a dangling pointer)
• how can views go awry?

  INSERT INTO CPSstudents VALUES
     ('Larry', 3.0);
  

• moral of the story: views of use only for queries, not updates

---

Rules in Datalog/PROLOG are views

---

   animal(emu,2).
   animal(llama,4).
   animal(cow,4).
   four_legs(X) ← animal(X,4).
   four_legs(dog).

---

Other things to note

---

• views just added to mySQL
• see Ullman's notes on differences between mySQL and standard SQL

---

References

[FCDB]

J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.