RELATIONAL ALGEBRA

1
RELATIONAL ALGEBRA
Lecture 7

• Prof. Sin-Min LEE
• Department of Computer Science

2

• Terminology
• table (relation)
• row (tuple)
• formally, a relation is a set of ordered
  n-tuples.
• Domain set of data values from which an
  attribute value can be drawn.
• Eg.
• set of department names
• set of legal salaries
• set of possible employee birth dates

3

• If D1, D2, D3. Dn are the domains of a relation
  R.
• Then, R ? D1 x D2x D3 x ..x Dn.

4
What are the query languages?

• It is an abstract language. We use it to express
  the set of operations that any relational query
  language must perform.
• Two types of operations
• 1.set-theoretic operations tables are
  essentially sets of rows
• 2.native relational operations focus on the
  structure of the rows Query languages are
  specialized languages for asking questions,or
  queries,that involve the data in database.

5
Database Scheme

• A relational database scheme, or schema,
  corresponds to a set of table definitions.
• Eg product(p_id, name, category, description)
• supply(p_id, s_id, qnty_per_month)
• supplier(s_id, name, address, ph)
• remember the difference between a DB instance
  and a DB scheme.

6
Keys

• The super key, candidate key and primary key
  notations presented for ER model apply for
  relational model also.

7
Query languages

• procedural vs. non-procedural
• commercial languages have some of both
• we will study
• relational algebra (which is procedural, i.e.
  tells you how to process a query)
• relational calculus (which is non-procedural i.e.
  tells what you want)

8
(No Transcript)
9
Relational Algebra

• Fundamental operators
• select s
• project p
• cartesian product ?
• union ?
• set difference -
• Other operators
• natural join JOIN (butterfly
  symbol)
• set intersection ?
• division ?

10

• A Simple DB
• account ac owner ss balance
• 1 bob 123 1000
• 2 sue 456 2000
• 3 jane 789 3000
• transaction t ac type amount outcome
  date
• 1 1 W 1500
  bounced 5/1/98
• 2 2 D 1000
  ok 5/2/98
• 3 1 W 100
  ok 5/4/98
• 4 3 D 500
  ok 5/7/98
• 5 2 W 200
  ok 5/9/98

account
had
transaction
11

• Select
• eg s balancegt1500 account
• result ac owner ss balance
• 2 sue 456 2000
• 3 jane 789 3000
• Project
• eg p owner, ss account
• result owner ss
• bob 123
• sue 456
• jane 789

12
(No Transcript)
13

• Cartesian product
• eg account ? transaction
• this will have 15 rows like the ones shown below
• ac owner ss balance t type amount
  outcome date
• 1 bob 123 1000 1 W 1500
  bounced 5/1/98
• 2 sue 456 2000 2 D 1000
  ok 5/2/98
• Composing operations
• eg show all transactions done by account owner
  Bob.
• s account.ano transaction.ano((s ownerBob
  account) ? transaction)

14

• Natural Join
• - combines s, p, ?
• - very commonly used
• Natural Join forms the cross product of its two
  arguments, does a selection to enforce equality
  of columns with the same name and removes
  duplicate columns.
• Eg show all transactions done by account owner
  Bob
• s ownerBob (account JOIN transaction)

15
Rename operation

• What if you need to access the same relation
  twice in a query?
• eg. person(ss, name, mother_ss, father_ss)
• Find the name of Bobs mother needs the
  person table to be accessed twice.
• The operation ? x (r) evaluates to a second
  logical copy of relation r renamed to x.

16
Rename operation (contd)

• eg
• p mother.name (
• (? mother (person))
• JOIN mother.ss person.mother_ss
• (s nameBob (person)))

17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
(No Transcript)
21
Additional Operations

• Additional Operations are those that can be
  expressed in terms of other operations.
• Set Intersection
• r ? s r-(r-s)
• eg. r a b s a b r
  ? s a b
• 1 a 1 a
  1 a
• 2 b 2 c
  3 d
• 3 d 3 d

r
s
22
Additional Operations(cntd.)

• Division ?
• useful for for all queries
• Definition Let r(R) and s(S), where R S are
  sets of attributes, be relations, where S is a
  subset of R. The relation r ? s has scheme R-S.
  The tuples in r ? s consist of the R-S part of
  the tuples of r such that some tuple tr in r with
  the those R-S attribute values matches every
  tuple in s.
• Can also be defined in terms of relational
  algebra.

23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
(No Transcript)
30
(No Transcript)
31
(No Transcript)
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
(No Transcript)
37
(No Transcript)
38
(No Transcript)
39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43
(No Transcript)
44
(No Transcript)
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
(No Transcript)
50
(No Transcript)
51
(No Transcript)
52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
(No Transcript)
56
(No Transcript)
57
Additional Operations(cntd.)

• Assignment operation
• Sometimes it is convenient to write a relational
  algebra expression as a sequence of steps rather
  than one large expression. To do this, you can
  use assignment
• relname expression
• eg.
• temp p pno (part)
• bigsuppliers supply ? temp