Relational Algebra

Unit 1 – Overview and Architecture of Database Systems

  • Topics Covered:

    • Overview of Database Management Systems (DBMS)

    • Architecture of database systems

    • Relational query languages including relational algebra, tuple and domain relational calculus, SQL3, DDL, and DML constructs.

    • Examples of DBMS: MYSQL, ORACLE, DB2, SQL Server.

Unit 2 – Introduction to Relational Model

Relational Query Languages

  • Definition: Relational query languages utilize relational algebra to interpret user requests and instruct the DBMS to execute these requests.

  • Types:

    • Procedural Query Languages: Require the user to specify how to retrieve data (e.g. get_CGPA procedure).

    • Non-Procedural Query Languages: Specify what data to retrieve without detailing how (e.g. SELECT statements).

Relational Algebra

  • Definition: A procedural query language that operates on tables and produces new tables as results. It allows for operations like selection and projection and can be unary (on one table) or binary (on two tables).

  • Characteristics:

    • Operates on entire tables, eliminating the need for iterative row access.

    • Specifies "how" and "what" to retrieve.

Operators in Relational Algebra

  • Select (σ): Retrieves rows based on a specified condition.

  • Project (π): Retrieves specific columns from a table and removes duplicates.

  • Join Operations: Combines records from multiple tables based on related columns:

    • Natural Join: Combines data based on common attributes and eliminates duplicate attributes.

    • Outer Joins: Retrieve data from one table even when there is no matching data in the other, with NULLs where matches do not exist.

      • Types include Left Outer Join, Right Outer Join, and Full Outer Join.

Set Operators

  • Combine results from two or more queries:

    • Union (U): Combines tuples from two relations, eliminating duplicates.

    • Intersection (∩): Retrieves tuples common to both relations.

    • Difference (−): Retrieves tuples in the first relation but not in the second.

Division Operator

  • Definition: Used to find tuples in one relation that are associated with all tuples in another relation.

  • Output: Attributes of the first relation minus those of the second.

Rename Operator (ρ)

  • Functionality: Changes the name of attributes or entire relations.

  • Examples: Can use to clarify outputs or adjust relation formats for clearer queries.

Aggregate Functions

  • Common Functions:

    • Sum, Max, Min, Avg, Count: Perform calculations across columns and return single output values.

Assignment Operator

  • Facilitates relational algebra expression assignment to temporary variable names, allowing complex queries to be structured clearly.

Equivalent Queries

  • Different approaches may yield the same result; these can be expressed using varying query structures but will return identical outputs.