Create a database name ColonialAdventureTours

In ColonialAdventureTours database, create a table named

ADVENTURE_TRIP. The table has the same structure as the TRIP table shown in Figure-1 except the TRIP_NAME column should use the VARCHAR data type and the DISTANCE and MAX_GRP_SIZE columns should use the integer data type.

Execute the command to describe the layout and characteristics of the ADVENTURE_TRIP table.

Add the following row to the ADVENTURE_TRIP table: trip ID: 45; trip name: Jay Peak; start location: Jay; state: VT; distance: 8; maximum group size: 8; type: Hiking and season: Summer. After adding the data, display the contents of the ADVENTURE_TRIP table.

Delete the ADVENTURE_TRIP table.

Figure 1: Colonial Adventure Tours Database Table Structure

Part 2:

You will create five tables in your ColonialAdventureTours database. Please do not write your own SQL Commands for this task, use data found in the following Colonial_create.txt file and copy and paste the commands into MySQL workbench. Then add Primary key, Foreign key, and not null constraints appropriately. Then run your codes.

Note:

Remember that since you enforced referential integrity (foreign key constraints) that you must create the "primary" tables before you can create the "related" tables in the relationship. [Create tables in right orders].

Part 3:

The Colonial_Insert.txt file provided with this homework contains the MySQL commands that you can use to insert the data into the tables that you created in part 2. Copy and paste the commands into MySQL environment and execute.

Note: insert data in the right order. Remember that since we enforced referential integrity (foreign key constraints) that you must insert all of the data into the "one" tables before you can enter the data into the "many" tables in the relationship.

Part 4:

Write and run MySQL Commands that will provide the following information listed below.

List all the table names in your database

List all the constraint names in your database

List the Column names and data types of each table

(only one Command per table)

List all data from each table that you created one table at a time.

Introduction to TAL Distributors, Colonial Adventure Tours, and Solmaris Condominium Group Colonial Adventure Tours wants to be able to ask questions concerning the data and to obtain answers to these questions easily and quickly. 9 In running the guided tours, management gathers and organizes information about guides, trips, customers, and reservations. Figure 1-4 shows sample guide data for Colonial Adventure Tours. Each guide has a number that uniquely identifies the uide. In addition, Colonial Adventure Tours records the guide's last name, first name, address, city, state, postal code, telephone number, and hire date. GUIDE GUIDE NUM LAST NAME FIRST NAME ADDRESS AM1AbramsMiles BRO1 DH01 G201 KS01 KS02 MROI RH01 SL01 UGO1 STATE POSTAL CODE PHONE NUM HIRE DATE 6/3/2012 3/4/2012 1/8/2012 603-555-8765 11/4/201:2 1/8/2013 603-555-00036/10/2013 781-555-2323 | 9/14/2015 6/2/2014 9/5/2014 203-555-8534 2/2/2015 CITY Williamsburg MA 01096 NH 03452 25 Old Ranch Rd. Sunderland MA01375 NH 03588 NH 03452 03580 24 Shenandoah Rd. Springheld MA 01101 12 Heather Rd. Mount Desert |ME 04660 05825 06810 54 Quest Ave. 617-555-6032 603-555-2134 781-555-7767 140 Oakton Rd. Jaffrey Boyers Devon GregoryZach Kiley Kelly Marston Rowan Stevens Unser Rita Harley 7 Moose Head Rd. Dummer 943 Oakton Rd. 9 Congaree Ave. Fraconia Jaffrey 603-555-1230 Susan Sam Ray Hal Lori NH 207-555-9009 802-555-3339 15 Riverton Rd.Coventry 342 Pineview StDanbury VT CT FIGURE 1-4 Sample guide data for Colonial Adventure Tours Figure 1-5 shows sample trip data for Colonial Adventure Tours. Each trip has a number that uniquely identifies the trip. 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