Hamish Burke | 2025-04-03

Related to: #databases

Relational Algebra

• Query language
• Introduced by Codd (1970)

Relation = A set of tuples with the same attributes (rows with the same columns)
Schema = Describes a relations attribute names and types

Operations

Selection

Ricks rows in $R$ that satisfy cond

Projection

Picks columns attrs from $R$ (and drops duplicates)

Rename

Change relation or attribute names

Questions

1. Given Relation Employee(EmpID, Name, Dept, Salary), write relational-algebra to get Name and Salary of employees in the "Sales" department

π{Name, Salary}(σ{Dept = 'Sales'}(Employee))

2. Rename employee to E and attributes EmpID -> ID, Salary -> Sal

ρ{E(Employee)}(Employee)
ρ{ID(EmpID)}(E)
ρ{Sal(Salary)}(E)

-- or

ρ{E(ID, Name, Dept, Sal)}(Employee)

3. Relation A(X, Y) and B(X, Y) share schema. Write RA to find tuples in A but not in B

π{X,Y}(A) - π{X,Y}(B)

-- As they share a schema, don't need to project

A - B

4. Given R(A,B) and S(C), show the RA for pairing every R row with every S row

π{A,B}(R) X π{C}(S)

-- No need to project (as using all attributes in both)

R x S

5. Relations Orders(OID, CustID, Total) and Customer(CustID, Name). Get OID,Name,Total for orders over $100

π{OID, Name, Total}(Customer ⋈{Total > 100} Orders)

6. Relations R(A,B,C) and S(B,D). Write RA using natural join. Then rewrite as a theta-join + projection

-- natural join
R ⋈ S

-- theta join
π{B}(R ⋈{R.B = S.B} S)

7. Enrolled(StudentID, CourseID) and Required(CourseID). Find students who've taken all required courses. Express in RA using division

π{StudentID, CourseID}(Enrolled) % π{CourseID}(Required)

8. Translate sql to RA

SELECT DiSTINCT Dept
FROM Employee
WHERE Salary BETWEEN 50000 AND 80000;

??