Documentation on Database Tables: Difference between revisions

From Expertiza_Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
 
(39 intermediate revisions by 4 users not shown)
Line 5: Line 5:
   <br>The current Expertiza database has issues similar to what is described below:
   <br>The current Expertiza database has issues similar to what is described below:
   <br>1. redundant record. For instance, <b>deadline_types</b> table has two <b>signup</b> and <b>team_formation</b> record.
   <br>1. redundant record. For instance, <b>deadline_types</b> table has two <b>signup</b> and <b>team_formation</b> record.
   <br>2. some field name is confused. For instance, in <b>users</b> table <b>assignment_id</b> becomes <b>parent_id</b> and in <b>response_maps_assignment_id</b> becomes  
   <br>2. some field name is confused. For instance, in <b>users</b> table <b>assignment_id</b> becomes <b>parent_id</b> and in <b>response_maps</b> <b>assignment_id</b> becomes  
   <b>reviewed_object_id</b>.
   <b>reviewed_object_id</b>.


   The scope of our project is to rectify these problems by performing the below steps.
   '''The scope of our project is to rectify these problems by performing the below steps.'''
   <br> 1. Go through the Expertiza_development database to find tables that not used any more or redundant records.
   <br> 1. Go through the Expertiza_development database to find tables that not used any more or redundant records.
   <br> 2. Write migrations to regularize the Expertiza database and also change code if necessary.
   <br> 2. Write migrations to regularize the Expertiza database and also change code if necessary.
Line 15: Line 15:


  '''DATABASE TABLE DESCRIPTION:'''
  '''DATABASE TABLE DESCRIPTION:'''
   <br>Below is the old detailed description of the tables in the Expertiza DB schema.<br>Our Primary Job is updating the wiki and making it more informative by including all the changes in the Expertiza Db in this wiki.
   <br>Below is the old detailed description of the tables in the Expertiza DB schema.
  <br>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:'''
  '''STEPS TO NAVIGATE TO expertiza_development SCHEMA:'''
   <br> 1. Open terminal.
   <br> 1. Open terminal.
   <br> 2. Change to the "expertiza" folder using the command: $cd expertiza
   <br> 2. Change to the "expertiza" folder using the command: <b>$cd expertiza</b>
   <br> 3. Invoke the rails database console using $rails dbconsole and give the password "expertiza" when prompted.
   <br> 3. Invoke the rails database console using the command: <b>"$rails dbconsole"</b> and give the password <b>"expertiza"</b> when prompted.
   <br> 4. List the databases present in the MYSQL server using the command >show databases;
   <br> 4. List the databases present in the MYSQL server using the command ><b>show databases;</b>
   <br> 5. Move to the "expertiza_development" database using the command >use expertiza_development
   <br> 5. Move to the "expertiza_development" database using the command ><b>use expertiza_development</b>
   <br> 6. List down the tables present in the "expertiza_development" using the command >show tables;
   <br> 6. List down the tables present in the "expertiza_development" using the command ><b>show tables;</b>


'''FLOWCHART:'''
                                                          [[File:FLOWCHART1.JPG]]
'''FINDING REDUNDANT TABLES'''
<b>STEPS:</b>
<br>  1. Write SQL scripts to find the duplicate records. For example in the table,<b>deadline_types</b> a SQL query to find duplicate records is as follows.
<br>
        <b>select name from deadline_types group by name having count(*)>1;</b>
<br>
        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:'''
<b>STEPS:</b>
<br>  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.
<br>
        <b>delete from deadline_types where id not in (select * from (select min(id) from deadline_types group by name) as temp);</b>
<br>
        Running this query in the SQL prompt,it will delete the duplicate records except one.
'''WRITING THE DATABASE MIGRATION SCRIPTS:'''
<b>STEPS:</b>
<br>  1. Write the rails migration scripts to migrate the SQL that would delete the redundant records for certain table.
<br>      The steps to write the rails migration scripts can be explained in the below steps:
<br>
          <b>STEPS:</b>
