PPT – DATABASE SYSTEMS - 10p Course No. ?? PowerPoint presentation

About This Presentation

Title:

DATABASE SYSTEMS - 10p Course No. ??

Description:

... Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield) ... SUPPLIES(SNAME, INAME, PRICE) INCLUDES(O#, INAME, QUANTITY) Kjell ... – PowerPoint PPT presentation

Number of Views:48

Avg rating:3.0/5.0

Slides: 87

Provided by: kjello

more less

Transcript and Presenter's Notes

Title: DATABASE SYSTEMS - 10p Course No. ??

1
DATABASE SYSTEMS - 10pCourse No. ??

A second course on development of database
systems
Kjell OrsbornUppsala Database
LaboratoryDepartment of Information Science,
Uppsala University, Uppsala, Sweden

2
Introduction to Relational Model
Elmasri/Navathe ch 7 Lecture 2

Kjell Orsborn
Department of Information Science
Uppsala University, Uppsala, Sweden

3
The Relational Model

The relational model was introduced by E. F. Codd
1970.
Many DBMSs are based on this data model.
It support simple declarative, but yet powerful,
languages for describing operations on data.
Operations in the relational model applies to
relations (tables) and produce new relations.
This means that an operation can be applied to
the result of another operation and that several
different operations can be combined.
Operations are described in an algebraic notation
that is based on relational algebra.

4
Relations as mathematical objects

In set theory, a relation is defined as a subset
of the product set (cartesian produkt) of a
number of domains (value sets).
The product set of the domains D1,D2,...,Dn is
written as D1?D2?.. ? Dn.
D1?D2?... ? Dn constitute the set of all ordered
sets ltv1,v2,...,vngt such that vi belongs to Di
for all i.
If n2, D1T, F and D2P, Q, R one gets the
product setsD1?D2 ltT,Pgt,ltT,Qgt,ltT,Rgt,ltF,Pgt,ltF,
Qgt,ltF,RgtD2?D1 ltP,Tgt,ltP,Fgt,ltQ,Tgt,ltQ,Fgt,ltR,Tgt,lt
R,Fgt
For example, we have the relationsR1 ? D2?D1
R1 ltP,Tgt,ltQ,Tgt,ltR,TgtR2 ? D2?D1 R2
ltP,Tgt,ltP,Fgt
Members of a relation is called tuples. If the
relation is of degree n, the tuples are called
n-tuples.

5
An example relation

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

6
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)

7
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
customer

8
Relations as tables
A relation
An attribute
A tuple
9
First Normal Form

Only simple or atomic values are allowed in the
relational model.
Attributes is not allowed to have composite or
multiple values.
The theory for the relational model is based on
these assumptions which is called
The first normal form assumption

10
Null values

A special value, null or ?, can sometimes be used
as an attribute value.
Every occurence of null is unique. Thus, two
occurences of null is not considered to be equal
even if they are represented by the same symbol.
null is used
when one does not know the actual value of an
attribute.
when a certain attribute does not have a value.
when an attribute is not applicable.
Examples of the use of null are showed later.

11
Keys

Because relations are sets, all tuples in the
relation are different.
There is usually a subset k of the attributes in
a relation R, i.e. k ? R, that has the
characteristic that if the tuplest1, t2 ? R och
t1 ? t2, då gäller att t1k ? t2k (hence the
value of k in t1 ? the value of k in t2)
Every such subset k is called a superkey for R.

12
Keys - continued . . .

A superkey k is minimal if there is no other
superkey k' such that k' ? k.
Every minimal superkey (OBS! ther can be more
than one) is called a candidate key for R.
The candidate key chosen by the database designer
as the key for R is called Rs primary key or
just key.
In addition, term foreign key is used when a
tuple is referenced, from another relation, with
its key.

13
Key examples

Example superkey
customer-name, customer- street and customer-
name are both superkeys of Customer, if no two
customers can possibly have the same name.
Example candidate key
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.

14
Determining keys from E-R types

Strong entity type. The primary key of the entity
type becomes the primary key of the relation.
Weak entity type. The primary key of the relation
consists of the union of the primary key of the
strong entity type and the discriminator of the
weak entity type.
Relationship type. The union of the primary keys
of the related entity types becomes a super key
of the relation.
For binary many-to-many relationship types, above
super key is also the primary key.
For binary many-to-one relationship types, the
primary key of the many entity type becomes the
relations primary key.
For one-to-one relationship types, the relations
primary key can be that of either entity type.

