Week 3 - Relational Algebra Flashcards
What can RA be thought of as?
An intermediary language between SQL and maths/semantics.
Principle relation operation: Select
Pick rows from a relation by some condition
Principle relation operation: Project
Pick columns by name
Principle relation operation: Join
Connect two relations usually by a Foreign Key
Set operation: Union
Make the table containing all the rows of two relations, removing duplicates. The two relations must be union compatible (i.e. have the same number of attributes drawn from the same domain, but maybe having different names)
Set operation: Intersection
Pick the rows which are common to two relations
Set operation: Difference
Pick the rows which are in one table but not another
Set operation: Cartesian Product
Pair off each of the tuples in one relation with those in another - creating a double sized row for each pair.
DEPARTMENT (Dnum, Dname, Manager)
How do you indicate that an attribute is a primary/foreign key?
Underlining it means it is a primary key.
Italicizing it means it is a foreign key.
Translate the SQL into RA:
SELECT * FROM employee WHERE CITY=‘London’
LOCALS := σ(EMPLOYEE, CITY = “LONDON”)
Translate the RA into SQL:
LONDONorYOUNGRICH :=σ(EMPLOYEE,
CITY=“LONDON” or(SALARY>100KandAGE<30))
SELECT * FROM employee WHERE CITY=‘LONDON’ OR (SALARY > 100000 AND AGE < 30]
Translate the RA into SQL:
Π(EMPLOYEE, (SEX, SALARY))
SELECT SEX, SALARY FROM EMPLOYEE
Explain what this RA does:
Π(σ(EMPLOYEE, CITY = “LONDON”), (NAME,NI))
It performs a selection, returning the rows in the employee table where the city attribute holds the value ‘LONDON’. After that, it performs a projection using those rows, where it returns the NAME and NI columns of those rows.
Give an example of a join in RA
⨂(EMPLOYEE, Dept, DEPARTMENT, DNum)
Give an example of a union operation in RA
LondonOrRich := σ(EMPLOYEE, CITY = “LONDON”) U σ(EMPLOYEE, SALARY > 100K)
