Part 1- OBJECTIVE:

Design a database based on client requirements

Part 1- SCENARIO FOR CLIENT

A high school wants to hold a fundraiser called a Golfathon. In this fundraiser, the school asks volunteer golfers to play 100 holes of golf on a specific date. The volunteers sign up to play golf as a way to raise money for one of the school's sports teams.

The volunteer golfers get donors to sponsor them. The donors are asked to pledge a specific amount of money for each hole that the golfer plays

. For example, if the donor pledges $1 per hole and the golfer plays 100 holes of golf, then the donor will donate $100 to the school. Your job is to design a scalable, normalized database to track data about this fundraiser.

DATA REQUIREMENTS:

The database must track the golfers that are participating in the Golfathon

Each golfer will play for only one sports team

More than one golfer can play for the same team

A donor can sponsor more than one golfer

A donor will pledge an amount per hole of golf for each golfer that he/she sponsors

A donor can pledge different per hole amounts for different golfers

For each golfer, you need to track their name, email, and shirt size, and the team that they are playing for.

For each donor, you need to track their name, email, golfers they are sponsoring, the pledge amount for each golfer, the amount of the pledge that has been paid, the payment date, and the payment method.

For each sports team you need to track the sport (e.g. volleyball, golf, etc.) the level (freshman, Junior Varsity, Varsity), and gender of players (boys vs. girls).

Part 2- INSTRUCTIONS

Start with the script created for part 1 of the Database Design Assignment. Add the following to this script:

Insert date into each of your database tables. Be sure to insert at least 5 golfers, at least 3 teams and at least 10 donors. Also be sure to insert at least 1 donor who is sponsoring multiple golfers for different pledge amounts.

Write a query to return the total amount pledged for each sports team. Remember, the pledge amount is per hole played and the goal is to play 100 holes of golf. Show the sports team name, level, and gender. Order the results by total pledged amount from highest to lowest amount.

Write a query to return the total amount pledged for each golfer. List the golfer name, spots team that he/she is playing for, and the total amount pledged. Order the results to show the golfers with the most to the least amount pledged.

Write a query to list the sponsors for each sports team. List the team sport, team level, and team gender. List the sponsor name and total amount pledged for the team. Order the results by Team ID.