'
&
$

Chapter 3: Relational Model
· Structure of Relational Databases
· Relational Algebra
· Tuple Relational Calculus
· Domain Relational Calculus
· Extended Relational-Algebra-Operations
· Modification of the Database
· Views
Database Systems Concepts 3.1 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Basic Structure
· Given sets A1, A2, ..., An a relation r is a subset of
A1 × A2 × ... × An
Thus a relation is a set of n-tuples (a1, a2, ..., an) where
ai  Ai
· Example: If
customer-name = {Jones, Smith, Curry, Lindsay}
customer-street = {Main, North, Park}
customer-city = {Harrison, Rye, Pittsfield}
Then r = {(Jones, Main, Harrison), (Smith, North, Rye), (Curry,
North, Rye), (Lindsay, Park, Pittsfield)} is a relation over
customer-name × customer-street × customer-city
Database Systems Concepts 3.2 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Relation Schema
· A1, A2, ..., An are attributes
· R = (A1, A2, ..., An) is a relation schema
Customer-schema = (customer-name, customer-street,
customer-city)
· r(R) is a relation on the relation schema R
customer (Customer-schema)
Database Systems Concepts 3.3 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Relation Instance
· The current values (relation instance) of a relation are
specified by a table.
· An element t of r is a tuple; represented by a row in a table.
customer-name customer-street customer-city
Jones Main Harrison
Smith North Rye
Curry North Rye
Lindsay Park Pittsfield
customer
Database Systems Concepts 3.4 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Keys
· Let K  R
· K is a superkey of R if values for K are sufficient to identify a
unique tuple of each possible relation r(R). By "possible r" we
mean a relation r that could exist in the enterprise we are
modeling.
Example: {customer-name, customer-street} and
{customer-name} are both superkeys of Customer, if no two
customers can possibly have the same name.
· K is a candidate key if K is minimal
Example: {customer-name} is a candidate key for Customer,
since it is a superkey (assuming no two customers can possibly
have the same name), and no subset of it is a superkey.
Database Systems Concepts 3.5 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Determining Keys from E-R Sets
· Strong entity set. The primary key of the entity set becomes
the primary key of the relation.
· Weak entity set. The primary key of the relation consists of
the union of the primary key of the strong entity set and the
discriminator of the weak entity set.
· Relationship set. The union of the primary keys of the related
entity sets becomes a super key of the relation.
For binary many-to-many relationship sets, above super key is
also the primary key.
For binary many-to-one relationship sets, the primary key of
the "many" entity set becomes the relation's primary key.
For one-to-one relationship sets, the relation's primary key can
be that of either entity set.
Database Systems Concepts 3.6 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Query Languages
· Language in which user requests information from the
database.
· Categories of languages:
­ Procedural
­ Non-procedural
· "Pure" languages:
­ Relational Algebra
­ Tuple Relational Calculus
­ Domain Relational Calculus
· Pure languages form underlying basis of query languages that
people use.
Database Systems Concepts 3.7 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Relational Algebra
· Procedural language
· Six basic operators
­ select
­ project
­ union
­ set difference
­ Cartesian product
­ rename
· The operators take two or more relations as inputs and give a
new relation as a result.
Database Systems Concepts 3.8 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Select Operation
· Notation: P(r)
· Defined as:
P(r) = {t | t  r and P(t)}
Where P is a formula in propositional calculus, dealing with
terms of the form:
<attribute> = <attribute> or <constant>
=
>

<

"connected by":  (and),  (or),  (not)
Database Systems Concepts 3.9 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Select Operation ­ Example
· Relation r: A B C D
  1 7
  5 7
  12 3
  23 10
· A = B  D > 5 (r) A B C D
  1 7
  23 10
Database Systems Concepts 3.10 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Project Operation
· Notation:
A1, A2, ..., Ak
(r)
where A1, A2 are attribute names and r is a relation name.
· The result is defined as the relation of k columns obtained by
erasing the columns that are not listed
· Duplicate rows removed from result, since relations are sets
Database Systems Concepts 3.11 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Project Operation ­ Example
· Relation r: A B C
 10 1
 20 1
 30 1
 40 2
