1 / 74

# RELATIONAL ALGEBRA and Computer Assignment 1 - PowerPoint PPT Presentation

Lecture 3. CS157B. RELATIONAL ALGEBRA and Computer Assignment 1. Prof. Sin-Min LEE Department of Computer Science. Codd’s Relational Algebra. A set of mathematical operators that compose, modify, and combine tuples within different relations

Related searches for RELATIONAL ALGEBRA and Computer Assignment 1

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'RELATIONAL ALGEBRA and Computer Assignment 1' - genero

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

CS157B

### RELATIONAL ALGEBRA and Computer Assignment 1

Prof. Sin-Min LEE

Department of Computer Science

• 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

• Six basic operations:

• Projection  (R)

• Selection  (R)

• Union R1UR2

• Difference R1 – R2

• Product R1X R2

• (Rename) ->b (R)

• And some other useful ones:

• Join R1 ⋈ R2

• Semijoin R1 ⊲ R2

• Intersection R1Å R2

• Division R1¥ R2

STUDENT

COURSE

Takes

PROFESSOR

Teaches

Rename, ab

• The rename operator can be expressed several ways:

• The book has a very odd definition that’s not algebraic

• An alternate definition:

ab(x) Takes the relation with schema  Returns a relation with the attribute list 

• Rename isn’t all that useful, except if you join a relation with itself

Why would it be useful here?

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

• 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 fid’s of professors for which there does not exist a subject that they haven’t taught”

• All possible teaching assignments: Allpairs:

• NotTaught, all (fid,subj) pairs for which professor fidhas 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))

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

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

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 B2’s.

- For all the set of rows in A which satisfy the above search, pick out their A1 values and put them in the answer.

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}

The formal definition of division is as follows:

A/B = x(A) - x((x(A)  B) – A)

• Example:

Emp table:

Dept table:

SS#

Name

age

salary

dno

dno

dname

floor

SS#

Name

age

salary

Emp.dno

Dept.dno

dname

floor

• Cartesian product of Emp and Dept: Emp × Dept:

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

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

• ∏name(бEmp.dno=Dept.dno(Emp × бdname=‘toy’(Dept)))

• ∏name(бdname=‘toy’(бEmp.dno=Dept.dno(Emp × Dept)))

SS#

Name

age

salary

Emp.dno

Dept.dno

dname

floor

• ∏name(бdname=‘toy’(б Emp.dno=Dept.dno(Emp × Dept)))

SS#

Name

age

salary

Emp.dno

Dept.dno

dname

floor

• ∏name(бdname=‘toy’(б Emp.dno=Dept.dno(Emp × Dept)))

SS#

Name

age

salary

Emp.dno

Dept.dno

dname

floor

• ∏name(бdname=‘toy’(б Emp.dno=Dept.dno(Emp × Dept)))

Name

• Equality join connects tuples from two relations that match on certain attributes. The specified joining columns are kept in the resulting relation.

• ∏name(бdname=‘toy’(Emp Dept)))

• Natural join connects tuples from two relations that match on the specified common attributes

• ∏name(бdname=‘toy’(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 = {=, ≠, ≤, ≥, <, >}

att op att

(dno)

(dno)

• Equality Join, (Emp Dept)))

Dept

EMP

(dno)

• Natural Join, (Emp Dept)))

Dept

EMP

(dno)

• Join, (Emp ρx(Emp))))

Dept

EMP

Salary > 5 * salary

• Example: retrieve the name of employees who earn more than Joe:

• ∏name(Emp (sal>x.sal)бname=‘Joe’(ρ 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)

Condition Joins:

- Defined as a cross-product followed by a selection:

R ⋈cS = σc(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.sid<R1.sid R1 yields

Condition Joins:

- Defined as a cross-product followed by a selection:

R ⋈cS = σc(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.sid<R1.sid R1 yields

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.sid=Ssid R1 yields

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

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.

• 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.

• 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 costg(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.

• 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 ∞)

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• Expand deepest unexpanded node

• Implementation:

• fringe = LIFO queue, i.e., put successors at front

• 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

= depth-first search with depth limit l,

i.e., nodes at depth l have no successors

• Recursive implementation:

• Suppose you have to make a series of decisions, among various choices, where

• You don’t 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”

?

?

start

?

?

?

success!

The (one) root node

Internal nodes

Leaf nodes

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

parent

children

children

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

• 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”

• 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”