BI5675: Database and Web Technologies
Assignment: Database Design
THE DANCE MARATHON DATABASE PREVIEW
In this assignment, you will design a relational database for a fund-raising dance marathon
at your university. After your tables are designed and created, you will populate the
database and create seven queries. The queries will address the following ques tions: Which
donors made the largest donations? Which teams earned the most money? Who were the
donors to a specific team? When is a specific team scheduled to dance? Other queries will
calculate how much additional money an anonymous donor is willing to donate and
calculate another donor’s doubling of her donations. The final query will summarize the
donations by donor and team.
BACKGROUND
For years, dance marathons have been a way for university students to create fund-raisers
for good causes. Studies have shown that participation is high and fund-raising is very
successful for such events. You have participated in your university’s dance marathons since
your first year but now you are in charge of the dance marathon at your university and you
realize that a lot of the work involved in running a dance marathon is keeping the
information logically organized. Armed with experience in database design, you decide to
tackle this problem.
You know that the dance marathon is organized into teams. Each team has a designated
leader and team name. Personal information is needed for each participant, including name,
address, phone number, and email address. The database must be able to distinguish
participants with the same name because you attend a large university. Participants might
be on multiple teams because some students belong to multiple organizations that are
sponsoring teams.
Teams are scheduled to lead the dancing at various times during the marathon. Teams may
dance during multiple time slots, and the length of time each team needs to lead the
dancing can vary. For example, the team named the IT Crowd might dance on Saturday
afternoon from 4 to 5 p.m. and then come back again early the next morning from 2 to 3:30
a.m.
Again, the whole premise behind the dance marathon is to raise money, so your database
requires that donors register and enter personal information, such as name and address.
This information is essential for tax deduction purposes. Once registered, a donor can make
a donation to any team or to multiple teams. For example, Joe Smith can donate to the IT
Crowd and to the Money Bags team. Assume that all donations are made via credit card and
are collected by a third party; in other words, your database does not require records of
how a payment is made, just the amount donated.
Once your database is designed and implemented, you know that a number of queries will
be useful. For example, a query to find the largest donation to the marathon will be helpful
because that donor will be formally recognized at the end of the marathon. A similar query
will report which team raises the most money; that team’s name will also be announced at
the end of the marathon. You also need to be able to compile a list of donors to a specific
team, along with their addresses and donation amounts. Other queries will display the
dance schedule for a specific team and calculate how much additional money an
anonymous donor is willing to give based on teams’ total dancing times. Still another query
will double all donations from a generous donor. After the marathon is complete, you’ll
need to submit a report to the university and you need to make a query to support this. The
report will list each of the donors, the teams they donated to, and their donation amounts.
DATABASE DESIGN
In designing your database, you should follow the first four stages of the Database Life Cycle as
shown below.
NOTES:
At the DBMS software selection stage you will choose MySQL as this is the DBMS currently
used by the university
You will not need to install the DBMS as it is already installed by the university
In the Physical Design you need only identify indexes and type of indexes to be used for each
table; this is a very small database likely to be used only by one person so further physical
design activities are not required
Stages after the Testing and evaluation stage are not required
When you have created your tables in MySQL. Use the following guidelines to load
appropriate data records:
o Create records for nine teams with unique names. For each team, create
records for 10 student participants with fictitious names, addresses,
telephone numbers, and email addresses. Associate the student participants
with multiple teams. For example, one student could dance with her hall of
residence and with a university society.
o Create records to show that most teams dance at least twice during the few
days of the dance marathon.
o Create records for at least 10 donors or more. Each donor should donate to
multiple teams.
QUERIES
As part of the Testing phase, you will create seven queries as outlined in the Background
section of this case.
Query 1
The organizers want to know the names of the marathon’s top donors. Create a query that
lists each donor name and calculates their total donations. Make sure to display the largest
donation at the top of the list.
Query 2
You need to determine which team raised the most money. Create a query called Top
Teams that calculates the total amount of money raised by team; display Team Name and
Total Donations as the column headings. List the team with the largest amount of donations
at the top of the query.
Query 3
The team leader of the Bean Counters wants to write thank-you notes to all donors to her
team. She asks you for a list of donors and their total donation amounts. Create a query that
displays columns for Donor Name, Address, City, State, Zip, and Country, and then calculates
Total Donations to the team. Note that donors should not be listed more than once, even if
they made multiple donations.
Query 4
The organizers want to be able to tell each team leader when his or her team is scheduled
to dance. Create a query that prompts the user to enter the team name and then displays
the team name, the team leader’s first and last names and email address, and the dates and
start times the leader’s team is dancing.
Query 5
An anonymous donor is willing to give £10 for each hour that each team dances at the
marathon. Create a query that displays each team name and the date(s) they are dancing
and then calculates the additional donation. The additional donation should be the number
of hours each team dances multiplied by 10. Display the results so that the largest donation
is shown at the top of the query.
Query 6
Donor number 1006 wants to double her donations. Create a query to perform. the
calculation and update the table.
Query 7
Create a query that will feed into the Donation Report. Display columns for the Donor Name,
Team Name, Date, and Donation. Group the data by Donor Name and include totals for
each donor’s donation.
SUBMISSION
Your submission should be in the form. of a single Microsoft Word or PDF document including the
following:
1. Database initial study
2. Database design – this should include all necessary business rules, lists of entities /
attributes/domains/relationships/constraints, ER diagrams, relational schemas and
dependency diagrams, index specifications, etc.
3. Implementation and Loading – you should submit the SQL statements used to create
each table/index/view as well as those used to insert the required data (you can
copy and paste statements from MySQL)
4. Testing and evaluation requires you to run each of the required queries against the
database to ensure that they function with the design you have made – you should
submit the SQL statements for each query (you can copy and paste them from