Announcements#

Lecture exercises 2 and 3 to be graded soon!
Lecture exercise 4 to be released later today (or tomorrow morning)
Hw#1 to be released soon, due next week

Relational algebra operators (recap)#

Basic operators#

Selection: \(\sigma_{C}(R)\) (or SELECT_C ( R ) )
Projection: \(\Pi_{A_1,\ldots,A_n} (R)\) (or PROJECT_C ( R ) )
Rename: X(A1,A2,…,An) = Y
Set union: \(R \cup S\) or (or R UNION S)
Set difference: \(R - S\)
Cartesian Product: \(R \times S\)

Derived operators#

Set intersection: \(R \cap S\) or (or R INTERSECT S)
\[ R \,\cap\, S = (R\,\cup\,S)- ((R-S)\,\cup\,(S-R)) \]
Theta_join: \(R \bowtie_C S\) (or R JOIN_C S)

\[R \bowtie_C S = \sigma_{C} (R \times S)\]

i.e. R join_C S = select_C (R x S)

Natural Join \(R \bowtie S\), , R JOIN S, or R * S

Join R and S on the equality of the attributes in common

If R(A,B) and S(B,C) then T=R*S has schema T(A,B,C) and it is computed as follows: S1(B1,C) = S T = Project_(A,B,C) ( R join_(B=B1) S1 )

If R(A,B) and S(A,B,C) then T=R*S has schema T(A,B,C) and it is computed as follows: S1(A1,B1,C) = S T = Project_(A,B,C) ( R join_(A=A1 and B=B1) S1 )

Note:

Set operations are only defined if the input relations have the same schema
Cartesian product requires the input relations to have completely different schema (i.e. no attributes in common)
A join condition C should only contain comparisons of attributes from the two relations, otherwise it is a selection condition

r = [ [1,2], [3,4], [5,6], [7,8] ]
s = [ ['b','c','d'], ['e','f','g'], ['h','e','f' ]]

cart = []
for val1 in r:
    for val2 in s:
        cart += [val1+val2]

print(cart)        

[[1, 2, 'b', 'c', 'd'], [1, 2, 'e', 'f', 'g'], [1, 2, 'h', 'e', 'f'], [3, 4, 'b', 'c', 'd'], [3, 4, 'e', 'f', 'g'], [3, 4, 'h', 'e', 'f'], [5, 6, 'b', 'c', 'd'], [5, 6, 'e', 'f', 'g'], [5, 6, 'h', 'e', 'f'], [7, 8, 'b', 'c', 'd'], [7, 8, 'e', 'f', 'g'], [7, 8, 'h', 'e', 'f']]

##cars
r = [ ['asd423','ny',1,'Red'],['rfw424','ny',4,'gray']]
##studentcars
s = [ ['r1','asd423','ny',1,'Red'], ['r2','asd423','ny',1,'Red'],['r3','rfw424','ny',4,'gray']]

cart = []
for val1 in r:
    for val2 in s:
        cart += [val1+val2]

print('R')
for val in r:
    print(val)
print('S')
for val in s:
    print(val)
print('RxS')    
for val in cart:
   print(val)

R
['asd423', 'ny', 1, 'Red']
['rfw424', 'ny', 4, 'gray']
S
['r1', 'asd423', 'ny', 1, 'Red']
['r2', 'asd423', 'ny', 1, 'Red']
['r3', 'rfw424', 'ny', 4, 'gray']
RxS
['asd423', 'ny', 1, 'Red', 'r1', 'asd423', 'ny', 1, 'Red']
['asd423', 'ny', 1, 'Red', 'r2', 'asd423', 'ny', 1, 'Red']
['asd423', 'ny', 1, 'Red', 'r3', 'rfw424', 'ny', 4, 'gray']
['rfw424', 'ny', 4, 'gray', 'r1', 'asd423', 'ny', 1, 'Red']
['rfw424', 'ny', 4, 'gray', 'r2', 'asd423', 'ny', 1, 'Red']
['rfw424', 'ny', 4, 'gray', 'r3', 'rfw424', 'ny', 4, 'gray']

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)

