Final Group Project for the Database and Applications Course - Monsoon'20 Semester
- K Aditya Hari
- Prince Varshney
- Shashwat Goel
A Menu-based Command Line Interface Database system to support a social networking platform for learning, with study group aggregation for the entire spectrum of courses available on the Internet. More details in our Phase 1 WhitePaper.
To run this on your system you need:
- SQL Server
- Python Module - PyMySQL
- To install MySQL in Windows use this link https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/windows-install-archive.html
For Ubuntu use this link https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04
For MacOS use this link https://database.guide/how-to-install-sql-server-on-a-mac/ - For Python Libraries run these commands on your terminal-
pip3 install pymysql
-
Make sure the user 'temp' is created with port 'localhost' along with an empty database called UNIVERSITY in your SQL server before running these commands.
-
Loading:
mysql -u temp data.sql -
Use the command
python main.pyto start the CLI.
A Menu based system was chosen instead of a command-based one to better reflect the different screens that would be available in a full-fledged realisation of this project, which if developed in the real-world would have a GUI to support layman users.
On executing the required command to run the program, you are prompted to choose between the User/Admin view. The User view requires a sign-up and then a log-in, but not the Admin view.
The User view has the functions available to the typical user of our platform
Being a social platform, there is obviously a feature to add friends! This helps you keep in touch with what your friends are learning, join study groups with them, view their blogs and reviews for courses, and a lot more! (in the future ;) )
Here you can list your interests for the world to see! You can also browse through the vast collection of courses which have study groups linked on our platform, and enroll in the ones of your choice, based on your preferred language, group size, crowd-sourced ratings and much more!
You can make your own posts (and edit and delete them). These can either be blogs, where you can share your knowledge, talk about what you're upto, how the platform helped you or reviews for a specific course, to let the community know what you think about it! You can also view the posts of your favourite user, which doesn't have to be a friend! Think LinkedIn, but for learning not Imposter Syndrome (even though this README reads like a LinkedIn post, brb, gonna post it there).
As a study group admin, you can manage the information of your study group that is seen by people around the world. You can make changes to reflect user roles, maintain how much they are contributing, add languages, courses and events (both meet announcements and targets).
As a user, you can currently rate the study groups you are a part of, more features later!
The Admin view is from the perspective of the back-end developers.
As an admin, you get to add/delete the availability of courses, subjects, languages, users themselves (ok not so boring), and possible interests.
But more importantly, you can create analysis reports using the rich variety of data available for selling insigh.. optimising the user experience ✴️ 2 of these are already implemented and are described in the compliance report!
The folder src contains 4 .py files, main.py, User.py, Admin.py and univutil.py. User.py contains the User class, which has functions for the User view. Similarly, Admin has the Admin class with functions for Admin view. main.py is the caller function that combines these together and prints the initial menus. univutil.py just contains some utility functions needed by multiple files.
The folder References contains the work of the older phases for reference. This shows the progress of the project over time. CHANGELOG.md describes the changes from Phase 3 to 4 (current). definition.sql contains the definition of the UNIVERSITY schema for readability. data.sql loads the definition with some basic test data for exemplifying the working of our database system.
We have implemented a super-set of the functional requirements described in our Phase 1 document. We had to make some changes, which are mentioned in CHANGELOG.md. Apart from the required functionality, we have also used triggers and views extensively. This section describes in detail the exact function where the requirements were met in different ways.
- Show subjects available - User.py/show_subject()
- Show course offerings - User.py/show_offerings() and main.py/see_all()
- Display posts of a user - User.py/view_user_posts()
- List of active study groups for a particular course - User.py/showSgForCourse()
- Show available courses by language code - main.py/see_available()
- Show available courses by subject name - main.py/see_available()
- Obtain passwords for authentication - main.py/login()
- Obtain friends for a user - User.py/show_friends_details()
- Show study groups of a user (with constraints on role being Member and Admin)- User.py/rate_sg() and User.py/admin_sg()
- Show languages of a particular study group - User.py/enroll()
- Show role for user in a particular study group - User.py/update_usercontrib()
- Obtain status of a study group - User.py/change_sgstatus()
- Count number of events in a study group - User.py/create_event()
- Count number of posts made by a user - User.py/make_post()
- Show average rating, number of users, number of events of different study groups for a course - User.py/showSgForCourse()
- Multiple used in Analysis 1 and 2
- Show subjects by keywords (using like) - main.py/see_available()
- Search friend details with a subset of their name - User.py/show_friends_details()
As ours is a social platform, it is important to track the social aspects, i.e. what do the 'friends' in our database share. We do this by tracking the average number of study groups shared between a pair of friends, and the average number of common interests between a pair of friends. This also reveals insights on how students make friends across the globe just by being connected through study groups on our platform!
Analysis 2 demonstrates the efficacy of the platform, and gives further insights on learning by correlating user outcomes with study groups, and friendships the user had within the study group. This data can not only inform our platform itself, but educators around the world on the value of healthy collaboration in learning.
The analysis generated is for an input-specified course, as mixing data across different styles and content would add noise to the report generated. This also allows individual instructors data to optimize their course accordingly.
User 'outcome' is seen as a mean of the User's performance and the rating the user gives to the course. This is because we believe a learner's satisfaction and motivation must be accounted for in the outcomes as well, not just how well they 'scored'.
The final data presented in this analysis report contains (sorted in descending order of 'outcome') for the particular course each user's number of study groups enrolled, average contribution across these study groups, average rating provided by the user for these groups, average number of students per group, and most interesting of all, average number of "friends" the user has across these study groups.
To show one possible way to quickly use this data, aggregate statistics of the above are shown for the top half and bottom half of the users based on outcome (again, this is for a particular course)
- Enrolling user in a course+study_group - User.py/enroll() and User.py/addoption_studygroup()
- Create a new study group related to a language and course - User.py/enroll()
- Add languages and courses to a study group - User.py/addoption_stduygroup()
- Add a meet to a study group - User.py/create_meet()
- Add a target to a study group - User.py/create_target()
- Create event in a study group - User.py/create_event()
- Create pin in a study group - User.py/create_pin()
- Add blog/review to a study group - User.py/make_post()
- Add tags to a blog - User.py/make_post()
- Add a friendship relation between users - User.py/befriend()
- Add a user to the database (on signup) - Admin.py/add_user()
- Add a course and it's difficulty (separate queries) - Admin.py/add_course()
- Add languages for a course - Admin.py/add_course()
- Add course to subject - Admin.py/add_course()
- Add a course instructor to a course - Admin.py/add_course()
- Add a prerequisite to a course - Admin.py/add_prerequisite()
- Add a subject - Admin.py/add_subject()
- Add a language - Admin.py/add_language()
- Add a language known to a user - Admin.py/add_languageknown()
- Change status of a study group - User.py/change_sgstatus()
- Update contribution rating for a member - User.py/update_usercontrib()
- Update role of a user - User.py/update_usercontrib()
- Update rating of a study_group as a member - User.py/rate_sg()
- Update a particular post of a user - User.py/update_post()
- Update interest type for a user - User.py/update_interest
- Delete a particular post of a user - User.py/delete_post()
- Delete a course - Admin.py/delete_course()
Special thanks to our mentors Jaidev and Mallika for valuable feedback and clearing our doubts throughout the duration of the course. We hope you enjoy playing with our project (pls resist the urge to break it, we dont like injections 👶 😢 ) a little more than what we can say for writing all this SQL 😛