15
Integrity constraintsfor a relational database
schema

1. Domain constraint
attribute values for attribute A shall be atomic
values from dom(A)
2. Key constraint
candidate keys for a relation must be unique
3. Entity integrity constraint
no primary key is allowed to have a null value
4. Referential integrity constraint
a tuple that refers to another tuple in another
relation must refer to an existing tuple
5. Semantic integrity constraint
e.g. an employees total work time per week can
not exceed 40 hours for all projects taken all
together

16
From E-R to relational model

The basic procedure defines a set of relational
schemas that represent entity and relationship
types in the E-R model. This model should further
with integrity constraints.
Primary keys allow entity types and relationship
types to be expressed uniformly as tables which
represent the contents of the database.
A database which conforms to an E-R diagram can
be represented by a collection of tables.
For each entity type and relationship type there
is a unique table which is assigned the name of
the corresponding entity type or relationship
type.
Each table has a number of columns (generally
corresponding to attributes), which have unique
names.
Converting an E-R diagram to a table format is
the basis for deriving a relational database
design from an E-R diagram.

17
Steps in translation from E-R model to
relational model

Translation of entity types and their attributes
Step 1) Entity types
Step 2) Weak entity types
Translation of relationships
Step 3) 1-1 Relationship
Step 4) 1-N Relationship
Step 5) M-N Relationship
Translation of multivalued attributes and
relationships
Step 6) Multivalued attributes
Step 7) Multivalued relationships

18
Translating entity types and their attributes

Step 1 Entity types - a strong entity type
reduces to a table with the same attributes.
Key attributes (primary key - pk) is made the
primary key column(s) for the table. Each
attribute gets their own column.
Composite attributes are normally represented by
their simple components.
Example customer schema and table
Customer(social-security, customer-name,
c-street, c-city)

pk
19
Translating entity types cont. . .

Step 2 Weak entity types - a weak entity type
becomes a table that includes a column for the
primary key of the identifying strong entity type
.

1
N
R
pk
k
E1
a1
a2
20
Translating entity types cont. . .

The table corresponding to a relationship type
linking a weak entity type to its identifying
strong entity type is redundant.
Example of the payment schema and table
The payment table already contains the
information that would appear in the loan-payment
table (i.e., the columns loan-number and
payment-no).
Payment(loan-number, payment-no, pay-date,
amount)

21
Translating relationship types

Step 3 1-1 Relationship types
The foreign key column (fk) is a copy of the
other entitys primary key column (pk). The
values in a fk-column point to unique row in the
other table, and thus implement the relationship.

1
1
R
pk1
pk2
E1
E2
a2
a1
pk1
a1
pk2
a2
f k1
Alt 1
pk2
a2
pk1
a1
f k2
Alt 2
22
Translating 1-1 relationship types cont. . .
E1
R
E2
pk1
a1
f k1
f k2
pk2
a2
Alt 3
E1
E2
pk1
a1
pk2
a2
Alt 4
23
Translating relationship . . . cont. . .

Step 4 1-N Relationship types
Include the primary key of the 1-side as a
foreign key on the N-side, (i.e. the foreign
key column is placed on the entity on the
N-side).
Alternatively, an extra table (R) is created
whose primary key is composed of the two foreign
keys.

1
N
R
pk1
pk2
E1
E2
a2
a1
pk1
a1
pk2
a2
f k1
Alt 1
pk1
a1
f k1
f k2
pk2
a2
Alt 2
24
Translating relationship . . . cont. . .

Step 5 M-N Relationship types
Always a separate table with columns for the
primary keys of the two participating entity
types, and any descriptive attributes of the
relationship type.

M
N
R
pk1
pk2
E1
E2
a2
a1
pk1
a1
f k1
f k2
pk2
a2
25
Translating relationship . . . cont. . .

Step 6 Multivalued attributes
A separate table is created for the multivalued
attribute. Its primary key is composed of the
owning entitys primary key, and the attribute
value itself.

a
pk
E
mva
E
E-MVA
pk
a
pk
mva
26
Translating relationship . . . cont. . .

