Question

a gymnastic school with hundreds of students. It needs a database to track all the different classes that are being offered, who is assigned to teach each class, and which students attend each class. Also, it is important to track the progress of each student as they advance. Design a database for the following requirements:

• Students are given a student number when they join the school. This is stored along with their name, date of birth, and the date they joined the school.

• Instructors need to be tracked of their names, address, phone number, email address, the date that they start working as an instructor, along with their instructor status (compensated or volunteer).

• There are two types of instructors: compensated and volunteer. For compensated instructors, the highest level of the courses they can teach (e.g., beginning, intermediate, and advanced) and the current pay rate (i.e., the amount per hour) need to be recorded. For volunteer instructors, their open days of the week (e.g., Mon, Tues, Fri, etc.) need to be recorded.

• A class needed to be stored with class no., title, the level, and the tuition. The tuition depends on the class level; beginning, intermediate, and advanced classes charges different tuition. A class may have multiple class sections (class meetings). Each section needs to store the meeting time, day of the week, and location.

• An instructor may be assigned to teach any number of class sections, but each section has one and only one assigned instructor. Some instructors, especially volunteer instructors, may not be assigned to any class.

• A student will attend many different class sections; and each class meeting is normally attended by many students. Some class meetings may have no students show up for that meeting. New students may not have attended any class meetings yet.

• Students may attend any class of the appropriate level during each week so there is no expectation that any particular student will attend any particular class session. The attendance of students at each individual class section need to be tracked including the section they joined and the grades (pass vs. no pass).

• Each student holds a rank/level in the Gym class. The rank no, rank name, and rank requirements are stored. Each rank will have numerous rank requirements. Each requirement is considered a requirement just for the rank at which the requirement is introduced. Every requirement is associated with a particular rank. All ranks have at least one requirement.

• A given rank may be held by many students. While it is customary to think of a student as having a single rank, it is necessary to track each student’s progress through the ranks. Therefore, every rank that a student attains is kept in the system. The date that a student is awarded each rank should be kept in the system.

Questions:

1.     Develop an ERD (Entity-Relationship Diagram) for the above case

2.     Convert the ERD to the relational tables using MySQL Server

Answer 1