Visual SQL

Aim of the Software Visual SQL

Database programming usually tough in university courses is mainly based on simple examples. Database programming in practice is far more complex. Applications lead often to queries that cannot be placed on a full sheet of paper, that range over more than a dozen of relations, and that have a complex join structure. Visualization is one of the features that may substantially improve correctness and completeness of query formulation.

Query formulation is still a difficult task whenever a database schema is large or complex. The user has to entirely understand the schema before a correct and complete formulation of the query will be found. Furthermore, users may overlook types in the SQL schema that must be used in the query. We show in this paper that visualization led in this case to higher conceptual correctness and conceptual completeness. Visualization is based on Visual SQL. Visual SQL follows the paradigm of entity-relationship representation. At the same time, it has the same expression power as SQL-92 and SQL-99. The quality of query formulation is, however, higher.

 

Visual SQL is at the same time

  • as powerful as SQL-92 and SQL-99 and
  • simpler to use and to comprehend, and less error-prone in complex settings.

 

 

Figure 1: Example of Visual SQL

In the time queries are modified, reused and refined. The result is that some code will be unclearly. The maintenance of those unclearly systems are difficult or impossible. The handling and processing of querys is an essential condition for databases in new environments, because of enhancements of  management database systems or also often migrating an database application into another  platform.

In Visual SQL you can declare querys across in a simple and conceptional format. Also expertly complex queries will be manageable in a form, that's will allow simply editing und upgrading those.

With the help of softwaretools based on Java queries can created and modify in Visual SQL. You can also transform queries from Visual SQL into typically queries.

 

 

 

Transformation from Visual SQL into SQL

Completely transformation: Every query created in Visual SQL it's possible to transform direktly into SQL.

 

 

Transformation from SQL into Visual SQL into SQL

Advanced users have problems to realize larger queries, too. Especially if they don't created this queries theirself or that dates of creation back several months. With a graphical preparation of existing queries there is the potential to accelerate the process of understanding. Furthermore a graphical presentation of queries can used for accompanying documents and makes possible to represent larger groups. In comparison to presentation by Visual SQL it has a better view and substantially smaller scale then the SQL-code pendant.

 

For this reason additionally existing queries can transform into VisualSQL illustration. These facility simplify the migration of existing queries severely.

 

Interfaces

XML: This tool has an standard interface for XML for Entity-Relationship-Schema and for queries by Visual SQL.

Integration directly: For some tools, which used in the developing of databases, there will be an extension for a direct interface.

 

A small example

Given the schema for a university application displayed in Figure 2. We use the extended entity relationship model:

 

Figure 2: Part of the HERM Diagram Specifying the Structure of the University Database

Let us now formulate a request:

Provide data on students who have successfully completed those and only those courses which have successfully been given or which are currently given by the student's supervisor?

The SQL-92 query is rather complex:

 

SELECT P1.Name, P1.BirthData, P1. Address,
P2.Name AS "Name of supervisor"
FROM Person P1, Professor P2, Student S1, Supervisor, Lecture L,
Enroll E
WHERE P1.Name = Student.Name AND P1.BirthData = Student.BirthData
AND S1.StudNo = E.StudNo
AND E.Result NOT NULL
AND S1.StudNo = Supervisor.StudNo
AND Supervisor.Name = Professor.Name
AND Supervisor.BirthData = Professor.BirthData
AND P2.Name = Professor.Name AND P2.BirthData = P2.BirthData
AND L.Name = Professor.Name AND L.BirthData = Professor.BirthData
AND
L.CourseNo
IN
(SELECT E2.CourseNo
FROM Enroll E2
WHERE
S1.StudNo = E2.StudNo AND
E2.Result NOT NULL
)
AND
E.CourseNo
IN
(SELECT L2.CourseNo
FROM Lecture L2
WHERE
L2.Name = P2.Name AND
L2.BirthData = P2.BirthData
);

The formulation of the query is much better to comprehend through the corresponding Visual SQL expression given in Figure 3. We are able to translate the Visual SQL expression directly in the corresponding SQL query given above. This query may have to be rephrased depending on the  translation of the ER schema.

 

Figure 3: Visual SQL Involving Equality On Two Visual SQL Subqueries