MPbase and SQL
Everything needed and no more
This paper endeavors to provide the information needed to form
your own opinion about a fairly complex issue; MPbase's
support of Structured Query Language (SQL).
MPbase has included sufficient SQL support to allow systems
that need or want to communicate in SQL to do so. MPbase
provides efficient support of several inefficient interfaces.
The MPbase view, which is an extremely enhanced version
of the traditional database view, allows efficient use of the
SQL interface in spite of itself.
As with any paradigm change document, several supporting points
need to be clarified before the above statements make sense. Here
they are:
- MPbase runs on "naturalized data." This is
a much more powerful structure for processing the information
than normalized data in an RDBMS.
- SQL was specifically created as both the DDL (data definition
language) and the DML (data manipulation language) for normalized
data in an RDBMS. It is highly specific to the assumptions and
limitations of both.
- The full functionality of SQL assumes the structures and limitations
of both a traditional RDBMS and normalized data. It was designed
to try to overcome as many of the limitations of this environment
as possible.
- SQL is a programming language. VERY few end users "code
SQL". Most end users enjoy some form of GUI. As an example,
in Microsoft's Access the user interface is a point-and-click
GUI. After you have defined your query, you have the option of
looking at the resulting SQL. Some advanced users(programmers)
might even modify it. However the primary end-user interface is
the GUI, not the SQL.
- SQL is both the most common and standard program/database
interface language. However, in almost all cases, the interface
is a small and well defined subset of SQL. MPbase has absolutely
no problem with such defined subsets, and therefore, can be used
directly as a plug-and-play replacement for the database supporting
such systems.
- MPbase can appear to be relational. It does not, however,
share the same limitations you would find in a traditional RDBMS.
Any question that could be asked using SQL can still be asked
and answered using SQL. However, a lot of the complexity of SQL
is not necessary when you are allowed to assume the ideal table
configuration for your query. This creates a situation where a
large portion of the SQL language just does not apply, and so
is not supported.
- DBA's spend more than a little of their time fixing SQL code
created by programmers. Any good DBA does not allow the types
of SQL constructs that create unnecessarily complex or database
intensive queries. This is precisely the type of SQL, not supported
by MPbase.
- In MPbase the database view allows perfect "virtual
tables" to be presented to any SQL interface. This means
there is no need to join several tables together to get the result
set. All that is required is to request the needed "virtual
rows" from the ideal virtual table. This moves some of the
complexity of the SQL interface down into the DBA's domain. After
all, they are the ones who are supposed to know the data and database.
- MPbase supports (with any appropriate view) SQL, which
thinks it is doing a complex summary and/or join. It will simply
ignore the sections of the SQL statements not relevant to the
efficient and complete resolving of the query. This compatibility
feature allows MPbase to replace complex RDBMS requests
with simple MPbase requests from an existing system in
a transparent manner.
- The bottom line is that SQL is part of the problem, not part
of the solution. MPbase has included sufficient SQL support
to allow systems that need to communicate in SQL to continue to
function. MPbase will provide efficient support of these
inefficient interfaces. The MPbase view, which is an extremely
enhanced version of the traditional database view, allows efficient
use of the SQL interface in spite of itself.
This represents the best possible bridge between the two database
paradigms. It provides the highest possible efficiency and flexibility
while at the same time supporting the current standards in the
best way possible.