Relational Algebra and SQL Fundamentals Explained

Relational Algebra Operations

The Divide Operation

The Divide operation takes two relations—one binary and one unary—to build a relationship consisting of all attribute values of the binary relation that match all values of the unary relation. It is represented by the operator DIVIDEBY:

NomTablaBin DIVIDEBY NomTablaUna

The Difference Operation

The Difference operation builds a relationship consisting of all tuples (rows) of the first relation that do not appear in the second relation. It is represented by the operator MINUS:

Nom_tablaA MINUS NomTablaB

The Union Operation

The Union operation builds a relationship consisting of all tuples from the first relation and all tuples from the second relation. The requirement is that both relationships must be of the same type:

Nom_TablaA UNION Nom_tablaB

The Intersection Operation

The Intersection operation builds a new table consisting of all tuples that are present in both the first and second table:

Nom_TablaA INTERSEC Nom_TablaB

Commercial Query Languages

A commercial query language provides a user-friendly interface. The most prominent example is SQL (Structured Query Language).

The most important parts of SQL are:

• DDL (Data Definition Language): Allows for the creation of structures.
• DML (Data Manipulation Language): Allows access to structures to delete, modify, and insert data.

Basic Structure of a SQL Expression

• Select clause: Used to list the attributes desired in the query result.
• From clause: Lists the relationships to be considered when evaluating the expression.
• Where clause: Defines the conditions to which a query may be subject.

The typical SQL query structure is as follows:

SELECT A1, A2, A3 ... An
FROM r1, r2, r3 ... rm
WHERE condition(s)

SQL Operations and Logic

SQL performs the Cartesian product of the tables involved in the FROM clause, filters them based on the WHERE condition, and projects the result using the SELECT command.

DISTINCT is a reserved word that eliminates duplicate rows in a query result.

Logical Connectors

• AND: Selects a row only if both conditions are met.
• OR: Selects rows that meet at least one of the conditions set.
• NOT: Excludes tuples that you do not wish to view.