Lecture 3 - Relational Algebra

Lecture 3 - Relational Algebra#

Announcements#

  1. No class this monday.

  2. Lecture Exercises 3 and 4 to be out at 4pm today on Submitty, due at midnight on monday.

  3. Homework #1 is to be out later today or tomorrow, due on Monday 9/14

  4. Lectures are being recorded and linked to course website: https://www.cs.rpi.edu/~sibel/csci4380/fall2026/index.html

  5. Please continue to send me your accommodations by email. If you are waiting for accommodations, you can email me to let me know as well.

Today’s topics#

  • Relational Algebra

  • Functional Dependencies

Example Database#

  • CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG, IsSelfD, isAWD)

  • Cars(License, State, CarID, Color, Mileage, VIN)

  • StudentCars(RIN, License, State)

  • FacultyCars(RIN, License, State)

CarTypes

CarId

Make

Model

Year

Pkgid

HP

Doors

Range

IsSelfD

isAWD

1

Kia

EV9

2024

Wind

215

4

304

No

Yes

2

Kia

EV9

2025

GT-Line

379

4

270

No

Yes

3

Rivian

R1T

2025

Adventure

533

4

420

No

Yes

4

MG

ZSEV

2025

SUV

140

5

231

No

No

5

MG

MG4

2025

SE

170

5

323

No

No

6

Tesla

S

2024

Luxe

1020

4

410

Yes

Yes

7

Tesla

3

2024

Performance

510

4

300

Yes

Yes

8

Tesla

3

2025

Standard

346

4

357

Yes

Yes

Cars

License

State

CarId

Color

Mileage

VIN

ASD423

NY

1

Red

10,000

123124124

RFW424

NY

4

Gray

20,000

124214576

FTH356

IL

1

Blue

5,000

654364522

EVT352

CA

3

Green

100,000

547756665

AGE423

NY

6

Red

20,000

745457457

EEV245

MA

2

White

50,000

457434674

EGY356

MA

2

Blue

2,000

657453463

DYH456

NY

7

White

5,000

364327345

JHY452

NY

7

Gray

1,000

346456346

StudentCars

RIN

License

State

R1

ASD423

NY

R2

RFW424

NY

R3

FTH356

IL

R4

DYH456

NY

R5

ASD423

NY

R6

JHY452

NY

FacultyCars

RIN

License

State

R4

DYH456

NY

R7

EVT352

CA

R8

AGE423

NY

R9

JHY452

NY

R10

FREEHV

GA

Relational Algebra#

Given a database containing a set of relations, relational algebra operations define new relations based on the existing ones.

Each relational algebra operator takes as input one or two relations, and returns a new relation.

  • SELECTION: Given a relation R and a Boolean condition C containing attributes in R, selection is written by

    \[\sigma_C (R)\]
    (OR SELECT_C ®) returns the set of all tuples in R that satisfy the condition C.

