Structured Query Language/Relational DBMS (rDBMS)

The Theory

Relational DBMS are implementations of data stores according to the design rules of the relational model. This approach allows operations on the data according to the relational algebra like projections, selections, joins, set operations (union, difference, intersection, ...) and more. Together with Boolean algebra (and, or, not, exists, ...) and other mathematical concepts relational algebra builds up a complete mathematical system with basic operations, complex operations and transformation rules between the operations. Neither a DBA nor an application programmer needs to know the relational algebra. But you should know that your rDBMS is based on this mathematical foundation - and that it has the freedom to transform queries into several forms.

The Data Model

The relational model designs data structures as relations (tables) with attributes (columns) and the relationship between those relations. The information about one entity of the real world is stored within one row of a table. In this spirit the term one entity of the real world must be used with care. May be that our intellect identifies a machine like a single airplane in this vein. Depending on the information requirements you may decide to put all information into one row of a table airplane. But in many cases it is necessary to breakup the entity into its pieces - and model the pieces as separate entities including the relation to the whole thing. If you, for example, need information about every single seat within the airplane, you need to have a second table seat and some way of joining seats to airplanes. And in the end you will have a great number of tables.

This way of breaking up information about real entities into a complex data model depends highly on the information requirements of the business concept. The resulting data model should conform to a so-called normal form. And the good news is: It will not predetermine the proceeding of applications. It is strictly descriptive and will not restrict the access to the data in any way.

Some more Basics

It is self-evident that operations within databases must have the ability to act not only on a single row but on a set of rows. Fortunately relational algebra offers this possibility. Therefor languages based on it, e.g.: SQL, can offer a powerful syntax to manipulate a great bunch of data within one single command.

As operations within relational algebra may be replaced by different but logically equivalent operations, a language based on relational algebra should not predetermine how its syntax is mapped to operations respectively the execution plan. The language should describe what should be done and not how to do it. Note: This choice of operations did not concern the use or neglect of indices.

As described before the relational model tends to breakup objects into sub-objects. In this and in other cases it is often necessary to collect associated information from a bunch of tables to one information unit. How is this possible without links between participated tables and rows? The answer is: All joining is done based on the values which are actually stored in the attributes. The rDBMS must make its own decision how to reach all concerned rows: read all potentially affected rows and ignore those who are irrelevant (full table scan)? Or, use some kind of index and read only those who match the criteria? This value-based approach allows even the use of other operators than the equal-operator, e.g.:

SELECT * FROM gift JOIN box ON gift.SIZE < box.SIZE;

This command will join all "gift" records to all "box" records with a larger "size" (whatever "size" means).