Step 7 Multivalued relationship types
First try to remove multivalued relationships on
the E-R model level by model transformation.
A separate table is created, with foreign keys to
all tables that are included in the relationship.
Its primary key is composed of all foreign keys.

N
N
R
pk1
pk2
E1
E2
N
a
R
pk3
E3
f k1
f k2
f k3
a
27
Translating relationship . . . cont. . .

Step 7 Multivalued relationship types continued
In the case where R is 1-N-N, the primary key on
R shall be set on the fk for the table with
cardinality 1.

1
N
R
pk1
pk2
E1
E2
N
a
pk3
E3
R
f k1
f k2
f k3
a
28
Summary

Entity types and their attributes
Step 1) Entity types
Each entity gets a corresponding table, with the
primary key column set to its key attribute.
Step 2) Weak entity types
The primary key of a weak entity type table has
the primary key of the owner table as a
component.
Relationships
Step 3) 1-1 Relationship
4 alternatives fk in E1 or E2, separate R table,
common table for E1 E2
Step 4) 1-N Relationship
fk i entity on the N-side, separate R table
Step 5) M-N Relationship
separate R table

29
Summary cont. . .

Multivalued attributes and relationships
Step 6) Multivalued attributes
Separate table for the attribute with its pk
composed of the owner pk and the value column.
Step 7) Multivalued relationships
Separate R table. N-N-N pk composed of all fks.
1-N-N pk is fk to the E1-table.

30
Example E-R to relational model translation
31
Relational schemas for the example

Schemas for the entity types in the example above
EMP(ENAME, SALARY, DEPT)
DEPTS(DNAME, DEPT, MGR)
SUPPLIERS(SNAME, SADDR)
ITEMS(INAME, ITEM, DNAME)
ORDERS(O, DATE, CUST)
CUSTOMERS(CNAME, CADDR, BALANCE)
Schemas for relationship types (MN)
SUPPLIES(SNAME, INAME, PRICE)
INCLUDES(O, INAME, QUANTITY)

32
Short summary E-R -gt R
33
Introduction to Relational AlgebraElmasri/Navath
e ch 7 Lecture 2

Kjell Orsborn
Department of Information Science
Uppsala University, Uppsala, Sweden

34
Query languages

Languages where users can express what
information to retrieve from the database.
Categories of query languages
Procedural
Non-procedural (declarative)
Formal (pure) languages
Relational algebra
Relational calculus
Tuple-relational calculus
Domain-relational calculus
Formal languages form underlying basis of query
languages that people use.

35
Relational algebra

Relational algebra is a procedural langaue
Operations in relational algebra takes two or
more relations as arguments and return a new
relation.
Relational algebraic operations
Operations from set theory
Union, Intersection, Difference, Cartesian
product
Operations specifically introduced for the
relational data model
Select, Project, Join
It have been shown that the select, project,
union, difference, and cartesian product
operations form a complete set. That is any other
relational algebra operation can be expressed in
these.

36
Operations from set theory

Relations are required to be union compatible to
be able to take part in the union, intersection
and difference operations.
Two relations R1 and R2 is said to be
union-compatible ifR1 ? D1xD2x...xDn andR2 ?
D1xD2x...xDni.e. if they have the same degree
and the same domains.

37
Union operation

The union of two union-compatible relations R and
S is the set of all tuples that either occur in
R, S, or in both.
Notation R ? S
Defined as R ? S t t ? R or t ? S
For example

R
S
?

A
B
A
B
A
B
a b b
1 2 1
a b
2 3
a a b b
1 2 1 3
38
Difference operation

The difference between two union-compatible sets
R and S is the set of all tuples that occur in R
but not in S.
Notation R ? S
Defined as R ? S t t ? R and t ? S
For example

R
S
?

A
B
A
B
A
B
a b b
1 2 1
a b
2 3
a b
1 1
39
Intersection

The intersection of two union-compatible sets R
and S, is the set of all tuples that occur in
both R and S.
Notation R ? S
Defined as R ? S t t ? R and t ? S
For example

R
S
?

A
B
A
B
A
B
a a b
1 2 1
a b
2 3
a
2
40
Cartesian product

