Database Design

You have been asked to design and implement a relational database to keep track of the following information for a yoga centre. Each Yoga Student must be referred to by a Yoga Guru and must present a prescription for yoga therapy in order to receive treatments. The center only treats yoga Patients who are at least 18 years old. The center only employs Therapists who have at least 2 years of prior experience. A Yoga Student may be referred to by different Ayurvedic Doctors at different times. The database must store each prescription, both current and past. Each prescription will list a diagnosis such as “sprained yoga” or “torn yoga” A Yoga Student must make an Appointment for a specific date and time. A Yoga Student can only make one Appointment for any given day. When a Yoga Student visits the center at the appointed time he will be assigned a specific Physical Therapist. A Yoga Student can be treated by different Yoga Guru Therapists on different days. The system will record which Yoga Gurus treated the Yoga Student on a particular Visit to the center. The system will also record what Treatment the Yoga Student received and what Equipment he used. A Yoga Student can receive more than one type of Treatment and use more than one piece of Equipment at any given Visit. Sample equipment “stationary bicycle” , “treadmill” , “weights” Sample treatment: “heat” “ice” “electrical stimulation” “ultrasound” “massage” Using MySQL Workbench draw an ERD to present your proposed design. You must identify what attributes are likely to be stored for each entity based on prior knowledge or research. Use SQL Server to implement your design, include PKs, FKs, and constraints. Use SQL Server to populate the database with at least 5 records in each table Write queries to answer the following questions: How many yoga Patients has each Yoga Gurus seen in the past month? Which piece of equipment has never been used? List all information available for yoga Patients who have been at the center List all information available for Therapists who work at the center Which Yoga Gurus has seen the most yoga Patients Which Yoga Student is the oldest Yoga Student in the clinic? Which Yoga Gurus has the most job experience? For each Therapist, list the Yoga Gurus name and the name of each Yoga Student he has treated. For each Patient, list the Yoga Student name and the name of each piece of equipment he has used. For each Patient, list the Yoga Student name and the name of each type of treatment he has received.