Database Creation for Desklib

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.

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:
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: