UW Housing & Food Services
WELCOME TO
UW HOUSING & FOOD SERVICES
Duration | September 2020 - December 2020
Role | Project Manager, Data Engineer, Data Analyst
Class | INFO 430
0.0 | WHO / WHAT IS UW HFS?
TEAM
Timothy Joo
Louis Ta
Tyson Thoi
Shravan Seshadri
TOOLS
Microsoft SQL Server
Azure Data Studio
Lucid Chart
Adobe Photoshop
UW HFS is from the University of Washington-Seattle Campus and is the Housing and Food Services. It was founded in Seattle, WA by UW. It offers services ranging from student meal plans, dorm rentals, and equipment rental.
It is a service provided to the students at UW if housing is needed.
In 2019, UW HFS had 9,734 students using on-campus housing.
1.0 | WHAT’S THE ISSUE?… DATA EFFICIENCY
Our database accumulates all student housing and food records including dining plans, dining services, living accommodations, leases, incidents, room amenities, and renting equipment into one database for our stakeholders.
Our database system will help our stakeholders assign students to their housing, assist in sorting & managing mail, and manage employees.
Given the new housing regulations by the CDC due to COVID-19, a centralized system that keeps track of and manages all the data related to student housing will be effective in overseeing the operations of student housing.
1.1 | Stakeholders
HFS
UW Mailing/Desk Services
UW Administration
Guest Services
3.0 | FINAL ERD DESIGN
2.0 | INITIAL ERD
Within our first draft, we wanted to build a deeper understanding of our stakeholders and their needs.
MAIN GOALS
Build initial entities to build empathy with stakeholders
Receive a general idea of how students connect with all other entities
Overall, our team knew that we created a good foundation. However, we recognized that our final draft needed more clarity and detail.
MAIN GOALS
Implement a cart system for students to purchase food items with their dining plan.
Identify and solidify the many-to-many relationships such as tblSTUDENT_MEAL_PLAN, recognizing the relationship between tblSTUDENT and tblMEALPLAN
Added more lookup tables such as tblLOCATION and tblROOM_TYPE
Defined deeper attributes for all tables
4.0 | DATA VISUALIZATIONS
4.2 | Relative Size distribution of incidents
4.1 | Number of StudentS with Dining Plans by Student Birth
As an Administrator at UW HFS, this illustration is essential because of the following:
Able to see age demographics that have meal plans
Able to strategize ways to increase student meal plan percentages by age range
Notice on the very right, in 1997, there is a massive drop. This is good to note as that is a year the UW Admin can focus on.
As an Administrator at UW HFS, this illustration is essential because of the following:
Due to the size and color of the box, they are able to see the highest recorded incidents
Admin is able to strategize and plan ahead for the most frequent incidents and create a safer campus for students
5.0 | FINAL THOUGHTS
Through this project, I was able to dive deeper into SQL and its higher-level performance/capabilities.
Some topics include:
Base Stored Procedures
GetID Stored Procedure
Data Warehousing
Views / Temporary Objects
NoSQL & Relational Theory
Error Handling
Database Design
I could not be more thankful for the opportunity. This project helped me realize that I found a deep love and passion for SQL and I’m excited for where it is going to take me.
Special shoutout to Greg Hay for being the absolute best professor I’ve had at my time at the University of Washington. You the best my guy!