RELATIONAL ALGEBRA and Computer Assignment 1

1
RELATIONAL ALGEBRA and Computer Assignment 1
Lecture 3
CS157B

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

2
Codds Relational Algebra

• A set of mathematical operators that compose,
  modify, and combine tuples within different
  relations
• Relational algebra operations operate on
  relations and produce relations (closure)
• f Relation ? Relation f Relation x Relation ?
  Relation

3
A Set of Logical Operations The Relational
Algebra

• Six basic operations
• Projection ?? (R)
• Selection ?? (R)
• Union R1UR2
• Difference R1 R2
• Product R1 X R2
• (Rename) ??-gtb (R)
• And some other useful ones
• Join R1 ?? R2
• Semijoin R1 ?? R2
• Intersection R1 Å R2
• Division R1 R2

4
(No Transcript)
5
(No Transcript)
6
Data Instance for Operator Examples
STUDENT
COURSE
Takes
cid subj sem
550-0105 DB F05
700-1005 AI S05
501-0105 Arch F05
sid name
1 Jill
2 Qun
3 Nitin
sid exp-grade cid
1 A 550-0105
1 A 700-1005
3 C 501-0105
PROFESSOR
Teaches
fid cid
1 550-0105
2 700-1005
8 501-0105
fid name
1 Ives
2 Saul
8 Roth
7
Rename, ?a?b

• The rename operator can be expressed several
  ways
• The book has a very odd definition thats not
  algebraic
• An alternate definition
• ?a?b(x) Takes the relation with schema
  ? Returns a relation with the attribute list ?
• Rename isnt all that useful, except if you join
  a relation with itself
• Why would it be useful here?

8
Deriving Intersection

• Intersection as with set operations, derivable
  from difference

A Å B
(A B) (A B) (B A) A (A B)
A-B
B-A
A B
9
Division

• A somewhat messy operation that can be expressed
  in terms of the operations we have already
  defined
• Used to express queries such as The fid's of
  faculty who have taught all subjects
• Paraphrased The fids of professors for which
  there does not exist a subject that they havent
  taught

10
Division Using Our Existing Operators

• All possible teaching assignments Allpairs
• NotTaught, all (fid,subj) pairs for which
  professor fid has not taught subj
• Answer is all faculty not in NotTaught

?fid,subj (PROFESSOR x ?subj(COURSE))
Allpairs - ?fid,subj(Teaches ? COURSE)
?fid(PROFESSOR) - ?fid(NotTaught)
?fid(PROFESSOR) - ?fid(
?fid,subj (PROFESSOR x ?subj(COURSE)) -
?fid,subj(Teaches ? COURSE))
11
Division R1 R2

• Requirement schema(R1) schema(R2)
• Result schema schema(R1) schema(R2)
• Professors who have taught all courses
• What about Courses that have been taught by all
  faculty?

?fid (?fid,subj(Teaches ? COURSE) ?subj(COURSE))
12
DIVISION

• - The division operator is used for queries which
  involve the all qualifier such as Find the
  names of sailors who have reserved all boats.
• - The division operator is a bit tricky to
  explain, and perhaps best approached through
  examples as will be done here.
• Cartesian Product (R1 R2) combines two
  relations by concatenating their tuples together,
  evaluating all possible combinations. If the name
  of a column is identical for two relations, this
  ambiguity is resolved by attaching the name of
  each relation to a column. e.g., Emp Dept
• (SS, name, age, salary, Emp.dno, Dept.dno,
  dname, floor, mgrSS)
• If t(Emp) and t(Dept) is the cardinality of the
  Employee and Dept relations respectively, then
  the cardinality of Emp Dept is t(Emp)
  t(Dept)

13
(No Transcript)
14
(No Transcript)
15
(No Transcript)
16
EXAMPLES OF DIVISION

17
DIVISION

• Interpretation of the division operation A/B
• - Divide the attributes of A into 2 sets A1 and
  A2.
