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
  • Relational Operators
  • More about null values

Was this helpful?

  1. The Relational Model - Details

Relational Query Languages

PreviousKeysNextRelational Algebra

Last updated 5 years ago

Was this helpful?

A query language is a language using which a user can request data from the database.

Query Languages can be procedural (the user instructs the system to perform a set of operations on the database) or non-procedural (the user specifies only what information is needed without specifying how to obtain that information).

We will discuss the following relational query languages:

  • Relational Algebra (procedural)

  • Tuple Relational Calculus (non-procedural)

  • Domain Relational Calculus (non-procedural)

Relational Operators

A relational operator takes as input one or more relations and outputs a new relation.

  • selection of tuples

    The selection operator σ\sigmaσ returns the rows of a relation that satisfy a selection predicate.

    Ex. σA=B and C>5(r)\sigma _{A=B\, and\, C>5}(r)σA=BandC>5​(r)

  • selection of columns (projection)

    The projection operator is denoted by Π\PiΠ

    Ex. ΠA, C(r)\Pi _{A,\, C} (r)ΠA,C​(r)

    Note that duplicate tuples will be eliminated automatically.

  • join (cartesian product)

    This operation is applied on two relations and the output is another relation. The join operator (×\times×) basically merges pairs of tuples, one from each relation, to create a new relation.

    Ex. If r and s are two relations , r×sr\times sr×s will be

  • union

    The union operation (∪\cup∪) can be performed on two relations that have the same attributes.

    Ex. If r and s are two relations , r∪sr\cup sr∪s will be

  • set difference

    The set difference (-) operation can be performed on two relations that have the same attributes. The result will contain tuples that are in the first relation but not those that are in the second relation.

    Ex. If r and s are two relations , r-s will be

  • intersection The intersection operation (∩\cap∩) can be performed on two relations that have the same attributes. The result will contain tuples that are both in the first and second relations. Ex. If r and s are two relations , r∩sr\cap sr∩s will be Note that r∩s=r−(r−s)r\cap s = r-(r-s)r∩s=r−(r−s)

  • natural join The natural join (⋈\bowtie⋈) of r and s results in the set of all combinations of tuples in r and s that are equal on their common attribute names. If r and s are two relations , r⋈sr\bowtie sr⋈s will be The result of the natural join operation is usually smaller than that of the cartesian product operation. Note that natural join is associative i.e. (a⋈b)⋈c=a⋈(b⋈c)(a\bowtie b) \bowtie c = a\bowtie (b \bowtie c)(a⋈b)⋈c=a⋈(b⋈c) and commutative i.e. a⋈b=b⋈aa \bowtie b = b \bowtie aa⋈b=b⋈a

  • theta join r⋈θs=σθ(r×s)r\bowtie _\theta s = \sigma _\theta (r \times s)r⋈θ​s=σθ​(r×s)

  • outer join The outer join operation (⊐⋈\sqsupset\bowtie⊐⋈, ⋈⊏\bowtie\sqsubset⋈⊏, ⊐⋈⊏\sqsupset\bowtie\sqsubset⊐⋈⊏) ia an extension of the join (natural join) operation that avoids loss of information. It computes the join and then adds tuples from one relation that do not match tuples in the other relation to the result of the join. null is used to denote missing values in the result.

    Ex. if we have two relations instructor and teaches, given by and respectively, then the natural join (instructor⋈teachesinstructor\bowtie teachesinstructor⋈teaches) would be:

    The left outer join (instructor⊐⋈teachesinstructor \sqsupset\bowtie teachesinstructor⊐⋈teaches) would be: The right outer join (instructor⋈⊏teachesinstructor \bowtie\sqsubset teachesinstructor⋈⊏teaches) would be: and the full outer join (instructor⊐⋈⊏teachesinstructor \sqsupset\bowtie\sqsubset teachesinstructor⊐⋈⊏teaches) would be:

  • rename The rename operator (ρ\rhoρ) allows us to name, and therefore refer to, the results of a relational operation.

  • assignment The assignment operator (←\leftarrow←) lets us assign the result of an operation to a temporary relation variable.

More about null values

  • Two nulls are treated to be the same

  • The result of any arithmetic operation involving a null value is null

  • Comparisons with null values return the special truth value: unknown

  • Three-valued logic using the truth value unknown:

    • OR:

      (unknown or true) = true,

      (unknown or false) = unknown

      (unknown or unknown) = unknown

    • AND:

      (true and unknown) = unknown,

      (false and unknown) = false,

      (unknown and unknown) = unknown

    • NOT:

      (not unknown) = unknown

  • The result of select predicate is treated as false if it evaluates to unknown