Relational algebra and computer assignment 1
Download
1 / 74

RELATIONAL ALGEBRA and Computer Assignment 1 - PowerPoint PPT Presentation


  • 100 Views
  • Updated On :

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

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

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
Relational algebra and computer assignment 1 l.jpg

Lecture 3

CS157B

RELATIONAL ALGEBRA and Computer Assignment 1

Prof. Sin-Min LEE

Department of Computer Science


Codd s relational algebra l.jpg
Codd’s 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


A set of logical operations the relational algebra l.jpg
A Set of Logical Operations: The Relational Algebra

  • 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


Data instance for operator examples l.jpg
Data Instance for Operator Examples

STUDENT

COURSE

Takes

PROFESSOR

Teaches


Rename a b l.jpg
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?


Deriving intersection l.jpg
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


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


Division using our existing operators l.jpg
Division Using Our Existing Operators

  • 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 r 1 r 2 l.jpg
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))


Division12 l.jpg
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)



Division17 l.jpg
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 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.


Division18 l.jpg
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}


Formal definition of division l.jpg
FORMAL DEFINITION OF DIVISION

The formal definition of division is as follows:

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


Cartesian product cont l.jpg
CARTESIAN PRODUCT (Cont…)

  • Example:

    Emp table:

    Dept table:

SS#

Name

age

salary

dno

dno

dname

floor

mgrSS#


Cartesian product cont22 l.jpg
CARTESIAN PRODUCT (Cont…)

SS#

Name

age

salary

Emp.dno

Dept.dno

dname

floor

mgrSS#

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


Cartesian product l.jpg
CARTESIAN PRODUCT

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


Cartesian product24 l.jpg
CARTESIAN PRODUCT

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

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


Cartesian product cont25 l.jpg
CARTESIAN PRODUCT (Cont…)

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

SS#

Name

age

salary

Emp.dno

Dept.dno

dname

floor

mgrSS#


Cartesian product cont26 l.jpg
CARTESIAN PRODUCT (Cont…)

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

SS#

Name

age

salary

Emp.dno

Dept.dno

dname

floor

mgrSS#


Cartesian product cont27 l.jpg
CARTESIAN PRODUCT (Cont…)

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

SS#

Name

age

salary

Emp.dno

Dept.dno

dname

floor

mgrSS#


Cartesian product cont28 l.jpg
CARTESIAN PRODUCT (Cont…)

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

Name


Equality join natural join join semi join l.jpg
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(б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)


Example join l.jpg
EXAMPLE JOIN

  • Equality Join, (Emp Dept)))

Dept

EMP

(dno)


Example join33 l.jpg
EXAMPLE JOIN

  • Natural Join, (Emp Dept)))

Dept

EMP

(dno)


Example join34 l.jpg
EXAMPLE JOIN

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

Dept

EMP

Salary > 5 * salary


Equality join natural join join semi join cont l.jpg
EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOIN (Cont…)

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


Join operators l.jpg
JOIN OPERATORS

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


Join operators39 l.jpg
JOIN OPERATORS

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


Slide43 l.jpg

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


Slide44 l.jpg

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.


Slide48 l.jpg

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.


Tree search example l.jpg
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.





Implementation states vs nodes l.jpg
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 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.


Search strategies l.jpg
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 ∞)


Depth first search l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search56 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search57 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search58 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search59 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search60 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search61 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search62 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search63 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search64 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search65 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Depth first search66 l.jpg
Depth-first search

  • Expand deepest unexpanded node

  • Implementation:

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


Properties of depth first search l.jpg
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


Depth limited search l.jpg
Depth-limited search

= depth-first search with depth limit l,

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

  • Recursive implementation:


Backtracking l.jpg
Backtracking

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


Backtracking animation l.jpg
Backtracking (animation)

dead end

?

dead end

dead end

?

start

?

?

dead end

dead end

?

success!


Terminology i l.jpg

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


Terminology ii l.jpg

parent

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


Real and virtual trees l.jpg
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”


The backtracking algorithm l.jpg
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”


ad