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.
TEST PLAN :
STEPS:
1. Validate the schema expertiza_development 2. Execute the delete sql scripts to remove the redundant records from the tables. 3. Rerun the sql scripts to find out the redundant records in the tables.It should return null records 4. Re validate the schema expertiza_development
- Assignments
- Goldberg
- Reviewing
- Teammate
- Courses
- Hierarchy
- Display
- Questionnaires/Rubrics
- question_advices
- questionnaires
- questions
- Deprecated Tables
- Surveys
- Other Features
- 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.