· A, C (r) A C A C
 1  1
-- 1-- =  1
 1  2
 2
Database Systems Concepts 3.12 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Union Operation
· Notation: r  s
· Defined as:
r  s = {t | t  r or t  s}
· For r  s to be valid,
1. r, s must have the same arity (same number of attributes)
2. The attribute domains must be compatible (e.g., 2nd
column of r deals with the same type of values as does the
2nd column of s)
Database Systems Concepts 3.13 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Union Operation ­ Example
· Relations r, s:
A B A B
 1  2
 2  3
 1 s
r
· r  s
A B
 1
 2
 1
 3
Database Systems Concepts 3.14 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Set Difference Operation
· Notation: r - s
· Defined as:
r - s = {t | t  r and t / s}
· Set differences must be taken between compatible relations.
­ r and s must have the same arity
­ attribute domains of r and s must be compatible
Database Systems Concepts 3.15 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Set Difference Operation ­ Example
· Relations r, s:
A B A B
 1  2
 2  3
 1 s
r
· r - s A B
 1
 1
Database Systems Concepts 3.16 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Cartesian-Product Operation
· Notation: r × s
· Defined as:
r × s = {t q | t  r and q  s}
· Assume that attributes of r(R) and s(S) are disjoint. (That is,
R  S = ).
· If attributes of r(R) and s(S) are not disjoint, then renaming
must be used.
Database Systems Concepts 3.17 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Cartesian-Product Operation ­ Example
· Relations r, s: A B C D E
 1  10 +
 2  10 +
r  20 -
 10 -
s
· r × s A B C D E
 1  10 +
 1  10 +
 1  20 -
 1  10 -
 2  10 +
 2  10 +
 2  20 -
 2  10 -
Database Systems Concepts 3.18 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Composition of Operations
· Can build expressions using multiple operations
· Example: A=C(r × s)
· r × s
­ Notation: r 1 s
­ Let r and s be relations on schemas R and S respectively.
The result is a relation on schema R  S which is obtained
by considering each pair of tuples tr from r and ts from s.
­ If tr and ts have the same value on each of the attributes in
R  S, a tuple t is added to the result, where
 t has the same value as tr on r
 t has the same value as ts on s
Database Systems Concepts 3.19 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Composition of Operations (Cont.)
Example:
R = (A, B, C, D)
S = (E, B, D)
· Result schema = (A, B, C, D, E)
· r 1 s is defined as:
r.A,r.B,r.C,r.D,s.E(r.B=s.B  r.D=s.D(r × s))
Database Systems Concepts 3.20 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Natural Join Operation ­ Example
· Relations r, s:
A B C D B D E
 1  a 1 a 
 2  a 3 a 
 4  b 1 a 
 1  a 2 b 
 2  b 3 b
r s
· r 1 s A B C D E
 1  a 
 1  a 
 1  a 
 1  a 
 2  b 
Database Systems Concepts 3.21 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Division Operation
r ÷ s
· Suited to queries that include the phrase "for all."
· Let r and s be relations on schemas R and S respectively,
where
­ R = (A1, ..., Am, B1, ..., Bn)
­ S = (B1, ..., Bn)
The result of r ÷ s is a relation on schema
R - S = (A1, ..., Am)
r ÷ s = {t | t  R-S(r)   u  s (tu  r)}
Database Systems Concepts 3.22 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Division Operation (Cont.)
· Property
­ Let q = r ÷ s
­ Then q is the largest relation satisfying: q × s  r
· Definition in terms of the basic algebra operation
Let r(R) and s(S) be relations, and let S  R
r ÷ s = R-S (r) - R-S ( (R-S (r) × s) - R-S,S(r))
To see why:
­ R-S,S(r) simply reorders attributes of r
­ R-S((R-S (r) × s) - R-S,S(r)) gives those tuples t in
R-S(r) such that for some tuple u  s, tu  r.
Database Systems Concepts 3.23 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Division Operation ­ Example
· Relations r, s: A B B
 1 1
 2 2
 3 s
 1
 1
 1
 3
 4
 6
