CS 2304: Intro to Databases and SQL Spring 2021 Assignment 1 To complete this assignment:

1. Using the data provided in the accompanying spreadsheet, normalize the data by creating the appropriate tables. Please follow all naming conventions.

2. Create an entity-relationship diagram for the VT Obstacle Racing business situation.

Make sure you include all the attributes for each entity.

a. All entities and attributes must follow the naming convention covered in class and provided below.

b. Your database should be fully normalized. If it is not fully normalized, you will need to provide a clear and very convincing argument as to why it isnt.

c. Any and all assumptions should be clearly documented.

Submission:

1. You will be submitting 2 documents. Your normalized database for the provided sample data and your entity-relationship diagram. Both documents should be submitted through the Assignments area in canvas. The uploaded documents should be saved as pdfs.

2. Somewhere on the documents, include your name and email address. Do not include your student ID number.

3. The documents should be named using the following convention: lastname_firstname_assign1_data.pdf lastname_firstname_assign1_diagram.pdf

Assessment: Followed naming convention 5% Neatness/Readability 15% Correctness 20% Fully normalized 30% Meets business requirements 30% Total 100%

Naming Convention:

1. Tables are plural a. Ex: Customers, Orders, OrderDetails

2. Tables and fields should be in camelCase, but the first letter should be capitalized a. Ex: OrderDetails, SaleReps b. Do not ALL CAPS your table or fields c. Do not use any special characters in your table or fields, especially the underscore ( _ )

3. Primary keys should be table name (singular) with pk on the front and the first attribute a. Ex: pkCustomer, pkOrder

4. Foreign keys should be the table name (singular) with ID added to the end a. Ex: In table Customers, a foreign key back to the SalesReps table would be SalesRepID

Very Tiring Obstacle Racing Very Tiring Obstacle Racing (VTOR) is one of the fastest growing companies specializing in obstacle course races. As a result of their unprecedented growth and popularity, VTOR realizes they need to automate and develop a system to track all of their activities and races. Congratulations!! You have been hired to design their database for their new system. There are three different types of obstacle races. The shortest is The Hokie Sprint which is a 5 km race with 20+ obstacles. Next is the Super Hokie which is an 8+ miles race with 25+ obstacles. Finally, there is the Hokie Beast which is a 13 miles race, with over 35 obstacles. To help fund the races, the company provides sponsorships to interested third parties. There are 4 levels of sponsorshipsBronze, Silver, Gold, and Platinum. Sponsors can be associated with more than one race, and a race will have more than one sponsor. However, some sponsors only want to participate in specific race types. In addition, sponsors can sell/give away items at various races. The company needs to keep track of which sponsors will be at which races. In addition, some Sponsors want to sponsor specific obstacles. Each obstacle can only have one sponsor for a particular race. However, that same obstacle might have no sponsor or a different sponsor the next race. A Sponsor can sponsor multiple obstacles during a race. The obstacles vary from race to race and can appear on any of the race types. It is important for the company to track which obstacles have appeared on which races. The company tries to minimize how many times an obstacle appears in a particular region of the United States. In addition, the obstacles are rated based on how hard they are to complete. This way the company can ensure a good variety of obstacle during any one race. Races take place through-out the United States. The company has broken the U.S. down into 5 different regions. This allows them to develop targeted marketing campaigns. At this point, the company has not gone international, nor do they have plans to do so. Contestants can sign up for any race. There are three main reasons why the company needs to keep track of participants. First, they send discounts/coupons and race information via mail and email to keep contestants coming back. Second, once a contestant completes three races in one calendar year, they earn a special medal. Finally, there is a limit as to how many people can participate in a race. For each participant, the company needs to collect an entrance fee, his/her personal information (i.e. name, age, address, email address, etc.), and a signed waiver. Fortunately, the company already has a billing system in place. Therefore, the financial component is out-of-scope for your system. The company has developed a unique RFID system for tracking contestants. The system records when a contestant enters and leaves an obstacle area. This allows VTOR to track how long it takes to complete an obstacle. This information is reviewed on a race-by-race basis to determine if any changes need to be made to an obstacle. Winners are determined by the fastest times. Winners are broken down by gender, age, and overall winner, with 4 winners being announced in each category. Each winner is assigned a number of points. The regional winners (the persons with the most points within a region) are announced at the end of the calendar year.

Player Cod Team Code Last Name First Name Class 85454 5 Alleman Andy JR 85455 5 Allen Alex FR 85456 5 Alphonse Michael FR 84905 8 Coffee Glen FR 1030727 8 Collins Jake SR 73067 8 Collins Matt SO 30138 8 Croyle Brodie SR 81804 37 Farrow Nathan FR 72898 37 Ferguson Lorenzo FR 72872 37 Field Blake FR 72873 37 Gandy Steve FR Position OL RB LB RB WR LB QB DL DB QB DB Team Akron Akron Akron Alabama Alabama Alabama Alabama Auburn Auburn Auburn Auburn Conference Conference 875 Mid-American Conference 875 Mid-American Conference 875 Mid-American Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference Player Cod Team Code Last Name First Name Class 85454 5 Alleman Andy JR 85455 5 Allen Alex FR 85456 5 Alphonse Michael FR 84905 8 Coffee Glen FR 1030727 8 Collins Jake SR 73067 8 Collins Matt SO 30138 8 Croyle Brodie SR 81804 37 Farrow Nathan FR 72898 37 Ferguson Lorenzo FR 72872 37 Field Blake FR 72873 37 Gandy Steve FR Position OL RB LB RB WR LB QB DL DB QB DB Team Akron Akron Akron Alabama Alabama Alabama Alabama Auburn Auburn Auburn Auburn Conference Conference 875 Mid-American Conference 875 Mid-American Conference 875 Mid-American Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference 911 Southeastern Conference