• - Divide the attributes of B into 2 sets B2 and
  B3.
• - Where the sets A2 and B2 have the same
  attributes.
• - For each set of values in B2
• - Search in A2 for the sets of rows (having the
  same A1 values) whose A2 values (taken together)
  form a set which is the same as the set of B2s.
• - For all the set of rows in A which satisfy the
  above search, pick out their A1 values and put
  them in the answer.

18
DIVISION

• Example Find the names of sailors who have
  reserved all boats
• (1) A ?sid,bid(Reserves). A1 ?sid(Reserves)
  A2 ?bid(Reserves)
• (2) B2 ?bid(Boats) B3 is the rest of B.
• Thus, B2 101, 102, 103, 104
• (3) Find the rows of A such that their A.sid is
  the same and their combined A.bid is the set B2.
• Thus we find A1 22
• (4) Get the set of A2 corresponding to A1 A2
  Dustin

19
FORMAL DEFINITION OF DIVISION

• The formal definition of division is as follows
• A/B ?x(A) - ?x((?x(A) ? B) A)

20
(No Transcript)
21
CARTESIAN PRODUCT (Cont)

• Example
• Emp table
• Dept table

SS
Name
age
salary
dno
345 John Doe 23 25,000 1
943 Jane Java 25 28,000 2
876 Joe SQL 22 32,000 1
dno
dname
floor
mgrSS
1 Toy 1 345
2 Shoe 2 943
22
CARTESIAN PRODUCT (Cont)

• Cartesian product of Emp and Dept Emp Dept

SS
Name
age
salary
Emp.dno
Dept.dno
dname
floor
mgrSS
345 John Doe 23 25,000 1 1 Toy 1 345
943 Jane Java 25 28,000 2 1 Toy 1 345
876 Joe SQL 22 32,000 1 1 Toy 1 345
345 John Doe 23 25,000 1 2 Shoe 2 943
943 Jane Java 25 28,000 2 2 Shoe 2 943
876 Joe SQL 22 32,000 1 2 Shoe 2 943
23
CARTESIAN PRODUCT

• Example retrieve the name of employees that
  work in the toy department

24
CARTESIAN PRODUCT

• Example retrieve the name of employees that
  work in the toy department
• ?name(?Emp.dnoDept.dno(Emp ?dnametoy(Dept)))

25
CARTESIAN PRODUCT (Cont)

• ?name(?dnametoy (? Emp.dnoDept.dno(Emp
  Dept)))

SS
Name
age
salary
Emp.dno
Dept.dno
dname
floor
mgrSS
345 John Doe 23 25,000 1 1 Toy 1 345
943 Jane Java 25 28,000 2 1 Toy 1 345
876 Joe SQL 22 32,000 1 1 Toy 1 345
345 John Doe 23 25,000 1 2 Shoe 2 943
943 Jane Java 25 28,000 2 2 Shoe 2 943
876 Joe SQL 22 32,000 1 2 Shoe 2 943
26
CARTESIAN PRODUCT (Cont)

• ?name(?dnametoy (? Emp.dnoDept.dno(Emp
  Dept)))

SS
Name
age
salary
Emp.dno
Dept.dno
dname
floor
mgrSS
345 John Doe 23 25,000 1 1 Toy 1 345
876 Joe SQL 22 32,000 1 1 Toy 1 345
943 Jane Java 25 28,000 2 2 Shoe 2 943
27
CARTESIAN PRODUCT (Cont)

• ?name(?dnametoy (? Emp.dnoDept.dno(Emp
  Dept)))

SS
Name
age
salary
Emp.dno
Dept.dno
dname
floor
mgrSS
345 John Doe 23 25,000 1 1 Toy 1 345
876 Joe SQL 22 32,000 1 1 Toy 1 345
28
CARTESIAN PRODUCT (Cont)

• ?name(?dnametoy (? Emp.dnoDept.dno(Emp
  Dept)))

