Unit A – Creating Tables

Connecting to EC2

If you do not have an SSH Secure Shell client, you will need to get one. For Windows users, I recommend PuTTY. Go to http://www.chiark.greenend.org.uk/~sgtatham/putty/ to download PuTTY. See http://docs.amazonwebservices.com/AWSEC2/latest/UserGuide/putty.html for details on how to connect to EC2 using PuTTY. 

Additional information can be found in the Lectures pages, including the address and keys required to connect.

Connecting to MySQL

Once you have SSH'd into the EC2 instance, you must connect to MySQL. Enter the following command at the prompt, replacing username with your USF Connect username.

mysql -u username -p

You will be prompted for your password. Your password is the same as your username.

Changing Your Password

It is a good idea to change your password once you have logged in to MySQL. Enter the following command at the prompt, replacing newpass with your new password.

SET PASSWORD = PASSWORD('potato');

To logout, type exit. Then, try logging in again with your new password.

Creating Tables

Once you are logged back into MySQL, try creating your own tables. First, make sure you let MySQL know which database you are working with. Replace username below with your USF Connect username:

USE username;

Choose three professors. Enter contact information for those three professors split amongst the following tables:

  • faculty
    Should contain a userid column that will be the primary key, and used as a foreign key in the other tables. Also include last and first name columns.
  • emails
    Should contain a emailid column that will be the primary key, an email column, and a userid column that references the faculty table.
  • phones
    Should contain a phoneid column that will be the primary key, an area code and phone number column, an optional label column.
You'll need to use the following types of SQL statements:
  • CREATE TABLE to create the tables
  • INSERT to insert values into those tables
  • SELECT to verify you inserted the values correctly
See the Lectures pages for column types and constraints.

Submission

To submit this lab, please include the output to the following SQL statements on Gist and fill out the Submission form. 

DESCRIBE faculty;
DESCRIBE emails;
DESCRIBE phones;
SELECT * FROM faculty;
SELECT * FROM emails;
SELECT * FROM phones;

Remember to out the link to your Gist page in the "Feedback" box.

Comments