CS346 Lecture Notes
Overview of QL Components

Last time for RedBase architecture:

RQL: The RedBase Query Language

  • Subset of SQL
  • Data retrieval: Select command
  • Data modification: Insert, Delete, Update commands

    For detailed specification see QL Component document.

    RQL Select Statement 

    Select attribute-list (or *)
From   relation-list
Where  condition1 And condition2 And ... And condition-N

    Example:

      student(ID, name, dno)
  dept(dno, bldg)
  course(cno, dno, time)
  taking(SID, cno)
    Students taking courses in a "Gates" building department other than their own department before noon: 
       Select student.name, course.cno
   From   student, taking, course, dept
   Where  student.ID = taking.SID
   And    taking.cno = course.cno
   And    time < 12
   And    course.dno = dept.dno
   And    bldg = 'Gates'
   And    dept.dno <> student.dno
  • SQL semantics
  • Parser will ensure basic syntactic validity

    Implementation Step 1: Semantic checking

    1. Relations in From clause exist, no repeated relations
      Question: Why no repeated relations?

    2. relName.attrName in Select and Where clauses:
      • relName appears in From clause
      • relName has attribute attrName
      • If relName omitted then attrName unambiguous

    3. Where conditions are type-compatible



    Implementation Step 2: Build query tree (= logical query plan) from flat arguments

    Mostly involves sorting out join and local selection conditions



    Implementation Step 3: Transform logical plan into physical plan

  • Mostly involves figuring out where to use indexes, how to order condition evaluation

  • May want to use general-purpose Filter physical operator

  • Don't need to reorder joins or make best index decisions
    => But sometimes simple decisions are easy and yield big performance improvements!

    Implementation Step 4: Execute physical plan using iterators

    Answer should be printed tuple-at-a-time, should look like a relation

    RQL Insert Statement 

    Insert Into R Values ("dog", 15, -3, 2.5E-5, "cat")
    Algorithm:
    1. construct tuple
    2. insert into relation
    3. insert index entries

  • Should also print inserted tuple

  • No need for query plan

    RQL Delete Statement 

    Delete From R
Where condition1 And condition2 And ... And condition-N
    Algorithm:
    1. find next tuple to delete
    2. delete it
    3. delete index entries
    For (1) use file or index scan based on one condition, then check "residual" conditions

  • Should also print deleted tuples

  • Should build physical query plan

    RQL Update Statement 

    Update R
Set    attribute = attribute/value
Where  condition1 And condition2 And ... And condition-N
    Algorithm:
    1. find next tuple to update
    2. update it and insert updated value
    3. delete old index entries
    4. insert new index entries

    For (1) use file or index scan based on one condition, then check "residual" conditions

  • Don't use index scan on updated attribute

  • Should also print new values of updated tuples

  • Should build physical query plan

    Important QL Advice

  • Read QL document very carefully!

  • Don't be overambitious and then fail to get the basic QL component working. It happens to students every year.