Overview In this assignment you will build a sample database similar to the preferred solution for Assignment 1. You will create the tables in MySQL, add sample data and create several queries.

Activities

Step 1. Using MySQL, create the tables: Teams (TeamID, TeamName) Agents (AgentID, FirstName, LastName, Street, City, State, Zip, Phone) Bikes (BikeID, BikeName, Description) Sponsors (SponsorID, SponsorName, Street, City, State, Zip, Phone) Riders (RiderID, FirstName, LastName, Street, City, State, Zip, Phone, DateJoined, AgentID, TeamID) Events (EventID, EventName, Description, Location, Street, City, State, Zip, StartDate, EndDate, SponsorID) Races (RaceID, RaceName, Description, RaceLevel, Location, RaceDate, StartTime, EndTime, EventID) Participation (PID, RiderID, RaceID, BikeID, Placement) Rider_Sponsorship (RSID, RiderID, SponsorID) STRUCTURE NOTES: Use the proper naming convention for your constraints: Example: Constraint TableName_FieldName_ConstraintID (Customer_CusNum_PK) Set up the Primary Keys for each table with Constraints listed. Add Your Foreign Keys for each table with Constraints listed. Make the Data Types for all the Primary Keys and their corresponding Foreign Keys Varchar(4). Make the Data Types for DateJoined, StartDate, EndDate and RaceDate all Date. All Required fields are listed above in red. Use appropriate Constraints. Make the Data Type for the Placement Int(3). Make the Data Types for the remaining fields either fixed or variable length character data. I will leave it up to you do decide which of the remain fields should be fixed vs. variable, as well as determining their size.

Step 2. Use the Insert Into Commands to add your data to the Teams and Agents tables. Team Data '1','Jim's Hardware' '2','Team Huffy 1' '3','Team Huffy 2' '4','Ace's Aces'' '5','Valley Spring Riders' '6','Team Coca Cola' Agents Data '1','Jim','Jenkins','123 One Street','Onervile','OH','83838-3838','(838) 383-8383' '2','lucy','luger','234 Two Street','Twooler','MI','73737-3737','(373) 737-3737' '3','Bob','Bright','345 Three Ave.','Threeley','PA','83737-3638','(763) 643-8738' '4','John','Jones','4565 Four Circle','Fourlord','FL','65445-6677','(566) 363-6776' '5','Stacey','Dagwood','5538 Five Flower Drive','Fiveville','IN','46353-4363', '(317) 363- 5363' EMBRL_insertStatement.txt The remaining of the Insert Statements for the remainder of the database tables can be found at the following file: Step 3. Create the queries listed below. Label each query as indicated. sql file for all queries need to submitted. 1. Select all records from each table - Label Q1a-Q1i 2. List all of your constraints in the database one table at a time. - Label Q2a-Q2i 3. List all of your table names in the database. - Label Q3 4. List the Columns and data types of each table - Label Q4a-Q4i 5. RIDER - POINT TOTALS BY RACE LEVEL LISTING List the Rider's Name, RaceLevel as Race_Level and the total number of all points based on their placement. Make sure that you don't list any riders who have not raced in any races yet (not placed yet). Sort the data from highest to lowest total points. I need a listing for the easy races, one for the intermediate races and one for the advanced races. - Label as Q5a-Q5c. 6. SEARCHING FOR A RUN List the EventName and the RaceName of any records that have the word "Run" in the Race Name. Label as Q6 7. RIDER - TEAM - AGENT BY RACE DATE LISTING List the Rider's Full Name AS "Rider_Name", The Rider's Team Name and their Agent's Full Name listed AS "Agent_Name" .Of those riders only list those who have or are schedule to participate in any races during April 2008 and, make sure that the riders information is only listed once. - Label as Q7 8. SUBQUERY List the Sponsor Name of any sponsors who sponsored both Riders and Events. Label as Q8