讲解：SQL、Database Design 、SQL、Database and Web Technologies

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 al代做SQL实验作业、Database Design 程序代做、代做留学生SQL语言、Database and Web Tready 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 & 转自：http://ass.3daixie.com/2018060448780592.html