Documentation on Database Tables: Difference between revisions

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

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
Answers
answers

answer_tags
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
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
Teammate
bids

invitations

join_team_requests

signed_up_teams

teams

teams_users
Courses
courses
Hierarchy
institutions

languages

sessions

sections

versions
Display
nodes

notifications

tree_folders

track_notifications
Questionnaires/Rubrics
question_advices

questionnaires

questions

quiz_question_choices

Deprecated Tables
questionnaire_types
Surveys
survey_deployments

survey_responses
Other Features
bookmarks

bookmark_ratings

sign_up_topics

resubmission_times

ta_mappings

tag_prompts

tag_prompt_deployments
Views
score_views

Back to Expertiza_documentation Main page.

@@ Line 1: / Line 1: @@
 <!--This page is under construction -->
-[[Image:pg_development.png|thumb|right|200px|Expertiza partial database diagram]]
 '''PROJECT TITLE: Regularize Expertiza DB schema'''
   <b>PROBLEM DESCRIPTION:</b>
-   <br>The current Expertiza database has some problems which needs to be rectified.The scope of our project is to rectify these problems by performing the below steps.
+   <br>The current Expertiza database has some problems which needs to be rectified.
+  <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>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>.
+  '''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> 2. Write migrations to regularize the Expertiza database and also change code if necessary.
@@ Line 10: / Line 15: @@
   '''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.
+ '''STEPS TO NAVIGATE TO expertiza_development SCHEMA:'''
+  <br> 1. Open terminal.
+  <br> 2. Change to the "expertiza" folder using the command: <b>$cd expertiza</b>
+  <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 ><b>show databases;</b>
+  <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 ><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]]
 #: [[assignment_questionnaires]]
+#: [[assignment_badges]]
+#: [[calculated_penalties]]
 #: [[deadline_types]]
 #: [[deadline_rights]]
@@ Line 20: / Line 75: @@
 #: [[late_policies]]
 #: [[participants]]
+#: [[plagiarism_checker_comparisons]]
+#: [[plagiarism_checker_assignment_submissions]]
+#: [[submission_records]]
 #: [[wiki_types]]
 #: ''Deprecated Tables''
 #:: [[assignments_questionnaires]]
+# '''Answers'''
+#: [[answers]]
+#: [[answer_tags]]
 # '''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]]
 # '''Reviewing'''
+#: [[automated_metareviews]]
+#: [[awarded_badges]]
+#: [[badges]]
 #: [[comments]]
 #: [[mapping_strategies]]
@@ Line 42: / Line 111: @@
 #: [[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]]
@@ Line 52: / Line 127: @@
 #:: [[teammate_reviews]]
 # '''Teammate'''
+#: [[bids]]
 #: [[invitations]]
+#: [[join_team_requests]]
+#: [[signed_up_teams]]
 #: [[teams]]
 #: [[teams_users]]
 # '''Courses'''
-#: [[Courses_table|courses]]
+#: [[courses_table|courses]]
 # '''Hierarchy'''
 #: [[institutions]]
 #: [[languages]]
+#: [[sessions]]
+#: [[sections]]
+#: [[versions]]
 # '''Display'''
 #: [[nodes]]
+#: [[notifications]]
 #: [[tree_folders]]
+#: [[track_notifications]]
 # '''Questionnaires/Rubrics'''
 #: [[question_advices]]
 #: [[questionnaires]]
-#: [[Questions_table|questions]]
+#: [[questions_table|questions]]
+#: [[quiz_question_choices]]
 #: ''Deprecated Tables''
 #:: [[questionnaire_types]]
 # '''Surveys'''
 #: [[survey_deployments]]
-#: [[survey_participants]]
 #: [[survey_responses]]
 # '''Other Features'''
-#: [[signed_up_users]]
+#: [[bookmarks]]
+#: [[bookmark_ratings]]
 #: [[sign_up_topics]]
 #: [[resubmission_times]]
 #: [[ta_mappings]]
+#: [[tag_prompts]]
+#: [[tag_prompt_deployments]]
+# '''Views'''
+<!-- Saraswati Mishra: New tables -->
+#: [[score_views]]

Documentation on Database Tables: Difference between revisions

Latest revision as of 14:25, 5 June 2021

Navigation menu

Search