Yoga Studio Database Management

Project Overview

This project involves the creation of a comprehensive relational database for managing a yoga studio. The database is designed to handle various aspects of the studio, including class offerings, studio rooms, teachers, students, and class attendance records.

Project Team

• Group 5 (cfg ck23intds10):
  • Seraphima Sara Gatkina
  • Nada Khas
  • Patricia Zayco
  • Deonisia Brito

Database Structure

The database, named "yogastudio," consists of the following tables:

1. class_offers: Information about yoga classes, including type, time, day, cost, studio room, and teacher.
2. studio_room: Details about different studio rooms, including room ID and name.
3. teachers: Information about yoga teachers, including ID, first name, and last name.
4. students: Details of yoga studio members, including ID, first name, last name, age, and membership status.
5. class_actual_records: Records actual class attendance, including record ID, class ID, date, and student ID.

Getting Started

To set up the database, run the provided SQL script in your preferred SQL environment. Ensure that you have a MySQL database server running and replace any placeholder values in the script.

Features

• Data Insertion: Populated tables with sample data to simulate realistic scenarios.
• Data Relationships: Established primary and foreign key constraints to ensure data integrity.
• Views: Created a view combining multiple tables for querying classes, teachers, and studio rooms.
• Stored Function: Implemented a function to calculate total revenue per class type for financial analysis.
• Stored Procedure: Developed a procedure to identify the top spending students based on attendance and class cost.
• Advanced Query: Formulated advanced queries with subqueries, grouping, and logical arrangements.

Usage

The project provides functionalities for data retrieval, financial analysis, and student management. Refer to the script for specific queries, functions, and procedures.

Conclusion

The SQL project concludes with a well-organized database structure catering to the needs of a yoga studio. It is equipped with features for data management, analysis, and reporting.

Feel free to reach out to the project team for any additional information or customization needs.

---

Note: Replace any placeholder values in the script and adapt it to your specific database environment.