Relational algebra
Download
1 / 34

Relational Algebra - PowerPoint PPT Presentation


  • 104 Views
  • Uploaded on

After this lecture, you should be able to: Understand the differences between SQL (Structured Query Language) and Relational Algebra expressions . Build queries in Relational Algebra. Understand how DBMS’s process the SQL queries. Make simple query optimization .

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' - berget


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

After this lecture, you should be able to:

Understand the differences between SQL (Structured Query Language) and Relational Algebra expressions.

Build queries in Relational Algebra.

Understand how DBMS’s process the SQL queries.

Make simple query optimization.

Be ready for Quiz 4 and Complete Assignment 7 (Part I).

Relational Algebra


Relational algebra1
Relational Algebra

  • Relational algebra deals with a set of relationsclosed under operators. Operators operate on one or more relations to yield a relation.

  • Algebraic Expressions

    (a + b) * c

    (A  B)  C

  • Relational algebra is a basis for database query languages.


Characteristics of relational algebra
Characteristics of Relational Algebra

  • Operational

    • The order of operations can be specified.

  • Set-at-a-time

    • Multiple rows are processed by one operation.

  • Not too detailed

    • How to access data is not specified.

    • Good for query optimization.

  • Who supplied P2?

    • SQL

      Select SName from S, SP

      where S.S# = SP.S# and P# = 'P2' ;

    • Relational Algebra

      SName (S ⋈ (p#=‘P2‘ SP ))


Relational algebra2
Relational algebra …

  • Union: A  B

  • Intersection: A  B

  • Difference: A ̶ B

  • Cartesian Product: S × SP

  • Division: A / B

  • Selection: city=‘Rome‘ S

  • Projection: SName,StatusS

  • Join: S ⋈ SP

  • Renamingρ(citypcity) P


Terms relational algebra database
Terms: Relational algebra ~ Database

Relation ~ Table

Attribute ~ Column

Tuple ~ Row (Record)

Relation schema ~ Structure of table

Relation Instance ~ Data of table


Union and intersection
Union and Intersection

P

Q

P  Q

P  Q


Union and intersection exercise
Union and Intersection: Exercise

P

Q

P  Q = ?

P  Q = ?


Difference
Difference (-)

Q

P

P ̶ Q


Difference exercise
Difference: Exercise

Q

P

P ̶ Q = ?


Cartesian product
Cartesian Product (×)

Q

P

P × Q = {(p, q)|p  P and q  Q}



Selection
Selection ()

R

B = 2 R


Get all the information of the suppliers located in paris
Get all the information of the suppliers located in Paris.

Table S

sno | sname | status | city

-----------------------------

s1 | Smith | 20 | London

s2 | Jones | 10 | Paris

s3 | Blake | 30 | Paris

s4 | Clark | 20 | London

s5 | Adams | 30 | Athens

select *

from s

where city = ‘Paris’;

city = ‘Paris‘ S


Projection
Projection ()

R

 C, AR


What are the s s and statuses of the suppliers
What are the S#’s and statuses of the suppliers?

Table S

sno | sname | status | city

-----------------------------

s1 | Smith | 20 | London

s2 | Jones | 10 | Paris

s3 | Blake | 30 | Paris

sno | status

------------

s1 | 20

s2 | 10

s3 | 30

select sno, status

from s;

sno, statusS


What are the s s and statuses of the suppliers located in paris
What are the S#’s and statuses of the suppliers located in Paris?

Table S

sno | sname | status | city

-----------------------------

s1 | Smith | 20 | London

s2 | Jones | 10 | Paris

s3 | Blake | 30 | Paris

s4 | Clark | 20 | London

s5 | Adams | 30 | Athens

sno | status

------------

s2 | 10

s3 | 30

select sno, status

from s

where city = 'Paris;

sno, status(city = ‘Paris‘ S)


Selection and projection exercise
Selection and Projection: Exercise Paris?

Get the names and weights of the green parts.

pno pname color weight city

---- ------ ------- ------ ------

p1 nut red 12 London

p2 bolt green 15 Paris

p3 screw green 17 Rome

pname weight

----- ------

bolt 15

screw 17

select pname, weight

from p

where color = ‘green’;

pname, weight(color = ‘green‘ P)


 Join Paris?

P

Q

B1 ≥ B2(P × Q)


Equijoin
Equijoin Paris?

Q

P

B1 = B2(P × Q)


Natural join
Natural Join ( Paris?⋈ )

P

Q

P ⋈ Q


Natural join example
Natural Join: Example Paris?

S

SP

S ⋈ SP = ?


Selection join and projection example
Selection, Join, and Projection: Example Paris?

What are the part numbers of the parts supplied by the

suppliers located in Taipei?

SQL:

select SP.pno

from S, SP

where S.city = ‘Taipei’

and S.sno = SP.sno;

Algebra:

pno((S.City=‘Taipei’S)⋈ SP)


Selection join and projection exercise
Selection, Join, and Projection: Exercise Paris?

What are the names of the suppliers who supplied green

parts?

SQL:

select sname

from P, SP, S

where P.color = ‘green’

and P.pno = SP.pno and SP.sno = S.sno;

Algebra:

sname((pno(color=‘green‘P))⋈ SP ⋈ S)


Subtraction examples
Subtraction Examples Paris?

Get the supplier numbers of the suppliers who did not

supply red parts.

  • The supplier numbers of all the suppliers:

    AS = s# (S)

  • The supplier numbers of the suppliers who supplied some red parts:

    RPS = s# (SP ⋈ (Color = ‘Red‘ (P))

  • The answer is:

    AS - RPS


Subtraction examples cont d
Subtraction Examples (cont’d) Paris?

Get the names of the suppliers who supplied only red

parts.

  • The supplier numbers of the suppliers who supplied some parts:

    SS = s# (SP)

  • The supplier numbers of the suppliers who supplied at least one non-red parts:

    NRS = s# (SP ⋈ (color <> ‘Red‘ (P))

  • The answer is:

    SName ((SS – NRS) ⋈ S)


Rename
Rename ( Paris?ρ)

Ρ(B->B2, 3C2)


Join with renaming example i
Join with Renaming: Example I Paris?

Get the pairs of the supplier numbers of the suppliers

who supply at least one identical part.

SQL:

select SPX.sno, SPY.sno

from SP SPX, SP SPY

where SPX.pno = SPY.pno

and SPX.sno < SPY.sno

Algebra:

snox,snoy(snox < snoy

((ρ(snosnox)sno,pnoSP)⋈

(ρ(snosnoy)sno,pnoSP)

)

)


Join with renaming example ii
Join with Renaming: Example II Paris?

What are the colors of the parts supplied by the suppliers

located in Taipei?

Table S Table P

sno | sname | sts | city pno | pname | color | wgt | city

-------------------------- ----------------------------------

s1 | Smith | 20 | London p1 | nut | red | 12 | London

s2 | Jones | 10 | Taipei p2 | bolt | green | 17 | Taipei

p3 | screw | blue | 17 | Rome

Table SP

sno | pno | qty

---------------

s1 | p1 | 300

s2 | p3 | 200


Join with renaming example ii1
Join with Renaming: Example II Paris?

What are the colors of the parts supplied by the suppliers

located in Taipei?

SQL:

select color

From S, SP, P

where S.city = ‘Taipei’

and S.sno = SP.sno and SP.pno = P.pno;

Algebra:

color(S.City=‘Taipei‘(S ⋈ SP ⋈ P))

Joined also by S.city = P.city !


Join with renaming example ii cont d
Join with Renaming: Example II (cont’d) Paris?

Not Correct:

color(S.City=‘Taipei‘(S ⋈ SP ⋈ P))

Renamed:

color(City=‘Taipei‘

(S ⋈ SP ⋈ρ(citypcity)P))

Optimized:

color((pno((sno(City=‘Taipei‘S))

⋈ SP))⋈ P)


Join example query plan
Join Example: Query Plan Paris?

color

pno

sno

city=‘Taipei‘S

P

S

SP


Sql query processing
SQL Query Processing Paris?

  • An SQL query is transformed into an algebraic form for query optimization.

  • Query optimization is the major task of an SQL query proccessor.

  • select A1, A2, … , Am

    from T1, T2, … , Tn

    where C

    is converted to

    A1, A2, …, AmC (T1 x T2 x … x Tn)

    and then optimized.


Query optimization guidelines
Query Optimization Guidelines Paris?

  • JOIN operations are associative and commutative

    • (R ⋈ S) ⋈ T = R ⋈ ( S ⋈ T)

    • R ⋈ S = S ⋈ R

  • JOIN and SELECT operations are associative and commutative if the SELECT operations are still applicable.

  • PROJECTIONs can be performed to remove column values not used.

  • These properties can be used for query optimization.

    • The order of operations can be changed to produce smaller intermediate results.


Optimization example
Optimization: Example Paris?

What are the part numbers of the parts supplied by the

suppliers located in Taipei?

Not Optimized:

pno (S.City=‘Taipei‘(S ⋈ SP))

Improved:

pno ((S.City=‘Taipei‘S)⋈ SP)

Optimized:

pno((sno(S.City=‘Taipei‘S))⋈(sno,pnoSP))


ad