Views

1
Unit III
2
Views

• A table that is derived from other tables
• Considered as a virtual table
• Does not store data physically

3
Views in SQL

• The syntax
• CREATE VIEW "VIEW_NAME"
• AS "SQL Statement"

4
Views in SQL

• CREATE VIEW DEPT_INFO
• AS
• SELECT DNAME, COUNT(), SUM(SALARY) FROM
  DEPARTMENT, EMPLOYEE
• WHERE DNUM DNO GROUP BY DNAME

5
Views in SQL

• ADV
• Simplification of certain queries
• Can also be used as a security mechanism
• View is always up-to-date.
• To dispose a view, DROP VIEW is used

6
Updation in Views

• A view with single defining table is updatable if
  the view attributes contain the primary key or
  some other key of the base relation.
• Views defined on multiple tables using joins are
  not updatable
• Views defined using grouping and aggregate
  functions are not updatable

7
Need for views

• Views provide a shorthand or macro capability
• Allow the same data to be seen by different users
  in different ways at the same time
• Provide automatic security for hidden data
• Views can provide logical data independence

8
Views

• Principle of Interchangability
• There must be no arbitrary and unnecessary
  distinctions between tables and views.
• Principle of Database Relativity
• User works with a mixture of base tables and
  views called an expressible database.

9
View Retrievals

• Let D be a database and V be a view on D
• V X ( D )
• Expression X is some funnction on D
• Let RO be a retrieval operation on V. The result
  of the retreival is
• RO (V ) RO ( X ( D ) )
• Thus the result of retrieval is equal to the
  result of applying X to D

10
View Retrievals

• Retrieval can be of two methods
• Materialization
• Materializing a copy of the relation that is the
  current value of view V and then applying RO to
  that materialized copy
• Substitution
• Materialization cannot be used for update
  operations
• Substitution is quite straightforward and works
  well in theory

11
View Updates

• View updatability is a semantic issue. Not a
  syntactic one.
• View updation must work correctly in the special
  case when the view in a base relvar
• The updating rules must preserve symmetry where
  applicable.
• The updating rules must take into account any
  applicable triggered actions, including in
  particular referential actions such as cascade
  delete

12
View Updates

• It is desirable to regard UPDATE as a short hand
  for DELETE-INSERT sequence.
• All updates on views must be implemented by the
  same kind of updates on the underlying relvars.
• Rules must be capable of recursive application.
  I.e, updates on views are all or nothing.

13
View Updates

• Union
• Insert Rule for A UNION B
• The new tuple must satisfy PA or PB or both. If
  it satisfies PA, it is inserted into A. If it is
  satisfies PB, it is inserted into B, unless it
  was inserted into B already as a side effect of
  inserting it into A

14
View Updates

• Union
• Delete Rule for A UNION B
• If the tuple to be deleted appears in A, it is
  deleted from A. If it appears in B, it is deleted
  from B

15
View Updates

• Union
• Update Rule for A UNION B
• The tuple to be updated must be such that the
  updated version satisfies PA or PB or both.
• If the tuple to be updated appears in A, it is
  deleted from A without performing any triggered
  actions and without checking the predicate for A.
  This may have the side effect of deleting from B
  also.

16
Contd

• Union
• Update Rule for A UNION B
• If the tuple (still) appears in B, it is deleted
  from B.
• If the updated version of the tuple satisfies PA,
  it is inserted into A. If the updated version
  satisfies PB, it is inserted into B , unless it
  is inserted into B already as a side effect of
  inserting it into A

17
View Updates

• Intersect
• INSERT
• The new tuple must satisfy both PA and PB. If it
  does not currently appear in A, it is inserted
  into A. If it (still) does not appear in B, it is
  inserted into B.
• DELETE
• The tuple to be deleted is deleted from A. If it
  (still) appears in B, it is deleted from B.

18
View Updates

• UPDATE
• The tuple to be updated must be such that the
  updated version satisfies both PA and PB.
• The tuple is deleted from A without performing
  any triggered actions or predicate checks. If it
  (still) appears in B, it is deleted from B
• If the updated version of the tuple does not
  currently appear in A, it is inserted into A. If
  it does not appear in B, it is inserted into B

19
View Updates

• Difference
• Rules for updating A MINUS B
• INSERT The new tuple must satisfy PA and not PB.
  It is inserted into A
• DELETE The tuple to be deleted is deleted from A
• UPDATE The tuple to be updated must be such that
  the updated version satisfies PA and not PB.The
  tuple is deleted from A and the updated version
  is inserted into A

20
View Updates- Project

• Let X and Y be two groups of attributes on
  relation A. Consider the projection of A over X.
  AX
• INSERT Let the tuple to be inserted be x. Let
  the default value of Y be y. The tuple (x,y) is
  inserted into A. (If no default values exists, it
  is an error

21
View Updates- Project

• DELETE All tuples of A with the same X value as
  the tuple to be deleted from AX are deleted
  from A
• UPDATE Let the tuple to be updated be (x)and the
  updated version is (x). Let a be a tuple of A
  with the same X value x, and let the value of Y
  in a be y. All such tuples a are deleted from A.
  Then for each value y, the tuple (x, y) is
  inserted into A
  
  

22
View Updates - Join

• Consider the join J A JOIN B where
• A, B and J have the headings X, Y, Y,Z and
  X, Y, Z respectively. Let the predicates for A
  and B be PA and PB respectively. Then Predicate
  for J is PJ and is equal to PA(a) and PB(b)

23
View Updates- JOIN

• INSERT The new tuple j must satisfy PJ. If the A
  portion of j does not appear in A, it is inserted
  into A. If B portion of j does not appear in B,
  it is inserted into B
• DELETE The A portion of the tuple to be deleted
  is deleted from A and the B portion is deleted
  from B

24
View Updates- JOIN

• UPDATE The tuple to be updated must satisfy PJ.
  The A portion is deleted from A and the B portion
  is deleted from B.
• If the A portion of the updated version does
  not appear in A, it is inserted into A. If the B
  portion does not appear in B, it is inserted into
  B.

25
View Updates- JOIN

• Implications of the rule for the cases
• One-to-one
• One-to-many
• Many-to-many

26
Functional Dependencies
27
Definition

• Let r be a relation and let X and Y be arbitrary
  subsets of the set of attributes of r.
• Then , Y is functionally dependent on X if and
  only if each X value in r has associated with it
  precisely one Y value in r.
• It is written symbolically as X Y
• In other words whenever two tuples of r agree on
  their X value, they also agree on their Y value

28
Trivial and Nontrivial Dependency

• A dependency is trivial if it cannot possibly
  fail to be satisfied.
• A dependency is trivial if and only if the right
  side is a subset of the left side.

29
Closure of a set of dependencies

• The set of all FDs that are implied by a given
  set S of FDs is called the closure of S, written
  as S .
• Armstrongs axioms allow to compute S from S
• Armstrongs axioms
• A set of inference rules by which new FDs can
  be inferred from given ones