Business System 12 (BS12)

Hugh Darwen <hd@thethirdmanifesto.com>
database specialist

republished from the BS12 page by Paul R. McJones

The planning of BS12 started in 1978, with a decision that the new state-of-the-art DBMS for IBM's Bureau Service would be a relational one, but the design and implementation took another four years. It became available to customers in 1982, at least two years after Oracle and SQL/DS. Although the Bureau Service died in about 1985, I am pretty sure one internationally available BS12 service is still running in The Netherlands. (IBM's US subsidiary, Service Bureau Corporation developed some pre-relational DBMS software in the early sixties. When SBC was sold to CDC under consent decree litigation, the business carried on in EMEA and AFE, and we inherited that software. I worked for the small lab that made that DBMS software into a deliverable. Business System 12 was its supersedent.)

We were influenced by System R people, but only on engine stuff and not in any matters to do with language design. Jim Gray, Bruce Lindsay and Ron Fagin (all at Almaden Research Center, though I'm not sure Ron was on the System R team) were all consulted.

For our language design, we consulted the folks at the UK Scientific Centre, who had developed the first ever relational language, ISBL, which they implemented in the single-user system that nobody claimed to be a DBMS, PRTV (Peterlee Relational Test Vehicle).

BS12's query language had the same operators as ISBL (and more) but not ISBL's infix syntax with operator symbols like *. Instead, BS12 used prefix syntax with key word operators. Thus, ISBL's R*S was JOIN(R,S) in BS12.

My words in response to the executive manager who asked me why we had rejected SQL in favour of relational algebra still haunt me: "Yes, we looked at System R too, but we found its language to be incomplete, unfriendly, unsound and unfaithful to Codd's Relational Model - take my word for it, it'll never catch on."

No BS12 table ever contained more than one occurrence of the same row. We did nearly fall into the "nulls" trap, but thankfully veered away from it at the last minute, under advice from Chris Date, Stephen Todd (of the ISBL team) and, wait for it ... System R's Bruce Lindsay!

Because BS12 spurned duplicate rows, it was obliged to make "duplicate removal avoidance" a strong feature of its optimiser - something that SQL implementations are only now beginning to catch up with (in SQL terms, this means not always firing up the duplicate elimination mechanism just because the user said DISTINCT - you might be able instead to prove that there cannot be any duplicates).

Off the top of my head, here is a list of interesting features in BS12:

• JOIN was natural join (over common columns), hence no need for those long WHERE clauses and, even worse than the long WHERE clauses, long SELECT lists needed just to remove duplicate columns.
• In fact, the common column principle was pervasive, being used in UNION, INTERSECTION, DIFFERENCE and certain other operations as well as JOIN. (Actually, INTERSECTION was generalised to the rather more useful "semijoin", and DIFFERENCE to what in SQL would be expressed as WHERE NOT EXISTS <correlated subquery>).
• Projection by column exclusion as well as by column inclusion. (By the way, we seriously considered bundling projection and extension together, as SQL did, but eventually rejected the idea.)
• No anonymous columns.
• No "correlation names", and no qualified column references.
• Query language relationally complete (relational expressions of arbitrary complexity always permitted as operands of relational operator invocations).
• Compulsory primary key for every base table (if I had my life again I would relax this a little and require at least one candidate key without insisting that some CK be nominated as PK - but what I would do if I had my life again is clearly documented in "The Third Manifesto" (see References, below). In fact, every table, base or derived, had a primary key, as the system did its level best to compute candidate keys of query results and arbitrate among them if it found more than one.
• Client-server architecture.
• Analogues of SQL/CLI and SQL/PSM in the very first release!
• Outer join (a null-free variety) available in the very first release, and updatable-through.
• Temporary tables (and views) in the very first release.
• Views permitted to take table-valued arguments. Thus, relational DIVIDE, which was not provided as a primitive operator, could be implemented as a user-defined function (and was).
• Step by step query development, using a DEFINE command in which the key word, DEFINE, is optional:

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

  What's more, if you liked the query you had developed, you could preserve it as a view definition with

      KEEP <definition name> AS <view name>
      

• Number of arbitrary restrictions (such as no more than one GROUP BY, no UNION in views and the like): zero.
• Tables with no columns at all were recognised as logical consequences, in the interests of completeness.
• CREATE TABLE t LIKE <table expression> was available (and greatly used). t inherited the primary key of the LIKE operand.
• UPDATE was like SQL's, but had a USING <table expression> extension allowing transactions to be batched.
• BOOLEAN data type, with aggregate functions ALL and ANY.
• User-defined "domains", but we made a big mistake here that was repeated verbatim, to my chagrin, in the SQL:1992 international standard.
• Explicit START TRANSACTION, thus allowing nested transactions, though we only had synchronous nesting.
• Catalogue tables updatable. So, you could create a new base table by adding a row to the TABLES table and several rows to the COLUMNS table, though a CREATE command was available as in SQL. BS12 dd not have DROP--you had to DELETE from the relevant catalogue table instead. Unqualified DELETE from TABLES(OWN) thus completely destroyed an entire database, and it was rather fun to give this in a transaction terminated by CANCEL.
• BS12's analogue of SQL's "descriptor area" was a regular table, delivered by invocation of COLUMNS(<table expression>).
• Blanks permitted in names without concomitant requirement for double quotes.
• Performance about the same as SQL systems of its time sometimes better, sometimes worse).

I'm afraid nobody got around to publishing any papers on BS12, as far as I know. It was developed by about 20 people in a small shop where publication of papers wasn't encouraged. I have some manuals, hardcopy only, squirreled away in a cupboard at IBM.

References

Hugh Darwen, C. J. Date: The Third Manifesto. SIGMOD Record 24(1): 39-49(1995) BibTeX
A hardcopy draft of version 2, including a paper on inheritance can be obtained from Hugh Darwen.

Stephen Todd: The Peterlee Relational Test Vehicle - A System Overview. IBM Systems Journal 15(4): 285-308(1976) BibTeX

Patrick A. V. Hall, Peter Hitchcock, Stephen Todd: An Algebra of Relations for Machine Computation. POPL 1975: 225-232 BibTeX