Unformatted Attachment Preview
Journal of Information Systems Education, Vol. 16(4)
Teaching Case
Gr eta's Gym: A Teaching Case for Ter m-Long Database
Pr ojects
Gina C. Gr een
Department of Information Systems
Baylor University
Waco, TX 76798-8005 USA
Gina_Green@baylor.edu
ABSTRACT
The following case supports a term-long team project for a senior-level or graduate-level Database course. It describes a
gymnastics facility whose business processes are primarily manual. Students design and implement a database supporting the
automation of competitive gymnastics team processes. In the analysis and design phase, students gather data from real gyms,
produce a conceptual data model, design and normalize relational tables, document field definitions, and calculate storage
space requirements. In the implementation phase, students write instructions for installing and using their tables, implement
the tables, and write queries, triggers, and stored procedures that manipulate data stored in their tables. This case contributes
to IS teaching in that it provides a fuller integration of database tasks than many existing cases, particularly in its inclusion of
data gathering from actual facilities, installation instructions, storage space calculations, and both stored procedure and trigger
requirements. Information on when and how the project is introduced and managed, and grading tips, is provided to potential
adopters of this case. Proposed solutions are provided for most project deliverables. Feedback from students indicates that the
case provides a satisfying challenge to them, and that skills gained from the project have been invaluable in their subsequent
MIS jobs.
Keywor ds: Data Modeling, Database Design, SQL, Team Project
1. CASE SUMMARY
Greta's Gym is a local gymnastics facility owned by John
and Greta Jones. Greta's Gym has been in operation for 15
years and has grown from two employees (John and Greta)
to 20 employees. In this same timeframe, the gym's
offerings have expanded from girls gymnastics classes only,
to both girls and boys gymnastics and cheerleading classes,
as well as girls and boys competitive team gymnastics. In
addition, the gym sells a full line of gymnastics and
cheerleading clothing and equipment.
While Greta's Gym has experienced tremendous success in
training young gymnasts and cheerleaders, John and Greta
have not been as successful in efficiently managing their
business processes. Major business processes such as
registering gymnasts and cheerleading students, processing
merchandise sales, and managing competitive team
operations are mostly done manually. John and Greta are
considering opening a new gym soon in a nearby town.
With this upcoming expansion, John and Greta realize they
can no longer continue with manual processes; thus, they
have called on your consulting group for help in automating
their business processes.
In the planning stage of your
project, your team, along with John and Greta, determined
that automating the gym's competitive team operations
would be the most beneficial process to implement first.
Therefore, your remaining analysis, design, and
implementation tasks will focus on providing a database
solution to support the automation of Greta's Gym
competitive team operations only.
2. CASE TEXT: Gr eta’s Gym
Greta's Gym, a local gymnastics facility, was opened in 1989
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 wellrespected competitive team program for girls and boys, and
cheerleading classes. In addition to these services, the gym
sells a full line of gymnastics clothing and equipment.
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
387
Journal of Information Systems Education, Vol. 16(4)
tremendously, but they have not changed most of their
manual procedures.
The Jones' are considering opening another gym in
Templetown, which is 45 miles 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
the student to participate in the competitive team program. If
a student accepts the invitation to join the competitive team,
an enrollment sheet is filled out by the gymnast’s parent and
kept on file at the gym. There are currently 125 gymnasts
involved in Greta's Gym Competitive Team Program.
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. Each team level also
has a different practice schedule, as shown in Table 1.
Team
Level
1
2
3
4
5
6
7
8
Min Age
Requirement
n/a
n/a
n/a
6
7
8
10
12
9
12
10
14
Monthly
Fee
$130
$150
$150
$200
$230
$230
$260
$275
Practice Schedule
T Th, 5p - 6p
T Th, 5p - 7p
T Th, 5p - 7p
T - Th, 5p - 7p
T Th F, 5p - 7:30p
T Th F, 5p - 7:30p
M T Th F, 4p - 7p
M 4:15p - 8:15p,
T - Th 3:30p - 8p
$325
M 4:15p - 8:15p,
T - F 3:30p - 8p
$325
M 4:15p - 8:15p,
T - F 3:30p - 8p
Table 1. Team Infor mation
Greta’s Gym Coaching Staff
John, a full-time employee, is the gym's co-owner and head
coach of the boys levels 1-7 competitive teams. Greta, coowner and also a full-time employee, serves as head coach of
the girls' levels 2-5 competitive teams, and teaches a few
preschool classes periodically at the gym. There are two
other full-time employees: 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. If there are no boys
or girls competing at a given team level, there is no head
coach assigned to that team level. The gym also employs ten
part-time class instructors, and 6 part-time team coaches.
There is no need to track which team a part-time coach is
assigned to.
Greta's Gym's full-time coaches receive a fixed monthly
salary, while part-time coaches are paid on an hourly basis.
Each part-time coach can have a different hourly rate,
depending on their experience. Coaches offer private lessons
to competitive team gymnasts; parents pay $25 per hour for
these lessons. 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.
Gymnastics Meets
Competitive team gymnasts compete in gymnastics meets
approximately twice per month. Meets are held in different
locations throughout the state. Not all gymnasts compete at
every meet. Gymnasts pay for each meet they compete in.
Meet fees vary by meet.
Coaches would like to be able to enter individual scores for
each gymnast after each meet they compete in. The coaches
and parents of team gymnasts would like to access the
system to get gymnasts' performance data and do their own
analyses. Each girl performs on 4 events at each meet: vault,
uneven bars, beam, and floor. Each boy performs on 6
events at each meet: parallel bars, high bars, floor, vault,
rings, and pommel horse. Gymnast scores on individual
events range from 0.000 to 10.000. In addition, each
gymnast earns an all-around score for each meet. The allaround score for a gymnast is the sum of the gymnasts’
individual event scores for that meet. Currently, Greta
collects all individual scores on all the events from the
coaches after each meet. She then types them into Excel and
posts them (along with the all-around scores) 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. They
could also use this data to better understand how well they
have to perform at upcoming meets in order to qualify for
state and national competitions. Gymnasts must achieve a
certain average all-around score to qualify for state and
national competitions.
Merchandise Sales
Finally, merchandise, including leotards, grips, leggings,
jackets, mats, t-shirts, shorts, bags and gymnastics pins, is
available for sale to all Greta’s Gym customers, including
competitive team gymnasts. The gym also sells bar chalk,
bandages, wraps, anti-bacterial creams, and other supplies.
All these products have a set reorder point, so that when the
amount on hand reaches that point, the item is reordered
from one of the gym's suppliers.
Competitive Team Billing
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,
minus any payments made during that month. Expenses that
a gymnast can incur each month include their regular
monthly team fee, private lessons taken that month,
merchandise purchases for the month, and/or meet fees for
meets the gymnast competed in that month.
Parents typically give payments to the office manager, who
records the payments in a ledger. The gym accepts checks,
388
Journal of Information Systems Education, Vol. 16(4)
cash, or money orders as forms of payment. The office
manager gives a receipt to the parent after a payment is
received.
2.1 Team Assignment 1 Deliver ables
Team Assignment 1 focuses on the analysis and design
phases of the SDLC. You should include ALL items below
in a soft binder, with TABS separating each item. ALL
items should be clearly labeled. EVERYTHING must be
computer-generated (i.e., no hand-written information). The
front of the binder should have a cover page which includes:
̇
̇
̇
̇
Project title
Date turned in
Team number and Section number
Team member names in alphabetical order
1. Assignment 1 Project Overview
This 1-2 page over view should (1) clearly and briefly
describe your database system, and (2) clearly describe
each specific deliverable you are providing at this stage of
the project. This information should be described in a way
that leaves no doubt that your team is highly organized,
skilled, professional, and understands Greta’s Gym team
processes. Your customer is not an IS person, so do not
confuse him with technical jargon that you do not explain
in clear, understandable terms.
2. Data Gathering
Visit a local gymnastics facility, health and fitness club,
dance studio, or local sports league and find a for m,
r epor t, or other document related to team/class
enrollment, meet/game schedules, meet/game scores, or
merchandise sales. You must have my approval on the
facility you choose prior to turning in your project. The
document you submit must indicate the name and location
of the facility that uses it. Incorporate applicable data
from this document on your ERD. Include the document
in your project binder, and use a Highlighter to highlight
the data elements on the document that you incorporate in
your ERD.
3. Conceptual Data Model: ERD
Use Visio to create an ERD that documents the data
required to support Greta’s Gym competitive team
processes, as documented in the case. Also include
highlighted data from the document you gathered above.
Document any assumptions as appropriate. The ERD
must be clearly labeled, and should not contain any M:M
relationships or multi-valued attributes. Follow all rules
and practices discussed in class.
4. Logical Data Design: Relational Tables
Use Access to design nor malized tables based on your
ERD. Ensure all primary keys, foreign keys, and
referential integrity constraints are clearly labeled using
the convention discussed in class. Populate all tables with
at least 5 rows of sample data. To print the populated
tables, copy and paste them into Word; ensure you include
table names. Try to fit as many tables as you can on a
page. Readability is important (i.e., graded), so you may
show your referential integrity constraints on a separate
page as discussed in class, if desired.
5. Physical Database Design: Fields, Records
(a) Using Visio, produce a Physical Design ERD that
depicts your physical database design. The ERD should
have information on estimated volumes, data
types/lengths, and for eign keys added to each entity.
Label the ERD appropriately. If this ERD differs from
your logical database design (i.e., if you denormalize any
tables), include a brief write-up explaining why there is a
difference. Document assumptions on volumes where
required.
(b) Using Excel or Word, type a 1-2 page document
stating estimated exter nal stor age r equir ements for
each table. Assume that records cannot span pages. Be
sure to explain how you arrived at the numbers. You must
state how many bytes of storage will be required to store
each table, and then a total number of bytes for the entire
database. Remember, leave your customer with no
questions as to how you arrived at these numbers, while at
the same time not overly bombarding him with jargon or
calculations.
2.2 Team Assignment 2 Deliver ables
Team Assignment 2 focuses on the implementation phase of
the SDLC. You should include ALL items below in the
binder, with TABS separating each item. ALL items should
be clearly labeled. EVERYTHING must be computergenerated (ie, no hand-written information). The front of the
binder should have a cover page which includes:
̇
̇
̇
̇
Project title
Date turned in
Team number and Section number
Team member names in alphabetical order
ALL scripts as well as your installation sheet must also be
stored on a floppy disk or CD; the disk must be labeled with
your team number and section number, and SECURLY
ATTACHED inside your project binder.
1. Assignment 2 Project Overview
This 1-2 page over view should (1) remind your customer
of the functionality that your database system is providing,
and (2) clearly describe each specific deliverable of this
phase of the project. This information should be described
in a way that leaves no doubt that your team is highly
organized, skilled, professional, and understands your
customer's business. Your customer is not an IS person,
so do not confuse him with technical jargon that you do
not explain in clear, understandable terms.
2. Assignment 2 Installation Instructions
Include a one-page installation sheet that tells your
customer how to install and access project components
including filenames for the scripts. Specifically, your
installation sheet should list the filenames for the (1) DB
Implementation script, (2) SQL Queries Script, and (3)
389
Journal of Information Systems Education, Vol. 16(4)
DB Removal Script. Ensure you use full path names for
your filenames. Include any special instructions or
examples needed to run your scripts and queries. Make
your instructions clear, concise, and easy to follow.
3. Database Implementation (SQL DDL and DML, PL/SQL)
Write a well-commented script creating three the three
types of objects described below. All script output should
be saved to a file.
(a) QL statements that create and load the tables you
designed in assignment 1. Load the sample data you
identified in team assignment 1. Include SELECT
statements in the script to show the contents of the
tables that have been created/loaded.
Ensure
appropriate check constraints are included.
(b) In this same script, create two database triggers.
The first trigger should calculate and store the allaround score for a gymnast whenever his/her individual
meet scores are initially entered. The second trigger
you create should create a record in an "audit table"
(you must create this table) whenever any meet score is
changed. This record should include the gymnast's ID,
the date the score was changed, the old value of the allaround score, and the userid of the user that performed
the update. Include in the script SQL statements that
cause the triggers you created to "fire", and verifies the
triggers worked.
(c) Also in this same script, create a database stored
procedure that produces the Bill for a given gymnast in
a given month and year, as described in the case
scenario.
The project you turn in should contain this entire DB
implementation scr ipt, as well as the printed output file
from executing the script.
the entire SQL Quer ies scr ipt, as well as the printed
output file from executing the script.
5. Database Removal
Create a short script to remove all objects created for this
project.
Additional Project Tips:
‚
Place a space after beginning comment delimiters
and before ending comment delimiters.
‚
Store all your dates and times using the DATE data
type.
‚
Use the SHOW ERRORS command if you get the
message that your trigger or procedure was created
with compilation errors.
‚
Watch the order in which you create and/or drop
tables—remember that tables with foreign keys in
them should be created last and dropped first.
AUTHOR BIOGRAPHY
Gina C. Gr een is an Assistant Professor of Information
Systems at Baylor University. She
holds a Ph.D. in management
information
systems
from
the
University of South Florida, and an
M.S.E. and B.S. in computer science.
She has worked as a systems engineer,
database administrator, and software
development manager. Her research
interests include the diffusion of process innovations, data
modeling, project management, and cognitive complexity in
computer-supported work. Her work has been published in
IEEE Software, the Journal of High Technology
Management, Information and Software Technology Journal,
Information Resources Management Journal, and others.
She is a member of ACM, SIGMIS, and DSI.
4. Queries, Reports (SQL Queries)
Write a well-commented script containing five SQL
queries that are useful to Greta's Gym. These queries
should include the following four: (1) for each team level,
show the name of the gymnast who has the highest allaround score, (2) print the scores for a given gymnast at a
given meet, (3) list the gymnasts on each team, in
descending order of their average all-around score,
showing only those gymnasts with an average all-around
score greater than 33.250, and (4) produce a bill for a
given gymnast in a given month and year. This fourth
query should simply invoke the stored procedure that
contains this functionality. For all queries (including the
fifth query that you create), more points will be given to
queries that are directly relevant to the case (i.e., useful to
Greta’s Gym), AND demonstrate reasonable complexity
(i.e., includes boolean and other operators, expressions,
functions,
categorizing/sorting,
subqueries,
joins,
substitution variables, and prompts).
Add SQL*Plus statements to script to save results of
queries in a file. The project you turn in should contain
390