Entity 'tce_answers'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK answer_id BigSerial YES NO This number represents the internal answer unique ID.
FK question_id/answer_question_id Bigint YES NO This number represents the internal question unique ID.
Indicates to which question this answer belongs.
answer_description Text YES NO Answer description. This text may contain special markup tags for formatting and image linking.
answer_isright Boolean YES NO Indicates when the answer is right.
The legal values are:
FALSE = wrong answer;
TRUE = right answer.
answer_enabled Boolean YES NO Indicates when the answer is enabled.
The legal values are:
FALSE = disabled;
TRUE = enabled.
Only enabled answers may be evaluated during test generation for a specific user.
answer_position Bigint NO NO Indicates the right answer order. This field can be used for ordering questions or for specify the appearance order of answers.
This order is used when test_random_answers_select and test_random_answers_order are both set to false.
answer_keyboard_key Bigint NO NO Keyboard key used to select this answer.
NULL = no key.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_question_answers Non-identifying tce_questions tce_answers 1:N
rel_answer_logs Identifying tce_answers tce_tests_logs_answers 1:N
Alternative keys
Name Attributes
ak_answer question_id, answer_description, answer_position
Description
This table contains all answers relative to a specific questions.
Notes
Each question may have an unlimited number of answers and at least one of these must be right.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_modules'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK module_id BigSerial YES NO This number represents the internal module unique ID.
module_name Varchar(n) (255) YES YES Unique name for the module.
module_enabled Char (20) YES NO The legal values are:
FALSE = disabled;
TRUE = enabled.
Only enabled modules can be used for a new test.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_module_subjects Non-identifying tce_modules tce_subjects 1:N
Alternative keys
Name Attributes
ak_module_name module_name
Description
A module is a container for subjects.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs No
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_questions'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK question_id BigSerial YES NO This number represents the internal question unique ID.
FK subject_id/question_subject_id Bigint YES NO This number represents the internal subject unique ID.
Indicates to which subject this question belongs.
question_description Text YES NO Question description (e.g.: What is a carbonate, and what is it used for?). This text may contain special markup tags for formatting and image linking.
question_type Smallint YES NO Indicates the type of question:
1 = single [radiobutton] (only one answer is right);
2 = multiple [checkbox] (more than one answer may be right);
3 = free-answer [textarea] (the user introduces the answer using keyboard);
4 = answers odering.
question_difficulty Smallint YES NO Integer value representing the difficulty level of the question.
This value will be multiplied with test_score_right to obtain the question score.
Higher values represent questions with an high grade of difficulty.
question_enabled Boolean YES NO Indicates when the question is enabled.
The legal values are:
FALSE = disabled;
TRUE = enabled.
Only enabled questions may be evaluated during test generation for a specific user.
question_position Bigint NO NO Indicates the right question appearance order. This order is used when test_random_questions_select and test_random_questions_order are both set to false.
question_timer Smallint NO NO Maximum visualization time for this question.
question_fullscreen Boolean YES NO If true diplay the question using the full screen area.
question_inline_answers Boolean YES NO If TRUE display answers one next each other instead of vertical list layout.
question_auto_next Boolean NO NO If TRUE automatically moves on next question when a MCSA answer is selected.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_subject_questions Non-identifying tce_subjects tce_questions 1:N
rel_question_answers Non-identifying tce_questions tce_answers 1:N
rel_question_logs Non-identifying tce_questions tce_tests_logs 1:N
Alternative keys
Name Attributes
ak_question subject_id, question_description
Description
This table contains all questions relative to specific subjects.
Notes
Each subject may have an unlimited number of questions.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_sessions'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK cpsession_id Varchar(n) (32) YES NO This string represents the user's Web session identification (ID).
Session IDs are identification tokens for users, and servers can use them to maintain session data (e.g., variables) for creating a session-like experience to the users.
cpsession_expiry Timestamp YES NO This attribute stores the last time this session experienced any call activity.
This information is used to close the user's session automatically, if there has been no session-related activity for the prescribed amount of time.
cpsession_data Text YES NO This attribute stores the encoded user's session data. In PHP this information may be encoded/decoded using the session_encode/session_decode functions.
Relationships
Relationship name Type Parent entity Child entity Card.
session_data Informative tce_users tce_sessions 1:N
Description
This table stores information about users' Web sessions.
Notes
The basic idea behind web session management is that the server generates a session identifier (ID) at some early point in user interaction, sends this ID to the user's browser and makes sure that this same ID will be sent back by the browser along with each subsequent request. Session IDs thereby become identification tokens for users, and servers can use them to maintain session data (e.g., variables) for creating a session-like experience to the users.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_subjects'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK subject_id BigSerial YES NO This number represents the internal subject unique ID.
subject_name Varchar(n) (255) YES NO Unique name of the subject (e.g.: History I, History II, Geography).
subject_description Text NO NO Subject description. This text may contain special markup tags for formatting and image linking.
subject_enabled Boolean YES NO Indicates when the subject is enabled.
The legal values are:
FALSE = disabled;
TRUE = enabled.
Only enabled subjects can be used for a new test.
FK user_id/subject_user_id Bigint YES NO Only the administrator and the author of this subject (or his/her group) may update or delete this record.
FK module_id/subject_module_id Bigint YES NO This number represents the internal module unique ID.
Indicates to which module this subject belongs.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_subject_questions Non-identifying tce_subjects tce_questions 1:N
rel_subject_set Identifying tce_subjects tce_test_subjects 1:N
rel_subject_author Non-identifying tce_users tce_subjects 1:N
rel_module_subjects Non-identifying tce_modules tce_subjects 1:N
Alternative keys
Name Attributes
ak_subject_name module_id, subject_name
Description
This table contains all tests subjects (topics, branch of studies).
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_test_subject_set'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description
PK tsubset_id BigSerial YES NO Unique ID representing a group of subjects for the specified test.
FK test_id/tsubset_test_id Bigint YES NO ID of the test
tsubset_type Smallint YES NO Indicates the type of question:
1 = MCSA - single [radiobutton] (only one answer is right);
2 = MCMA - multiple [checkbox] (more than one answer may be right);
3 = free-answer [textarea] (the user introduces the answer using keyboard).
tsubset_difficulty Smallint YES NO Integer value representing the difficulty level of the question.
tsubset_quantity Smallint YES NO Maximum number of questions to be selected.
tsubset_answers Smallint YES NO Number of alternative answers to be selected for the single and multiple questions.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_test_subjset Non-identifying tce_tests tce_test_subject_set 1:N
rel_set_subjects Identifying tce_test_subject_set tce_test_subjects 1:N
Description
This table lists the type and quantity of the questions associated to each set of subjects selected for a particular test.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs No
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_test_subjects'
General info
Entity type: dependent
Attributes
Key Attribute/role name Data type Not null Unique Description
PFK tsubset_id/subjset_tsubset_id Bigint YES NO
PFK subject_id/subjset_subject_id Bigint YES NO This number represents the internal test unique ID.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_subject_set Identifying tce_subjects tce_test_subjects 1:N
rel_set_subjects Identifying tce_test_subject_set tce_test_subjects 1:N
Description
This table contains the list of subjects for each subject set relative to the selected test.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_testgroups'
General info
Entity type: dependent
Attributes
Key Attribute/role name Data type Not null Unique
PFK test_id/tstgrp_test_id Bigint YES NO
PFK group_id/tstgrp_group_id Bigint YES NO
Relationships
Relationship name Type Parent entity Child entity Card.
rel_test_group Identifying tce_tests tce_testgroups 1:N
rel_group_test Identifying tce_user_groups tce_testgroups 1:N
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs No
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_tests'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK test_id BigSerial YES NO This number represents the internal test unique ID.
test_name Varchar(n) (255) YES NO Unique name of the test (e.g.: History I 2004, Geography 1st class 2005).
test_description Text YES NO Test description. This text may contain special markup tags for formatting and image linking.
test_begin_time Timestamp NO NO Date and time (relative to server clock) after which the test will be active.
test_end_time Timestamp NO NO Date and time (relative to server clock) after which the test will be deactivated.
test_duration_time Smallint YES NO Maximum duration of the test in minutes. Once started, the test will have to be completed within the specified amount of time.
test_ip_range Varchar(n) (255) YES NO Enabled IP addresses. This attribute may contain a comma-separated list of the different IP addresses of the client computers entitled to perform the test.
An IP address can also contain wildcards (* = any number),
(e.g.: 192.168.1.*, 192.168.2.*).
test_results_to_users Boolean YES NO If true enable users to view their test results immediately after finish.
The legal values are:
FALSE = disabled;
TRUE = enabled.
test_report_to_users Boolean NO NO If true enable users to view a detailed report on executed tests.
test_score_right Numeric(p,s) (10,3) NO NO Default score for right answers.
This value will be multiplied by question_difficulty value to obtain the question score.
test_score_wrong Numeric(p,s) (10,3) NO NO Score to be assigned to the wrong answers.
This value will be multiplied by question_difficulty value to obtain the question score.
test_score_unanswered Numeric(p,s) (10,3) NO NO Score to be assigned to the unanswered questions.
This value will be multiplied by question_difficulty value to obtain the question score.
test_max_score Numeric(p,s) (10,3) YES NO Maximum score that could be reached with this test.
This value could be calculated using the related tables and it's saved for statistical purposes.
FK user_id/test_user_id Bigint YES NO User ID of the author of the test. Only the administrator and the author of the test (or his/her group) may update/delete the test.
test_score_threshold Numeric(p,s) (10,3) NO NO Minimum score required to pass the exam.
test_random_questions_select Boolean YES NO If true the questions will be selected randomly for each user.
The legal values are:
FALSE = disabled;
TRUE = enabled.
test_random_questions_order Boolean YES NO If true the questions will be ordered randomly for each user.
The legal values are:
FALSE = disabled;
TRUE = enabled.
test_random_answers_select Boolean YES NO If true the answers will be selected randomly for each question.
The legal values are:
FALSE = disabled;
TRUE = enabled.
test_random_answers_order Boolean YES NO If true the answers will be ordered randomly for each question.
The legal values are:
FALSE = disabled;
TRUE = enabled.
test_comment_enabled Boolean YES NO If true enable test comment: the user may leave a general test comment on a textarea.
The legal values are:
FALSE = disabled;
TRUE = enabled.
test_menu_enabled Boolean YES NO If true enables the navigation menu for questions during test execution.
The legal values are:
FALSE = disabled;
TRUE = enabled.
test_noanswer_enabled Boolean YES NO If true enable the 'no-answe' option on available answers.
The legal values are:
FALSE = disabled;
TRUE = enabled.
test_mcma_radio Boolean YES NO If true uses radiobuttons instead of checkboxes for MCMA questions.
The legal values are:
FALSE = disabled;
TRUE = enabled.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_test_users Non-identifying tce_tests tce_tests_users 1:N
rel_test_author Non-identifying tce_users tce_tests 1:N
rel_test_group Identifying tce_tests tce_testgroups 1:N
rel_test_subjset Non-identifying tce_tests tce_test_subject_set 1:N
Alternative keys
Name Attributes
ak_test_name test_name
Description
This table contains all tests data.
Notes
Tests can contain several topics (subjects).
You cannot modify a test that has already performed.
When a test is deleted, so they are all its logs.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_tests_logs'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK testlog_id BigSerial YES NO This number represents the internal test-log unique ID.
FK testuser_id/testlog_testuser_id Bigint YES NO This number represents the internal test-user unique ID.
testlog_user_ip Varchar(n) (15) NO NO IP address of the client computer from where the aswer has been received.
FK question_id/testlog_question_id Bigint YES NO This number represents the internal question unique ID.
testlog_answer_text Text NO NO Text of the answer given by user. This will be set only if the question is free-answer type.
testlog_score Numeric(p,s) (10,3) NO NO Total score gained by the user for this question. This score may be automatically calculated in case of multiple-choice question, otherwise may be manually set.
testlog_creation_time Timestamp NO NO Date and time (relative to server clock) indicating when the question (tuple) has been generated.
testlog_display_time Timestamp NO NO Date and time (relative to server clock) indicating when the question has been displayed to the user for the first time.
testlog_change_time Timestamp NO NO Date and time (relative to server clock) indicating when the last answer has been set.
testlog_reaction_time Bigint NO NO Time (milliseconds) elapsed between question visualisation and user's answer.
testlog_order Smallint YES NO Display order of the questions.
testlog_num_answers Smallint YES NO Number of alternative answers associated to this question.
testlog_comment Text NO NO Teacher or Supervisor comment to the question
Relationships
Relationship name Type Parent entity Child entity Card.
rel_question_logs Non-identifying tce_questions tce_tests_logs 1:N
rel_testuser_logs Non-identifying tce_tests_users tce_tests_logs 1:N
rel_testlog_answers Identifying tce_tests_logs tce_tests_logs_answers 1:N
Alternative keys
Name Attributes
ak_testuser_question testuser_id, question_id
Description
This table contains all questions and user answers relative to a specific test for a specific user.
Notes
All tuples (rows) are created during test creation process (when user request to execute a specific test).
The tuples are updated when user answer questions.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_tests_logs_answers'
General info
Entity type: dependent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PFK testlog_id/logansw_testlog_id Bigint YES NO This number represents the internal test-log unique ID.
PFK answer_id/logansw_answer_id Bigint YES NO This number represents the internal answer unique ID.
logansw_selected Smallint YES NO Indicate the user's answer.
Legal values are:
-1 = no answer;
0 = false or not selected;
1 = true or selecte
logansw_order Smallint YES NO Presentation order of the answer inside the question This avoid an automatic ordering problem that happens in MySQL
logansw_position Bigint NO NO User's selected order for this answer. This field is used only for ordering questions.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_answer_logs Identifying tce_answers tce_tests_logs_answers 1:N
rel_testlog_answers Identifying tce_tests_logs tce_tests_logs_answers 1:N
Description
This table contains the list of the selected alternative answers for each multiple-choice question.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_tests_users'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK testuser_id BigSerial YES NO This number represents the internal user-test unique ID.
FK test_id/testuser_test_id Bigint YES NO This number represents the internal test unique ID.
Indicates the test type selected by the user.
FK user_id/testuser_user_id Bigint YES NO This number represents the internal user's identification (ID).
Indicates to which user the test belongs.
testuser_status Smallint YES NO Numeric code that indicates the user-test status. TCExam legal values are:
0 = the test generation process is started but not completed;
1 = the test has been successfully created;
2 = all questions have been displayed to the user;
3 = all questions have been answered;
4 = test locked (for timeout);
testuser_creation_time Timestamp YES NO Date and time (relative to server clock) indicating the end of the test creation process.
testuser_comment Text NO NO User's optional comment about the test.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_user_tests Non-identifying tce_users tce_tests_users 1:N
rel_test_users Non-identifying tce_tests tce_tests_users 1:N
rel_testuser_logs Non-identifying tce_tests_users tce_tests_logs 1:N
Alternative keys
Name Attributes
ak_testuser test_id, user_id
Description
This table contains the list of tests generated for each user.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_user_groups'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description
PK group_id BigSerial YES NO This number represents the internal group identification (ID).
group_name Varchar(n) (255) YES YES Unique name of this group.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_group_user Identifying tce_user_groups tce_usrgroups 1:N
rel_group_test Identifying tce_user_groups tce_testgroups 1:N
Description
Users' groups. Each user belongs to one of the groups defined in this table.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs No
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_users'
General info
Entity type: independent
Attributes
Key Attribute/role name Data type Not null Unique Description Notes
PK user_id BigSerial YES NO This number represents the internal user's identification (ID).
user_name Varchar(n) (255) YES NO Username, unique name by which a user is identified when accessing a multi-user system.
user_password Varchar(n) (255) YES NO Password, keyword, code word, alphanumeric code that together with the username is used to gain access to this system. Passwords are encrypted by a one way algorithm (MD5).
user_email Varchar(n) (255) NO NO User's email address (e.g.: name@domain.com).
user_regdate Timestamp YES NO User registration date and time relative to server clock.
user_ip Varchar(n) (15) YES NO IP address of the client computer from where the user's registration request has been received.
user_firstname Varchar(n) (255) NO NO User's real first name (e.g.: Albert, Maria).
user_lastname Varchar(n) (255) NO NO User's real last name, family name, surname (e.g.: Smith, Einstein).
user_birthdate Date NO NO Date of birth, year month and day on which user was born.
user_birthplace Varchar(n) (255) NO NO User's place of birth (city or country).
user_regnumber Varchar(n) (255) NO NO Unique user's registration number (e.g.: school registration number).
user_ssn Varchar(n) (255) NO NO User's Social Security number (SSN) or other equivalent unique code (e.g.: Codice Fiscale for Italian users)
user_level Smallint YES NO The user's level is a numeric value that indicates which resources (pages, modules, services) are accessible by the user.


