[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
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.
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.
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
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ů.'); |
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
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'); |
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
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’.
[ << ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
This document was generated by Ales Cepek on January 18, 2018 using texi2html 1.82.