3 relational algebra and sql n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
3. Relational Algebra and SQL PowerPoint Presentation
Download Presentation
3. Relational Algebra and SQL

Loading in 2 Seconds...

play fullscreen
1 / 15

3. Relational Algebra and SQL - PowerPoint PPT Presentation


  • 118 Views
  • Uploaded on

3. Relational Algebra and SQL. Example: Let the following relations describe point sets: A(x, y), B(x, y), J(x, y) 2D points in the plane H(x, y, z), I(x, y, z) 3D points in space F(z) line on the z-axis

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 '3. Relational Algebra and SQL' - columbia


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
3 relational algebra and sql
3. Relational Algebra and SQL

Example:

Let the following relations describe point sets:

  • A(x, y), B(x, y), J(x, y) 2D points in the plane
  • H(x, y, z), I(x, y, z) 3D points in space
  • F(z) line on the z-axis
  • K(y,z) 2D points in (y, z) plane
3 1 relation algebra operators
3.1 Relation algebra operators

A and B

A  B

A  B

A \ B

slide4

 y,z H

 x,y H

 2x+z = 0 I

slide5
Example:

Find the SSN and tax for each person.

πSSN,Taxσwages+interest+capital_gain = income Taxrecord × Taxtable

Example:

Find the area of Lincoln reached by a radio station.

( πX,Y ( σName=“Lincoln” Town ) )  ( πX,Y Broadcast )

slide6
3.2 SQL

SELECT a1, ..., an

FROM R1, R2, …, Rm

WHERE Con1, …,Conk

This means:

πa1, ..., an( σ Con1( … (σ Conk ( R1× R2 × … ×Rm))…))

slide7
Example:

Find the SSN and tax for each person.

SELECT SSN, Tax

FROM Taxrecord, Taxtable

WHERE wages + interest + capital_gain = income

slide8
AS – keyword used to rename relations

Two SQL expressions can be combined by:

INTERSECT

UNION

MINUS– set difference

slide9
Example:

Find the names of the streets that intersect.

SELECT S.NAME, T.NAME

FROM Streets AS S, Streets AS T

WHERE S.X = T.X and

S.Y = T.Y

slide10
Example: Assume we have the relations:

Broadcast ( Radio, X , Y )

Town ( Name, X, Y )

Find the parts of Lincoln, NE that can

be reached by at least one Radio station.

(SELECT X, Y

FROM Town

WHERE Name = “Lincoln”)

INTERSECT

(SELECT X, Y

FROM Broadcast)

slide11
Another way of connecting SQL expressions

is using the IN keyword.

SELECT ……..

FROM ……..

WHERE a IN ( SELECT b

FROM …..

WHERE ….. )

slide12
SQL with aggregation –

SELECT aggregate_function

FROM …….

WHERE ……

aggregate_function–

Max (c1a1 + ……..+ cnan) where ai are attributes

Min (c1a1 + ……..+ cnan) and ci are constants

Sum(a) where a is an attribute that is

Avg(a) constant in each constraint tuple

Count(a)

slide13
Example:

Package(Serial_No, From, Destination, Weight)

Postage (Weight , Fee)

Find the total postage of all packages sent

from Omaha.

SELECT Sum(Fee)

FROM Package, Postage

WHERE Package.Weight = Postage.Weight AND

Package.From = “ Omaha “

slide14
GROUP BY–

SELECT a1, …, an, aggregate_function

FROM …..

WHERE ……

GROUP BY a1, ..., ak

  • Evaluates basic SQL query
  • Groups the tuples according to different values of a1,..,ak
  • Applies the aggregate function to each group separately
  • {a1, …, ak}  {a1, …, an}
slide15
Example:

Find the total postage sent out from each city.

SELECT Package.From, Sum(Postage.Fee)

FROM Package, Postage

WHERE Package.Weight = Postage.Weight

GROUP BY Package.From