To gain access to a specific resource, the user's level must be equal or greater to the one specified for the requested resource.
TCExam has 10 predefined user's levels:
0 = anonymous user (not registered).
1 = basic user (registered);
2-9 = configurable/custom levels;
10 = administrator with full access rights
user_verifycode Varchar(n) (32) NO YES Unique code used to verify user's email during remote registration process.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_user_tests Non-identifying tce_users tce_tests_users 1:N
session_data Informative tce_users tce_sessions 1:N
rel_test_author Non-identifying tce_users tce_tests 1:N
rel_subject_author Non-identifying tce_users tce_subjects 1:N
rel_user_group Identifying tce_users tce_usrgroups 1:N
Alternative keys
Name Attributes
ak_user_name user_name
ak_user_regnumber user_regnumber
ak_user_ssn user_ssn
Description
This table contains all registered users' data, including system administrators and a special 'anonymous' user.
Notes
Just the registered users granted with a username and a password are entitled to access the restricted areas of TCExam and the public area to perform the tests.
It is possible to specify different access levels for users to gain access to a specific resource (pages, forms, sections). The user's level must be equal or greater to the one specified for the requested resource.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs Yes
Schema
Tablespace
Using index tablespace (for Primary key)


Entity 'tce_usrgroups'
General info
Entity type: dependent
Attributes
Key Attribute/role name Data type Not null Unique Description
PFK user_id/usrgrp_user_id Bigint YES NO This number represents the internal user unique ID.
PFK group_id/usrgrp_group_id Bigint YES NO This number represents the internal group unique ID.
Relationships
Relationship name Type Parent entity Child entity Card.
rel_user_group Identifying tce_users tce_usrgroups 1:N
rel_group_user Identifying tce_user_groups tce_usrgroups 1:N
Description
This table contains the list of groups to whom each user belongs.
User-defined variables
Name Value
Temporary Table No
Inherited Tables
Without OIDs No
Schema
Tablespace
Using index tablespace (for Primary key)