MySQL_Homework1_1

Colonial Adventure Tours Database Data Definitions

Use MySQL Workbench to do the following tasks:

Part 1:

• 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.