Complete the Colonial Adventure Tours Exercises 1-4
u lo tourth specifik only have one supervisor u s his woman S is considering changing urth normal form Client Lue . La Raupervis , Buper cu supood D P a changing its business models that many consulat can models that many consultants an ice on can represent many clients. Using hieman Information on the towing un d t ent , Cliente Street, City State Code alance. Credi t , Const , Las Pirot , Street e the following normalized relation to olourth normal omsing consultants can represent one client and one consultare many che Intel P et rate client clientName, street, B e t e Balance Convert the following many consultants nts as in Question CreditLimit) any difference between the tables you created in Quis 4 and 52 Wh or wity not The following us onial Adventure Tours Case wwwing exercises are based on the Colonial Adventure Tours has shown in Figures 1-15 per 1. No computer work is required Gatabase shown in Figures 1-15 through 1-19 in ng the types of entities found in the Color A s dients and reserve create an example of a table that is in first normal form but not in second normal form and an ecame on is in condo o m but not in thind normal form chase out ons and convert to the higher forms Colonial Adventure Tours is considering changing the way hande reservations. Instead of storing the number of persons associated with one reservation, the company would like to wore the name and address of each person associated with each reservation Colonial A nure Tour decides to implement this change, men orice and other fee amounts for each trip would be dependent on only the trio ID Determine the multivalued dependencies in the following table, and then convert this table to an equivalent collection of tables that are in fourth normal form: Reservation Reservation D. TripID, Tripate. Trap Price, Other Fees, (Client Num, ClientLastName, Client Frame Address, City State. ZipCode, Phone)) Identify the functional dependencies in the following unnormalized table Convert the table to third normal form. Is the result also in fourth normal form? Why or why not? Trip (TripID, TripName, State Abbreviation, Stateltane, (GuideNum, GuideLast, GuideFirst)) 4. Currently, each trip is identified with only season. For example, the Arethusa Falls trip is offered only in the Summer season. Colonial Adventure Tours is considering offering the same trip in more than one seasonthat is, the Arethusa Falls trip could be offered in both the Summer and Late Spring seasons. Using this new infor- mation, identify all dependencies and convert the current Trip table to third normal form. You may need to make some assumptions. Identify these assumptions in your solution Chapter 1 new revenue streams Big data is a source for ongoing discovery and analysis, and the demand for Information from big data will require new approaches to database management, architecture, tools practions INTRODUCTION TO THE COLONIAL ADVENTURE TOURS DATABASE CASE Colonial Adventure Tours is a small business that organizes daylong guided trips of New England. To suppo the company's growing business management uses a database to ensure that the company's data is current accurate, and easily accessible In running the guided tours, management gathers and organizes information about guides, trips, customers, and reservations. Figure 1-15 shows sample guide data. Each guide is identified by a unique four-character code that consists of two uppercase letters and two digits. For each guide, the table also stores the guide's last name, first name, address, city, state, zip code, telephone number, and hire date Guide City State ZipCode GuideNum LastName FirstName Address AMO1 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 G201 Gregory Zach Moose Head Rd. Dummer NH 03588 KSOI Kiley Susan 943 Oakton Rd Jaffrey NH 03452 KSO2 Kelly Sam 9 Congaree Ave. H Tia NH 0358 MR01 Marston Ray 24 Shenandoah Rd. Springfield MA 01101 RH01 Rowan Hal 12 Heather Rd. Mount Desert ME 0-1660 SL.01 Stevens Lori 15 Riverton Rd. Coventry VT 05825 UG01 Unser Glory 342 Pineview St. Danbury CT 06810 Phone Num HireDate 617-555-6032 6/3/2012 603-555-2134 34/2012 781-555-7767 1/8/2017 603-555-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. Introduction to Database Management Trip Triplo Trip Name Start Location I Arethusa Falls State Distance MaxOrpie Type Season 2 Mt Ascutney - North Peak 10 Haling Summer Weathersfield 3 Mt Asutney - West Peak 6 Hiking Late Spring Wewhere 4 Bradbury Mountain Ride 10 Making Hary PI Lewiston Auburn 5 Baldate Mountain ME * Ting Tarly all North Newry 6 Blueberry Mountain 10 Hiking Late Spring Batchwders Cant 7 Bloomfield - Maidstone Hiking Tarly Fall 8 Black Pond 6 Paddling Late Spring NH 9 Big Rock Cave 12 Hiking Summer Tamworth 10 ML Cardigan - Pirscrew 10 Hiking Summer Orange 11 Chocorua Lake Tour Hiling Summer Tamworth NH12 12 Cadillac Mountain Ride 15 Padding Summer Har Harber 13 Cadillac Mountain 16 Bing Early Fall Bar Harbor ME 8 Hing Lane Spring 14 Cannon Mit Franconia 6 Hog arty Full 15 Crawford Path Presidentials Hike Crowford Notch NH16 4 Hiking Summer 16 Cherry Pond Whitefield NH 16 Hiking Spring 17 Huguenot Head Hike Bar Harbor ME 10 Hiking Early Fall 18 Low Bald Spor Hike Pinkam Notch N H 6 Hiking Early Fall 19 Mason's Farm Nerth Stratford 7 Paddling Le Spring 20 Lake Mephremagog Tour Newport VT 15 Padding Late Spring 21 Long Pond Rusland MA 12 Hiking Summer 22 Long Pond Tour Greenville ME 10 Paddling Summer 23 Lower Pond Tour Poland ME 15 Paddling Late Spring 24 Mt Adams Randolph 6 Hilding Summer 25 Mount Battle Ride Camden ME 8 Biking Early Wall 26 Mount Cardigan Hike | Cardigan 16 Hiking at all 27 Mt Chocorua Albany NH 10 Hiking Spring 28 Mount Garfield Hike Woodstock NH 10 Hiking Early Hall 29 Metacomet-Monadnock Trail Hike Pelham MA 12 Hiking Late Spring 30 McLennan Reservation Hike Tyringham MA 16 Hiking Summer Lowell VT 10 Paddling Summer 31 Missisquoi River - VT Stark 10 Paddling Summer 15 32 Northern Forest Cance Trail NH 27 Mount Desert Island ME 8 Biking Late Spring 33 Park Loop Ride NH 15 Dummer 14 Paddling Late Spring 34 Pontook Reservoir Tour 10 Bling Summer Northborough 35 Pisgah State Park Ride NH 15 16 Biking Late Spring White Mountains 36 Pondicherry Trail Ride ubor Bar Harbor ME 5 Early Spring 16 Hiking 37 Seal Beach Harbor NH 10 Mount Carrigain 18 Biking Early Fall 38 Sawyer River Ride Summer 10 Hiking Thorton 5 NH 39 Welch and Dickey Mountains Hike 8 Hiking Early Spring Princeton MA 40 Wachusett Mountain Late Spring MF 10 Biking 20 41 Westfield River Loop NH Fort Fairfield FIGURE 1-16 Sample trip data for Colonial Adventure Tours Figure 1-17 on the next page shows sample customer data for Colonial Adventure Tours. Each customer is identified by a unique customer number. In addition, management stores each customer's last name, first name, address, city, state, zip code, and telephone number. Chapter 1 03 Q & A 1.8 SHIN LAN 233 South St. A Sorg Ma Hantaran Sound 156 Scholars Uch Juan Martha Shop TOC St. 65 Cires Veccan Adam 1 22 Chuan Walk 7 Cherry St. CAL Gene Sadie 24 Sum d Bell Sam 10 O Main SE Cambridge Heffens Oeuh 132 South St Apt 27 Manchester Larry 25 Song Hd Keren 12 Foster St. South Windsor Penn Bec St Fredrick St. Albi 154 Central St. Vernet 802-555-39 207-555-4507 802-555-3443 603-555-3476 860-555-9876 857-555-5532 The table number and gable, and the some trips Trip Guide NY 14411 960.555.1234 FIGURE 1-17 Sample customer data for Colonial Adventure Tours Figure 1-18 show sample reservations data for Colonial Adventure Tours. Each reservation is identified by a unique reservation number that uses the last two digits of the current year followed by a five-digit num- ber that is incremented sequentially as each reservation is received. The table also stores the trip ID, the trip date, the number of persons, the trip price per person, any additional fees for transportation and equipment rentals, and the customer number Reservation Reservation Tripid TripDate NumPersons Trip Price OtherFees Customer Num 40 30018 2 995.00 $0.00 TOT 28 9/12/2018 1 S35.00 $0.00 103 100004 26 F16 2018 4 $45.00 $15.00 104 39 6/25/2018 SOLO TOS 32 WIR2018 1 $0.00 $0.00 105 10.07 22 7/92018 8 $75.00 10.00 107 180g 28 12/2018 $0.00 IOS 1800009 2 $90.00 $40.00 109 1800010 2 5/14/2018 3 $25.00 $0.00 102 1800011 395/2018 3 $25.00 $0.00 102 1800012 16/12/2018 4 $15.00 $0.00 115 TR00013 8 791/2018 1 $20.00 $50 1800014 12 T IS 2 540.00 $5.00 119 1800015 10 MIS 1 $20.00 $0.00 120 1800016 11 7/23/2018 6 $75.00 $15.00 121 1800017 39 6/18/2018 3 $20.00 1800018 35.00 122 38 9/18/2018 4 1800019 $85.00 $15.00 126 25 8/29/2018 2 $110.00 $25.00 124 28 827/2018 2 $35.00 1800021 $10.00 124 326/112015 3 990.00 1800022 $20.00 112 21 68/2018 1 $95.00 1800024 $25.00 119 38 9/11/2015 I 1800025 $70.00 530.00 121 38 9/11/2018 2 $7000 1800026 54500 125 2 ISON $10.00 4 9/19/2018 $0.00 126 4 1802030 $105.00 $25.00 15 120 7/25/2018 6 $60.00 $15.00 104 FIGURE 1-18 Sample reservations data for Colonial Adventure Tours FIGU 48 reservations m Answer Arnold Ocean made reservation 1800010. Find the row in the Reservation table ID 1800010 (see Figure 1-18), and then find the customer number 102. Next, review the Elure 1-17), and determine that the customer name with the customer number 102 is Karen Busa has reservations for trips on August 29, 2018, and August 27, 2018. To Karen Busa, find her customer number (124) in the Customer table. Next, find all rows table that contain the customer number 124 Trip Guides The table named TripGuides shown in Figure 1-19 is used to relate trips and gui uber and the guide number. The trip number in the TripGuides table matches al Cable, and the guide number in the TripGuides table matches a guide number in the some trips use more than one guide. TripGuides (continued) TriplD Guide Num TriplD Guide Num 1 GZ01 19 DH01 1 RH01 20 SL01 2 AM01 21 AM01 2 SLOI 22 UG01 3 SL01 23 DH01 4 BROI 23 SL.01 4 GZ01 24 BR01 5 KS01 25 BR01 5 UGO1 26 GZ01 6 RH01 27 GZ01 7 SL01 28 BR01 8 BRO1 29 DH01 9 BRO1 30 AM01 10 GZ01 31 SL01 11 DHO1 32 KS01 11 KS01 33 UG01 11 UG01 34 KS01 12 BRO1 35 GZ01 13 RHO1 36 KS02 14 KS02 37 RH01 15 GZ01 38 KSO2 16 KSOZ 39 BR01 17 RHO1 40 DHO1 18 KS02 41 BR01 FIGURE 1-19 Table used to relate trips and guides u lo tourth specifik only have one supervisor u s his woman S is considering changing urth normal form Client Lue . La Raupervis , Buper cu supood D P a changing its business models that many consulat can models that many consultants an ice on can represent many clients. Using hieman Information on the towing un d t ent , Cliente Street, City State Code alance. Credi t , Const , Las Pirot , Street e the following normalized relation to olourth normal omsing consultants can represent one client and one consultare many che Intel P et rate client clientName, street, B e t e Balance Convert the following many consultants nts as in Question CreditLimit) any difference between the tables you created in Quis 4 and 52 Wh or wity not The following us onial Adventure Tours Case wwwing exercises are based on the Colonial Adventure Tours has shown in Figures 1-15 per 1. No computer work is required Gatabase shown in Figures 1-15 through 1-19 in ng the types of entities found in the Color A s dients and reserve create an example of a table that is in first normal form but not in second normal form and an ecame on is in condo o m but not in thind normal form chase out ons and convert to the higher forms Colonial Adventure Tours is considering changing the way hande reservations. Instead of storing the number of persons associated with one reservation, the company would like to wore the name and address of each person associated with each reservation Colonial A nure Tour decides to implement this change, men orice and other fee amounts for each trip would be dependent on only the trio ID Determine the multivalued dependencies in the following table, and then convert this table to an equivalent collection of tables that are in fourth normal form: Reservation Reservation D. TripID, Tripate. Trap Price, Other Fees, (Client Num, ClientLastName, Client Frame Address, City State. ZipCode, Phone)) Identify the functional dependencies in the following unnormalized table Convert the table to third normal form. Is the result also in fourth normal form? Why or why not? Trip (TripID, TripName, State Abbreviation, Stateltane, (GuideNum, GuideLast, GuideFirst)) 4. Currently, each trip is identified with only season. For example, the Arethusa Falls trip is offered only in the Summer season. Colonial Adventure Tours is considering offering the same trip in more than one seasonthat is, the Arethusa Falls trip could be offered in both the Summer and Late Spring seasons. Using this new infor- mation, identify all dependencies and convert the current Trip table to third normal form. You may need to make some assumptions. Identify these assumptions in your solution Chapter 1 new revenue streams Big data is a source for ongoing discovery and analysis, and the demand for Information from big data will require new approaches to database management, architecture, tools practions INTRODUCTION TO THE COLONIAL ADVENTURE TOURS DATABASE CASE Colonial Adventure Tours is a small business that organizes daylong guided trips of New England. To suppo the company's growing business management uses a database to ensure that the company's data is current accurate, and easily accessible In running the guided tours, management gathers and organizes information about guides, trips, customers, and reservations. Figure 1-15 shows sample guide data. Each guide is identified by a unique four-character code that consists of two uppercase letters and two digits. For each guide, the table also stores the guide's last name, first name, address, city, state, zip code, telephone number, and hire date Guide City State ZipCode GuideNum LastName FirstName Address AMO1 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 G201 Gregory Zach Moose Head Rd. Dummer NH 03588 KSOI Kiley Susan 943 Oakton Rd Jaffrey NH 03452 KSO2 Kelly Sam 9 Congaree Ave. H Tia NH 0358 MR01 Marston Ray 24 Shenandoah Rd. Springfield MA 01101 RH01 Rowan Hal 12 Heather Rd. Mount Desert ME 0-1660 SL.01 Stevens Lori 15 Riverton Rd. Coventry VT 05825 UG01 Unser Glory 342 Pineview St. Danbury CT 06810 Phone Num HireDate 617-555-6032 6/3/2012 603-555-2134 34/2012 781-555-7767 1/8/2017 603-555-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. Introduction to Database Management Trip Triplo Trip Name Start Location I Arethusa Falls State Distance MaxOrpie Type Season 2 Mt Ascutney - North Peak 10 Haling Summer Weathersfield 3 Mt Asutney - West Peak 6 Hiking Late Spring Wewhere 4 Bradbury Mountain Ride 10 Making Hary PI Lewiston Auburn 5 Baldate Mountain ME * Ting Tarly all North Newry 6 Blueberry Mountain 10 Hiking Late Spring Batchwders Cant 7 Bloomfield - Maidstone Hiking Tarly Fall 8 Black Pond 6 Paddling Late Spring NH 9 Big Rock Cave 12 Hiking Summer Tamworth 10 ML Cardigan - Pirscrew 10 Hiking Summer Orange 11 Chocorua Lake Tour Hiling Summer Tamworth NH12 12 Cadillac Mountain Ride 15 Padding Summer Har Harber 13 Cadillac Mountain 16 Bing Early Fall Bar Harbor ME 8 Hing Lane Spring 14 Cannon Mit Franconia 6 Hog arty Full 15 Crawford Path Presidentials Hike Crowford Notch NH16 4 Hiking Summer 16 Cherry Pond Whitefield NH 16 Hiking Spring 17 Huguenot Head Hike Bar Harbor ME 10 Hiking Early Fall 18 Low Bald Spor Hike Pinkam Notch N H 6 Hiking Early Fall 19 Mason's Farm Nerth Stratford 7 Paddling Le Spring 20 Lake Mephremagog Tour Newport VT 15 Padding Late Spring 21 Long Pond Rusland MA 12 Hiking Summer 22 Long Pond Tour Greenville ME 10 Paddling Summer 23 Lower Pond Tour Poland ME 15 Paddling Late Spring 24 Mt Adams Randolph 6 Hilding Summer 25 Mount Battle Ride Camden ME 8 Biking Early Wall 26 Mount Cardigan Hike | Cardigan 16 Hiking at all 27 Mt Chocorua Albany NH 10 Hiking Spring 28 Mount Garfield Hike Woodstock NH 10 Hiking Early Hall 29 Metacomet-Monadnock Trail Hike Pelham MA 12 Hiking Late Spring 30 McLennan Reservation Hike Tyringham MA 16 Hiking Summer Lowell VT 10 Paddling Summer 31 Missisquoi River - VT Stark 10 Paddling Summer 15 32 Northern Forest Cance Trail NH 27 Mount Desert Island ME 8 Biking Late Spring 33 Park Loop Ride NH 15 Dummer 14 Paddling Late Spring 34 Pontook Reservoir Tour 10 Bling Summer Northborough 35 Pisgah State Park Ride NH 15 16 Biking Late Spring White Mountains 36 Pondicherry Trail Ride ubor Bar Harbor ME 5 Early Spring 16 Hiking 37 Seal Beach Harbor NH 10 Mount Carrigain 18 Biking Early Fall 38 Sawyer River Ride Summer 10 Hiking Thorton 5 NH 39 Welch and Dickey Mountains Hike 8 Hiking Early Spring Princeton MA 40 Wachusett Mountain Late Spring MF 10 Biking 20 41 Westfield River Loop NH Fort Fairfield FIGURE 1-16 Sample trip data for Colonial Adventure Tours Figure 1-17 on the next page shows sample customer data for Colonial Adventure Tours. Each customer is identified by a unique customer number. In addition, management stores each customer's last name, first name, address, city, state, zip code, and telephone number. Chapter 1 03 Q & A 1.8 SHIN LAN 233 South St. A Sorg Ma Hantaran Sound 156 Scholars Uch Juan Martha Shop TOC St. 65 Cires Veccan Adam 1 22 Chuan Walk 7 Cherry St. CAL Gene Sadie 24 Sum d Bell Sam 10 O Main SE Cambridge Heffens Oeuh 132 South St Apt 27 Manchester Larry 25 Song Hd Keren 12 Foster St. South Windsor Penn Bec St Fredrick St. Albi 154 Central St. Vernet 802-555-39 207-555-4507 802-555-3443 603-555-3476 860-555-9876 857-555-5532 The table number and gable, and the some trips Trip Guide NY 14411 960.555.1234 FIGURE 1-17 Sample customer data for Colonial Adventure Tours Figure 1-18 show sample reservations data for Colonial Adventure Tours. Each reservation is identified by a unique reservation number that uses the last two digits of the current year followed by a five-digit num- ber that is incremented sequentially as each reservation is received. The table also stores the trip ID, the trip date, the number of persons, the trip price per person, any additional fees for transportation and equipment rentals, and the customer number Reservation Reservation Tripid TripDate NumPersons Trip Price OtherFees Customer Num 40 30018 2 995.00 $0.00 TOT 28 9/12/2018 1 S35.00 $0.00 103 100004 26 F16 2018 4 $45.00 $15.00 104 39 6/25/2018 SOLO TOS 32 WIR2018 1 $0.00 $0.00 105 10.07 22 7/92018 8 $75.00 10.00 107 180g 28 12/2018 $0.00 IOS 1800009 2 $90.00 $40.00 109 1800010 2 5/14/2018 3 $25.00 $0.00 102 1800011 395/2018 3 $25.00 $0.00 102 1800012 16/12/2018 4 $15.00 $0.00 115 TR00013 8 791/2018 1 $20.00 $50 1800014 12 T IS 2 540.00 $5.00 119 1800015 10 MIS 1 $20.00 $0.00 120 1800016 11 7/23/2018 6 $75.00 $15.00 121 1800017 39 6/18/2018 3 $20.00 1800018 35.00 122 38 9/18/2018 4 1800019 $85.00 $15.00 126 25 8/29/2018 2 $110.00 $25.00 124 28 827/2018 2 $35.00 1800021 $10.00 124 326/112015 3 990.00 1800022 $20.00 112 21 68/2018 1 $95.00 1800024 $25.00 119 38 9/11/2015 I 1800025 $70.00 530.00 121 38 9/11/2018 2 $7000 1800026 54500 125 2 ISON $10.00 4 9/19/2018 $0.00 126 4 1802030 $105.00 $25.00 15 120 7/25/2018 6 $60.00 $15.00 104 FIGURE 1-18 Sample reservations data for Colonial Adventure Tours FIGU 48 reservations m Answer Arnold Ocean made reservation 1800010. Find the row in the Reservation table ID 1800010 (see Figure 1-18), and then find the customer number 102. Next, review the Elure 1-17), and determine that the customer name with the customer number 102 is Karen Busa has reservations for trips on August 29, 2018, and August 27, 2018. To Karen Busa, find her customer number (124) in the Customer table. Next, find all rows table that contain the customer number 124 Trip Guides The table named TripGuides shown in Figure 1-19 is used to relate trips and gui uber and the guide number. The trip number in the TripGuides table matches al Cable, and the guide number in the TripGuides table matches a guide number in the some trips use more than one guide. TripGuides (continued) TriplD Guide Num TriplD Guide Num 1 GZ01 19 DH01 1 RH01 20 SL01 2 AM01 21 AM01 2 SLOI 22 UG01 3 SL01 23 DH01 4 BROI 23 SL.01 4 GZ01 24 BR01 5 KS01 25 BR01 5 UGO1 26 GZ01 6 RH01 27 GZ01 7 SL01 28 BR01 8 BRO1 29 DH01 9 BRO1 30 AM01 10 GZ01 31 SL01 11 DHO1 32 KS01 11 KS01 33 UG01 11 UG01 34 KS01 12 BRO1 35 GZ01 13 RHO1 36 KS02 14 KS02 37 RH01 15 GZ01 38 KSO2 16 KSOZ 39 BR01 17 RHO1 40 DHO1 18 KS02 41 BR01 FIGURE 1-19 Table used to relate trips and guides
