CS-GY 6083: Principles of Database Systems
1.0.0
1.0.0
  • Introduction
  • DBMS Basics
    • Introduction to DBMS
    • Why use a DBMS instead of a File System?
    • Levels of Abstraction
    • Instances and Schemas
  • Data Models
    • Introduction to Data Models
    • Database Languages
    • Database Design
  • DBMS Internals
    • Introduction to DBMS Internals
    • Storage Manager
    • Query Processor
    • Transaction Management
    • Database Users
    • Database Architecture
  • DBMS History
  • Some Popular Database Systems
  • OLTP, OLAP, and Data Mining
  • Databases vs. Information Retrieval
  • The Entity-Relationship Model - Details
    • Introduction
    • Cardinality Constraints
    • ER Diagram Components
    • ER Diagram to Relational Schema
    • Design Issues
  • The Relational Model - Details
    • Relations
    • Keys
    • Relational Query Languages
      • Relational Algebra
      • Relational Calculus
      • Relative Expressive Power
    • Relational Operators
  • SQL
    • Introduction to SQL
    • Domain Types in SQL
    • DDL Commands
      • Creating a Table
      • Alter and Drop
    • DML Commands
      • Basic Query Structure
      • Select
      • From
      • Where
      • Joins
      • Rename
      • String Operations
      • Ordering
      • Set Operations
      • Group By and Having
      • Nested Subqueries
      • Test for Empty Relations
      • Test for Duplicate Tuples
      • Derived Relations
      • With
      • Database Modification
    • Intermediate SQL
      • Joins Revisited
      • Views
      • Transactions
      • Integrity Constraints
      • More SQL Data Types and Schemas
        • Other Features
      • Authorization
    • Advanced SQL
      • Accessing SQL From a Programming Language
        • ODBC and JDBC
        • Embedded SQL
        • PHP
        • Some Security Issues
      • Accessing Metadata
      • Text Operations
        • Like
        • Contains
      • Cursors
      • Functions and Procedures
        • Procedural Constructs
        • External Language Routines
      • Triggers
      • Ranking
      • Windowing
      • OLAP
Powered by GitBook
On this page
  • Extended Relational Algebra Operations
  • Generalized Projection
  • Aggregate Functions
  • Multiset Relational Algebra

Was this helpful?

  1. The Relational Model - Details
  2. Relational Query Languages

Relational Algebra

Relational Algebra is a procedural language based on the previously discussed relational operators, mainly:

  • selection σ\sigmaσ

  • projection Π\PiΠ

  • union ∪\cup∪

  • set difference -

  • cartesian product ×\times×

  • rename ρ\rhoρ

Extended Relational Algebra Operations

There are mainly two kinds of extended relational algebra operations:

  • generalized projection

  • aggregate functions

Generalized Projection

This is an extension of the projection operation.

It basically allows us to use arithmetic functions in the projection list.

ΠF−1,F2,...,Fn(E)\Pi _{F-1, F_2, ..., F_n} (E)ΠF−1,F2​,...,Fn​​(E)

where F1,F2,...,FnF_1, F_2, ..., F_nF1​,F2​,...,Fn​ are arithmetic operations involving constants and the attributes in the schema of E.

Ex. ΠID,name,department,salary/12(instructor)\Pi _{ID, name, department, salary/12} (instructor)ΠID,name,department,salary/12​(instructor)

Note the "/12" in the projection list.

Aggregate Functions

Aggregation functions take a collection of values and return a single value as the result.

  • avg: average value

  • min: minimum value

  • max: maximum value

  • sum: sum of values

  • count: number of values

are some aggregate functions.

The aggregate operation in relational algebra is denoted as follows:

where the Gs are a list of attributes on which to group (can be empty), the Fs are aggregate functions and the As are attribute names.

The result of the aggregation operation doesn't have a name. We can name the result using the rename operator or as follows:

The above will output the average salary of instructors grouped by department.

Multiset Relational Algebra

Pure relational algebra removes all duplicates, however, multiset relational algebra retains duplicates.

The following explains how the operations differ in multiset relational algebra:

  • selection: the result has as many duplicates of a tuple as in the input, if the tuple satisfies the selection

  • projection: the result will contain one tuple per input tuple, even if it is a duplicate

  • cross product: if there are m copies of t1 in r, and n copies of t2 in s, there will be m x n copies of t1.t2 in r ×\times× s

  • union: will have m + n copies

  • intersection: will have min(m, n) copies

  • difference: will have min(0, m – n) copies

PreviousRelational Query LanguagesNextRelational Calculus

Last updated 5 years ago

Was this helpful?