Relational Algebra - Georgia State University Algebra.pdfIntroduction to Database Management Systems...

Introduction to Database Management Systems

Dave McDonald, CIS, GSU 5-1

Relational Algebra 1CIS

Relational Algebra

ο Why Relational Algebra?

ο Traditional Set Operators.υ Union

υ Intersection

υ Difference

υ Cartesian Product

ο Special Relational Operators.υ Restriction (Select)

υ Projection

υ Join


Why Relational Algebra?Purposes:

υ Provides a theoretical foundation for relational data manipulation.

υ Helps the study of query optimization.

υ Is an important component of relational data model.

Relational algebra is based on the mathematical set theoryυ Operands are relations, attributes, and tuples.

υ Operators are the extension of the set theory.




Union CompatibleTwo relations, A and B, are union-compatible iff they have the

same degree and you can find corresponding attributes in A and B having been defined in the same domain

E.g.

CIS-Student (ID: Did; Name: Dname; Address: Daddr; Grade: Dgrade);

Senior-Student (SName: Dname; S#: Did; Home: Daddr; Grade: Dgrade);

Course (C#: Dnumber; Title: Dstr; Credits: Dnumber)

Are CIS-Student and Senior-Student union compatible?

Are CIS-Student and Course union compatible?


Traditional Set Operators

• Union

• Intersection

• Difference

• Cartesian Product




Union, Intersection, & Difference

A B

A B

A B

A and B must be two compatible relations

ο Union ( ∪∪∪∪ )

A ∪∪∪∪ B = {t | t in A or t in B}

UNION relation-1 WITH relation-2 GIVING relation-3

ο Intersection ( ∩∩∩∩ )A ∩∩∩∩ B = { t | t in A and t in B}

INTERSECT relation-1 WITH relation-2 GIVING relation-3

ο Difference ( - )A −−−− B = {t | t in A and t not in B}

SUBTRACT relation-1 FROM relation-2 GIVING relation-3


Example I

CIS-Student ∪ Senior-Student =?

CIS-Student ∩ Senior-Student =?

CIS-Student − Senior Student =?

CIS-Student ∩ Course =?

IDs1s3s4

NameJoseTomSue

Age213220

AddressStone Mountain

DunwoodyAtlanta

GPA3.73.02.9

IDs2s3s4s5

NameAliceTomSue

Steve

Age18322027

AddressBuck HeadDunwoody

AtlantaStone Mountain

GPA4.03.02.93.5

CIS-Student:

Senior-Student:

c#c1c2c3

titlexxxyyyzzz

credits535

Course




Cartesian Product

a1 a2b1 b2 b3 b1 b2 b3a1 a2

1

2

x

y

a

m

g

s

n

j

m

r

m

1 x a s m

1 x m n r

1 x g j m

2 y a s m2 y m n r2 y g j m

A B

X=

• Need not be union compatible• Resulting Cardinality is the product of the two• Resulting Degree is the addition of the two

PRODUCT relation-1 BY relation-2 GIVING relation-3


Example II

IDs1s2s3

NameJoseAliceTom

...

...

Emp#e1e2e2

IDs1s2s1

C#CIS 3210CIS 2010CIS 3730

...

...

StudentClass




Special Relational Operators

• Restriction (Selection of rows/tuples)

• Projection (Picking out certain columns/attributes

• Join

• Division


RestrictionSelection of tuples that satisfy a given Boolean condition θθθθ

ΣΣΣΣθθθθ( R ) = {t | t in R and θθθθ is true}

SELECT relation-1 WHERE condition GIVING relation-2

List all students with GPA > 3.0SELECT Student WHERE GPA>3.0 GIVING temp

ΣΣΣΣGPA > 3.0( Student )

IDs1s2s3s4s5

NameJoseAliceTomSue

Steve

Age2118322027


Buck HeadDunwoody


GPA3.74.03.02.93.5

Student

Result?




Complex RestrictionList all students with GPA > 3.0 and are over 20 years of age.

ΣΣΣΣGPA > 3.0 and Age > 20( Student )

SELECT Student WHERE GPA>3.0 and Age>20 GIVING answer

IDs1s2s3s4s5

NameJoseAliceTomSue

Steve

Age2118322027


Buck HeadDunwoody


GPA3.74.03.02.93.5

Student

Result?

ΣΣΣΣGPA > 3.0 or Age > 20( Student ) = ?


Equivalent RestrictionEquivalence Properties:

ΣΣΣΣx and y ( R ) = ΣΣΣΣx ( R ) ∩∩∩∩ ΣΣΣΣy ( R )

ΣΣΣΣx or y ( R ) = ΣΣΣΣx ( R ) ∪∪∪∪ ΣΣΣΣy ( R )

ΣΣΣΣnot x ( R ) = R −−−− ΣΣΣΣx ( R )

ΣΣΣΣGPA > 3.0 and Age > 20(Student) = ΣΣΣΣGPA > 3.0 (Student) ∩∩∩∩ ΣΣΣΣAge > 20(Student)

ΣΣΣΣGPA > 3.0 or Age > 20(Student) = ΣΣΣΣGPA > 3.0 (Student) ∪∪∪∪ ΣΣΣΣAge > 20(Student)

ΣΣΣΣNot GPA > 3.0(Student) = Student −−−− ΣΣΣΣGPA > 3.0 (Student)

IDs1s2s3s4s5

NameJoseAliceTomSue

Steve

Age2118322027


Buck HeadDunwoody


GPA3.74.03.02.93.5

Student




ProjectionA projection of a relation R on attributes A1, A2, ..., An, is a relation

which consists attributes, A1, A2, ..., An.

ΠΠΠΠA1,A2,...An ( R ) = R'( A1, A2, ..., An )

PROJECT attribute-1 [attribute-2, attribute-3...] FROM relation-1 GIVING relation-2

List IDs, names, and addresses of all students.

ΠΠΠΠ ID, Name, Address ( STUDENT )

IDs1s2s3s4s5

NameJoseAliceTomSue

Steve

Age2118322027


Buck HeadDunwoody


GPA3.74.03.02.93.5

Student

Result?


Equal Joins (Equijoin)The equal join of two product-compatible relations (join attributes must

be from the same domain), R1 and R2 based on the join attribute R1.xand R2.y is

R1 ΞΞΞΞx=y R2 = ΣΣΣΣx=y ( R1 ×××× R2 )

JOIN relation-1 USING attribute-1 WITH relation-2 USING attribute-2 GIVING relation-3

List all students that are taking the CIS 3210 course.

SIDs1s2s3

NameJoseAliceTom

...

...

Emp#e1e2e2

IDs1s2s1

C#CIS 3210CIS 2010CIS 3210

...

...

Student Class

ΞΞΞΞStudent.SID = Class.ID

Results?




Natural Joinsο An equijoin by removing one of the repeating joining attributes.

ο By default, "Join" refers to the natural join

Student ΞS.ID=C.ID Class ?


Join...Greater-Than Join (A ΞΞΞΞx > y B)

The comparison operator is '>'. A joined tuples are selected if its x value is greater than the y value.

JOIN relation-1 USING attribute-1 WITH relation-2 USING attribute-2 GIVING relation-3 [WHERE equality/inequality/θθθθ

List student pairs such that the first student's name follows the second student's name in alphabetical order.

Student ΞName > Name Student

IDs1s2s3

NameJoseAliceTom

...

...

Student

ID Name ... ID Name ...s1 Jose s1 Jose ...s1 Jose ... s2 Alice ...s1 Jose s3 Toms2 Alice s1 Joses2 Alice s2 Alices2 Alice s3 Toms3 Tom s1 Joses3 Tom s2 Alices3 Tom s3 TomResult =?




Other Joinsο Not-equal join (A ΞΞΞΞx <> y B)

Select those Cartesian tuples that do not have matched join values

ο Greater-than join (A ΞΞΞΞx > y B)Select those Cartesian tuples in which R1.x values are greater than R2.y values


Why Joins?4 Relations are linked through common attributes

4 Joins allow navigate through relations

4 Retrieval of data from multiple relations

QUERY

For each student who is taking course from instructor Fred and who has GPA > 3.0 and age > 20, list their names and addresses.

Student Instructor

Class Course

ID Emp#

Course#




SummaryUnion Intersection Difference

Cartesian ProductR1 R2


Summary...Restriction

Projection

x y=>

<>...

Join

R1 R2




Examples

• List all information about students who live in Stone Mountain

• What time does CIS3210 begin?

• What time does CIS2010 meet and where?

• What are the names of the assistant professors?

• What courses are offered?

• Which students have less than a “B” average?


Example

List all students who attend night classes by their name,the class they’re taking and who their professor is for thatclass




A Relation Example

ID Name

s1 s2 s3 s4 s5

Jose Alice Tom Sue Steve

Student

Age

21 18 32 20 27

Address

Stone Mountain Buck Head Dunwoody Atlanta Stone Mountain

GPA

3.7 4.0 3.0 2.9 3.5

Emp# Name Rank Dept

e1 e2 e3 e4

Lisa Fred John Abe

Professor Ass. Prof. Assc. Prof. Ass. Prof.

CIS MATH CSc EE

Instructor

EMP# SID Course#

e1 e3 e2 e2 e3 e4 e2 e1 e1

s1 s3 s2 s1 s1 s4 s2 s3 s2

CIS2010 CIS3210 CIS3215 CIS3215 CIS3210 CIS3730CIS3300 CIS2010 CIS2010

Class Time Room

12:10 pm 8:00 am 4:30 pm 4:30 pm 8:00 am 7:15 pm 4:30 pm 12:10 pm 12:10 pm

100-CS 521-K 327-ALS 327-ALS 521-K 621-G 327-S 100-CS 100-CS

Course

Course# Credits Title

CIS2010CIS3210 CIS3215 CIS3730CIS3300

3.0 3.0 3.0 3.0 3.0

Intro to Info Systems Intro to Program. w. VB Intermediate Visual Program. Intro to DBMSAnalysis/Design Info System

Relational Algebra - Georgia State University Algebra.pdfIntroduction to Database Management Systems...

Documents

Transcript of Relational Algebra - Georgia State University Algebra.pdfIntroduction to Database Management Systems...