Understanding Database 3 - Relational Algebra (관계대수)

Relational Algebra: Formalisation of Operations(SELECT, JOIN, etc.), Uses operations on relations(set of tuples), Allows us to understand and create more efficient queries 

 

Unary Operations

SELECT Operation [ σ<cond> (r) ]

: Select the rows of the relation where the condition is true

σ salary >10000 (Employee)

: It is commutative, which means it can be applied in any order. Plus, it can be cascaded. 

σ <condition1> (σ <condition2> (Employee))= σ <condition2> (σ <condition1>(Employee))
σ <condition1 ^ condition2>(Employee)

Project Operation[ ∏ <A1, A2,...> (r) ]

: Select the columns of the relation that match the attributes given, and no duplicated values (same as SELECT DISTINCT) 

: Below will return only ID and Salary columns

∏ ID, salary (Emplyee)

Combining Project and Select

: Nesting is possible [ ∏ id ( σ age<30 (Employee)) ] 

 

Assignment [ ← ] 

test ← σ age<30 (Employee): simple assignment 

test(number, name, pay) ←  ∏  ID, the_name, salary (Employee) : Renaming possible

 

Rename Operator [ ρ ]

ρ test (Employee): Employee-> test

ρ (name, number, payment...) (Employee): change the attributes name

ρ test(name, number, payment,...) (Employee): change relation name and attribute name 

 

Binary Operations

Union [ ⋃ ]

All from both relations, removing duplicates must be type-compatible

The exact number of attributes, positions and domains (data type) should be the same

 

Intersect [⋂]

items matching both R1 and R2, removing duplicates

 

Minus[ - ]

Removes items from R1 that are in R2

 

Cross-product(Cartesian product) [ X ] 

Pairs each row from R1 with each row from R2

 

Conditional join [ R1⋈ age <30 R2 ]

Joins R1 and R2, where the condition is true (Same as cross product, but there is a condition)

-> R1⋈<cond> R2 ≡  σ<cond> (R1XR2) 

 

Equi Join [R1 ⋈ age R2 ]

Returns only R1 and R2' age value is same (R1.age = R2.age)