Entity 'tce_answers'
General info

Entity type: | independent

Attributes

Key | Attribute/role name | Data type | Not null | Unique | Description
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.
| 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.

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

Description

This table contains all answers relative to a specific questions.

User-defined variables

Name | Value
Temporary Table | No
Inherited Tables |
Without OIDs | Yes
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
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?).
| 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).
| 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.
| question_enabled | Boolean | YES | NO | Indicates when the question is enabled. The legal values are: FALSE = disabled; TRUE = enabled.

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.

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
PK | cpsession_id | Varchar(n) (32) | YES | NO | This string represents the user's Web session identification (ID).
| cpsession_expiry | Timestamp | YES | NO | This attribute stores the last time this session experienced any call activity.
| cpsession_data | Text | YES | NO | This attribute stores the encoded user's session data.

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.

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
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.
| subject_enabled | Boolean | YES | NO | Indicates when the subject is enabled. The legal values are: FALSE = disabled; TRUE = enabled.
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.

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

Alternative keys

Name | Attributes
ak_subject_name | 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
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.
| 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.
| test_ip_range | Varchar(n) (255) | YES | NO | Enabled IP addresses.
| test_random_questions | Boolean | YES | NO | If true the questions will be selected and sorted randomly for each user. The legal values are: FALSE = disabled; TRUE = enabled.
| 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_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_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.

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.

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
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.
| testlog_score | Numeric(p,s) (10,3) | NO | NO | Total score gained by the user for this question.
| 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.

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.

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
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 | Boolean | YES | NO | Indicates when this alternative answer has been selected by user. The legal values are: FALSE = not selected; TRUE = selected.
| logansw_order | Smallint | YES | NO | Order of the answer inside the question

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
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.
| 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
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.
| 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.
| 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.

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) |