Key of each relation is underlined

Example Queries#

Return the RIN of all students who own a red car that a faculty also owns
Return the make of all cars driven by students
Return the make of all cars on campus
Return the make of all cars that no on campus owns
Find the RIN of all students who own a Kia with at least 20,000 miles and is not red
Find the RIN of all faculty or students who own a Kia
Find the state(s) in which a Tesla owned by someone on campus is registered
Find cars owned by two different students

Return the make of all cars driven by students

R1(RIN, L1, S1) = StudentCars
R2(L2,S2, CarId2) = Project_(License, State, CarId) Cars
R3 = R1 x R2
R4 = SELECT_(L1=L2 and S1=S2) (R3)
R5 = R4 x CarTypes
R6 = SELECT_(CarId2=CarId) (R5)
Result = Project_(Make) (R6)

Alternate solution:

R1(RIN, L1, S1) = StudentCars
R2(L2,S2, CarId2) = Project_(License, State, CarId) Cars
R4 = R1 JOIN_(L1=L2 and S1=S2) R2
R6 = R4 JOIN_(CarId2=CarId) CarTypes

Result = Project_(Make) (R6)

Alternate solution

Result = Project_(Make) (CarTypes * Cars * StudentCars)

Join is an operation between intersection and Cartesian products)!

Given: R(A,B) and S(A,B)

S1(A1,B1) = S R1 = R join_(A=A1 and B=B1) S1 Result = Project_(A,B) (R1) #identical to R intersect S

Given R(A,B) and T(C,D)

Result = R join_(true) T #identical to Cartesian product, R x T

Return the make of all cars on campus

R1(L1,S1) = (Project_(License, State) StudentCars) union (Project_(License, State) FacultyCars)
R1(L1,S1) = Project_(License, State) (StudentCars union FacultyCars)
R2(CarId1) = Project_(CarId) ( R1 join_(L1= License and S1=State) Cars )
Result = Project_(Make) R2 join_(CarId1=CarId) CarTypes

Alternate solution

R1 = (Project_(License, State) StudentCars) union (Project_(License, State) FacultyCars)
Result = Project_(Make) (R1 * Cars * CarTypes)

Return the RIN of all students who own a red car that a faculty also owns

#cars owned by faculty and student
R1 = Project_(License, State) (StudentCars) intersect Project_(License, State) (FacultyCars)
R2 = SELECT_(Color=‘red’)(R1 * Cars)
R3 = Project_(RIN) (R2 * StudentCars)

Return the make of all cars that no one on campus owns

##All cars someone owns
R1 = Project_(License, State) (StudentCars) union Project_(License, State) (FacultyCars)
R2 = (Project_(License, State) Cars) - R1 ##cars no one owns
R2 = Cars - (R1*Cars) ##cars no one owns
Result = Project_(Make) = R2 * CarTypes

Find the RIN of all students who own a Kia with at least 20,000 miles and is not red

R1 = Select_(Make=‘Kia’ and color <>‘red’ and mileage>=20000) (CarTypes * Cars * StudentCars)
Result = Project_(RIN) (R1)

Find cars owned by at least two different students, return license, state

R1(RIN1, L1, S1) = StudentCars
R2 = StudentCars join_(L1=License and S1=State and RIN1 <> RIN) R1
Result = Project_(License, State) (R2)

R1 c1
R2 c1
R3 c1
R4 c2

R1 c1 R1 c1
R1 c1 R2 c1 *
R1 c1 R3 c1 *
R1 c1 R4 c2
R2 c1 R1 c1 *
R2 c1 R2 c1
R2 c1 R3 c1 *
R2 c1 R4 c2
R3 c1
R4 c2