Microsoft Office Tutorials and References
In Depth Information
Case 3: The Intramural Sports Database
THE INTRAMURAL SPORTS
Designing a Relational Database to Create Tables, Forms,
Queries, and Reports
In this case, you will design a relational database for a paper-based system that tracks students who
participate in intramural sports at the local university. After your design is complete and correct, you will populate
the tables with data and create a form, queries, and a report. The form will be used for adding new members
to the teams. The queries will address the following questions: Which students have not had a medical exam?
Which students are members of a particular team? How long do the games last? How many students are on
each team? Which students have quit a team, meaning they can be deleted from the active database? Finally,
you will create a report that lists the amount of time each referee has worked in the intramural games.
Before attempting this case, you should have some experience in database design and in using
Complete any part of Database Design Tutorial A that your instructor assigns.
Complete any part of Access Tutorial B that your instructor assigns, or refer to the tutorial as
Refer to Tutorial F as necessary.
You are a student at a local university and have landed a part-time job with the intramural sports department
because of your expertise in database design and implementation. The intramural sports department has
been keeping track of its members, teams, and events on paper, and now it is drowning in paperwork. You
have been hired to design a database and later implement it in Microsoft Access.
On the first day at your job, you interview John, the director of the intramural program. John tells you
what types of information he has been keeping on paper and how he uses the information.
John keeps an enormous three-ring binder that contains a tab for each intramural sport. For example,
there are tabs for squash, disk golf, and swimming. Each tabbed section has a cover sheet that lists the name
of the sport, the names of the intramural teams, and the faculty adviser. Each team is named for a color, and
each color is unique. If John needs to get in touch with a team’s faculty adviser, he opens his address book,
which is ordered by last name, and finds the adviser’s telephone number. In all the years that John has been
directing the program, he has never had two faculty advisers with the same last name.
Each cover sheet is followed by a preprinted sheet that is filled out for each student on an intramural
team for the particular sport. The sheet contains personal information about the students, including local and
home addresses and health insurance numbers. John is careful to record each student’s identification number
because some students have the same name. The bottom of the sheet includes a space to note whether the
student has had a medical exam this year.