Database Design, Implementation and Reporting for HEA Achieve

Verified

Added on 2022/12/27

AI Summary

This assignment details the creation of a database for the HEA Achieve project, using MySQL. The solution includes the database creation script, table creation with primary and foreign key constraints, and data population using insert queries. It also covers the implementation of triggers to automatically set HEA type, the creation of views to simplify complex queries, and the development of various SQL queries for reporting purposes, divided into participant, mentor, peer reviewer, workshop, and scheme periodic and historical reporting sections. Furthermore, the assignment utilizes Tableau for data visualization, creating bar charts to visualize candidate enrollment trends. This comprehensive solution demonstrates a complete database system implementation, addressing data storage, retrieval, and presentation requirements.

Database Creation Part:
As per the details given about the HEA programs in the university. The main operation happens in this
scheme is the registration for the program and the workshop enrollment and allocation of the mentors.
The purpose is to migrate the manual entry system from excel to DBMS. The Tool which is used for this
development of the database is MySQL.
In MySQL, the database named “SLU” is created. For creation this database ,Script is document is
created and which is executed on the MySQL database. The database creation script has scripts for table
creation which has primary key constraints and foreign key constraints. There in total 11 tables are
created in the database which is created by keeping in mind the current flow of operations in the
system. All the tables has foreign key constraint which maintains the integrity of the database and does
not allow any records from deletion or updation or insertion until the parent table has its value.
The below snapshot shows the execution of the query on MYSQL and the later snapshot shows the
tables which are created in the database.
Here the on the database SLU the scripts are executed and it will created all tables with all the
constraints which are defined in the scripts.
After successful completion of the script of database creation the below tables are visible inside the
database and number of rows are zero as till now we have not executed the scripts for insertion. Hence
the column “Rows” has value as 0.

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

DATA Population in the table with the help of Insert queries:
In this section the insert queries are created on the table which are created in the above section:
The insert queries are executed on the database and the rows are added to the table as shown below:
After successful completion of the insert queries the column of the below snapshot has values
corresponding to the number of rows in the table.

Triggers:
Trigger Created on the table Candidate_enrolled which by default set the HEA type to 1.
Triggers has important function in the database, these are the events which are called based on the
definition (Befor / After ) of the trigger. There three different type of triggers Deletion, Insertion and
Updation. Here in this database we have created the trigger on the insertion on the table
Candidate_enrolled. The purpose of the trigger here is to add the value in the HEA_Type default to 1
whenever the insertion starts on this table. This event occurs before inserting into the table.
Similarly other kind of triggers can also be created on the other tables based on the requirement.
Views:

The purpose of the view is to simplify the complex queries and use directly view like a table. Here the
view is created to get the active mentors, it will simplify to get the active mentors in the system just by
selecting directly from view.
View Created to get the Mentors who are active:
Getting results from newly created view:
Select Queries:

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

In this section the focus is to create queries which are for the achieve reporting. This section has queries
divided into six section and each section has 4 queries and in total there are 24 queries. These queries
uses foreign key constraints to join the other tables.
Participants:
Query 1: All participants those how have enrolled for any scheme:
Here in this query the data is extracted by using the table Candidate_enrolled and candidates table. The
candidate table has the details of the candidate and the candidate_enrolled table has the details about
enrollment of the student. The query has filter to check that the student has registered, if student is
registered then pull firstname and surname from the table candidates.
Output:
Query 2: Show all active Participant report:

Here in this query the data is extracted by using the table Candidate_enrolled and candidates table. The
candidate table has the details of the candidate and the candidate_enrolled table has the details about
enrollment of the student. The query has filter to check that the student enrollment enddate is not over.
Output:
Query 3: Show participants with a specific submission date
Output:

Query 4: Show all participants by school
Here in this query the data is extracted by using the table Candidate_enrolled and candidates table. The
candidate table has the details of the candidate and the candidate_enrolled table has the details about
enrollment of the student. The query uses the group by to group the user on the basis of schools.
Output:

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

Mentors:
Query 5: Show all mentors (institution)
Here in this query the data is extracted by using the table mentor_reviewers and mentors table. The
mentors table has the details of the mentors and the mentor_reviewers table has the details about
mentors and reviewers. The query has filter to check that the mentor has gone through the mentor
training is yes then he is mentor.
Output:
Query 6: Show all mentors in a specified school:
Here in this query the data is extracted by using the table mentor_reviewers and mentors table. The
mentors table has the details of the mentors and the mentor_reviewers table has the details about
mentors and reviewers.

Query 7: Show mentors by school and fellowship category
Here in this query the data is extracted by using the table mentor_reviewers and mentors table. The
mentors table has the details of the mentors and the mentor_reviewers table has the details about
mentors and reviewers. The group operation is applied on mentors school
Output:

Query 8: Show all active mentors
Here in this query the data is extracted by using the table mentor_reviewers and mentors table. The
mentors table has the details of the mentors and the mentor_reviewers table has the details about
mentors and reviewers. The query filters the mentors who are active by checking column Active =YES.
Output:
Peer reviewers:

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

Query 9: Show all peer reviewers (institution)
Here in this query the data is extracted by using the table mentor_reviewers and mentors table. The
mentors table has the details of the peer reviewer and the mentor_reviewers table has the details about
mentors and reviewers. The filter is applied on attended_peer_training.
Query 10: Show all peer reviewers for a specified school
Here in this query the data is extracted by using the table mentor_reviewers and mentors table. The
mentors table has the details of the peer reviewer and the mentor_reviewers table has the details about
mentors and reviewers. Group operation is applied on the school of the peer reviewer.