UWaterloo Schedule Helper Application

A schedule helper Application created for cs348 (Introduction to Database Management) course to mainly showcase the use of SQL. The app is developed using Posgres, React JS(frontend) and Python3(backend).

Project Description

The Application helps University of Waterloo students find classes that fit into their current schedule, so that they do not have to manually search for classes. Students can easily upload their schedule by copy and pasting their schedule into the app and the app will generate a list of classes they can add. The app shows what classes they can add based on multiple factors.

The project uses University of Waterloo's Open Data API to leverage data on Waterloo's provided courses, classes and instructor info, and stores the necessary information in an SQL database.

More details are provided in the presentation, please check it out:

Presentation and Contributions

Here is the link to our final presentation for the project:

https://drive.google.com/file/d/1oOxVMWzIMNMPm5R02OHhXzFNEaaU-FWO/view?usp=sharing

Team Members

• Ana Wan
• Sunny Yang
• Keat Chong
• Alex Wang

Features

• A simple user signup and login. The username gets stored in the database.
• On the profile page, the user can add the courses that they took in the past. The courses will be added to CoursesTaken table
• User uploads their schedule. Our backend grabs the class numbers of the classes that they are currently taking and stores it in the UserSchedule table. When the user logs in again, the class schedules will be shown on the main page.
• On the main page, the user can view all the classes that they can take in their schedule's free slots. The following scenarios would cause a class to be ineligible:
  • The user has already taken the course.
  • The user is currently taking the course.
  • The user is missing prerequisites.
  • The user has taken an antirequisite.
  • The user is busy during the main lecture time.
  • The user is available during the main lecture time, but is busy during the related component time (like tutorials or tests).

The user can also add and remove classes from their schedule. A dialog will appear if the class has related components.

• On the classes page, the user can view all the available classes. They can filter their search by start time, end time, weekdays, subject and catalog number

• On the courses page, the user can view all the courses that waterloo provides. They can filter their search by subject and catalog number

• On the instructor page, the user can view all classes taught by a certain instructor.

Generating the production dataset

The production dataset is generated by doing many calls to the uWaterloo OpenAPI. To populate our local databases, we run the code in backend/database/scripts/populate_tables.py, which executes the API calls in backend/database/scripts/util_get_waterloo_data.py. Here is a summary of the steps:

1. Get courses from each subject at /courses.
2. Get the course description for each course at /course/{course_id}.
3. Get all classes for each course at /courses/{subject}/{catalog_number}. For each class time associated with a class, insert into ClassTime.

For Developers

To test the app locally

The README.txt of the backend and frontend repo shows the instruction to run the app.

Database Creation

The queries for creating tables are located in the file /backend/database/createtables.sql. The backend app will execute that sql file which creates the tables for the SQL database.

How the Web app receives data from the SQL database

The backend grabs the data from the local SQL database (via sqlalchemy) and sends that data to the web app whenever the webapp executes a GET request to the backend API (on /courses route). The App can also send data to the backend using POST requests that will be used to update the table such as for adding a new user to AppUser

How to populate local database with sample data

For connecting to local database:

Steps:

brew install postgresql

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

psql postgres

Our database name is schedulemaker, type in this when posgres started:

postgres=# CREATE DATABASE schedulemaker

To run scripts for local database:

You can try this:

python3 ./database/scripts/execute_sql.py -f createtables

To Populate all data into local database:

python3 ./database/scripts/populate_tables.py -u yourUsername

To Populate specific table into local database:

python3 ./database/scripts/populate_tables.py -t 'table_you_want_to_populate'