• Complete the Colonial Adventure Tours Exercises 1 10(in the first picture) and submit your responses (or screen shots). Please note that instead of Access, we will be using MySQL.

Introduction to Database Management ration want to gather in the future? What kind CRITICAL THINKING 13. What kinds of unstructured data or big data might the BITS corporation want 13. W of devices might they use for input? How would they store the data? Colonial Adventure Tours Case ure Tours data shown in Figures 1-15 through Answer each of the following questions using the Colonial Adventure Tours data sh 1-19. No computer work is required. 1. List the last name of each guide who does not live in New Hampshire (NH). 2. List the trip name of each trip that has the type Paddling. 3. List the trip name of each trip that has the season Late Spring. Biking and that has a distance longer than 20 miles. 4. List the trip name of each trip that has the type Biking and that has a distance longer 5. List the trip name of each trip that is in the state of Vermont (VT) or that that is in the state of Vermont (VT) or that has a maximum group size greater than 10. list the trip name of each trip that has the season Early Fall or Late Fall 7. How many trips are in the states of Vermont (VT) or Connecticut (CT)? 8. List the trip name of each trip that has Miles Abrams as a guide. List the trip name of each trip that has the type Biking and that has Rita Boyers as a quide For each reservation that has a trip date of July 23, 2018, list the customer's last name, the trio name start location 11. How many reservations have a trip price that is greater than $50.00 but less than $100.00? 19 For each trip that has more than one guide that can lead the trip, list the trip name, the trip type and the first and last names of each guide. 13. For each customer that has more than one reservation, list the customer's first and last names, the trip name. and the trip type. 14. For each reservation with a trip price of greater than $100.00, list the customer's last name, the trip name, and the trip type. 15. List the last name of each customer who has a reservation for a trip in Maine (ME). DAL 16. The trip price in the Reservation table is a per-person price. The total price for a trip includes the total of them KING price and the other fees multiplied by the number of people taking the trip. Should total price be a field in the Reservation table? Why or why not? 17. Currently, trip cost is determined by the number of people taking the trip and the type of p m based only on the type of trip, in which table would you place the trip cost field? Sports Physical Therapy Case Answer each of the following questions using the Sports Physical Therapy dala No computer work is required. sports Physical Therapy data shown in Figures 1-21 through 1-24 I. List the patient number, last name, and first name of every patient. le session number for each session that lasted 60 minutes or more 3. List the font 2. List the session number to Dolm Rivers Chapter 1 new revenue streams. Big data is a source for ongoing discovery and anan information from big data will require new approaches to database mane practices. Md analysis, and the demand for base management, architecture, tools, and INTRODUCTION TO THE COLONIAL A TOURS DATABASE CASE OLONIAL ADVENTURE "business that organizes daylong, guided trips of New England. To support cos, management uses a database to ensure that the company's data is current, Colonial Adventure Tours is a small business that the company's growing business, management use accurate, and easily accessible. In running the guided tours, managemer customers, and reservations. Figure 1 unique four-character code that cons table also stores the guide's last na hire date. uided tours, management gathers and organizes information about guides, trips, reservations. Figure 1-15 shows sample guide data. Each guide is identified by a haracter code that consists of two uppercase letters and two digits. For each guide, the 100 stores the guide's last name, first name, address, city, state, zip code, telephone number, and Guide Guide Num LastName FirstName Address City State Zip Code AM01 Abrams Miles 54 Quest Ave. Williamsburg MA 01096 BROI Boyers Rita 140 Oakton Rd. Jaffrey NH 03452 DH01 Devon Harley 25 Old Ranch Rd. Sunderland MA 01375 GZ01 Gregory Zach 7 Moose Head Rd. Dummer NH 03588 KS01 Kiley Susan 943 Oakton Rd. Jaffrey | NH 03452 KSOZ Kelly Sam 9 Congaree Ave. Fraconia | | NH 03580 MR01 Marston Ray 24 Shenandoah Rd. SpringfieldM A 01101 RHO1 Rowan Hal 12 Heather Rd. Mount Desert ME 04660 SL01 Stevens Lori 15 Riverton Rd. Coventry VT 05825 UGO1 Unser Glory 342 Pineview St. Danbury CT 06810 PhoneNum HireDate 617-555-6032 6/3/2012 603-555-2134 3/4/2012 781-555-7767 1/8/2017 603-535-8765 11/4/2013 603-555-1230 4/8/2016 603-555-0003 6/10/2016 781-555-2323 9/14/2015 207-555-9009 6/2/2014 802-555-3339 9/5/2014 203-555-8534 2/2/2017 FIGURE 1-15 Sample guide data for Colonial Adventure Tours Figure 1-16 shows sample trip data for Colonial Adventure Tours. Each trip is identified by a unique number called Tripld. In addition, management tracks the trip name, the trip's starting location, the state in which the trip originates, the trip's total distance in miles), the trip's maximum group size, the trip's type, and the trip's season. Trip Introduction to Database Management 10 Hicing Tripid Trip Name Arethusa Falls 2 Mt Ascutney - North Peak 3 Mt Ascutney - West Peak 4 Bradbury Mountain Ride 5 Baldpate Mountain 6 Blueberry Mountain 7 Bloomfield - Maidstone 8 Black Pond 9 Big Rock Cave 10 Mt. Cardigan - Firescrew 11 Chocorua Lake Tour 12 Cadillac Mountain Ride 13 Cadillac Mountain Season Summer Late Spring Early Fall Early Fall Late Spring Early Fall 6 Paddling Late Spring 8 Hiking 10 StartLocation Harts Location Weathersfield Weathersfield Lewiston-Auburn North Newry Batchelders Grant Bloomfield Lincoln Tamworth Orange Tamworth Bar Harbor Bar Harbor Franconia Crawford Notch Whitefield Bar Harbor Pinkam Notch 00 ME NH Summer Summer Summer 15 Paddling Summer 16 Biking Early Fall Late Spring Farly Fall Summer Spring Early Fall Early Fall 0 ME 0 Newport Greenville State Distance MakGrpSize Type NH VT 6 Hiking VT 10 Hiking ME 25 8 Biking ME 10 Hiking ME CT NH 12 Hiking NH 10 Hiking NH 7 8 Hiking NH 12 14 Cannon Min ME 15 Crawford Path Presidentials Hike 8 Hiking 16 Cherry Pond 6 Hiking 4 Hiking 17 Huguenot Head Hike 16 Hiking 18 Low Bald Spot Hike 10 Hiking 19 Mason's Farm NH 6 Hiking 20 Lake Mephremagog Tour North Stratford CT 7 Paddling Late Spring 21 Long Pond VT 15 Paddling Late Spring 22 Long Pond Tour Rutland MA 12 Hiking Summer 23 Lower Pond Tour ME 10 Paddling Summer 24 Mt Adams Poland ME 15 Paddling Late Spring Randolph NH 25 Mount Battie Ride 6 Hiking Summer Camden 26 Mount Cardigan Hike 8 Biking Early Fall NH 16 Hiking Late Fall 27 Mt. Chocorua Albany NH 6 10 Hiking Spring 28 Mount Garfield Hike Woodstock NH 5 10 Hiking Early Fall 29 Metacomet-Monadnock Trail Hike Pelham MA 12 Hiking Late Spring 30 McLennan Reservation Hike Tyringham MA 16 Hiking Summer 31 Missisquoi River - VT Lowell VT12 10 Paddling Summer 32 Northern Forest Canoe Trail Stark NH 15 10 Paddling Summer 33 Park Loop Ride Mount Desert Island ME 8 Biking Late Spring 34 Pontook Reservoir Tour Dummer NH 14 Paddling Late Spring 35 Pisgah State Park Ride Northborough NH 10 Biking Summer 36 Pondicherry Trail Ride White Mountains NH 16 Biking Late Spring 37 Seal Beach Harbor Bar Harbor ME 16 Hiking Early Spring 38 Sawyer River Ride Mount Carrigain NH 1 0 18 Biking Early Fall 39 Welch and Dickey Mountains Hike NH5 10 Hiking Summer 40 Wachusett Mountain Princeton MA 8 Hiking Early Spring 41 Westfield River Loop Fort Fairfield ME 10 Biking Late Spring Cardigan 10 27 FIGURE 1-16 Sample trip data for Colonial Adventure Tours for Colonial Adventure Tours. Each customer management stores each customer's last name, first Figure 1-17 on the next page shows sample customer data for Colonial Adventure Tours is identified by a unique customer number. In addition, management stores each customers name, address, city, state. zip code, and telephone number. Ulir understanding of the relationship between custom questions. Which customer made reservation 1800107 For reservations? Answer: Arnold Ocean made reservation 18000 10. Find the tow in the in 1900010 (see Figure 1-18), and then find the customer number 10 cloure 1-17), and determine that the customer name with the customer Karen Busa has reservations for trips on Aust 29, 2018, and cu Karen Busa, find her customer number (124) in the Customer table. Nes table that contain the customer number 124. and reservations. Answer cb try dates des Karen Busa have Karen Busa, find hervations for trin table that contain the mer number (124) en Busa, find is reservation the custom the testomer www in the Reservation table with th a me with the customer number 102 , 2018, and August 27, 201 he Up Next, Teview the Customer cable er number 102 is Arnold Ocean. 27, 2018 To find the trip dates for Next, find allows in the Reservation on The table named Trip Guides shown in Figure 1-19 is used to relate number and the guide number. The trip number in the TripGuides table ma table, and the guide number in the Trin IripGuides table matches a guide number in the some trips use more than one guide. ad guides. It includes the matehes a trip number in the in the Guide table. Note t TripGuides (continued) TriplD GuideNum DH01 20 SL01 19 DLY 21 TripGuides TriplD Guide Num 1 GZ01 1 RHOI AM01 2 SLO1 3 SL01 4 BR01 GZ01 KS01 5 UGO1 6 RHO1 SLO1 8 BR01 9 BR01 10 GZ01 11 DHOI 11 KS01 11 UG01 12 BRO1 13 RH01 14 KS02 15 GZ01 16 KSO2 17 RH01 18 KS02 AMO1 22 UG01 23 DH01 23 SLO1 24 BR01 25 BRO1 26 GZ01 27 GZ01 28 BRO1 29 DH01 30 AM01 31 SLOT 32 KS01 33 UG01 34 KS01 35 GZ01 36 KSOZ 37 RH01 38 KSO2 39 BROT 40 DH01 41 BR01 FIGURE 1-19 Table used to relate trips and guides Introduction to Database Management ration want to gather in the future? What kind CRITICAL THINKING 13. What kinds of unstructured data or big data might the BITS corporation want 13. W of devices might they use for input? How would they store the data? Colonial Adventure Tours Case ure Tours data shown in Figures 1-15 through Answer each of the following questions using the Colonial Adventure Tours data sh 1-19. No computer work is required. 1. List the last name of each guide who does not live in New Hampshire (NH). 2. List the trip name of each trip that has the type Paddling. 3. List the trip name of each trip that has the season Late Spring. Biking and that has a distance longer than 20 miles. 4. List the trip name of each trip that has the type Biking and that has a distance longer 5. List the trip name of each trip that is in the state of Vermont (VT) or that that is in the state of Vermont (VT) or that has a maximum group size greater than 10. list the trip name of each trip that has the season Early Fall or Late Fall 7. How many trips are in the states of Vermont (VT) or Connecticut (CT)? 8. List the trip name of each trip that has Miles Abrams as a guide. List the trip name of each trip that has the type Biking and that has Rita Boyers as a quide For each reservation that has a trip date of July 23, 2018, list the customer's last name, the trio name start location 11. How many reservations have a trip price that is greater than $50.00 but less than $100.00? 19 For each trip that has more than one guide that can lead the trip, list the trip name, the trip type and the first and last names of each guide. 13. For each customer that has more than one reservation, list the customer's first and last names, the trip name. and the trip type. 14. For each reservation with a trip price of greater than $100.00, list the customer's last name, the trip name, and the trip type. 15. List the last name of each customer who has a reservation for a trip in Maine (ME). DAL 16. The trip price in the Reservation table is a per-person price. The total price for a trip includes the total of them KING price and the other fees multiplied by the number of people taking the trip. Should total price be a field in the Reservation table? Why or why not? 17. Currently, trip cost is determined by the number of people taking the trip and the type of p m based only on the type of trip, in which table would you place the trip cost field? Sports Physical Therapy Case Answer each of the following questions using the Sports Physical Therapy dala No computer work is required. sports Physical Therapy data shown in Figures 1-21 through 1-24 I. List the patient number, last name, and first name of every patient. le session number for each session that lasted 60 minutes or more 3. List the font 2. List the session number to Dolm Rivers Chapter 1 new revenue streams. Big data is a source for ongoing discovery and anan information from big data will require new approaches to database mane practices. Md analysis, and the demand for base management, architecture, tools, and INTRODUCTION TO THE COLONIAL A TOURS DATABASE CASE OLONIAL ADVENTURE "business that organizes daylong, guided trips of New England. To support cos, management uses a database to ensure that the company's data is current, Colonial Adventure Tours is a small business that the company's growing business, management use accurate, and easily accessible. In running the guided tours, managemer customers, and reservations. Figure 1 unique four-character code that cons table also stores the guide's last na hire date. uided tours, management gathers and organizes information about guides, trips, reservations. Figure 1-15 shows sample guide data. Each guide is identified by a haracter code that consists of two uppercase letters and two digits. For each guide, the 100 stores the guide's last name, first name, address, city, state, zip code, telephone number, and Guide Guide Num LastName FirstName Address City State Zip Code AM01 Abrams Miles 54 Quest Ave. Williamsburg MA 01096 BROI Boyers Rita 140 Oakton Rd. Jaffrey NH 03452 DH01 Devon Harley 25 Old Ranch Rd. Sunderland MA 01375 GZ01 Gregory Zach 7 Moose Head Rd. Dummer NH 03588 KS01 Kiley Susan 943 Oakton Rd. Jaffrey | NH 03452 KSOZ Kelly Sam 9 Congaree Ave. Fraconia | | NH 03580 MR01 Marston Ray 24 Shenandoah Rd. SpringfieldM A 01101 RHO1 Rowan Hal 12 Heather Rd. Mount Desert ME 04660 SL01 Stevens Lori 15 Riverton Rd. Coventry VT 05825 UGO1 Unser Glory 342 Pineview St. Danbury CT 06810 PhoneNum HireDate 617-555-6032 6/3/2012 603-555-2134 3/4/2012 781-555-7767 1/8/2017 603-535-8765 11/4/2013 603-555-1230 4/8/2016 603-555-0003 6/10/2016 781-555-2323 9/14/2015 207-555-9009 6/2/2014 802-555-3339 9/5/2014 203-555-8534 2/2/2017 FIGURE 1-15 Sample guide data for Colonial Adventure Tours Figure 1-16 shows sample trip data for Colonial Adventure Tours. Each trip is identified by a unique number called Tripld. In addition, management tracks the trip name, the trip's starting location, the state in which the trip originates, the trip's total distance in miles), the trip's maximum group size, the trip's type, and the trip's season. Trip Introduction to Database Management 10 Hicing Tripid Trip Name Arethusa Falls 2 Mt Ascutney - North Peak 3 Mt Ascutney - West Peak 4 Bradbury Mountain Ride 5 Baldpate Mountain 6 Blueberry Mountain 7 Bloomfield - Maidstone 8 Black Pond 9 Big Rock Cave 10 Mt. Cardigan - Firescrew 11 Chocorua Lake Tour 12 Cadillac Mountain Ride 13 Cadillac Mountain Season Summer Late Spring Early Fall Early Fall Late Spring Early Fall 6 Paddling Late Spring 8 Hiking 10 StartLocation Harts Location Weathersfield Weathersfield Lewiston-Auburn North Newry Batchelders Grant Bloomfield Lincoln Tamworth Orange Tamworth Bar Harbor Bar Harbor Franconia Crawford Notch Whitefield Bar Harbor Pinkam Notch 00 ME NH Summer Summer Summer 15 Paddling Summer 16 Biking Early Fall Late Spring Farly Fall Summer Spring Early Fall Early Fall 0 ME 0 Newport Greenville State Distance MakGrpSize Type NH VT 6 Hiking VT 10 Hiking ME 25 8 Biking ME 10 Hiking ME CT NH 12 Hiking NH 10 Hiking NH 7 8 Hiking NH 12 14 Cannon Min ME 15 Crawford Path Presidentials Hike 8 Hiking 16 Cherry Pond 6 Hiking 4 Hiking 17 Huguenot Head Hike 16 Hiking 18 Low Bald Spot Hike 10 Hiking 19 Mason's Farm NH 6 Hiking 20 Lake Mephremagog Tour North Stratford CT 7 Paddling Late Spring 21 Long Pond VT 15 Paddling Late Spring 22 Long Pond Tour Rutland MA 12 Hiking Summer 23 Lower Pond Tour ME 10 Paddling Summer 24 Mt Adams Poland ME 15 Paddling Late Spring Randolph NH 25 Mount Battie Ride 6 Hiking Summer Camden 26 Mount Cardigan Hike 8 Biking Early Fall NH 16 Hiking Late Fall 27 Mt. Chocorua Albany NH 6 10 Hiking Spring 28 Mount Garfield Hike Woodstock NH 5 10 Hiking Early Fall 29 Metacomet-Monadnock Trail Hike Pelham MA 12 Hiking Late Spring 30 McLennan Reservation Hike Tyringham MA 16 Hiking Summer 31 Missisquoi River - VT Lowell VT12 10 Paddling Summer 32 Northern Forest Canoe Trail Stark NH 15 10 Paddling Summer 33 Park Loop Ride Mount Desert Island ME 8 Biking Late Spring 34 Pontook Reservoir Tour Dummer NH 14 Paddling Late Spring 35 Pisgah State Park Ride Northborough NH 10 Biking Summer 36 Pondicherry Trail Ride White Mountains NH 16 Biking Late Spring 37 Seal Beach Harbor Bar Harbor ME 16 Hiking Early Spring 38 Sawyer River Ride Mount Carrigain NH 1 0 18 Biking Early Fall 39 Welch and Dickey Mountains Hike NH5 10 Hiking Summer 40 Wachusett Mountain Princeton MA 8 Hiking Early Spring 41 Westfield River Loop Fort Fairfield ME 10 Biking Late Spring Cardigan 10 27 FIGURE 1-16 Sample trip data for Colonial Adventure Tours for Colonial Adventure Tours. Each customer management stores each customer's last name, first Figure 1-17 on the next page shows sample customer data for Colonial Adventure Tours is identified by a unique customer number. In addition, management stores each customers name, address, city, state. zip code, and telephone number. Ulir understanding of the relationship between custom questions. Which customer made reservation 1800107 For reservations? Answer: Arnold Ocean made reservation 18000 10. Find the tow in the in 1900010 (see Figure 1-18), and then find the customer number 10 cloure 1-17), and determine that the customer name with the customer Karen Busa has reservations for trips on Aust 29, 2018, and cu Karen Busa, find her customer number (124) in the Customer table. Nes table that contain the customer number 124. and reservations. Answer cb try dates des Karen Busa have Karen Busa, find hervations for trin table that contain the mer number (124) en Busa, find is reservation the custom the testomer www in the Reservation table with th a me with the customer number 102 , 2018, and August 27, 201 he Up Next, Teview the Customer cable er number 102 is Arnold Ocean. 27, 2018 To find the trip dates for Next, find allows in the Reservation on The table named Trip Guides shown in Figure 1-19 is used to relate number and the guide number. The trip number in the TripGuides table ma table, and the guide number in the Trin IripGuides table matches a guide number in the some trips use more than one guide. ad guides. It includes the matehes a trip number in the in the Guide table. Note t TripGuides (continued) TriplD GuideNum DH01 20 SL01 19 DLY 21 TripGuides TriplD Guide Num 1 GZ01 1 RHOI AM01 2 SLO1 3 SL01 4 BR01 GZ01 KS01 5 UGO1 6 RHO1 SLO1 8 BR01 9 BR01 10 GZ01 11 DHOI 11 KS01 11 UG01 12 BRO1 13 RH01 14 KS02 15 GZ01 16 KSO2 17 RH01 18 KS02 AMO1 22 UG01 23 DH01 23 SLO1 24 BR01 25 BRO1 26 GZ01 27 GZ01 28 BRO1 29 DH01 30 AM01 31 SLOT 32 KS01 33 UG01 34 KS01 35 GZ01 36 KSOZ 37 RH01 38 KSO2 39 BROT 40 DH01 41 BR01 FIGURE 1-19 Table used to relate trips and guides