BUS ADM 434 Database Management Systems
Group Project: Details
The following case will be used for the project. Bear in mind that it will be used for data modeling, normalization, database design, and implementation in Oracle, SQL-Developer, and Visual Basic. The segments involving modeling, nornzalization, and design; will be performed in a group. To benefit fully from the implementation exercise, you need to have direct hands-on experience with these environments.
Details about individual report requirements and due-dates will be handed out separately. The following description forms the only source of information for completion of the project reports. In areas that it is lacking, appropriate assumptions need to be made.
The system deals with management of data at a local fitness center. While the data may conceptually appear simple, there is a fair amount of complexity that needs to be dealt with.
The Environment
Great Lakes Fitness Center is a small health-oriented athletic club located in the suburban area of a large mid-western city. It is a mature enterprise, established in 1990, and works a little differently from chain fitness clubs. The club was started when four fitness conscious young urban professionals decided that the existing local health clubs, and athletic/fitness centers did not meet their needs in a convenient manner. After a quick initial survey of the potential market, they were able to convince a venture capital firm to invest in the enterprise, with the four founding partners/owners.
The club started as a small enterprise with a limited set of activities - fitness training, running tracks, indoor pools, and accompanying amenities. However, as its popularity grew, it started adding other features, including indoor tennis, racquetball, and basketball courts, and outdoor tennis and volleyball courts. In addition, in response to suggestions from members, it now operates a health-conscious restaurant and concession stand on the club premises. It has hired instructors for each of the different sports it supports, and members and guests can avail of lessons from trained professional. Another change is the sponsoring of numerous special events that can be attended by both members and guests.
The personnel involved in running Great Lakes Fitness Center have been augmented as part of the growth process. The enterprise is overseen by the Club Manager, who has responsibility for membership, billing, finance, and personnel decisions. Decisions involving new facilities are still made in conjunction with the owners. Reporting directly to the Club Manager are billing and accounting clerks, and the cashier. Also reporting to the Club Manager are the other managers in GLFC. The facilities are supervised by the Facilities Manager, who has direct responsibility for the operation and maintenance of equipment and facilities. Persons reporting to the Facilities Manger include the groundskeepers, the equipment service team, and a11'the trainers and instructors. The restaurant and concession stand are under the purview of the Dining Services Manager. The restaurant is served by one health-conscious chef, two assistants, and five waitpersons. The concession stand is operated by-two clerks who alternate shifts. Special events come under the purview of the Club Manager, but in reality are coordinated with the Facilities and the Dining Services Managers.
The club is in growth mode, both in terms of membership (now approaching 2000), as well as the range of activities. This success has had some drawbacks though, primarily reflected in the problems of tracking and billing of activities. In addition, GLFC has had to modify its membership structure. It initially operated using a comprehensive fee basis, whereby a member was changed an annual fee, and could avail of all facilities and amenities. As new facilities were added, retaining this approach would result in significant increases in membership fees - which was not received very well by members who would not be using these new facilities. The owners and managers then decided to go with a fee-for service structure for the additional facilities, retaining the comprehensive fee structure for the basic set of facilities. This has complicated things to some extent, and has resulted in some errors during billing. The owners are of the opinion that a new database driven information system would adequately address these problems, and have retained your services to design the database for this system.
The System
The current system is relatively simple. People seeking to become members fi11 out an application form — illustrated in Figure 1. They are charged an initiation fee at the time of application, and the first installment of their dues. Dues can be paid either in monthly, quarterly, or annual installments. Members may join in an individual, couple, or family category. Prospective members can. pay by cash, check or charge to a credit card. Cash payments are processed immediately. and the applicants are considered members and can avail of the facilities immediately. Applications involving payments by check are deferred till the check clears, and are approved/denied at that stage. Credit card payments involve the immediate charge to the specific card, and a credit verification with the appropriate credit agency. The club accepts VISA, MasterCard, Amex, Diners Club, and Discover.
Upon approval of the initiation fee and initial dues, the member is issued a membership card and a hard copy of the club's rules and regulations. Members are also provided with a small plastic barcode that can be attached to a key-ring for convenient use.
Payment details are recorded on a charge slip. Charge slips are then routed to the billing clerk, who sets up an account for the member. Charge slips for other charges that the member may incur during their use of the facility are also sent to the billing clerk.
Every time members wish to use the club facilities, they present their card at the entrance for admittance. Once admitted, they may use any of the facilities. The billing processes for these facilities are somewhat different though, as alluded to in the section describing the environment Unassisted use of the fitness equipment, running track, indoor pools, saunas, are treated as part of the dues and are not charged. Some of the exercise classes are free, though members have to sign up for them, since the space is limited. These classes are held on a specific schedule, with most classes running 45 minutes, thereby giving members time to wind down and prepare for the next class. Classes are held as long as there is a minimum of five members signed up. Members can sign up at the front desk, or over the phone, or fill up a form that accompanies notices for new classes. Fees are assessed for some advanced exercise classes. A sampling of classes offered appears in Figure 4. Note that all fees are monthly fees. These fees are assessed on the day that the member first attends the class, and are discontinued when the member drops the class. If a member elects to discontinue a class, then they must notify the club to earn the appropriate credit. Supply not showing up does not accomplish the same result, since the instructors do not take note of who attends, particularly for large classes.
Members can also request one-on-one training with instructors. In this case, a fee is assessed, based on the number of training sessions as well as the duration of each session, and the sophistication of training provided. The specific rates are based upon those established by the Club Manager, and are updated annually as part of the personnel exercises. For convenience purposes, this exercise is performed at the start of each calendar year. The member has to sign up in advance with the specific individual, who will fill up an appropriate charge slip and route this to the billing clerk. Typically these are presented as a package and are charged up front For example, a member wishing to learn swimming may sign up for 6 classes for $80 with a specific swim instructor. These will be charged as of the first swim lesson, even though they may span two months. After the set of 6 lessons is complete, the member may sign up for more, depending on their need. As with the exercise classes, a member can cancel these midway through the set, and receive a credit for unused lessons and training sessions.
Other facilities, e.g. the racquetball, tennis, volleyball, and basketball courts must be reserved. They can either l~ reserved in-person or over the phone. In addition, the club also hosts internal tournaments for members, which also restrict availability of these facilities. Some of these reservations may involve training and/or instruction, and will require the. presence of an instructor. In these cases, an instruction fee is assessed. This works in a manner similar to the individual exercise training, with the exception that a facility reservation is also involved. Currently, the reservations are handled through a program available at the-front desk, as illustrated in Figure 5, recording member numbers, reservation date and times, and any instructor involved.
Members may cancel a reservation (or lesson -receiving credit). However, they must cancel at least an hour in advance. Members who reserve a court and fail to use it are initially sent a warning letter. After three such violations, they are assessed a penalty, which is added to their monthly bill.
Dining services are handled in yet another fashion. The concession stand accepts cash and charges to the member account. The restaurant, on the other band, allows payments through cash, check, or charge. The charge could be to a regular credit card, or to the member's account. Receipts from the dining services are routed to the accounting clerk, and also the billing clerk if necessary (when meals are charged to the account).
Special events include lectures by prominent individuals in the discipline, seasonal parties, tournaments, etc. They have specific fees associated with them, and are usually advertised several weeks or months s ahead of time. In addition to posters plastered on the club notice boards, events are advertised by flyers mailed to the members' residence. Members can sign up for these events in person, over the phone, or on a tear-off attached to the flyers mailed to the members. If no payment accompanies the sign-up, it is automatically charged to the member's account at the time of sign-up, regardless of whether the event is in the same month or not. If there is a limit on the capacity of the event, then it becomes. important to monitor that this is not exceeded. Members may cancel their plans about attending a special event. up to three days prior to the event and receive a credit for it.
As a general rule, members can bring in guests, for which they will be billed The rates associated with guests vary with the activity. Guests for special events and dining services are billed at the member rates, while other facilities are covered through a general guest charge.
At the end of each month, the billing clerk generates the total charges accumulated in the month for each member. These include any membership dues, fees for trainers/instructors used, any meals charged to the account, fees for special events, and any other fees assessed (e.g. willful damage of equipment, etc.). A bill is sent out to the member, which is due by the tenth of the month. If the member does not settle by the time it is due, they are regarded as delinquent, and are denied admission to the club. As before the member can pay by cash, check or charge to a regular credit card. All payments received are noted in the member's account.
Members can elect to terminate their membership at any time. If the termination' occurs in the first year, then they are automatically billed the rest of their annual dues in their next monthly statement, since they contract to be members for a minimum of a year. After a year of membership, the members are merely billed their outstanding balance in their account. Members are required to turn in their card and access barcode.
