Ch.10 SML# feature: seamless SQL integration

§ 10.1. Relational databases and SQL

Most of practical database systems are relational databases. To understand SML# database integration, this section review the basics notions of relational databases and SQL.

In the relational model, data are represented by a set of relations. A relation is a set of tuples, each of which represents association of attribute values such as name, age, and salary. Such a relation is displayed as a table of the following form.

name age salary
”Joe” 21 10000
”Sue” 31 20000
”Bob” 41 20000

A relational database is system to manipulate a collection of such tables. A relation R on the sets A1,A2,,An of attribute values is mathematically a subset of the Cartesian product A1×A2×An. Each element t in R is an n element tuple (a1,,an). In an actual database system, each component of a tuple has attribute name, and a tuple is represented as a labeled record. For example, the first line of the example table above is regarded as a record {name="Joe", age=21, salary=1000}. On these relations, a family of operations are defined, including union, projection, selection, and Cartesian product. A set of tables associated with a set of these operations is called the relational algebra. One important thing to note on this model is that, as its name indicates, the relational model is an algebra and that it is manipulated by an algebraic language. An algebraic language is a functional language that does not have function expression.

In relational databases, the relational algebra is represented by the language called SQL, which is language of set-value expressions. The central construct of SQL is the following SELECT expression.

SELECT t1.l1 as l1,, tm.lm as lm
FROM R1 as t1, , Rn as tn
WHERE P(t1,,tn)

Here we used the following meta variables.

  • R: relation variables

  • t: tuple variables

  • l: labels, or attribute names

  • t.l: the l attribute of tuple l

The operational meaning of a SELECT expression can be understood as follows.

  1. Evaluate each Ri in FROM clause, and generate their Cartesian product R1××Rn

  2. Let (t1,,tn) be any representative tuple in the product.

  3. Select the tuples that satisfies the predicate P(t1,,tn) specified in WHERE clause from the product.

  4. For each element (t1,,tn) in the select set, construct a record {l1=t1.l1, , lm=tm.lm}.

  5. Collect all these records.

For example, let the above example table be named as Persons and consider the following SQL.

SELECT as name, P.age as age
FROM Persons as P
WHERE P.salary > 10000

This expression is evaluated as follows.

  • The Cartesian product of the soul relation Persons is Persons itself.

  • Let P be any tuple in Persons.

  • Select from Person all the tuples P such that P.Salary > 10000. We obtain the following set.

    name age salary
    ”Sue” 31 20000
    ”Bob” 41 20000
  • For each tuple P in this set, compute the new tuple {, age=P.age} to obtain the following set.

    name age
    ”Sue” 31
    ”Bob” 41

    The is the result of the expression.

    This result represent the set (list) of records: {{name="Sue", age=31}, {name="Bob", age=31}}.