Description
INSTRUCTIONS
Please read the description and the requirements of Deliverable #3 carefully before responding. If you have any questions or clarifications, feel free to contact me via email (aditimukherjee@ ufl.edu), during my office hours, or by appointment. Keep your responses succinct and clear. No additional explanations should be required in addition to your write up to convey your ideas.
All submissions should be individual work only. Do not discuss your answers with your class mates or group members. Plagiarism will not be tolerated.
No late submissions will be accepted. If you have trouble submitting assignments to the e-learning system, please contact me before the submission deadline.
Page Limit: The SQL statements for each table be written on a single page. Your submission should have at least one page per table, and the SQL statements for each table should start on a new page.
Format: You will submit the SQL statements described below. Copy and paste all the statements and the diagram into a Word documents that you can save and submit as a PDF. See page 3 of this document for an example of how to present the statements. The name of your file should be <LastName>_<FirstName>_IP3.pdf. Submissions that do not follow the formatting guidelines will not be graded. Submissions where the results are not legible will not be graded.
DELIVERABLE #3 – SQL DDL
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:
| Department of Information Systems & Operations Management Warrington College of Business Administration, University of Florida | ISM 4210
Database Management |
| Individual Project |
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:
- Write one INSERT statement for each of the tables (you may use any dummy data that you can make up)
- Write a select statement for each of the table to display the contents of each table.
- 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 |
| Department of Information Systems & Operations Management Warrington College of Business Administration, University of Florida | ISM 4210
Database Management |
| Individual Project #3 Template |
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;
|
