COMP 300539055905 Database Systems Formal Query Languages

1
COMP 3005/3905/5905Database Systems - Formal
Query Languages
2
Formal Query Languages

• Based on mathematics or logic
• Useful for exploring the expressive power of
  query languages
• Provide precise semantics
• Provide a metric for evaluation of real query
  languages
• But ignore
• user interface
• data definition
• data modification

3
Relational Algebra

• Imperative style
• Consists of expressions using relations and
  operators
• Each operator works directly on relations,
  producing a relation as the result
• Evaluating expressions returns the result of a
  query

4
Fundamental Operators

• select
• project
• product
• union
• difference
• plus a derived operator
• natural join
• The first 5 forms a minimal useful set

5
Select

• Result is the subset of the tuples in a relation
  which satisfy the predicate
• the rows of A where P is
  true
• e.g.
• selects the rows from the table EMP where the Sal
  attribute is greater than 27000

6
Project

• Result is the named subset of the attributes in a
  relation
• the attributes of A
  called X and Y
• e.g.
• Projects out the ID and Sal attributes from the
  table EMP
• Duplicates must be eliminated to ensure that
  resultant table satisfies relational criteria

7
Union

• Result has those tuples present in either
  relation
• Both relations must have the same attribute list
• A U B the tuples in A or B
• Eliminates duplicates

8
Difference

• Result has those tuples present in the first
  relation but not in the second
• Both relations must have the same attribute list
• A B tuples in A and not in B
• Do not include tuples in B and not in A

9
(Cartesian) Product

• Take all combinations of tuples from each
  relation
• Do not remove duplicated attributes, but
  distinguish the names by their source relation
• A X B results in table where the attribute
  list is a concatenation of the attributes of A
  and the attributes of B

10
Natural Join

• Derived in the following way
• form the product
• select for equality on all pairs of matching
  named attributes
• project onto one copy of each name
• A B results in a relation joined on
  the common attributes of A and B

11
Relational Completeness

• A language is relationally complete if it can
  express everything that can be calculated using
  the relational algebra
• it does not have to produce the result using only
  one command
• Modern versions of SQL are relationally complete
• Most modern languages can express more
• e.g. aggregation
• But is relational completeness enough?

12
Domain Relational Calculus

• Declarative rather than imperative
• Consists of logical expressions involving
• variables, each with a domain (type)
• relation membership
• logical operators and quantifiers
• Query output is set of values for which the
  expression is true

13
Syntax

• Query expression is
• each is a variable
• P is a formula of predicate logic
• A formula is built from Boolean atoms
• etc.

14
Syntax

• A formula is either
• an atom

15
Semantics

• Evaluate a formula given an interpretation
  (choice of values for each free variable in the
  expression formula) and a database state
• evaluate each atom using the interpretation
• Rel(a, b, ...) iff the tuple a, b, ... is in
  relation Rel
• build up value for the formula as usual in logic
• The meaning of the query is a relation where each
  row is a set of values for the target list such
  that the expression evaluates to True

16
Example

• Find the identifier and quantity in stock of each
  part weighing over 50 kg

17
Example

• Find names of employees who work on Project 37

18
Example

• Find companies supplying every red part

19
Relational Completeness

• Domain relational calculus is relationally
  complete
• i.e it has the same power as the relational
  algebra
• Some queries can return values not in the
  database e.g.
• Avoid this problem by using safe queries
  (disjunctive normal form)
• Any safe expression can be expressed in the
  relational algebra

20
Limitations

• Is the expressive power of relationally complete
  languages enough?
• consider the following component table
• Component(PartNo, UsedIn)
• let this show the parts explosion for assembly
  of part used in other part
• assume that the top most component has a value
  of NULL in the UsedIn column

21
Component Example

• Component

P1
P3
P4
P2
P7
P5
P6
P8
22
Component Example

• Find all the major components used in part P1
• where major means used directly as a sub-part
• select partno
• from component
• where usedin P1

23
Component Example

• Find all the parts used in the construction of
  part P1
• We can easily extend the last query to get the
  next level down in the component hierarchy
• select c1.partno
• from component c1
• where c1.usedin in (
• select c2.partno
• from component c2
• where c2.usedin P1)

24
Component Example

• But we cant produce a single SQL statement which
  returns all the parts used at all levels in the
  construction of part P1
• This is equivalent to taking the transitive
  closure on a graph and it can be shown to be a
  weakness of languages based on first-order
  predicate calculus
• such as SQL, the relational algebra and all
  relationally complete languages

25
Extensions

• Further evidence for a procedural wrapper around
  SQL
• Solved in extensions to the SQL standard
• as in Oracle
• select distinct partno
• from component
• connect by prior partno usedin
• start with partno P1
• Transitive queries are proposed for SLQ3

26
Other Approaches to Queries

• The hope of SQL as the query language of choice
  by all DBMS end users has not been realised
• User like to be provided support for common
  queries, and to be guided by a form
• values are entered into fields of the form
• It is desirable to be able to present output from
  queries in visually useful ways
• tables, graphs, pictures
• DBMS should interface to typical business tools
• spreadsheets, word processors, etc.

27
Forms

• Form based I/O for DBMS applications is typical
• Most vendors provide tools for rapid development
  of form based interfaces to their DBMS
• 4GL allows high level description of interface,
  queries and subsequent application
• system generates the underlying DBMS queries and
  application
• SQL becomes the backend language
• No standardisation in this area
• compare with Query By Example from IBM

28
(No Transcript)