Relational Query Languages
Query languages let users request information from a database. In the relational world, the theoretical foundations are **relational algebra (procedural)** and **relational calculus (declarative)**, while SQL is the practical language used in real DBMS products.
The Basics
From the master notes:
- Procedural languages: specify what you want and how to obtain it (Relational Algebra).
- Non-procedural / declarative languages: specify what you want; the system finds how (Relational Calculus, SQL style).
Relational algebra is an algebra where operands are relations and operators create new relations (closure property). This is why it underpins SQL optimization and query execution planning.
Technical Details
Relational model (quick base)
- A relation can be viewed as a set of tuples over domains (D1 × D2 × … × Dn).
- Attribute values should be atomic (1NF idea); NULL values exist but complicate theory.
Relational algebra operations (master list)
Basic operations include: Select (σ), Project (π), Union (∪), Set Difference (−), Cartesian Product (×) and Rename (ρ).
Selection (σ)
Filters tuples that satisfy a predicate.
Example idea: σ(branch='Perryridge')(loan)
Projection (π)
Keeps only selected attributes and removes duplicates.
Example idea: π(account_number, balance)(account)
Composition
Operations can be combined (e.g., selection after product to form a join-like result).
Tuple and Domain Relational Calculus
- Tuple relational calculus (TRC): describes the set of tuples that satisfy a predicate.
- Domain relational calculus (DRC): uses variables over domains (attribute values).
SQL is closest to tuple relational calculus (declarative), but practical DBMS also perform procedural planning internally.
Open-source vs commercial DBMS
- Open source: PostgreSQL, MySQL, MariaDB, SQLite
- Commercial: Oracle, Microsoft SQL Server, IBM DB2
(Choice depends on scale, licensing, tooling, and enterprise features.)
Examples
Example 1: Names of students enrolled in DBMS
Relational Algebra:
π(Name)(σ(CourseName='DBMS')(Student ⋈ Enrollment ⋈ Course))
SQL (conceptual form):
SELECT DISTINCT S.Name
FROM Student S
JOIN Enrollment E ON S.SID = E.SID
JOIN Course C ON E.CID = C.CID
WHERE C.CourseName = 'DBMS';
Example 2: Students with no enrollments
Relational Algebra:
π(SID)(Student) − π(SID)(Enrollment)
Example 3: Why union-compatibility matters
Union requires same number of attributes and compatible domains.
Example idea: π(customer_name)(depositor) ∪ π(customer_name)(borrower)
Real-World Use
Practical tips
- SQL engines translate queries into internal algebra/plan trees, then optimize join order and access paths.
- Learning relational algebra makes it easier to understand why indexes and join order affect performance.
Recommended practice
- Write a SQL query → mentally map to σ/π/⋈ operations → simplify by pushing σ early.
For exams
Expected exam questions
- Define relational algebra. List basic operations and their purpose.
- Explain selection and projection with examples.
- What is union compatibility?
- Differentiate relational algebra vs tuple/domain relational calculus.
- Convert a SQL query to a relational algebra expression (and vice versa).
- Compare open-source and commercial DBMS with examples.
Key points
- Relational algebra is procedural and forms the basis of query processing/optimization.
- Selection (σ) reduces rows; projection (π) reduces columns and duplicates.
- Union/difference require compatible schemas.
- SQL is practical and largely declarative, but DBMS executes it via optimized internal plans.