Documentation on Database Tables

PROJECT TITLE: Regularize Expertiza DB schema

PROBLEM DESCRIPTION:
 
The current Expertiza database has some problems which needs to be rectified.
 
The current Expertiza database has issues similar to what is described below:
 
1. redundant record. For instance, deadline_types table has two signup and team_formation record.
 
2. some field name is confused. For instance, in users table assignment_id becomes parent_id and in response_maps assignment_id becomes 
 reviewed_object_id.

 The scope of our project is to rectify these problems by performing the below steps.
 
 1. Go through the Expertiza_development database to find tables that not used any more or redundant records.
 
 2. Write migrations to regularize the Expertiza database and also change code if necessary.
 
 3. Make sure all existing tests are passed and change test code if necessary
 
 4. Modify database wiki page to make it up to date

DATABASE TABLE DESCRIPTION:
 
Below is the old detailed description of the tables in the Expertiza DB schema.
Our Primary Job is updating the wiki and making it more informative by including all the changes in the Expertiza Db in this wiki.

STEPS TO NAVIGATE TO expertiza_development SCHEMA:
 
 1. Open terminal.
 
 2. Change to the "expertiza" folder using the command: $cd expertiza
 
 3. Invoke the rails database console using the command: "$rails dbconsole" and give the password "expertiza" when prompted.
 
 4. List the databases present in the MYSQL server using the command >show databases;
 
 5. Move to the "expertiza_development" database using the command >use expertiza_development
 
 6. List down the tables present in the "expertiza_development" using the command >show tables;

FLOWCHART:

FINDING REDUNDANT TABLES

STEPS:

   1. Write SQL scripts to find the duplicate records. For example in the table,deadline_types a SQL query to find duplicate records is as follows.
       select name from deadline_types group by name having count(*)>1;
       Running this query in the SQL prompt, we find that there are two rows with the same information, hence they are redundant.

WRITING SCRIPTS TO REGULARIZE THE EXPERTIZA SCHEMA:

STEPS:

   1. Write SQL scripts to delete the redundant scripts and tables.For example in the table, <deadline_types> a SQL query to delete the duplicate records are as follows.
       delete from deadline_types where id not in (select * from (select min(id) from deadline_types group by name) as temp);
       Running this query in the SQL prompt,it will delete the duplicate records except one.

WRITING THE DATABASE MIGRATION SCRIPTS:

STEPS:

   1. Write the rails migration scripts to migrate the SQL that would delete the redundant records for certain table.
       2. The migration scrips present in the expertiza/db/migrate folder can be migrated using the command rails db:migrate 

TEST PLAN :

STEPS:

 1. Validate the schema expertiza_development 
     2. Rerun the sql scripts to find out the redundant records in the tables.It should return null records
     3. Re validate the schema expertiza_development

1. Assignments

   assignments

   assignment_questionnaires

   deadline_types

   deadline_rights

   due_dates

   late_policies

   participants

   wiki_types

   Deprecated Tables

   assignments_questionnaires

2. Goldberg

   content_pages

   controller_actions

   markup_styles

   menu_items

   permissions

   plugin_schema_info

   roles

   roles_permissions

   schema_info

   site_controllers

   users

   system_settings

3. Reviewing

   comments

   mapping_strategies

   responses

   response_maps

   review_strategies

   score_caches

   scores

   Deprecated Tables

   review_feedbacks

   review_mappings

   review_of_review_mappings

   review_of_reviews

   reviews

   teammate_reviews

4. Teammate

   invitations

   teams

   teams_users

5. Courses

   courses

6. Hierarchy

   institutions

   languages

7. Display

   nodes

   tree_folders

8. Questionnaires/Rubrics

   question_advices

   questionnaires

   questions

   Deprecated Tables

   questionnaire_types

9. Surveys

   survey_deployments

   survey_participants

   survey_responses

10. Other Features

    signed_up_users

    sign_up_topics

    resubmission_times

    ta_mappings

11. New Tables

    versions

    user_pastebins

    Track_notifications

    Tag_prompts

    Tag_prompt_deployments

    suggestions

    suggestion_comments

    submission_records

    signed_up_teams

    sessions

    sections

    score_views

    schema_migrations

    Review_grades

    Review_scores

    review_of_review_scores

    review_comments_paste_bins

    requested_users

    quiz_question_choices

    plagiarism_checker_comparisons

    plagiarism_checker_assignment_submissions

    password_resets

    notifications

    Join_team_requests

    Goldberg_users

    Goldberg_system_settings

    Goldberg_site_controllers

    Goldberg_roles_permissions

    Goldberg_roles

    Goldberg_permissions

    Goldberg_menu_items

    Goldberg_markup_styles

    Goldberg_controller_actions

    Goldberg_content_pages

    Delayed_jobs

    Courses_users

    calculated_penalties

    bookmarks

    bookmark_ratings

    Bids

    Badges

    Awarded_badges

    automated_metareviews

    Answers

    Assignment_badges

    answer_tags

Back to Expertiza_documentation Main page.