1
2
r
· r ÷ s A

Database Systems Concepts 3.24 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Another Division Example
· Relations r, s:
A B C D E D E
 a  a 1 a 1
 a  a 1 b 1
 a  b 1 s
 a  a 1
 a  b 3
 a  a 1
 a  b 1
 a  b 1
r
· r ÷ s A B C
 a 
 a 
Database Systems Concepts 3.25 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Assignment Operation
· The assignment operation () provides a convenient way to
express complex queries; write query as a sequential program
consisting of a series of assignments followed by an
expression whose value is displayed as the result of the query.
· Assignment must always be made to a temporary relation
variable.
· Example: Write r ÷ s as
temp1  R-S (r)
temp2  R-S ((temp1 × s) - R-S,S(r))
result = temp1 - temp2
­ The result to the right of the  is assigned to the relation
variable on the left of the .
­ May use variable in subsequent expressions.
Database Systems Concepts 3.26 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find all customers who have an account from at least the
"Downtown" and "Uptown" branches.
­ Query 1
CN(BN = "Downtown"(depositor 1 account)) 
CN(BN = "Uptown"(depositor 1 account))
where CN denotes customer-name and BN denotes
branch-name.
­ Query 2
customer-name, branch-name (depositor 1 account)
÷ temp(branch-name)({ ("Downtown"), ("Uptown") })
Database Systems Concepts 3.27 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find all customers who have an account at all branches
located in Brooklyn.
customer-name, branch-name (depositor 1 account)
÷ branch-name (branch-city = "Brooklyn" (branch))
Database Systems Concepts 3.28 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Tuple Relational Calculus
· A nonprocedural query language, where each query is of the
form
{t | P (t) }
· It is the set of all tuples t such that predicate P is true for t
· t is a tuple variable; t[A] denotes the value of tuple t on
attribute A
· t  r denotes that tuple t is in relation r
· P is a formula similar to that of the predicate calculus
Database Systems Concepts 3.29 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Predicate Calculus Formula
1. Set of attributes and constants
2. Set of comparison operators: (e.g., <, , =, =, >, )
3. Set of connectives: and (), or (), not ()
4. Implication (): x  y, if x if true, then y is true
x  y   x  y
5. Set of quantifiers:
·  t  r (Q(t))  "there exists" a tuple t in relation r
such that predicate Q(t) is true
·  t  r (Q(t))  Q is true "for all" tuples t
in relation r
Database Systems Concepts 3.30 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Banking Example
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-city)
account (branch-name, account-number, balance)
loan (branch-name, loan-number, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
Database Systems Concepts 3.31 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find the branch-name, loan-number, and amount for loans of
over $1200
{t | t  loan  t [amount] > 1200}
· Find the loan number for each loan of an amount greater than
$1200
{t |  s  loan (t[loan-number] = s[loan-number]
 s[amount] > 1200)}
Notice that a relation on schema [customer-name] is
implicitly defined by the query
Database Systems Concepts 3.32 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find the names of all customers having a loan, an account, or
both at the bank
{t | s  borrower(t[customer-name] = s[customer-name])
u  depositor(t[customer-name] = u[customer-name])}
· Find the names of all customers who have a loan and an
account at the bank.
{t | s  borrower(t[customer-name] = s[customer-name])
u  depositor(t[customer-name] = u[customer-name])}
Database Systems Concepts 3.33 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find the names of all customers having a loan at the
Perryridge branch
{t | s  borrower(t[customer-name] = s[customer-name]
 u  loan(u[branch-name] = "Perryridge"
 u[loan-number] = s[loan-number]))}
· Find the names of all customers who have a loan at the
Perryridge branch, but no account at any branch of the bank
{t | s  borrower(t[customer-name] = s[customer-name]
 u  loan(u[branch-name] = "Perryridge"
 u[loan-number] = s[loan-number])
 v  depositor (v[customer-name] = t[customer-name]}
Database Systems Concepts 3.34 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find the names of all customers having a loan from the
Perryridge branch and the cities they live in
{t |  s  loan (s[branch-name] = "Perryridge"
  u  borrower (u[loan-number] = s[loan-number]
 t[customer-name] = u[customer-name]
  v  customer (u[customer-name] = v[customer-name]
 t[customer-city] = v[customer-city])))}
Database Systems Concepts 3.35 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find the names of all customers who have an account at all
branches located in Brooklyn:
{t |  s  branch (s[branch-city] = "Brooklyn" 
 u  account (s[branch-name] = u[branch-name]
  s  depositor (t[customer-name] = s[customer-name]
 s[account-number] = u[account-number])))}
Database Systems Concepts 3.36 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Safety of Expressions
· It is possible to write tuple calculus expressions that generate
infinite relations.
· For example, {t |  t  r} results in an infinite relation if the
domain of any attribute of relation r is infinite
· To guard against the problem, we restrict the set of allowable
expressions to safe expressions.
· An expression {t | P (t)} in the tuple relational calculus is safe
if every component of t appears in one of the relations, tuples,
or constants that appear in P
Database Systems Concepts 3.37 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Domain Relational Calculus
· A nonprocedural query language equivalent in power to the
tuple relational calculus.
· Each query is an expression of the form:
{< x1, x2, ..., xn > | P(x1, x2, ..., xn)}
­ x1, x2, ..., xn represent domain variables
­ P represents a formula similar to that of the predicate
calculus
Database Systems Concepts 3.38 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find the branch-name, loan-number, and amount for loans of
over $1200:
{< b, l, a > | < b, l, a >  loan  a > 1200}
· Find the names of all customers who have a loan of over
$1200:
{< c > |  b, l, a (< c, l >  borrower < b, l, a >  loan
 a > 1200)}
· Find the names of all customers who have a loan from the
Perryridge branch and the loan amount:
{< c, a > |  l (< c, l >  borrower
  b (< b, l, a >  loan  b = "Perryridge"))}
Database Systems Concepts 3.39 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Example Queries
· Find the names of all customers having a loan, an account, or
both at the Perryridge branch:
{< c > |  l (< c, l >  borrower
  b, a (< b, l, a >  loan  b = "Perryridge"))
  a (< c, a >  depositor
  b, n (< b, a, n >  account  b = "Perryridge"))}
· Find the names of all customers who have an account at all
branches located in Brooklyn:
{< c > |  x, y, z (< x, y, z >  branch  y = "Brooklyn") 
 a, b (< x, a, b >  account  < c, a >  depositor)}
Database Systems Concepts 3.40 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Safety of Expressions
{< x1, x2, ..., xn > | P (x1, x2, ..., xn)}
is safe if all of the following hold:
1. All values that appear in tuples of the expression are values
from dom(P) (that is, the values appear either in P or in a tuple
of a relation mentioned in P).
2. For every "there exists" subformula of the form  x (P1(x)), the
subformula is true if and only if there is a value x in dom(P1)
such that P1(x) is true.
3. For every "for all" subformula of the form x (P1(x)), the
subformula is true if and only if P1(x) is true for all values x
from dom(P1).
Database Systems Concepts 3.41 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Extended Relational-Algebra-Operations
· Generalized Projection
· Outer Join
· Aggregate Functions
Database Systems Concepts 3.42 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Generalized Projection
· Extends the projection operation by allowing arithmetic
functions to be used in the projection list.
F1,F2,...,Fn
(E)
· E is any relational-algebra expression
· Each of F1, F2, . . . , Fn are arithmetic expressions involving
constants and attributes in the schema of E.
· Given relation credit-info(customer-name, limit, credit-balance),
find how much more each person can spend:
customer-name, limit - credit-balance (credit-info)
Database Systems Concepts 3.43 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Outer Join
· An extension of the join operation that avoids loss of
information.
· Computes the join and then adds tuples from one relation that
do not match tuples in the other relation to the result of the join.
· Uses null values:
­ null signifies that the value is unknown or does not exist.
­ All comparisons involving null are false by definition.
Database Systems Concepts 3.44 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Outer Join ­ Example
· Relation loan
branch-name loan-number amount
Downtown L-170 3000
Redwood L-230 4000
Perryridge L-260 1700
· Relation borrower
customer-name loan-number
Jones L-170
Smith L-230
Hayes L-155
Database Systems Concepts 3.45 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Outer Join ­ Example
· loan 1 Borrower
branch-name loan-number amount customer-name
Downtown L-170 3000 Jones
Redwood L-230 4000 Smith
· loan ­
­1 borrower
branch-name loan-number amount customer-name loan-number
Downtown L-170 3000 Jones L-170
Redwood L-230 4000 Smith L-230
Perryridge L-260 1700 null null
Database Systems Concepts 3.46 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Outer Join ­ Example
· loan 1­
­ Borrower
branch-name loan-number amount customer-name
Downtown L-170 3000 Jones
Redwood L-230 4000 Smith
null L-155 null Hayes
· loan ­
­1­
­ borrower
branch-name loan-number amount customer-name
Downtown L-170 3000 Jones
Redwood L-230 4000 Smith
Perryridge L-260 1700 null
null L-155 null Hayes
Database Systems Concepts 3.47 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Aggregate Functions
· Aggregation operator G takes a collection of values and returns
a single value as a result.
avg: average value
min: minimum value
max:maximum value
sum: sum of values
count: number of values
G1,G2,...,Gn
GF1 A1, F2 A2,..., Fm Am
(E)
­ E is any relational-algebra expression
­ G1, G2, . . . , Gn is a list of attributes on which to group
­ Fi is an aggregate function
­ Ai is an attribute name
Database Systems Concepts 3.48 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Aggregate Function ­ Example
· Relation r:
A B C
  7
  7
  3
  10
· sumC(r) sum-C
27
Database Systems Concepts 3.49 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Aggregate Function ­ Example
· Relation account grouped by branch-name:
branch-name account-number balance
Perryridge A-102 400
Perryridge A-201 900
Brighton A-217 750
Brighton A-215 750
Redwood A-222 700
· branch-name Gsum balance(account)
branch-name sum-balance
Perryridge 1300
Brighton 750
Redwood 700
Database Systems Concepts 3.50 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Modification of the Database
· The content of the database may be modified using the
following operations:
­ Deletion
­ Insertion
­ Updating
· All these operations are expressed using the assignment
operator.
Database Systems Concepts 3.51 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Deletion
· A delete request is expressed similarly to a query, except
instead of displaying tuples to the user, the selected tuples are
removed from the database.
· Can delete only whole tuples; cannot delete values on only
particular attributes.
· A deletion is expressed in relational algebra by:
r  r - E
where r is a relation and E is a relational algebra query.
Database Systems Concepts 3.52 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Deletion Examples
· Delete all account records in the Perryridge branch.
account 
account - branch-name = "Perryridge" (account)
· Delete all loan records with amount in the range 0 to 50.
loan  loan - amount  0 and amount  50 (loan)
· Delete all accounts at branches located in Needham.
r1  branch-city = "Needham" (account 1 branch)
r2  branch-name, account-number, balance (r1)
r3  customer-name, account-number (r2 1 depositor)
account  account - r2
depositor  depositor - r3
Database Systems Concepts 3.53 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Insertion
· To insert data into a relation, we either:
­ specify a tuple to be inserted
­ write a query whose result is a set of tuples to be inserted
· In relational algebra, an insertion is expressed by:
r  r  E
where r is a relation and E is a relational algebra expression.
· The insertion of a single tuple is expressed by letting E be a
constant relation containing one tuple.
Database Systems Concepts 3.54 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Insertion Examples
· Insert information in the database specifying that Smith has
$1200 in account A-973 at the Perryridge branch.
account  account  {("Perryridge", A-973, 1200)}
depositor  depositor  {("Smith", A-973)}
· Provide as a gift for all loan customers in the Perryridge
branch, a $200 savings account. Let the loan number serve as
the account number for the new savings account.
r1  (branch-name = "Perryridge" (borrower 1 loan))
account  account  branch-name, loan-number,200 (r1)
depositor  depositor  customer-name,loan-number (r1)
Database Systems Concepts 3.55 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Updating
· A mechanism to change a value in a tuple without changing all
values in the tuple
· Use the generalized projection operator to do this task
r  F1,F2,...,Fn
(r)
­ Each Fi is either the ith attribute of r, if the ith attribute is
not updated, or, if the attribute is to be updated
­ Fi is an expression, involving only constants and the
attributes of r, which gives the new value for the attribute
Database Systems Concepts 3.56 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Update Examples
· Make interest payments by increasing all balances by 5
percent.
account  BN,AN,BAL  BAL 1.05 (account)
where BAL, BN and AN stand for balance, branch-name
and account-number, respectively.
· Pay all accounts with balances over $10,000
6 percent interest and pay all others 5 percent.
account  BN,AN,BAL  BAL 1.06 (BAL > 10000 (account))
 BN,AN,BAL  BAL 1.05 (BAL  10000 (account))
Database Systems Concepts 3.57 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Views
· In some cases, it is not desirable for all users to see the entire
logical model (i.e., all the actual relations stored in the
database.)
· Consider a person who needs to know a customer's loan
number but has no need to see the loan amount. This person
should see a relation described, in the relational algebra, by
customer-name, loan-number (borrower 1 loan)
· Any relation that is not part of the conceptual model but is
made visible to a user as a "virtual relation" is called a view.
Database Systems Concepts 3.58 Silberschatz, Korth and Sudarshan c 1997
'
&
$

View Definition
· A view is defined using the create view statement which has
the form
create view v as <query expression>
where <query expression> is any legal relational algebra
query expression. The view name is represented by v.
· Once a view is defined, the view name can be used to refer to
the virtual relation that the view generates.
· View definition is not the same as creating a new relation by
evaluating the query epression. Rather, a view definition
causes the saving of an expression to be substituted into
queries using the view.
Database Systems Concepts 3.59 Silberschatz, Korth and Sudarshan c 1997
'
&
$

View Examples
· Consider the view (named all-customer) consisting of branches
and their customers.
create view all-customer as
branch-name, customer-name (depositor 1 account)
 branch-name, customer-name (borrower 1 loan)
· We can find all customers of the Perryridge branch by writing:
customer-name
(branch-name = "Perryridge" (all-customer))
Database Systems Concepts 3.60 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Updates Through Views
· Database modifications expressed as views must be translated
to modifications of the actual relations in the database.
· Consider the person who needs to see all loan data in the loan
relation except amount. The view given to the person,
branch-loan, is defined as:
create view branch-loan as
branch-name, loan-number (loan)
Since we allow a view name to appear wherever a relation
name is allowed, the person may write:
branch-loan  branch-loan  {("Perryridge", L-37)}
Database Systems Concepts 3.61 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Updates Through Views (Cont.)
· The previous insertion must be represented by an insertion
into the actual relation loan from which the view branch-loan is
constructed.
· An insertion into loan requires a value for amount. The
insertion can be dealt with by either
­ rejecting the insertion and returning an error message to
the user
­ inserting a tuple ("Perryridge", L-37, null) into the loan
relation
Database Systems Concepts 3.62 Silberschatz, Korth and Sudarshan c 1997
'
&
$

Views Defined Using Other Views
· One view may be used in the expression defining another view
· A view relation v1 is said to depend directly on a view relation
v2 if v2 is used in the expression defining v1
· A view relation v1 is said to depend on view relation v2 if and
only if there is a path in the dependency graph from v2 to v1.
· A view relation v is said to be recursive if it depends on itself.
Database Systems Concepts 3.63 Silberschatz, Korth and Sudarshan c 1997
'
&
$

View Expansion
· A way to define the meaning of views defined in terms of other
views.
· Let view v1 be defined by an expression e1 that may itself
contain uses of view relations.
· View expansion of an expression repeats the following
replacement step:
repeat
Find any view relation vi in e1
Replace the view relation vi by the expression defining vi
until no more view relations are present in e1
· As long as the view definitions are not recursive, this loop will
terminate.
Database Systems Concepts 3.64 Silberschatz, Korth and Sudarshan c 1997