Relational Operators

Relational Operators Lecture 7

Relational data model • relational data objects • relational operators • relational data integrity

Relational operators • preamble • why do we need relational operators? • what kind of operations do we need to perform? • how many operators would we like to have? • what do you think a “powerful set of operators” means? • procedural / declarative • relational algebra / relational calculus

Relational algebra operators • basic  primitive • 4 “set specific” operations • 4 “relation specific” operations • relational closure

“Set specific” operators • specialised for relations • type compatibility • attribute name inheritance • candidate key inheritance • they are • union • intersection • difference • Cartesian product • exemplify them with a couple of relations of your choice

“Relation specific” operators • restriction • <relation name> WHERE <condition> • projection • <relation name> [ <attr_1>, <attr_2>, …, <attr_n> ] • join • <relation name> JOIN <relation name> • difference • <relation name> DIVBY <relation name>

Restriction • conditional expressions on atomic values • primitive data types • supported in SQL • non-conditional scalar operators cannot be used in conditional expressions; however, they can be introduced via EXTEND • domains • no support provided in SQL • PostgreSQL - extendable set of operators on atomic value • atomic / non-atomic conditions • nested conditions • logical expressions

Join • natural join • theta-join • can be expressed via a Cartesian product and a restriction; how? • properties • commutative • associative

Join

Division

Relational algebra operators • closure • nested expressions • primitive set • restriction, projection, Cartesian product, union, difference

Examples

Examples • get the name of the tutors who teach at least one module • get the name position and salary of all the tutors who teach at least one module • get the name of the tutors who do not teach any module • get the name and address of all the students who take level one courses • get the name of students who take all optional modules • ... • think of other queries and devise relational algebra expressions for them

Extensions • EXTEND • you can think as allowing scalar computations in WHERE clauses • SUMMARISE • projections combined with the performance of summary calculations

Implementation • DML in SQL • relational operators can be implemented via SELECT statements • SQL provides a richer set of operations than just the set of basic relational algebra operations • e.g. sub-queries

The optimiser • optimiser • for a specific implementation, the module that ‘decides’ what is the best strategy of evaluating an expression (a query)(note that evaluation  record level operations) • example • get the addresses of all students who take the “AI” module • 3000 students; 4 modules per student, on average; some 100 students for the AI module; • ((Students JOIN Registration) WHERE Mname = “AI”) [Sname, Address] • (Students JOIN (Registration WHERE Mname = “AI”)) [Sname, Address]

Summary • relational algebra • operators (set and relation specific) • relational closure • extensions • implementation - SQL • the optimiser • relational calculus

Relational Operators