Name
John Doe
Joe SQL
29
(No Transcript)
30
(No Transcript)
31
EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOIN

• Equality join connects tuples from two relations
  that match on certain attributes. The specified
  joining columns are kept in the resulting
  relation.
• ?name(?dnametoy(Emp Dept)))
• Natural join connects tuples from two relations
  that match on the specified common attributes
• ?name(?dnametoy(Emp Dept)))
• How is an equality join between Emp and Dept
  using dno different than a natural join between
  Emp and Dept using dno?
• Equality join SS, name, age, salary, Emp.dno,
  Dept.dno,
• Natural join SS, name, age, salary, dno,
  dname,
• Join is similar to equality join using different
  comparison operators
• A S op , ?, , , lt, gt
• att op att

(dno)
(dno)
32
EXAMPLE JOIN
SS Name Age Salary dno
1 Joe 24 20000 2
2 Mary 20 25000 1
3 Bob 22 27000 1
4 Kathy 30 30000 2
5 Shideh 4 4000 1
dno dname floor mgrss
1 Toy 1 5
2 Shoe 2 1

• Equality Join, (Emp Dept)))

Dept
EMP
SS Name Age Salary EMP.dno Dept.dno dname floor mgrss
1 Joe 24 20000 2 2 Shoe 2 1
2 Mary 20 25000 1 1 Toy 1 5
3 Bob 22 27000 1 1 Toy 1 5
4 Kathy 30 30000 2 2 Shoe 2 1
5 Shideh 4 4000 1 1 Toy 1 5
(dno)
33
EXAMPLE JOIN
SS Name Age Salary dno
1 Joe 24 20000 2
2 Mary 20 25000 1
3 Bob 22 27000 1
4 Kathy 30 30000 2
5 Shideh 4 4000 1
dno dname floor mgrss
1 Toy 1 5
2 Shoe 2 1

• Natural Join, (Emp Dept)))

Dept
EMP
SS Name Age Salary dno dname floor mgrss
1 Joe 24 20000 2 Shoe 2 1
2 Mary 20 25000 1 Toy 1 5
3 Bob 22 27000 1 Toy 1 5
4 Kathy 30 30000 2 Shoe 2 1
5 Shideh 4 4000 1 Toy 1 5
(dno)
34
EXAMPLE JOIN
SS Name Age Salary dno
1 Joe 24 20000 2
2 Mary 20 25000 1
3 Bob 22 27000 1
4 Kathy 30 30000 2
5 Shideh 4 4000 1
dno dname floor mgrss
1 Toy 1 5
2 Shoe 2 1

• Join, (Emp ?x(Emp))))

Dept
EMP
SS Name Age Salary dno x.SS x.Name x.Age x.Salary x.dno
2 Mary 20 25000 1 2 Shideh 4 4000 1
3 Bob 22 27000 1 3 Shideh 4 4000 1
4 Kathy 30 30000 2 4 Shideh 4 4000 1
Salary gt 5 salary
35
(No Transcript)
36
EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOIN
(Cont)

• Example retrieve the name of employees who earn
  more than Joe
• ?name(Emp (salgtx.sal)?nameJoe(? x(Emp)))
• Semi-Join selects the columns of one relation
  that joins with another. It is equivalent to a
  join followed by a projection
• Emp (dno)Dept ?SS, name, age, salary,
  dno(Emp Dept)

37
(No Transcript)
38
JOIN OPERATORS

• Condition Joins
• - Defined as a cross-product followed by a
  selection
• R ?c S sc(R ? S)
  (? is called the bow-tie)
• where c is the condition.
• - Example
• Given the sample relational instances S1 and R1

The condition join S ?S1.sidltR1.sid R1 yields
39
JOIN OPERATORS

• Condition Joins
• - Defined as a cross-product followed by a
  selection
• R ?c S sc(R ? S)
  (? is called the bow-tie)
• where c is the condition.
• - Example
• Given the sample relational instances S1 and R1

