Note

Please refer to https://github.com/RahulVC02/dbms_a3/blob/main/Alumni%20Database%20Management%20System_4.pdf for the final report of Assignment 4.

DBMS Assignment 3 - Alumni Database Webapp

Made with Flask and MySQL

G1 already populated the database with dummy/imaginary values into it in the Assignment 2 submission. The dump file was also submitted in the Assignment 2.

Setup (G1 and G2)

Install Python 3.6.5 or above
Install MySQL 5.7 or above
Install the required packages for python:
```
pip install flask
pip install flask-mysql
pip install flask-session
pip install pymysql
pip install cryptography
```
Run the above commands in the terminal in order to install the required packages.
Create a database named alumni in MySQL

In order to do this, run the 'database_dump' file in the 'Dump Files' directory in your SQL Workbench. This will create the database and populate it with the required tables and data.
Create a database named users in MySQL

In order to do this, run the 'users_database_dump' file in the 'Dump Files' directory in your SQL Workbench. This will create the database and populate it with the required tables and data.
Create a new user 'student' in your MYSQL Workbench.

In order to do this, do the following:
- Login in to MYSQL Workbench using your root user.
- Click on the 'Administration' tab.
- Click on the 'Users and Privileges' option.
- Click on the 'Add Account' button.
- Enter the following details keeping the rest of the fields as default:
  - Login Name: student
  - Password: Pass@1234
- Click on the 'Apply' button.
- Click on 'Administrative Roles' and then click on 'Revoke All Privileges'.
- Click on 'Schmema Privileges' and then click on 'Revoke All Privileges'.
- Go to your home tab in Workbench and then add a new connection by clicking here:
- Here Enter the following:
  - Connection Name: student
  - Username: student
- Click on 'Test Connection' and then enter the password Pass@1234 when prompted. Then click on 'OK'.
Repeat the entire above point for a new user 'employee' in your MYSQL Workbench with the following details:
- Login Name: employee
- Connection Name: employee
- Username: employee
- Password: Pass@5678
Grant Permissions to the 'student' role.

In order to do this, run the 'student_role_permissions' file in the 'Dump Files' directory in your SQL Workbench. This will grant the required permissions to the 'student' role.
Grant Permissions to the 'employee' role.

In order to do this, run the 'employee_role_permissions' file in the 'Dump Files' directory in your SQL Workbench. This will grant the required permissions to the 'employee' role.
Now we need the update the credentials in the 'credentials. yaml' file.

Here in 'mysql_password1' field, enter the password of your root user in MySQL. In 'mysql_password2' and 'mysql_password3' fields, enter the passwords of the 'student' and 'employee' users respectively which we decided above.

Make sure you have the following credentials in the 'credentials.yaml' file:
```
mysql_host: 'localhost'
mysql_user1: 'root'
mysql_user2: 'student'
mysql_user3: 'employee'

mysql_password1: 'Your SQL Root Password'
mysql_password2: 'Pass@1234'
mysql_password3: 'Pass@5678'

mysql_db: 'alumni'
login_db: 'users'
```

Running the Webapp

Now that we have setup the database and the credentails, we can run the webapp.

Open the terminal and navigate to the directory where the 'web_app.py' file is located.
Run the following command in the terminal:
```
python web_app.py
```
or
```
python3 web_app.py
```
depending on your python version.

Or you can simply click on the 'Run' button in your IDE for the 'web_app.py' file.
Open your browser and go to the URL that is displayed in the terminal, like this:
```
http://127.0.0.1:4999
```

This will open the home page of the webapp.

Welcome Screen

Tables

Admin
Employee
Student

Operations

Insert
- Before
- After
Update
- Before
- After
Delete
- Before
- After
Rename
- Before
- After
Where
- Where is being implemented in the above examples.

Views

Alumni Details
Alumni Education
Alumni Achievements
Alumni Extra-curriculars
Alumni Projects
Alumni Work Experience
Instructors
Faculty Advisors

Insert operation is not allowed in Views because its not a table of the database. Below is the error shown after inserting in one of the Views.

Only specific actions can be performed when logged in as different users based on the privileges granted to that user.

Only Insert and Delete actions can be performed by an employee.
A student is only allowed to view the tables available to it and not make any changes.

Work Distribution

Rahul Chembakasseril

Worked on developing the backend using Flask, MySQLdb from scratch. Set up the backend integration pipeline and query processing functionality through the multiple backend routes.
Developed basic wireframe for the frontend (proof of concept) and integrated those pages with the backend.

Kanishk Singhal

Worked with flask and html to add routes to various webpage.
Setup login and authentication feature from scratch which identifies various roles of users.
Setup connection to different databases in the webapp using appropriate libraries so that different roles can access database from their account(admin, students and employees).
Used tailwindCSS to modify the frontend of the project.

Inderjeet Singh Bhullar

Worked on developing the web application using Flask and Integrating our database in the web app using MySQL.
Worked on developing the final rendered HTML pages for the web application.
Contributed to the Front-end development of these HTML pages using CSS, Bootstrap and Tailwind in order to make it look user-friendly.
Worked on creating the Navigation bar for the web application that makes it easier for the users to navigate.
Helped in developing the ‘student’ and ‘employee’ roles for our database and granting them the desired permissions and privileges along with contributing to the Readme file.

Kalash Kankaria

Contributed to the Front-end with styling and also with the addition of a navigation bar in our web app.
Worked on the roles/ users and their privileges, i.e., admin, student, and employee
Created various views and granted their privileges based on logical reasoning that should be provided to a student as a role/ user
Helped with the development of the backend of our Web app and integrating our MySQL database.

Bhavesh Jain

Worked on flask integration with MySQL.
Worked on designing backend routes.
Worked on combined work of G1&G2.

Joy Makwana

Worked on the Front-end using HTML, CSS and Tailwind.
Documented the screenshots of the execution in the ReadMe.MD file.
Worked on the combined work of G1&G2

Medhansh Singh

Developed Front-end using HTML, CSS and Tailwind CSS.
Setup the required connections to fully test our database and the webapp.
Tested the webapp to check if it supports dynamic operations.
Documented the testing output with snapshots in the ReadMe.MD file.

Nokzendi Aier

Worked on the front-end with HTML, CSS and Tailwind CSS.
Setup the required connections to fully test our database and the webapp.
Tested the webapp, views, and dynamic operations.
Helped in debugging errors found during testing.
Documented the testing output and snapshots.
Contributed documentation to the ReadMe.MD file.

Harshvardhan Vala

Worked on the front-end with tailwindCSS and HTML.
Setup the required connections to fully test our database and the webapp.
Tested the webapp, dynamic operations, and documenting the screenshots of successfull operations readme.

Dhairya Shah

Worked on the frontend of the WebApp using tailwind.
Setup the required connections to fully test our database and the webapp.
Tested the webapp, views, and dynamic operations.
Testing views, clicked screenshots of successful executions, and documented them in readme.

RahulVC02/Alumni-Database-Webapp