Description
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.
What to Hand In
NOTE: Make sure that you combine all of your separate commands and results
into ONE TEXT FILE (SQL SCRIPT) and upload it back to the Assignments link.