The condition join S ?S1.sidltR1.sid R1 yields
40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43

• Equijoin
• Special case of the condition join where the join
  condition consists solely of equalities between
  two fields in R and S connected by the logical
  AND operator (?).
• Example Given the two sample relational
  instances S1 and R1

The operator S1 R.sidSsid R1 yields
44

• Natural Join
• - Special case of equijoin where equalities are
  implicitly specified on all fields having the
  same name in R and S.
• - The condition c is now left out, so that the
  bow tie operator by itself signifies a natural
  join.
• - N. B. If the two relations have no attributes
  in common, the natural join is simply the
  cross-product.

45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
Computer 1st Project
Show how to place            non-attacking
queens on a triangular board of side n. Show
that this is the maximum possible number of
queens.                  
49
Tree search example

• You need to use
• Depth First Search
• Backtracking Algorithm
• Due Date Feb. 12, Input, Output format
• Feb. 19 Depth first search
• Feb. 26 Complete the project.

50
Tree search example
51
Tree search example
52
Implementation general tree search
53
Implementation states vs. nodes

• A state is a (representation of) a physical
  configuration
• A node is a data structure constituting part of a
  search tree includes state, parent node, action,
  path cost g(x), depth
• The Expand function creates new nodes, filling in
  the various fields and using the SuccessorFn of
  the problem to create the corresponding states.
  

54
Search strategies

• A search strategy is defined by picking the order
  of node expansion
• Strategies are evaluated along the following
  dimensions
• completeness does it always find a solution if
  one exists?
• time complexity number of nodes generated
• space complexity maximum number of nodes in
  memory
• optimality does it always find a least-cost
  solution?
  
• Time and space complexity are measured in terms
  of
• b maximum branching factor of the search tree
• d depth of the least-cost solution
• m maximum depth of the state space (may be 8)

55
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

56
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

57
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

58
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

59
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

60
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

61
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

62
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

63
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

64
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

65
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

66
Depth-first search

• Expand deepest unexpanded node
• Implementation
• fringe LIFO queue, i.e., put successors at
  front
  

67
Properties of depth-first search

• Complete? No fails in infinite-depth spaces,
  spaces with loops
• Modify to avoid repeated states along path
• ? complete in finite spaces
• Time? O(bm) terrible if m is much larger than d
• but if solutions are dense, may be much faster
  than breadth-first
  
• Space? O(bm), i.e., linear space!
• Optimal? No

68
Depth-limited search

• depth-first search with depth limit l,
• i.e., nodes at depth l have no successors
• Recursive implementation

69
Backtracking

• Suppose you have to make a series of decisions,
  among various choices, where
• You dont have enough information to know what to
  choose
• Each decision leads to a new set of choices
• Some sequence of choices (possibly more than one)
  may be a solution to your problem
• Backtracking is a methodical way of trying out
  various sequences of decisions, until you find
  one that works

70
Backtracking (animation)
dead end
?
dead end
dead end
?
start
?
?
dead end
dead end
?
success!
71
Terminology I
A tree is composed of nodes
There are three kinds of nodes
Backtracking can be thought of as searching a
tree for a particular goal leaf node
72
Terminology II

• Each non-leaf node in a tree is a parent of one
  or more other nodes (its children)
• Each node in the tree, other than the root, has
  exactly one parent

Usually, however, we draw our trees downward,
with the root at the top
73
Real and virtual trees

• There is a type of data structure called a tree
• But we are not using it here
• If we diagram the sequence of choices we make,
  the diagram looks like a tree
• In fact, we did just this a couple of slides ago
• Our backtracking algorithm sweeps out a tree in
  problem space

74
The backtracking algorithm

• Backtracking is really quite simple--we explore
  each node, as follows
• To explore node N
• 1. If N is a goal node, return success
• 2. If N is a leaf node, return failure
• 3. For each child C of N,
• 3.1. Explore C
• 3.1.1. If C was successful, return success
• 4. Return failure