9

Is it feasible or even possible to use relational algebra and/or relational calculus in the form of proofs to test/verify the correctness of SQL statements, functions, and stored procedures?

It seems to me like it should be at least possible, but I don't know if there's a detail I'm missing that makes a 1:1 mapping between the proof and the code incorrect.

Have any of you tried a method like this? Did it work? What were your experiences like?

  • 2
    Please repeat after me: SQL is not relational. – Deer Hunter Nov 14 '13 at 7:11
4

There are some known mapping rules between relational algebra operators and SQL statements. For example, the Sigma operator maps to a SELECT statement, there's a one-to-one mapping for the join operators, Delta maps to selecting a subset of columns, etc.

Is it possible to use relational algebra to verify the correctness of SQL statements?

Yes, it is possible. Of course you'll need to have a clear schema of the database with all the relationships, foreign keys, etc. In some cases it's even easier to manipulate relational algebra statements than SQL statements (there are proven transformation rules for modifying and simplifying statements).

But on the other hand, I don't think that verifying statements using relational algebra is much easier than testing SQL statements, especially when you have a database ready where you can execute the queries and see the results.

  • 1
    Just wanted to say: Relational operators map to "SELECT DISTINCT" since relational algebra uses sets of tuples and therefore cannot handle duplicates. The sigma itself corresponds to the WHERE-clause, the pi to the SELECT and so on. – contradictioned Jun 17 '14 at 14:46
  • The join operators are not 1:1, the SQL versions treat nulls specially, and similarly predicates are not Boolean. – philipxy Sep 4 '17 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.