DELIVERABLE #3 – SQL DDL Solution

For this deliverable, you will be creating the database for the Hippodrome database in MySQL. The relations for the database have been defined as shown on Page 2. Based on this information, you must complete the following:
STEP 1: Create a new database in MySQL called TheHipp.
STEP 2: Create all the tables as follows:
• Write the CREATE statements for the table corresponding to each of the relations. You must decide which data types (and field size, when applicable) will be appropriate for each column in each table. Each table must have a Primary Key and the appropriate Foreign keys (if applicable)
• Write at least two ALTER statements to either add columns or Primary Key or Foreign Key constraints to any of the tables.
• Execute all the CREATE and ALTER statements in MySQL.
• Use the DESC commmand to describe each table and include the results as shown in the attached template.
• List all the relationships in your database using the following
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE WHERE (CONSTRAINT_SCHEMA = 'TheHipp') ;
STEP 3: Insert data into the tables as follows:
1. Write one INSERT statement for each of the tables (you may use any dummy data that you can make up)
2. Write a select statement for each of the table to display the contents of each table.
3. Execute all the INSERT and SELECT statements in MySQL.
NOTE: You may use the snipping tool or screenshot tool to copy the results from the MySQL Workbench to the Word document.
Department of Information Systems & Operations Management
Warrington College of Business Administration, University of Florida ISM 4210
Database Management
Individual Project
Version: October 26, 2015
Department of Information Systems & Operations Management
Warrington College of Business Administration, University of Florida ISM 4210
Database Management
Individual Project #3 Template
Version: October 26, 2015
For each relation, you must present your results using the following template (a blank word document is available on Canvas).
RELATION NAME:
CREATE Statement
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
LastName VARCHAR(30) NOT NULL,
FirstName VARCHAR(30)
);
ALTER Statement (Optional)
INSERT statement
INSERT INTO Customer (CustomerID, LastName, FirstName)
VALUES (123456, "Pond", "Amy");
SELECT statement
SELECT * FROM Customer;
DESC statement
DESC Customer;