Let R and S be relations with k1 and k2 arities
resp. The cartesian product of R and S is the set
of all possible k1k2 tuples where the first k1
components constitute a tuple in R and the last
k2 components a tuple in S.
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. (i.e. R ? S ?). If attributes of r(
R) and s( S) are not disjoint, then renaming must
be used.

41
Cartesian product example
?

A
B
C
D
A
B
C
D
a b
1 2
a b b c
5 5 6 5
a a a a b b b b
1 1 1 1 2 2 2 2
a b b c a b b c
5 5 6 5 5 5 6 5
42
Selection operation

The selection operator, ?, selects a specific set
of tuples from a relation according to a
selection condition P.
Notation ?p( R)
Defined as ?p( R) t t ? R and P( t)
Where P is a logical expression built up by
attribute names
aritmetic operators , lt, gt, , , and
logical operators (connectors) ? (and), ? (or),
(not).
?V(R) is the set of tuples in R that fulfill the
condition P. Example ?SALARYgt30000(EMPLOYEE)

43
Selection example

R
A
B
C
D
a a b b
a b b b
1 5 2 4
7 7 3 9
?AB ? D gt 5 (R)

A
B
C
D
a b
a b
1 4
7 9
44
Projection operation

The projection operator, ?, picks out (or
projects) listed columns from a relation and
creates a new relation consisting of these
columns.
Notation ?A1,A2,...,Ak (R)where A1, A2 are
attribute names and R is a relation name.
The result is a new relation of k columns.
Duplicate rows removed from result, since
relations are sets. Exemple ?LNAME,FNAME,SALARY
(EMPLOYEE)

45
Projection example

R
A
B
C
a a b b
1 2 3 4
1 1 1 2
A
C
A
C
?AC (R)

a a b b
1 1 1 2
a b b
1 1 2
46
Join operator

The join operator, ? (almost), creates a new
relation by joining related tuples from two
relations.
Notation R ?C ?SC is the join condition which
has the form Ar ? As , where ? is one of , lt,
gt, , , ?. Several terms can be connected as C1
?C2 ?...Ck.
A join operation with this kind of general join
condition is called Theta join.

47
Example Theta join
R ?AltD ?S
R
S
?AltD

A
B
C
B
A
B
C
B
C
D
C
D
1 1 1 6 9
2 2 2 7 7
3 3 3 8 8
2 2 2 7 7
1 6 9
2 7 7
3 8 8
2 7 7
3 3 8
4 5 9
3 3 3 8 8
4 5 9 9 9
48
Equijoin

The same as join but it is required that
attribute Ar and attribute As should have the
same value.
Notation R ?C ?SC is the join condition which
has the form Ar ? As. Several terms can be
connected as C1 ?C2 ?...Ck.

49
Example Equijoin
R ?BC ?S
R
S
?BC

A
B
C
D
A
B
C
D
E
E
a a
2 4
2 4
d d
a a
2 4
2 4 9
d d d
e e e
e e
50
Natural join

Natural join is equivalent with the application
of join to R and S with the equality condition Ar
As (i.e. an equijoin) and then removing the
redundant column As in the result.
Notation R Ar,As SAr,As are attribute pairs
that should fulfil the join condi-tion which has
the form Ar ? As. Several terms can be connected
as C1 ?C2 ?...Ck.

51
Example Natural join
R ?BC ?S
R
S
?BC

A
B
D
A
B
C
D
E
E
a a
2 4
d d
a a
2 4
2 4 9
d d d
e e e
e e
52
Semijoin

The Semijoin operation is an equijoin betweeen R
and S followed by a projection of Rs attribute.
Notation R ?semi S ?R (R ??S)
That is, the answer of the query Which tuples
in R are joinable with S?

53
Example Semijoin
R ?semi?S
R
S
?semi

A
B
C
A
B
C
B
C
D
1 4 3
2 2 1
3 3 4
1 4 2 3
2 2 2 1
3 3 6 4
2 2 1
3 3 4
4 5 2
54
Composition of operations

Expressions can be built by composing multiple
operations
Example ?AC (R ? S)

?

A
B
C
D
A
B
C
D
R ? S

a b
1 2
a b b c
5 5 6 5
a a a a b b b b
1 1 1 1 2 2 2 2
a b b c a b b c
5 5 6 5 5 5 6 5
?AC (R ? S)

A
B
C
D
a b b
1 2 2
a b b
5 5 6
55
Additional relational operations

