Relational Calculus and Relational Algebra Review DDL and ...

Relational Calculus and Relational Algebra Review DDL and DML SQL

Lesson 5 Northeastern University Kathleen Durant

1

Outline for today

? Review of Chapter 4

? Quantifiers ? Relational Algebra & Relational Calculus

? Introduce examples from the text

? Students, Courses, Lecturers ? Boats, Sailors, Reservations

? Review of DDL SQL Queries ? Introduction to the SELECT command

? Basics, Set operations, Nested Queries, Aggregation functions

? Additional information for the homework assignment

2

Data manipulation via Relational Algebra

? Data is represented as mathematical relations. ? Manipulation of data (query and update operations)

corresponds to operations on relations ? Relational algebra describes those operations ? Relational algebra contains two kinds of operators:

? common set-theoretic operators ? operators specific to relations (for example projection of

columns).

3

Relational Algebra

One or more relations

Operation

Resulting Relation

? A collection of operations that users can perform on relations

to obtain a desired result (which is also a relation)

? For each operation (steps in the computation), both the

operands and the result are relations

? Basic (Relational) operations:

? Selection ( ): Selects a subset of tuples from a relation.

? Projection ( ): Selects columns from a relation.

? Cross-product ( ? ): Allows us to combine two relations.

? Set-difference ( - ): Tuples in relation 1, but not in relation 2.

? Union ( ): Tuples in relation 1 and in relation 2.

4

? Relational Algebra treats relations as sets: duplicates are

removed

Example: Different solutions ? same

answer

SID

Name Login

S1

55515 Smith smith@ccs

DoB

GPA

Jan 10,1990 3.82

Find the names of students registered for History 101

55516 Jones 55517 Ali 55518 Smith

jones@hist ali@math smith@math

Feb 11, 1992 2.98 Sep 22, 1989 3.11 Nov 30, 1991 3.32

Solution1: (( = 101 Courses) S1) Solution2: ( = 101 (Courses S1))

Solution3: (Temp1, ( = 101 Courses) ) (Temp2,(Temp1 S1))

Name(Temp2)

Sid

CId

C1

55515 History 101

Grade C

Answer Name Smith Ali

55516 Biology 220

A

55517 History 101

B

5

55518 Music 101

A

Example: 3 Table join

Find the lecturers

teaching History 101

Whose Students GPA >3.2

Solution1: ((Sid,GPA( > 3.2 ) )

(( = 101 C1) = 101 L1))

S1 SID

Name

55515 Smith

55516 Jones

55517 Ali

55518 Smith

Login smith@ccs jones@hist ali@math smith@math

DoB

GPA

Jan 10,1990 3.82

Feb 11, 1992 2.98

Sep 22, 1989 3.11

Nov 30, 1991 3.32

L1

LID

Name CID

C1

Sid

CId

LID Grade

Solution2:

45

Fisk

History 101

46

Alder Biology 220

55515 History 45 C 101

(Temp1,

47

Wong History 101

55516 History 47 A 101

( = 101 C1) ) 48

Foster Music 101

(Temp2, (Temp1

55517 History 45 B 101

55518 Music 48 A

= 101 L1) )

(Temp3, (Sid,GPA( Temp2))

>

3.2

S1)

Answer Name

101

Why did I need Sid,GPA to use a natural join? Any other solution?

6

Name(Temp3)

Fisk

Table

B1

BID

BName Color

Instances 101

? We will use

102

these instances 103

of the Sailors 104

and Reserves

Interlake blue Interlake red Clipper green Marine red S1

relations in our SID

examples.

22

? If the key for the 31

Reserves

58

relation

S2

Sname Dustin Lubber Rusty

contained only SID

Sname

the attributes sid 28

and bid, how

31

would the

44

semantics differ? 58

Yuppy Lubber Guppy Rusty

SID 22 58

Rating 7 8 10

Rating 9 8 5 10

R1

BID DAY 101 10/10/96 103 11/12/96

Age 45.0 55.5 35.0

Age

35.0

55.5

7

35.0

35.0

Relational calculus

? A formal, logical description, of what you want from the database

? Sometimes describing the set you desire is easier than figuring out the operations you need to do to get to the desired set

? Case in point: Division

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download