Timeline: March 5 - 7 (36 hours)
Deliverables: Database, Website
Event: Tartan Hacks 2021
Back-End Team: Kyle Yang and Sara Song
Front-End Team Leah Walko and Lisa Leung
Tools: PostgreSQL, Google Cloud, Flask, Python, Figma, HTML, CSS, Javascript, Bootstrap, Vertabelo, UML, LucidChart
Our plan for this hackathon was to help make vaccine distribution more efficient.
So we created a website called The Vaccine Watch. Using this site, people can find locations offering COVID-19 vaccines in the area and sign up to be put on a mailing list.
The system automatically sorts through the people and finds those most at risk. When vaccines become available in their area, they are automatically emailed and notified via cell phone that they can schedule their appointment.
We gathered data sets of actual Pennsylvania locations offering COVID vaccinations to use for our project. Other than that, all data was fabricated to test the site.
We started off by using our experience from 262, AKA Database Design & Development, to design a quick database.
Using LucidCharts, we created a basic ERD in Crow’s Foot notation that maps out relationships between entities like users, health conditions, vaccine providers, and insurance companies...basically anything that could be related to assessing a user’s risk level, contacting them, and directing them to a vaccine provider.
Given the limited time scope, we rushed straight to creating a physical model in Vertabelo, a database modeling tool that can generate SQL scripts.
Finally, we did some quick research to learn how to use Postgres in Google Cloud in order to create our actual database system.
The backend for the website is written in python. Using flask, we could render the html files and retrieve the input values from the HTML form.
Then, using psycopg, we could connect to an instance of a postgresql database on google cloud. We cleaned csv datasets and added them to buckets in order to be imported into tables in our database.
Currently, a user could enter their information on the website and it would automatically be inserted into the database through a SQL insert statement.
Future features of the project would include more complex SQL queries, updates, and deletes, trying to classify a user’s risk level with machine learning, and using the collected data to direct them to a vaccine provider.