Assignment and Rename
Division
Outer join and outer union
Aggregate functions (presented together with SQL)
Update operations (presented together with SQL)
(not part of pure query language)

56
Assignment operation

The assignment operation (??) makes it possible
to assign the result of an expression to a
temporary relation variable.
Example
temp ??dno 5(EMPLOYEE)
result ?fname,lname,salary (temp)
The result to the right of the ???is assigned to
the relation variable on the left of the ?.
The variable may use variable in subsequent
expressions.

57
Renaming relations and attribute

The assignment operation can also be used to
rename relations and attributes.
Example NEWEMP ??dno 5(EMPLOYEE)R(FIRSTNAME,L
ASTNAME,SALARY) ?fname,lname,salary (NEWEMP)

58
Division operation

Suited to queries that include the phrase for
all.
Let R and S be relations on schemas R and S
respectively, where R ( A1,...,A m
,B1,...,Bn) S ( B1,...,Bn)
The result of R S is a relation on schemaR - S
( A1 ,...,A m)
R S t t ? ?R-S (R)? u ? S ? tu ? R

59
Example Division operation
R ?S
R
S

A
A
B
B
a e
a a a b c d d d d e e
1 2 3 1 1 1 3 4 6 1 2
1 2
60
Outer join/union operation

An extension of the avoids loss of information.
Computes the join/union and then adds tuples from
one relation that do not match tuples in the
other relation to the result of the join.
Fills out with null values
null signifies that the value is unknown or does
not exist.
All comparisons involving null are false by
definition.

61
Example Outer join

Relation loan
Relation borrower

branch-name
loan-number
amount
Downtown Redwood Perryridge
1-170 L-230 L-260
3000 4000 1700
customer-name
loan-number
Jones Smith Hayes
1-170 L-230 L-155
62
Example Outer join cont...

loan borrower (natural join)
loan ?left borrower (left outer join)

branch-name
loan-number
amount
customer-name
Downtown Redwood
1-170 L-230
3000 4000
Jones Smith
customer-name
loan-number
branch-name
loan-number
amount
Jones Smith null
1-170 L-230 null
Downtown Redwood Perryridge
1-170 L-230 L-260
3000 4000 1700
63
Example Outer join cont...

loan ?right borrower (right outer join)
loan ?full borrower (full outer join)

customer-name
branch-name
loan-number
amount
Jones Smith Hayes
Downtown Redwood null
L-170 L-230 L-155
3000 4000 null
customer-name
branch-name
loan-number
amount
Jones Smith null Hayes
Downtown Redwood Perryridge null
L-170 L-230 L-260 L-155
3000 4000 1700 null
64
Aggregation operations

Presented together with SQL later
Examples of aggregation operations
avg
min
max
sum
count

65
Update operations

Presented together with SQL later
Operations for database updates are normally part
of the DML
insert (of new tuples)
update (of attribute values)
delete (of tuples)
Can be expressed by means of the assignment
operator

66
Example DB schema

In the following example we will use a database
with the following relation schemas
emps(ename, salary, dept)
depts(dname, dept, mgr)
suppliers(sname, addr)
items(iname, item, dept)
orders(o, date, cust)
customers(cname, addr, balance)
supplies(sname, iname, price)
includes(o, item, quantity)

67
Relation algebra as a query language

Relational schema supplies(sname, iname, price)
What is the names of the suppliers that supply
cheese?
?sname(?iname'CHEESE'(SUPPLIES))
What is the name and price of the items that
cost less than 5 and that are supplied by
WALMART
?iname,price(?sname'WALMART' ? price lt 5
(SUPPLIES))

68
Introduction to Relational CalculusElmasri/Navat
he ch 9 Lecture 2

Kjell Orsborn
Department of Information Science
Uppsala University, Uppsala, Sweden

69
Relation calculus?

Relation calculus is, in similarity with relation
algebra, a formal query language for the
relational data model.
Relation calculus is based on one branch of
mathematical logic that is called predicate
logic.
Relation calculus is divided into two
subcategories
Tuple calculus (e.g. QUEL and SQL is related with
tuple calculus)
Domain calculus (e.g. QBE is related with domain
calculus)
Relation calculus is declarative (or
non-procedural) in contrast to relation algebra
which is a procedural langauge.

