3.9

Relational Database Concepts

Tables and Logical Expressions

• Tables in a relational database are theoretical structures that store data and are primarily of theoretical interest.

• A basic logical expression often looks like:

  • SELECT <columns> FROM <Table> WHERE <expression>

• The SELECT operation is used to pick specific columns from the table.

  • Example Columns: Column 1, Column 2

Join Operations

• Combining two tables into one result set can be done using various join operations:

  • CROSS JOIN:

    • Syntax: SELECT FROM Table1 CROSS JOIN Table2

    • This is essentially a Cartesian product of both tables, multiplying rows from both.

• INNER JOIN:

  • Combines rows that match based on specified conditions.

  • Syntax: SELECT FROM Table1 INNER JOIN Table2 ON <expression>

  • The join expression here can often be represented with the Greek letter theta (θ).

Types of Joins

• Left Join, Right Join, Full Join:

  • Variants of joins that determine which records are included based on the presence of rows in one or both tables.

Set Operations

Union and Intersect Operations

• UNION:

  • Combines all unique rows from two compatible tables into a single table.

  • Syntax: SELECT FROM Table1 UNION SELECT FROM Table2

• INTERSECT:

  • Returns only the rows that exist in both tables.

  • Syntax: SELECT FROM Table1 INTERSECT SELECT FROM Table2

Difference Operation

• MINUS:

  • Removes rows in one table that also appear in another compatible table.

  • Syntax: SELECT FROM Table1 MINUS SELECT FROM Table2

Aggregate Functions

Grouping Data

• Aggregate operations compute summary values across groups of records. Common functions include SUM, AVG, MIN, MAX.

• Syntax for grouping:

  • SELECT <Function> FROM <Table> GROUP BY <GroupColumn>

  • If the GroupColumn is omitted, the result is a summary of all records.

Query Optimization

• The process by which a SQL query is translated into a sequence of low-level database actions known as a query execution plan:

  1. Convert the query to a relational algebra expression.

  2. Generate equivalent expressions for optimization.

  3. Estimate the cost for each operation in the expressions.

  4. Identify the optimal expression with the least total cost.

  5. Transform the optimal expression into an execution plan.

• Cost estimation typically encompasses storage media access and computation time.