Business System Twelve

Business System 12 - BS12

A RelationalLanguage based on a FunctionalProgramming-like syntax, as opposed to the nested approach of the StructuredQueryLanguage.

http://www.mcjones.org/System_R/bs12.html

Sample from website:

    [DEFINE] T1 = JOIN(EMP, DEPT)
             T2 = SUMMARY(T1, GROUP(DEPTNUM), EMPS=COUNT,
                  SALSUM=SUM(SALARY))
             T3 = SELECT(T2, SALSUM > BUDGET)
             etc.

"Define" is optional if I remember correctly. This example, including a possible typo, is further discussed under TutorialDee.

This allows you to reference complex parts by name instead of having to nest them. The nesting-only approach (such as SQL) often makes it too hard to match the upper and lower breads of the sandwich for complex queries. Nesting also makes it harder to factor out duplication.

Does the use of CTEs (SQL99's WITH) fail to provide a remedy to this? Or is it just unfamiliar to developers? Are there other ways in which CTEs fall down? I feel there are more interesting places than nesting VS named declarations to highlight where BS12 is different/better than SQL, but they aren't discussed here.

To the best of my knowledge, it's not widely supported by DB vendors, but I haven't checked lately. It first has to spread to most implementations, and then people have to be assured that its usage is cross-portable. There seem to be limits with it in Oracle if used with UNION, for example.

CTEs (as of 2013) are fairly well supported, at least as far as using them as a way of using symbolic references to reduce the nesting problems. Quite a few developers seem to still not be very familiar with them, though, and often seem to have been beaten into accepting that giant nested clauses of nested clauses are just How Things Are in SQL land - as in, some folks I've met (who represent the average SQL monkey) don't have enough experience outside of SQL to realize that there is a problem to begin with, or just don't care to learn anything new. In some cases the more interesting things possible with CTEs are not portable enough for inclusion in discussions of vanilla SQL, and so the Postgres and Oracle communities have their own slowly growing body of CTE lore that isn't portable. In any case, CTEs do indeed go a long way to alleviating the ThickBreadSmell problem.

But SQL's problems aren't restricted to a lack of segmented references and nestmania. Those problems are compounded by the fact that every SQL operator has a different syntax, so the way each different sort of element gets nested (or would be symbolically referenced out if there were something really useful like "partial CTEs") is different from every other sort of element. That renders the grammar a bad match for the mental composition of ideas in your head, adding a layer of translation between what you know you want and the way you have to describe it. I've met plenty of people who think in C or Python or Scheme, but none who think in SQL. BS12, AP5, and other functional notations alleviate this not just by providing a way to create symbolic references to avoid deep nesting, but by providing a regular grammar that makes it obvious where a clause starts and ends and what connects where. (On that note, TqlExampleOne is interesting.) Consider how this approach can radically change the mish-mash mess of JOIN ON(foo) ... WITH [stuff AND ...] ... HAVING [morestuff]. In this sense the problems with SQL are similar to some of the problems with COBOL -- the "natural language" assumptions (in these cases, "English-like" but its not a problem of English, per se) built into their syntax render trivial cases easy for beginners and managers to "see what's happening" at the expense of making non-trivial cases grow exponentially uglier the less trivial the case gets. And that sucks since anything so trivial doesn't require an RDBMS. --CraigEverett?

Perhaps the above can be worked into the SqlFlaws topic.

Using function-like expressions as an atom also makes it easier to port queries from one DB vendor to another. If the other vendor does not have a given function, the DBA can add it. You can't do that very easily with SQL because it tends toward one big blob expression instead of building on atomic pieces. Functional is more LegoToy-like in that you have smaller, discrete building blocks.

BS-12 allegedly was influenced by an experimental IBM language called ISBL.

ISBL Query Example

LIST RECEIPT * EMPLOYEE : Date &<2/1/91 % Receipt_Number, Date, Total_Receipt, Sales_Person_Number, Last_Name, First_Name

Compare to SQL:

  SELECT Receipt_Number, Date, Total_Receipt, Sales_Person_Number, Last_Name, First_Name
  FROM Receipt NATURAL JOIN Employee
  WHERE Date < '2/1/1991'
  /* Note: date format and natural join support vary per dialect */

Sample of ISBL operators:

'*' = Natural Join
':' = Selection
'%' = Projection
'+' = Union
'-' = Difference
'.' = Intersection
'=' = Assignment of relation to a name
'LIST' = Display relation (result)

(source: http://www2.bus.orst.edu/faculty/brownc/lectures/db_tutor/database_languages.htm and http://www.dcs.warwick.ac.uk/people/academic/Meurig.Beynon/CS319/pdf/RelMod.pdf)