Project Overview
This project was developed as part of the CPSC Relational Database course at UBC to design a relational database system that efficiently manages various aspects of a ski resort.
Project Description
Ski resort management involves handling guest information, equipment rentals, lift ticket sales, ski lessons, dining services, and ski infrastructure. The goal of this database is to optimize resort operations and provide structured, accessible data for different stakeholders, including ski instructors, management, and maintenance teams.
The project began with an ER diagram, where key entities were identified, relationships between tables were established, and functional dependencies were defined to normalize the database. After the initial database design, a Relational Schema was developed to define cardinality relationships, ISA hierarchies, and weak entities.
The relational schema was implemented using DDL (Data Definition Language) CREATE TABLE statements, and INSERT statements were used to populate the database. Those statements can be found in a PDF at the bottom of this page. Once the database structure was finalized, SQL queries were written to retrieve specific details. Finally, a frontend user interface was developed to allow resort staff to interact with the system and manage operations defined by the queries.
The following image is the ER diagram that the project follows:
Key Functionalities
- Patron & Ski Instructor Management: Tracks guest details, ski lesson enrollments, and instructor availability.
- Lift & Run Status Tracking: Enables efficient scheduling of maintenance and lift/runs tracking.
- Class Scheduling & Reservations: Helps instructors plan and monitor ski classes.
- Lift Ticket & Restaurant Integration: Offers a way to manage season passes, day tickets, and restaurant discounts.
- Data Analytics: Supports queries like aggregations, joins, and projections to extract insights for resort decision-making.
Queries
The following images demonstrate various queries implemented in the database and their functionality.
Insert: Insert a new rental into the database.
Deletion: Delete a rental from the database.
Update: Update a patron’s name or email address.
Search: Search for a patron.
Join: Join all lifts and runs, then retrieve runs for specific lifts.
Aggregation by GROUP BY: Calculate the total number of classes taught by each ski instructor.
Aggregation by HAVING: Find all days with enrollment below half of the overall average.
Nested Aggregation with GROUP BY: Find the average class size for each skill level.
Division: Find all lifts that provide access to every run of a specific difficulty level (e.g., Lift 1 accesses all green runs).
Conclusion
This project successfully implemented a relational database system to manage ski resort operations efficiently. By leveraging SQL queries, the system enables structured data retrieval and management for various aspects such as rentals, patrons, ski instructors, and lift operations. The combination of entity-relationship modeling, database normalization, and front-end integration allows different stakeholders to access and modify data as needed. This project highlights the practical applications of database management in real-world scenarios and serves as a strong foundation for further improvements and scalability.
Acknowledgements: Initial source code from Tutorial 2 Node.js/JS sample project.