Teaching Structured Design of Network Algorithms in Enhanced Versions of SQL

Abstract

From time to time developers of (database) applications will encounter, explicitly or implicitly, structures such as trees, graphs, and networks. Such applications can, for instance, relate to bills of material, organization charts, networks of (rail)roads, networks of conduit pipes (e.g., plumbing, electricity), telecom networks, and data dictionaries. Algorithms on such data structures often require recursion or iteration where the number of repetitions is unknown a priori. Such algorithms are usually implemented in a third generation language (3GL) and, therefore, are typically "record-at-a-time." A vast amount of theoretical work on recursive queries in logical languages (and related problems in research prototypes) is available, but these "extensions" typically are not available in commercial database management systems. Hence, they do not directly help the database developer "in the field" who has available only "ordinary" SQL with a few enhancements. Extensions of SQL with assignments and "control of flow" constructions such as the while-loop enable database developers to manage and solve such graph problems more completely and compactly on a 4GL-level in their daily work. Such SQL-extensions have existed for some time in several commercially available database management systems. Incorporating this GL-approach in the educational field constitutes a challenge as well as an opportunity, as we how in this paper. We also illustrate various classical aspects of algorithm design at 4GL-level. In this paper we elaborate on the idea of graph algorithms on 4GL-level. In the Introduction we give a simple criterion to recognize in a general way whether such network structures are "hidden" in our data. We start with the "standard" recursive graph problem of the computation of the set of all paths in a graph. We show that the computation of the paths themselves can easily be extended with the computation of additional path properties. Such algorithms essentially operate differently from the algorithms on 3GL-level. This paradigm shift from 3GL to 4GL constitutes an important educational attention point. It turns out that intuition regarding the correctness (and the termination) concerning these subtle "set-at-a-time" algorithms sometimes falls short. Therefore, we also pay special attention to the correctness and termination of the algorithms (using invariants). Actually, this combines some educational themes from different disciplines in computer science, namely programming (correctness, termination, invariants) and databases (4GL, stored procedures), in an elegant and useful manner. One of the advantages of our uniform 4GL-approach is that it makes the practical development of ad hoc queries in such (recursive) application areas considerably easier, i.e., both simpler and faster, than a mixed 3GL/4GL-approach, using 3GL host languages with embedded SQL. As a consequence, it facilitates both the development and management of information systems in those application areas. Even on 4GL-level the student can influence the efficiency of the graph algorithms. Therefore, we present some incremental improvements on our algorithms, all successively leading to better results. All programs turn out to be rather compact; they consist of only a small number of SQL-statements. This clearly contributes to the transparency of the structure of the algorithms and the maintainability of the software, and therefore makes it also very suitable for educational purposes. (Contains 1 table and 6 figures.