70
Comparison of relation calculus and relation
algebra

Relation calculus and relation algebra have an
identical basic level of expressability.
A relational query language with a corresponding
expressability is called relational complete.
Relation algebra is oriented towards relations
but relation calculus is oriented towards tuples
(tuple calculus) or domain values of the
attributes (domain calculus).

71
Tuple relational calculus

A nonprocedural query language, where each query
is of the formt 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.

72
Predicate calculus formula

1. Set of attributes and constants
2. Set of comparison operators (e.g., lt,, ,
?, gt, )
3. Set of connectives and (?), or (?), not ()
4. Logical implication (?) x ? y, if x is
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

73
Banking example

branch (branch-name,branch-city,assets)
customer (customer-name,customer-street,customer-c
ity)
account (branch-name,account-number,balance)
loan (branch-name,loan-number,amount)
depositor (customer-name,account-number)
borrower (customer-name,loan-number)

74
Example queries of t. c.(Elmasri/Navathe syntax)

Find the branch-name, loan-number, and amount for
loans of over 1200.t loan(t) ? t.amount ?
1200
Find the loan number for each loan of an amount
greater than 1200.t.loan-number loan(t) ?
t.amount gt 1200)

75
Example queries of t. c.(Elmasri/Navathe syntax)

Find the names of all customers who have a loan
and an account at the bank.t.customer-name
borrower(t) ? ((?d) depositor(d) ?
t.customer-name d.customer-name)
Find the names of all customers having a loan, an
account, or both at the bank.t.customer-name
((?b) borrower(b) ? t.customer-nameb.customer-
name) ? ((?d) depositor(d) ? t.customer-named.c
ustomer-name)

76
Example queries of t. c.(Elmasri/Navathe syntax
ver.2)

Find the names of all customers who have a loan
and an account at the bank. t.customer-name
customer(t) ? (((?b) borrower(b) ?
t.customer-name b.customer-name) ? ((?d)
depositor(d) ? t.customer-name
d.customer-name))
Find the names of all customers having a loan, an
account, or both at the bank. t.customer-name
customer(t) ? (((?b) borrower(b) ?
t.customer-name b.customer-name) ? ((?d)
depositor(d) ? t.customer-name
d.customer-name))

77
Example queries of t. c.(Elmasri/Navathe syntax)

Find the names of all customers who have an
account at all branches located in Brooklyn.
t customer(t) ? (?b) (branch(b)
??b.branch-city Brooklyn) ? (?a)
(account(a) ? a.branch-name b.branch-name)
? (?d) (depositor(d) ? d.customer-name
t.customer-name ? d.account-number
a.account-number)

78
Example queries of t. c.(Silberschatz et al
syntax)

Find the branch-name, loan-number, and amount for
loans of over 1200.t t ? loan ? t amount gt
1200
Find the loan number for each loan of an amount
greater than 1200.t ? s ? loan
(tloan-number sloan-number ?
samount gt 1200)
Notice that a relation on schema customer-name
is implicitly defined by the query.

79
Example queries of t. c.(Silberschatz et al
syntax)

Find the names of all customers having a loan, an
account, or both at the bank.t ?s ? borrower(
tcustomer-name scustomer-name) ? ?u ?
depositor( tcustomer-name ucustomer-name)
Find the names of all customers who have a loan
and an account at the bank.t ?s ? borrower(
tcustomer-name scustomer-name) ? ?u ?
depositor( tcustomer-name ucustomer-name)

80
Example queries of t. c.(Silberschatz et al
syntax)