<br>        1.1 Open a terminal
<br>        1.2 Navigate to the <b>expertiza</b> folder using the command: <b>$cd expertiza</b>
<br>        1.3 Generate a migration script using the command in the terminal: <b>$ rails generate migration RemoveDuplicateDeadlineTypes</b>
<br>        1.4 Write the SQL query to be executed to delete the redundant records inside the migration script.
<br>        1.5 Save the migration script.
<br>  2. The migration scrips are present in the <b>expertiza/db/migrate</b> folder can be migrated using the command <b>rails db:migrate</b>
'''TEST PLAN :'''
<b>STEPS:</b>
<br> 1. Validate the schema <b>expertiza_development</b>
<br> 2. Rerun the sql scripts to find out the redundant records in the tables.<b>It should return null records</b>
<br> 3. Re validate the schema <b>expertiza_development</b>


# '''Assignments'''
# '''Assignments'''
#: [[assignments]]
#: [[assignments]]
#: [[assignment_questionnaires]]
#: [[assignment_questionnaires]]
#: [[assignment_badges]]
#: [[calculated_penalties]]
#: [[deadline_types]]
#: [[deadline_types]]
#: [[deadline_rights]]
#: [[deadline_rights]]
Line 34: Line 75:
#: [[late_policies]]
#: [[late_policies]]
#: [[participants]]
#: [[participants]]
#: [[plagiarism_checker_comparisons]]
#: [[plagiarism_checker_assignment_submissions]]
#: [[submission_records]]
#: [[wiki_types]]
#: [[wiki_types]]
#: ''Deprecated Tables''
#: ''Deprecated Tables''
#:: [[assignments_questionnaires]]
#:: [[assignments_questionnaires]]
# '''Answers'''
#: [[answers]]
#: [[answer_tags]]
# '''Goldberg'''
# '''Goldberg'''
#: [[content_pages]]
#: [[content_pages]]
#: [[controller_actions]]
#: [[controller_actions]]
#: [[delayed_jobs]]
#: [[markup_styles]]
#: [[markup_styles]]
#: [[menu_items]]
#: [[menu_items]]
#: [[password_resets]]
#: [[permissions]]
#: [[permissions]]
#: [[plugin_schema_info]]
#: [[plugin_schema_info]]
#: [[requested_users]]
#: [[roles]]
#: [[roles]]
#: [[roles_permissions]]
#: [[roles_permissions]]
#: [[schema_info]]
#: [[schema_info]]
#: [[schema_migrations]]
#: [[site_controllers]]
#: [[site_controllers]]
#: [[users]]
#: [[users]]
#: [[user_pastebins]]
#: [[system_settings]]
#: [[system_settings]]
# '''Reviewing'''
# '''Reviewing'''
#: [[automated_metareviews]]
#: [[awarded_badges]]
#: [[badges]]
#: [[comments]]
#: [[comments]]
#: [[mapping_strategies]]
#: [[mapping_strategies]]
Line 56: Line 111:
#: [[response_maps]]
#: [[response_maps]]
#: [[review_strategies]]
#: [[review_strategies]]
#: [[review_grades]]
#: [[review_scores]]
#: [[review_of_review_scores]]
#: [[review_comments_paste_bins]]
#: [[score_caches]]
#: [[score_caches]]
#: [[scores]]
#: [[scores]]
#: [[suggestions]]
#: [[suggestion_comments]]
#: ''Deprecated Tables''
#: ''Deprecated Tables''
#:: [[review_feedbacks]]
#:: [[review_feedbacks]]
Line 66: Line 127:
#:: [[teammate_reviews]]
#:: [[teammate_reviews]]
# '''Teammate'''
# '''Teammate'''
#: [[bids]]
#: [[invitations]]
#: [[invitations]]
#: [[join_team_requests]]
#: [[signed_up_teams]]
#: [[teams]]
#: [[teams]]
#: [[teams_users]]
#: [[teams_users]]
# '''Courses'''
# '''Courses'''
#: [[Courses_table|courses]]
#: [[courses_table|courses]]
# '''Hierarchy'''
# '''Hierarchy'''
#: [[institutions]]
#: [[institutions]]
#: [[languages]]
#: [[languages]]
#: [[sessions]]
#: [[sections]]
#: [[versions]]
# '''Display'''
# '''Display'''
#: [[nodes]]
#: [[nodes]]
#: [[notifications]]
#: [[tree_folders]]
#: [[tree_folders]]
#: [[track_notifications]]
# '''Questionnaires/Rubrics'''
# '''Questionnaires/Rubrics'''
#: [[question_advices]]
#: [[question_advices]]
#: [[questionnaires]]
#: [[questionnaires]]
#: [[Questions_table|questions]]
#: [[questions_table|questions]]
#: [[quiz_question_choices]]
#: ''Deprecated Tables''
#: ''Deprecated Tables''
#:: [[questionnaire_types]]
#:: [[questionnaire_types]]
# '''Surveys'''
# '''Surveys'''
#: [[survey_deployments]]
#: [[survey_deployments]]
#: [[survey_participants]]
#: [[survey_responses]]
#: [[survey_responses]]
# '''Other Features'''
# '''Other Features'''
#: [[signed_up_users]]
#: [[bookmarks]]
#: [[bookmark_ratings]]
#: [[sign_up_topics]]
#: [[sign_up_topics]]
#: [[resubmission_times]]
#: [[resubmission_times]]
#: [[ta_mappings]]
#: [[ta_mappings]]
#: [[tag_prompts]]
#: [[tag_prompt_deployments]]
# '''Views'''
<!-- Saraswati Mishra: New tables -->
#: [[score_views]]




