E-R model

Prepare an entity relationship model to support the operations of the Gretas Gym. as described. Note that the current way of doing things and storing data may not be the best way. So design a data model that is good and also support the functions. Clearly indicates the attributes of the entities and the relationships.

Entity Definition Sheet A Primary Key Attributes 1 Entity name Business Definition- Customer A customer is any person who has brought from us repeatedly. Customer Number Customer Number + CustomerName + CustomerGender + CustomerAddress + ... 4 Data Element Definition Sheet Domain Name Customer Number 1 64 Description Data Types Length A unique Integer code which is used to identify customer Gender of Character 12 the customer Address of Character 100% the customer CustomerGender "F" or "M" CustomerAddress 1. Greta's Gym Database Project Greta's Gym, a local gymnastics facility, was opened by John and Greta Jones. Initially the gym focused on gymnastics classes for girls. The gym has since expanded its programs to include gymnastics classes for boys, a well-respected competitive team program for girls and boys, and cheerleading classes. Initially, John and Greta were the only employees and every business process was handled manually. New employees have been added and the number of students has grown tremendously, but they have not changed most of their manual procedures. The Jones' are considering opening another gym, which is 45 km south of the current gym. They know it will be impossible to manually monitor activities if they have two gyms to manage. Automating competitive team administrative activities will help greatly in managing the two gyms. Competitive Teams When gymnastics students reach a certain skill level in the regular class program, John or Greta may choose to invite them to participate in the competitive team program. If a gymnast accepts the invitation to join the competitive team, the start date of joining particular team level is recorded. There are currently 125 gymnasts involved in Greta's Gym Competitive Team Program. Each gymnast has a unique gymnast number. The name, age, gender, and phone number of the gymnast are also recorded. There are 10 team levels for boys and girls, some team levels have minimum age requirements, others do not. For example, level 10 is the highest (i.e. most skilled) level and has a minimum age requirement of 14 years. Level 1 is the lowest skill level and has no minimum age requirement. Level 6 has a minimum age requirement of 8 years. Gymnasts on each team pay a fixed monthly fee for team practices, this team fee varies by level. Team Levele 1 2e 32 42 52 Min Age Requiremente Monthly Fee n/a 1300 n/a 15002 n/a 1500 62 2000 23002 8 2300 102 2600 12e 2750 12 32502 142 3250 76 he 7 8 102 Since gymnasts will progress from one level to a higher level, the joining date and the leaving date of a gymnast for a team level need to be recorded. Moreover, when a student stop participating in competitive team training, the leaving date will also be filled. Greta's Gym Staff Gym has 15 full-time employees, some of them are coaches and some are responsible for trative works. For example, Kristin is the girls' levels 8-10 head coach, and Annie serves as the gym's office manager. There is only one head coach per team level. John and Greta only want to know who the current head coach of each team level is. No historical coach assignment needs to be recorded. If there are no boys or girls competing at a given team level, there is no head coach assigned to that team level. Each employee has a unique employee ID. The name, age, gender, phone number, monthly salary and position of the employee are also recorded. Coaches offer private lessons to competitive team gymnasts, parents pay $200 per hour for these lessons. The data recorded about each private lesson includes the gymnast and the coach involved, the date, the start time, and the duration (in term of hours) of the private lesson. Less than half the gymnasts take private lessons each year, but for those who do, the private lesson costs are added to the bill the gymnast receives each month. Coaches can share 30% of the incomes from private lessons that they taught in addition to their monthly salary. Gymnastics Meets Competitive team gymnasts compete in gymnastics meets approximately twice per month. Meets are held in different locations. Not all gymnasts compete at every meet. Gymnasts pay for each meet they compete in Meet fees vary by meet. Data about meets include a unique meet ID, date, time, location, and the fee. Coaches would like to be able to enter individual scores and ranking for each gymnast after each meet they compete in Gymnast scores range from 0.00 to 50.00. Currently, Greta collects all scores and rankings on all the events from the coaches after each meet. She then types them into Excel and posts them on the team bulletin board. Coaches would like to analyze team member scores to see if, for example, scores are generally lower on a particular event, if scores for boys or girls at a particular level are lower than boys and girls at other levels. Monthly Bill Bills are sent to team members monthly. The bill lists the total amount due from the gymnast for that month, which includes expenses incurred by the gymnast for that month. Expenses that a gymnast can incur each month include their regular monthly team fee, private lessons taken that month, and/or meet fees for meets the gymnast competed in that month. Each bill has a unique bill number. The issue date and due date of the bill are also recorded. (Note: A real-life gym information system will be much more complex. I greatly simplified it to contain the scope. For example, in real-life, historical records of head coach of the team needs to be kept, payment processing needs to be included, etc.) This case is based on Green, G. C. (2005). Greta's Gym: A Teaching Case for Term-Long Database Projects. Journal of Information Systems Education, 10(4)