Next: , Previous: How to install Sqltutor, Up: Top


3 Internals

Sqltutor schema is design to enable multiligual translations of tutorial questions with common problem atributes and SQL solutions (anwers). For a given problem one or more questions can be defined with one or more possible solutions. A dataset is a set of problems with given point evaluation and a common set of tables. Tutorials are defined as selections of datasets.

./schema.png

Figure 3.1: Sqltutor database schema

Tutorials, datasets and problems are defined in SQL language with a set of stored procedures. An example of how to define a dataset is given a file compute_store.sql with description of all steps in the comments.

3.1 How to add new dataset

To define a dataset we have to introduce its name (function init_dataset and optionaly describe its data source or sources (function add_ds_source). Dataset tables are described by calls to function add_ds_table where parameters are dataset name, priority in which tables are listed in tutorail questions, table name and the list of its column names (attributes).

Example:

     SELECT init_dataset ('computer_store');
     SELECT add_ds_source('computer_store', 2010,
            'http://en.wikibooks.org/wiki/SQL_Exercises/The_computer_store');
     SELECT add_ds_table ('computer_store', 1, 'manufacturers', 'code, name');
     SELECT add_ds_table ('computer_store', 2, 'products',
                          'code, name, price, manufacturer');

For each problem we can formulate one or more quesitions and one or more answers (sql queries). Within a dataset each problem is described by a unique identification number (secon parameter of the function insert_problem) followed by its point rating.

     -- dataset name, internal problem_id, points, category
     -- (currently unused)
     SELECT insert_problem ('computer_store', 10, 1, 'select');
     
     -- dataset name, internal problem id, order, language, question text
     SELECT insert_question('computer_store', 10, 1, 'en',
            'Select the names and the prices of all the products
             in the store.');
     
     --  dataset name, internal dataset id, order, language, sql answer
     SELECT insert_answer  ('computer_store', 10, 1,
                            'SELECT name FROM products;');

Tutoriual questions can be translated to other languages.

     -- Problem defines id, point rating and category
     SELECT insert_problem ('computer_store', 30, 2, 'select');
     SELECT insert_question('computer_store', 30, 1, 'en',
            'Select the names of the products with a price less
             than or equal to $200.');
     SELECT insert_answer  ('computer_store', 30, 1,
            'SELECT name FROM products WHERE price <= 200;');
     
     -- Czech translation of problem id 30.  Answers (SQL code) are shared
     -- among all translations
     SELECT insert_question('computer_store', 30, 1, 'cs',
                            'Vypište jména všech produktů, jejichž cena
                             je menší nebo rovna 200 dolarů.');

3.2 How to add new tutorial

A tutorial definition is simple, we introduce its language and name and add to it selected datasets.

     SELECT init_tutorial ('en', 'Demo');
     SELECT insert_dataset('Demo', 'en', 'computer_store');
     
     SELECT init_tutorial ('cs', 'Demo');
     SELECT insert_dataset('Demo', 'cs', 'computer_store');

Tutorials can be easily deleted. Datasets are independent on tutorials and they are not affected in any way when a tutorial referencing to them is deleted.

     SELECT delete_tutorial('cs', 'Demo');

3.3 Doxygen

Doxygen generated source documentation is available only partly for classes implementing CGI interface for Sqltutor. Change to directory doc and run make doxygen. Generated output is stored in directories html and latex.