Team Name:
Group 4
Team Members:
Lindsay Fleishman @LindsayFleishman
Jonathan Li @JonathanL02
Omar Meshaal @OmarMeshaal2004
Arti Girish @artig123
Anish Kapoor @a-kapoor731
Problem Description:
The purpose of the project is to model and create a relational database describing any scenario of choosing. For our group, we created a relational database showing the general workings of a gym. We displayed the relationships between the entities that we believe are the backbone and foundation every gym needs, and we populated the entities with sampling data to show how each relationship accurately represents the business. In addition, we used the data to perform queries to gather more information on the inner workings of a gym, and how these queries can help us lead to business decisions to further improve the gym.
Data Model:
Explanation of data model:
Based off of our personal experience with gyms, and what we personally believe is necessary for a gym to operate, our model is based on the structure of a hypothetical gym.
The Employee entity represents the different workers inside the gym based on their ID. Within this entity, there is also a One-to-One recursive relationship as gyms have supervisors that supervise other employees within their department. This can help those that are supervised to learn how to teach classes and more from more experienced workers there. The employees have a direct relationship with the department that they work in, thus a many to one relationship as many employees can be in one department. Our Department entity represents the different areas within the gym (Front Desk, Sanitation, Swimming, Rec Sports, etc.). We believe the Department entity truly represents all of the aspects within the gym.
Our Employee entity has a one to many relationship with session entity, as one employee can teach many sessions at the gym. For example, a session can happen many times a week in the gym schedule, and an employee can be assigned to teach all of those sessions. Each session can be taught by one employee. The Session entity includes the time, duration, and attendance of classes, which is valuable to understanding the success of a certain workout class and track the popularity of sessions.
There are many branches that come from the session entity that include Classes, Rooms, and Members. One workout class can have multiple sessions, as the popularity of a class can result in it being on the workout schedule more often. This is displayed in a one to many relationship between the entities. Additionally, many members can take as many sessions as they desire at the gym. Because of this, the relationship between the Member entity and Session entity is a many to many relationship, resulting in an associative entity "Members_Taking_Sessions". The Members_Taking_Sessions table allows the user to see which members are in what sessions, if the members complete a session, how they would rate it, and which employees are teaching these sessions. Many members can be under one type of membership, represented by a one to many relationship. A membership can have many members, but each member can only have one membership to the gym. The Membership table shows the cost, type, and description of the membership as well as how often the members can go with that membership package. This helps to understand the range in memberships that is provided at the gym.
For Sessions entity, many Sessions can be in one room. However, you can have a workout session without a room, for example, outdoor classes like running or yoga. This is represented by an indirect many to one relationship between the Sessions entity and the Rooms entity. Each room has one amenity, and an amentiy can only exist in one room, meaning there is a one to one identifying relationship between the two. The Amenities table (Spa, Food, Massage, Pool, etc.) shows the benefits of the gym membership that clients can receive.
Lastly, a gym is not complete without its equipment, which can be a valuable tool in a workout. The Equipment table includes the equipment name, as well as its ID, to track what the gym owns in inventory. This has a direct many to one relationship to the Rooms table, as one room can have many pieces of equipment for the workout offered in that room.
Overall, the data model truly organizes the functionality of a gym, and pieces together how the business of the gym can build revenue.
Data Dictionary:
Queries:
- Query 1 lists the number of members taking a specific class, grouping by the name of the class and ordering by descending.
Query 1 allows managers to see which sessions are the most popular, which can be seen by the count of members taking each class. This information is likely needed to help organize scheduling, to make sure the classes that have the highest turnout are more populated on the schedule than those that do not have anyone taking it. Managers are also able to identify trends through this query by understanding more about their members and clients' needs.
- Query 2 lists the number of sessions that are above the average class duration, which are grouped by the type of class.
A class that goes above the duration time can impact the scheduling of classes, as this can be due by an instructor teaching off the normal time or a class that's offered for a longer period of time for those who want to workout for longer. If the session duration is longer than average and the managers don't schedule around this, it may cause delays to classes that are right after the session before, giving employees a harder time to change over. In addition, the number of classes offered can allow managers to see who are taking these longer sessions, and if the time it is offered fits those certain members; maybe it is students after school or people who are taking a lunch time break. It helps for managers to achieve their goal of making sure each session gets capacity members taking it for most profit.
- Query 3 lists the members who are not taking any classes at the gym. The results were ordered by the members' last names.
Query 3 shows insight into the specific information on the clients that do not attend any sessions at the gym. This is an analysis tool to understanding if there are any patterns with those who do not take classes: do they prefer personal training? working out on their own? This offers new ideas and strategies to outreach to these customers to make sure the gym is still profitting from them, maybe offering personal training instead of sessions for a different charge.
-
Query 4 lists the least popular membership, grouping by membership type and counting how many people have each type of membership.
By analyzing the least popular membership, the business can determine the next steps for what type of membership should be involved instead to achieve higher amount of clients attending the gym. Showing results for least popular membership can help managers see if they should get rid of the membership completely and stick with the other types or adjust it slightly to market it better to the target market.
-
Query 5 finds employees who supervise the most people and their rating for sessions is less than 5.
Query 5 shows us if supervising is helping employees properly learn to teach sessions, or if the members do not like these teachers due to it. Those that supervise and have a poor rating show us they should not be supervisors since the memebrs do not like their classes. This also shows us that if a supervisor is supervising too many people, is it hindering their ratings and is there any correlation between that.
- Query 6 lists the members who take sessions between January and March, as well as October through December, and lists the percentage of sessions in comparison to the total yearly sessions.
This query allows managers to analyze and compare these two seasons to see how popular the gym is. October through December is holiday season when members usually take vacations, so this query can show us if there is a dip in the amount of memebrs that take sessions during this time. January through March is usually when gyms pick back up in attendance, so managers can see if this is true through the query.
- Query 7 lists the amount of members grouped by where they are from.
This query allows managers to get a better understanding of the demographics of their clients. If more come from a certain area, this can help with marketing strategies to help relate to the members and produce more revenue.
- Query 8 lists the rooms that have treadmills and dumbbels in them and the amount in each room.
This query can serve as an inventory check for the business to track how much and where all of the equipment is. This can help in regards to if a specific piece of equipment needs a repurchase, if there a rooms that don't already have the equipment in it and need it for a certain session, or if more members join a session in that room and a piece of equipment is needed. This organizes and analyzes inventory, a key component to running a gym.
- Query 9 lists the employees and members where the employee taught the same member in at least 2 of their sessions.
This query allows the business to see who the members' favorite teachers are, and can help to make the business decision on whether the employee should teach more sessions and if that will help populate the schedule. For example, the same employee in this table was favored by multiple members, showing that the employee can be promoted to a higher teaching position if they believe it will bring in more revenue and better feedback.
- Query 10 lists how many employees are supervised under the supervisor for a specialty.
Query 10 allows managers to see which departments may be understaffed and if there is any depth within each department that can help. For example, recreational sports is much more staffed than spin class and pilates. This can help with decision making for hiring and if and when they may need to hire, like if pilates becomes more popular.
Database information:
Additional Information: The queries are stored in the Stored Procedures section of the database which can be called using CALL TP_QX();
Name of the database: ns_4610Fa24Group4