Team 1 MIST4610 Group Project 1

Database information

Name:

cs_g1p1

Team Members:

Noah Leon @noahnbleon
Lenny Keenan @leonardkeenan
Katie Gelderman @katiegelderman

Project

Instruction:

For our project, we were to construct a database and query it sufficiently so that we could demonstrate our knowledge of MySQL. We chose to create a database for a tennis club.

Client:

ChatGPT 4 was the client/owner of the tennis club for whom we built our database. To see our prompt and the response, please visit this link: https://chat.openai.com/share/12733a36-8c47-4f03-92ab-ea641e4d3f43.

Overview:

After consulting our client, we spent some time brainstorming and created an overview of the database:

Tables:

Queries:

1. Our first query allows us to see how many courts are one of the three possible court types. As the club grows, this might become useful for scheduling or planning certain types of tennis events, as the different court types offer different pros and cons.
   

2. Our second query allows the user to assess how many different items the shop has in stock. As management tracks transaction patterns among the Pro Shop customers, it could become important to narrow or widen the available options at the store depending on sales trends.
   

3. Our third query allows the user an element of customer service. In this way, the database could be used for assisting customers in scheduling their training sessions.
   

4. Our fourth query allows the user to assess who is active in our tournaments. This query and others like it could help management gauge participation.
   

5. Our fifth query allows the user to gauge the average salary of all employees. This could be a useful tool that allows management to make choices based on the data returned.
   

6. Our sixth query returns information for the employees currently making a salary above the average employee salary, which could be useful in financial planning.
   

7. Our seventh query allows the user to track attendance trends to gauge which tournaments are more successful in turnout, and might be used to make assumptions regarding why (ie: alluring name; good time of year or good weather; etc).
   

8. Our eighth query allows for cross-checking a member's attendance in a tournament with another member's attendance in the same tournament, to see if there might be any trends in tournament attendance. (For instance, when you run this query with '1' and '4', almost every tournament comes back- perhaps one's attendance impacts the other's desire to come!)
   

9. Our ninth query allows the user to check tournament attendance in another novel way to analyze attendance trends over different times.
   

10. The best for last, our tenth query, is yet another tournament attendance query. This time, we can see the frequency of attendance for all members that have attended at least two tournaments. (The Tournament Analysts at the Ace Tennis Club are rolling in data. They could utilize this data to incentivize attendance; perhaps the member with the most entries in a year might win a prize!)

Additional information:

Each query we've crafted is stored in the database using procedures.

A log of all procedures:

*CALL Q1countTypesOfCourtsIn(aType);
CALL Q2();
CALL Q3();
CALL Q4();
CALL Q5();
CALL Q6();
CALL Q7();
CALL Q8();
CALL Q9();
CALL Q10();

*aType can be 'clay', 'hard', or 'grass'