Review “Query Languages”

1. Review “Query Languages” Algebra, Calculus, and SQL

2. Review - Objectives • Why learn about formal query languages (QLs)? • How to write a (general) algebra expression • How to write a (general) calculus expression • Differences between TRC and DRC • How to write easy SQL queries • How to write complex SQL queries

3. Why Formal QLs? • Relational Calculus enables you to write complex SQL queries that are difficult to write “on the fly” • Anyone can write simple SQL • Relational Algebra enables you to write more efficient SQL queries • You help the ‘optimizer’ subsystem • Formal theory means RDBMS has strong foundations • Understanding the foundation makes you a better DBMS specialist

4. How to write general RA expressions • Steps to take: • Identify which relations (tables) are needed • Identify which join operations (if any) are needed • Or identify if a set operation is necessary instead • Identify which extra (non-join) conditions are necessary, and where to place those selections • Identify which attributes should be in the result, and thus projected.

5. General structure of simple RA expressions • Then see whether some selection conditions can be moved ‘inwards’, closer to the relation • Make sure condition ranges over attributes that are in scope • Then see whether some attribute projections can be moved ‘inwards’, closer to the relation • Make sure conditions are still valid (attributes must still be in scope of the selection). Πlist of attributes (σconditions (T1 jop T2 jop T3 …)) (Where jop is a join operation such as natural join, …)

6. How to write simple RC expressions • Steps to take: • Identify which relations (tables) are needed, placing them in the [Table Range] part of the [Declarations Part] see next slide • Identify which join conditions (if any) are needed in the [Condition Part] • Identify which extra (non-join) conditions are necessary in the [Condition Part] • Identify which attributes should be in the [Result Part], and thus which variables are free. • Identify which other variables are used in the Condition Part, and bind these variables in the [Quantify Part] of the [Declarations Part]

7. General structure of simple RC expressions { var-list |  var-list (Table-plus-var)  list-of-conditions } [Result Part] [Quantify Part] [Table Range] [Condition Part] [Declarations Part]

8. Differences between TRC and DRC • Differences are minor • ‘Type’ of variables is different • Thus, vars have different values: tuples or attribs • DRC: • More variables needed, and declared • Join and constant conditions can be written ‘directly’ within relations • Eg: Hotel(hotNo, ‘Grosvenor’, cit)  Room(romNo, hotNo, typ, pric) • TRC: • Fewer variables needed, and declared • Need to explicitly state join conditions and comparisons between an attribute and a constant • Eg: Hotel(H)  Room(R )  H.hotNo = R.hotNo  H.hotNa = ‘Grosvenor’

9. How to write easy SQL expressions • Generally possible to write ‘on the fly’ • No need to try RC first • Steps: • Which tables needed  put in FROM clause • Which joins needed  put in FROM or WHERE • Which conditions needed  put in WHERE • Which attribs in result  put in SELECT • Grouping?  check rules for SELECT and WHERE • Sorting? • Set operations between such easy SQL statements necessary?

10. How to write complex SQL expressions • Check English query statement, look for words: • All combinations, exactly, at most, at least, … ? • Not necessarily complex! But good hint… • Write Relational Calculus expression that logically answers the question • ‘Complex’ will mean one or more sub-conditions quantified with  or  • Translate RC expression into SQL • Use of sub-queries with EXISTS or NOT EXISTS necessary to translate sub-conditions