TCExam Database Documentation
Entities
Entity 'tce_answers'
General info
| Entity type: | independent |
Attributes
| Key | Attribute/role name | Data type | Not null | Unique | Notes | |
| PK | answer_id | BigSerial | YES | NO | ||
| FK | question_id/answer_question_id | Bigint | YES | NO | ||
| answer_description | Text | YES | NO | This text may contain special markup tags for formatting and image linking. | ||
| answer_explanation | Text | NO | NO | This field must be displayed only on results. | ||
| answer_isright | Boolean | YES | NO | |||
| answer_enabled | Boolean | YES | NO | Only enabled answers may be evaluated during test generation for a specific user. | ||
| answer_position | Bigint | NO | NO | 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 | |||
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 |
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 | Notes | |
| PK | module_id | BigSerial | YES | NO | ||
| module_name | Varchar(n) (255) | YES | YES | |||
| module_enabled | Char (20) | YES | NO | Only enabled modules can be used for a new test. | ||
| FK | module_id/module_user_id | Bigint | YES | NO | ||
Relationships
| Relationship name | Type | Parent entity | Child entity | Card. |
| rel_module_subjects | Non-identifying | tce_modules | tce_subjects | 1:N |
| rel_module_author | Non-identifying | tce_users | tce_modules | 1:N |
Alternative keys
| Name | Attributes |
| ak_module_name | module_name |
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 | Notes | |
| PK | question_id | BigSerial | YES | NO | ||
| FK | subject_id/question_subject_id | Bigint | YES | NO | ||
| question_description | Text | YES | NO | This text may contain special markup tags for formatting and image linking. | ||
| question_explanation | Text | NO | NO | This field must be displayed only on results. | ||
| question_type | Smallint | YES | NO | |||
| question_difficulty | Smallint | YES | NO | Higher values represent questions with an high grade of difficulty. | ||
| question_enabled | Boolean | YES | NO | Only enabled questions may be evaluated during test generation for a specific user. | ||
| question_position | Bigint | NO | NO | This order is used when test_random_questions_select and test_random_questions_order are both set to false. | ||
| question_timer | Smallint | NO | NO | |||
| question_fullscreen | Boolean | YES | NO | |||
| question_inline_answers | Boolean | YES | NO | |||
| question_auto_next | Boolean | NO | NO | |||
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 |
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 | Notes | |
| PK | cpsession_id | Varchar(n) (32) | YES | NO | 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 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 | 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 |
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 | Notes | |
| PK | subject_id | BigSerial | YES | NO | ||
| subject_name | Varchar(n) (255) | YES | NO | |||
| subject_description | Text | NO | NO | This text may contain special markup tags for formatting and image linking. | ||
| subject_enabled | Boolean | YES | NO | Only enabled subjects can be used for a new test. | ||
| FK | module_id/subject_user_id | Bigint | YES | NO | ||
| FK | module_id/subject_module_id | Bigint | YES | NO | ||
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 |
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 | ||
| PK | tsubset_id | BigSerial | YES | NO | ||
| FK | test_id/tsubset_test_id | Bigint | YES | NO | ||
| tsubset_type | Smallint | YES | NO | |||
| tsubset_difficulty | Smallint | YES | NO | |||
| tsubset_quantity | Smallint | YES | NO | |||
| tsubset_answers | Smallint | YES | NO | |||
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 |
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 | ||
| PFK | tsubset_id/subjset_tsubset_id | Bigint | YES | NO | ||
| PFK | subject_id/subjset_subject_id | Bigint | YES | NO | ||
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 |
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 | Notes | |
| PK | test_id | BigSerial | YES | NO | ||
| test_name | Varchar(n) (255) | YES | NO | |||
| test_description | Text | YES | NO | This text may contain special markup tags for formatting and image linking. | ||
| test_begin_time | Timestamp | NO | NO | |||
| test_end_time | Timestamp | NO | NO | |||
| test_duration_time | Smallint | YES | NO | Once started, the test will have to be completed within the specified amount of time. | ||
| test_ip_range | Varchar(n) (255) | YES | NO | 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 | |||
| test_report_to_users | Boolean | NO | NO | |||
| test_score_right | Numeric(p,s) (10,3) | NO | NO | |||
| test_score_wrong | Numeric(p,s) (10,3) | NO | NO | |||
| test_score_unanswered | Numeric(p,s) (10,3) | NO | NO | |||
| test_max_score | Numeric(p,s) (10,3) | YES | NO | |||
| FK | module_id/test_user_id | Bigint | YES | NO | 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 | |||
| test_random_questions_select | Boolean | YES | NO | |||
| test_random_questions_order | Boolean | YES | NO | |||
| test_random_answers_select | Boolean | YES | NO | |||
| test_random_answers_order | Boolean | YES | NO | |||
| test_comment_enabled | Boolean | YES | NO | |||
| test_menu_enabled | Boolean | YES | NO | |||
| test_noanswer_enabled | Boolean | YES | NO | |||
| test_mcma_radio | Boolean | YES | NO | |||
| test_repeatable | Boolean | YES | NO | |||
| test_mcma_partial_score | Boolean | YES | NO | |||
| test_logout_on_timeout | Boolean | YES | NO | |||
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 |
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 | Notes | |
| PK | testlog_id | BigSerial | YES | NO | ||
| FK | testuser_id/testlog_testuser_id | Bigint | YES | NO | ||
| testlog_user_ip | Varchar(n) (39) | NO | NO | |||
| FK | question_id/testlog_question_id | Bigint | YES | NO | ||
| testlog_answer_text | Text | NO | NO | This will be set only if the question is free-answer type. | ||
| testlog_score | Numeric(p,s) (10,3) | NO | NO | This score may be automatically calculated in case of multiple-choice question, otherwise may be manually set. | ||
| testlog_creation_time | Timestamp | NO | NO | |||
| testlog_display_time | Timestamp | NO | NO | |||
| testlog_change_time | Timestamp | NO | NO | |||
| testlog_reaction_time | Bigint | NO | NO | |||
| testlog_order | Smallint | YES | NO | |||
| testlog_num_answers | Smallint | YES | NO | |||
| testlog_comment | Text | NO | NO | |||
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 |
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 | Notes | |
| PFK | testlog_id/logansw_testlog_id | Bigint | YES | NO | ||
| PFK | answer_id/logansw_answer_id | Bigint | YES | NO | ||
| logansw_selected | Smallint | YES | NO | |||
| logansw_order | Smallint | YES | NO | This avoid an automatic ordering problem that happens in MySQL | ||
| logansw_position | Bigint | NO | NO | 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 |
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 | Notes | |
| PK | testuser_id | BigSerial | YES | NO | ||
| FK | test_id/testuser_test_id | Bigint | YES | NO | ||
| FK | module_id/testuser_user_id | Bigint | YES | NO | ||
| testuser_status | Smallint | YES | NO | 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 | |||
| testuser_comment | Text | NO | NO | |||
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, module_id |
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 | ||
| PK | group_id | BigSerial | YES | NO | ||
| group_name | Varchar(n) (255) | YES | YES | |||
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 |
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 | Notes | |
| PK | module_id | Bigint | YES | NO | ||
| user_name | Varchar(n) (255) | YES | NO | |||
| user_password | Varchar(n) (255) | YES | NO | Passwords are encrypted by a one way algorithm (MD5). | ||
| user_email | Varchar(n) (255) | NO | NO | |||
| user_regdate | Timestamp | YES | NO | |||
| user_ip | Varchar(n) (39) | YES | NO | |||
| user_firstname | Varchar(n) (255) | NO | NO | |||
| user_lastname | Varchar(n) (255) | NO | NO | |||
| user_birthdate | Date | NO | NO | |||
| user_birthplace | Varchar(n) (255) | NO | NO | |||
| user_regnumber | Varchar(n) (255) | NO | NO | |||
| user_ssn | Varchar(n) (255) | NO | NO | |||
| user_level | Smallint | YES | NO | 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 | |||
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 |
| rel_module_author | Non-identifying | tce_users | tce_modules | 1:N |
Alternative keys
| Name | Attributes |
| ak_user_name | user_name |
| ak_user_regnumber | user_regnumber |
| ak_user_ssn | user_ssn |
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 | ||
| PFK | module_id/usrgrp_user_id | Bigint | YES | NO | ||
| PFK | group_id/usrgrp_group_id | Bigint | YES | NO | ||
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 |
User-defined variables
| Name | Value |
| Temporary Table | No |
| Inherited Tables | |
| Without OIDs | No |
| Schema | |
| Tablespace | |
| Using index tablespace (for Primary key) |