SELECT_C ® = {t | t is a tuple in R and t satistifies condition C}

  • Selection returns a relation with the same data model (schema), but a subset of the tuples in that relation.

  • Projection: Given a relation R and a subset X of attributes in R, the projection of R into X written as

    \[\pi_X(R)\]
    (or PROJECT_X® and returns a new relation that has all the tuples in R but the tuples have values for only the attributes X.

PROJECT_X® = {t’ | t is a tuple in R and t’ contains only the attributes X of t}

  • Projection will return all the tuples but a subset of the attributes, and may remove duplicates if any are created.

  • Rename: Rename written as R1(…) = R, creates an alias for R and renames all attributes in R. All attributes in R must be listed in R1 with their name (changed or unchanged).

R1 = Cars ** R1 is an alias for Cars

R2(L, S, CarId, Color, Mileage, VIN2) = Cars

  • Set compatibility: Two relations R and S are set compatible if they have the same schema (same attributes and the same names)

  • Set Union Given two relations R and S that are set compatible, the union of R and S is the set of all tuples that are either in R or in S or in both. R UNION S or

    \[R\cup S\]

  • Set Intersection Given two relations R and S that are set compatible, the intersection of R and S is the set of all tuples that are in R and in S. R INTERSECT S or

    \[R\cap S\]

  • Set Differences Given two relations R and S that are set compatible, the set difference R-S is the set of all tuples in R that are NOT in S.

\[R\cap S = (R\cup S) - ((R-S) \cup (S-R))\]

  • Cartesian Product Given two relations R and S that have no attributes in common. The cartesian product RxS is a new relation such that

    • RxS has all the attributes in R and in S

    • RxS = { r.s | r is a tuple in R, and s is a tuple in S, and r.s is a tuple containing all the attributes values from R and S}

R A B
a1 b1
a1 b2
a2 b3

S

C D
c1 d1
c2 d2

Result = RxS

A B C D
a1 b1 c1 d1
a1 b1 c2 d2
a1 b2 c1 d1
a1 b2 c2 d2
a2 b3 c1 d1
a2 b3 c2 d2

-JOIN Given two relations R and S that have no attributes in common, and C is a join condition, the join of R and S (given by R JOIN_© S or

\[R\bowtie_C S\]
is given as following:

R JOIN_© S = SELECT_C (R x S)

  • A join condition is a Boolean condition involving attributes from two different relations

R(A,B) S(C,D)

A=5 selection condition
A=C join condition A=C and B =D join condition

  • CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG, IsSelfD, isAWD)

  • Cars(License, State, CarID, Color, Mileage, VIN)

  • StudentCars(RIN, License, State)

  • FacultyCars(RIN, License, State)

  • Find all cars that are not red, and return license and state.

R1 = SELECT_(Color <> ‘Red’) (Cars)

Result = PROJECT_(License, State) (R1)

  • Find all students who do not own any cars registered in ‘NY. Return their RIN.

StudentCars

RIN License State 1 ABC NY 2 CDE MD 3 FGH CA 3 HJK NY

(All Students who own a car) - (Students who own a car from NY)

R1 = SELECT_(state=NY) (StudentCars)

Result = PROJECT_(RIN) (StudentCars) - PROJECT_(RIN) (R1)

  1. Find the license and state of all cars owned by a faculty and a student both

R1 = PROJECT_(License, State) (StudentCars)

R2 = PROJECT_(License, State) (FacultyCars)

Result = R1 intersect R2

  1. ** Special challenge: exclude cases where the student and the faculty are the same (i.e. same RIN)**

R1 = PROJECT_(License, State) (StudentCars)

R2 = PROJECT_(License, State) (FacultyCars)

R3 = PROJECT_(License, State) (StudentCars INTERSECT FacultyCars)

Result = (R1 intersect R2) - R3

StudentCars

RIN License State
1 ABC NY
2 CDE MD
3 FGH CA
3 HJK NY

FacultyCars
2 CDE MD
4 ABC NY

  1. Find license and state of all cars with at least 50,000 mileage owned by a faculty

(Cars with 50K mileage) intersect (Cars owned by faculty)

R1 = PROJECT_(License, State) (Select_(Mileage>=50,000) Cars)

R2 = PROJECT_(License, State) (FacultyCars)

Result = R1 intersect R2

  1. Find license and state of all cars that are not registered to a student

R1 = PROJECT_(License, State) (Cars)

R2 = R1 = PROJECT_(License, State) (StudentCars)

Result = R1 - R2

Cartesian product examples

FC(RIN, L1, S1) = FacultyCars

R1 = CarsxFC

(R1 schema RIN, L1, S1, License, State, CarID, Color, Mileage, VIN)

R2 = SELECT_(License=L1 and State=S1) (R1)

  • Find the RIN of all faculty who own a red car.

Result = PROJECT_(RIN) = SELECT_(Color=Red) (R2)

  • Find the RIN of all faculty who do not own a car with less than 10K mileage

R3 = PROJECT_(RIN) (SELECT_(Mileage<=10K) (R2))

All faculty who own a car with less than 10K mileage

Result = (PROJECT_(RIN) (FacultyCars) - R3

  • Find RIN of all students who own a KIA.

R1(C1) = PROJECT_(CarId) (SELECT_(Make=‘Kia’) (CarTypes))

R1 is all KIA models.

R2 = CarsxR1

R3(L1, S1) = PROJECT_(License, State) (SELECT_(CarId = C1) (R2))

R3 is all cars that are a Kia model.

R4 = R3xStudentsCars

R5 = SELECT_(License=L1 and State=S1) (R4)

R5 is all students who own a Kia.

Result = PROJECT_(RIN) (R5)

Join examples

  • Find RIN of all students who own a KIA.

R1(C1) = PROJECT_(CarId) (SELECT_(Make=‘Kia’) (CarTypes))

R2 = Cars JOIN_(CarId = C1) R1

R3(L1, S1) = PROJECT_(License, State) (R2)

R4 = R3 JOIN_(L1=License and S1=State) StudentsCars

Result = PROJECT_(RIN) (R4)

  • Return RIN all faculty who own a Red car registered in NY.

R1(RIN, L1, S1) = FacultyCars
R2 = R1 JOIN_(L1=License and S1=State) Cars
R3 = PROJECT_(RIN) = SELECT_(Color=Red AND State=NY) (R2)

  • Find all students who own a car made in 2026.

R1(RIN, L1, S1) = StudentCars
R2 = R1 join_(L1=License and S1=State) Cars
R3(C1) = PROJECT_(CarId) ( SELECT_(Year=2026) = CarTypes)
R4 = R3 join_(C1=CarId) R2
Result = Project_(RIN) (R4)

  • Find all car makes with at least two models in the database.

R1 = PROJECT_(make, model) (CarTypes)
R2(make2, model2) = R2
R3 = R1 join_(make=make2 and model<>model2) R2
Result = PROJECT_(make) (R3)

Contents