Latest revision as of 14:25, 5 June 2021

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.

      The steps to write the rails migration scripts can be explained in the below steps:


         STEPS:

         1.1 Open a terminal

         1.2 Navigate to the expertiza folder using the command: $cd expertiza

         1.3 Generate a migration script using the command in the terminal: $ rails generate migration RemoveDuplicateDeadlineTypes

         1.4 Write the SQL query to be executed to delete the redundant records inside the migration script.

         1.5 Save the migration script.

   2. The migration scrips are 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
    assignment_badges
    calculated_penalties
    deadline_types
    deadline_rights
    due_dates
    late_policies
    participants
    plagiarism_checker_comparisons
    plagiarism_checker_assignment_submissions
    submission_records
    wiki_types
    Deprecated Tables
    assignments_questionnaires
  2. Answers
    answers
    answer_tags
  3. Goldberg
    content_pages
    controller_actions
    delayed_jobs
    markup_styles
    menu_items
    password_resets
    permissions
    plugin_schema_info
    requested_users
    roles
    roles_permissions
    schema_info
    schema_migrations
    site_controllers
    users
    user_pastebins
    system_settings
  4. Reviewing
    automated_metareviews
    awarded_badges
    badges
    comments
    mapping_strategies
    responses
    response_maps
    review_strategies
    review_grades
    review_scores
    review_of_review_scores
    review_comments_paste_bins
    score_caches
    scores
    suggestions
    suggestion_comments
    Deprecated Tables
    review_feedbacks
    review_mappings
    review_of_review_mappings
    review_of_reviews
    reviews
    teammate_reviews
  5. Teammate
    bids
    invitations
    join_team_requests
    signed_up_teams
    teams
    teams_users
  6. Courses
    courses
  7. Hierarchy
    institutions
    languages
    sessions
    sections
    versions
  8. Display
    nodes
    notifications
    tree_folders
    track_notifications
  9. Questionnaires/Rubrics
    question_advices
    questionnaires
    questions
    quiz_question_choices
    Deprecated Tables
    questionnaire_types
  10. Surveys
    survey_deployments
    survey_responses
  11. Other Features
    bookmarks
    bookmark_ratings
    sign_up_topics
    resubmission_times
    ta_mappings
    tag_prompts
    tag_prompt_deployments
  12. Views
    score_views



Back to Expertiza_documentation Main page.

Retrieved from "https://wiki.expertiza.ncsu.edu/index.php?title=Documentation_on_Database_Tables&oldid=138960"