Find the names of all customers having a loan at
the Perryridge branch.t ?s ? borrower(
tcustomer-name scustomer-name ? ?u ?
loan( ubranch-name Perryridge ?
uloan-number sloan-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(
tcustomer-name scustomer-name) ? ?u ?
loan( ubranch-name Perryridge ?
uloan-number sloan-number) ? ??v ?
depositor(vcustomer-name tcustomer-name

81
Example queries of t. c.(Silberschatz et al
syntax)

Find the names of all customers having a loan
from the Perryridge branch and the cities they
live in.t ?s ? loan( sbranch-name
Perryridge ? ?u ? borrower( uloan-number
sloan-number ? tcustomer-name
ucustomer-name ? ?v ? customer(
ucustomer-name vcustomer-name ? t
customer-city vcustomer-city)))

82
Example queries of t. c.(Silberschatz et al
syntax)

Find the names of all customers who have an
account at all branches located in Brooklyn.t
?s ? branch(sbranch-city Brooklyn ) ?u ?
account( sbranch-name ubranch-name ? ?s
? depositor( tcustomer-name
scustomer-name ? saccount-number
uaccount-number)))

83
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.
As for instance t ??loan(t))
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.

84
Domain relational calculus

A nonprocedural query language equivalent in
power to the tuple relational calculus.
Each query is an expression of the form
ltx1, x2, ..., xngt P(x1, x2, ..., xn)
x1, x2, ..., xn represent domain variables
P represents a formula similar to that of the
predicate calculus

85
Example queries of d. c.(Elmasri/Navathe syntax)

Find the branch-name, loan-number, and amount for
loans of over 1200. b,l,a loan(b,l,a) ? a gt
1200
Find the names of all customers who have a loan
of over 1200. c (?b) (?l) (?a)
(borrower(c,l) ? loan(b,l,a) ? a gt 1200)
Find the names of all customers who have a loan
from the Perryridge branch and the loan amount.
c,a (?l) (borrower(c,l) ? (?b) (loan(b,l,a)
? b Perryridge))

86
Example queries of d. c.(Silberschatz et al
syntax)

Find the branch-name, loan-number, and amount for
loans of over 1200. ltb,l,agt ltb,l,agt ? loan ?
a gt 1200
Find the names of all customers who have a loan
of over 1200. ltcgt ?b,l,a (ltc, lgt ? borrower
? ltb,l,agt ? loan ? a gt 1200)
Find the names of all customers who have a loan
from the Perryridge branch and the loan amount.
ltc,agt ?l (ltc,lgt ? borrower ? ?b (ltb,l,agt
? loan ? b Perryridge))

87
Example queries of d. c.(Silberschatz et al
syntax)

Find the names of all customers having a loan, an
account, or both at the Perryridge branch. ltcgt
?l (ltc,lgt ? borrower ? ?b,a(ltb,l,agt ?
loan ? b Perryridge)) ? ?a(ltc,agt ?
depositor ? ?b,n(ltb,a,ngt ? account ? b
Perryridge))
Find the names of all customers who have an
account at all branches located in Brooklyn.
ltcgt ?x,y,z (ltx,y,zgt ? branch ? y Brooklyn)
? ?a,b (ltx,a,bgt ? account ? ltc,agt ?
depositor)

88
Safety of expressions

ltx1, x2, ..., xngt 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).

89
The QBE query language(Query-By-Example)

QBE is a query language based on domain calculus.
Develped by IBMs research center at Yorktown
Heights for DB2 and others.
Interactive and graphically oriented
The user asks for a set of templates
The system shows the templates on screen.
The user fills in the templates with information
that retrieved tuples should match and marks the
attributes that should be showed in the result.
The system fills the templates with the
information that was sought.

90
Templates - example
ORDERS
O
DATE
CUST
INCLUDES
O
ITEM
QUANTITY
SUPPLIES
NAME
ITEM
PRICE
91
A query example

Print the name of suppliers that supply items
to Steve Stone

ORDERS
O
DATE
CUST
_x
Steve Stone
INCLUDES
O
ITEM
QUANTITY
_x
_item
SUPPLIES
NAME
ITEM
PRICE
_item
P.
92
Another query example

Print customer name, address, orderno and date
for all orders

ORDERS
O
DATE
CUST
_x
_cust
_date
CUSTOMERS
NAME
CADDR
BALANCE
_cust
_address
Alt. 1
P._x
P._cust
P._date
P._address
Alt. 2
_x
_cust
_date
_address
P.
93
How does QBE work

The system creates a tupel variable for each row
in every template.
E.g., in the previous example two tupel variables
t1 (for ORDERS) and t2 (for CUSTOMERS) are
created.
For k nos of variables k nested loops are created
where each loop iterates over tuples in
respective relation. When all tupel variables
have gotten their values, it is checked if the
domain variables can be assigned consistent
values.
E.g. each time t1CUST t2NAME are fulfilled
we have a possible value for _cust.
Each time the conditions are fulfilled the
commands that have been selected are executed
(e.g. P.).