Relational Algebra and Joins

Relational Algebra

Relational Algebra

• Theoretical basis for relational databases
• Formal set of operations on relations
• projection
• selection
• cartesian product
• set operations
• joins
• Operations result in relations

Projection

• Takes a vertical subset of a relation by identifying the attributes to include

𝚷_{col1, col2, …, coln}(R)

​

• The operation removes any duplicates in the result relation

Projection

𝚷_{Name, DoB}(MUSICIAN)

Selection

• Includes only the tuples in R that satisfy the given condition (predicate)

𝛔_predicate(R)

​

• The operation removes any duplicates in the result relation

Selection

𝛔_{DoB < 01/01/1970} (MUSICIAN)

Combining relations

• Set operations:
• Union R ∪ S
• Intersection R ∩ S
• Difference R – S
• Cartesian product R 𝚾 S
• Result relation contains the concatenation of every tuple in R with every tuple in S
• Joins
• Combine relations based on “matching” values

SQL

• Structured Query Language
• Implementation of operations from relational algebra on relational database tables
• and much more

Joins

SELECT statement

• What we’ve seen so far

SELECT column(s)

FROM table(s)

WHERE condition(s)

GROUP BY column(s)

ORDER BY column(s)

LIMIT number

Joins

• Joining tables combines the columns into a bigger table
• A join condition determines which values must match in each row in the joined table
• Ex: join the COUNTRY and CITY tables on CountryCode
• COUNTRY has a 1-N relationship with CITY
• Code is a PK in COUNTRY
• CountryCode is a FK in CITY

SELECT *

FROM country

JOIN city ON code = countrycode

ORDER BY code;

JOIN syntax

• Previous examples

SELECT city.Name, country.Name, Region

FROM country, city

WHERE code = countrycode;�

​

SELECT *

FROM title

JOIN aka USING (tid)

WHERE title = 'Moana'

AND type = 'movie';

JOIN syntax

• JOIN .. ON syntax is preferable, because ON conditions are simply part of the database design (PK/FK relationships) and not the most interesting part of the query
• JOIN .. USING depends on the PK and FK having the same name

​

​

Cross product

• If you join tables without a join condition
• Every row from one table is combined with every row from the other table
• Also known as a Cartesian product
• This query returns 974881 rows (239 countries x 4079 cities):

SELECT city.Name, country.Name, Region

FROM country, city

order by countrycode;

• The resulting data obviously makes no sense
• Another reason to prefer the JOIN … ON syntax

COUNTRY and CITY joined with no join condition

Joining more than 2 tables

• Show the city name, country name and all languages spoken in countries beginning with the letter ‘C’
• Must join all 3 tables

​

SELECT city.name, country.name, countrylanguage.language

FROM country

JOIN city ON code = city.countrycode

JOIN countrylanguage ON code = countrylanguage.countrycode

WHERE country.name LIKE 'C%';

​

Joining more than 2 tables

SELECT city.name, country.name, countrylanguage.language

FROM country

JOIN city ON code = city.countrycode

JOIN countrylanguage ON code = countrylanguage.countrycode

WHERE country.name LIKE 'C%';

​

​

• Note how the highlighted text creates a table, which is then joined with COUNTRYLANGUAGE

Aliasing using AS

​

SELECT cy.name as 'City Name', cn.name, cl.language

FROM country as cn

JOIN city as cy ON cn.code = cy.countrycode

JOIN countrylanguage cl ON cn.code = cl.countrycode

WHERE cn.name LIKE 'C%';

​

• Alias columns and tables
• AS is optional