Version 1.0 - initial release


Due: 11:59PM on Sunday, Feb 4th
Late Period: 11:59PM on Tuesday, Feb 6st


Lab Assignment 1a: Finding your way around in SQL

The purpose of this assignment is twofold:

  • To introduce you to using MySQL on faure;

  • To help you become familiar with some common SQL commands;
(You might also wish to download MySQL for your own machine, and run this program there as well.)


Overview of Task

For this project you will learn how to access our department MySQL server(faure) and work with a sample database provided below.   The database implements a simple employees file and consists of several simple tables for organizing departments, managers, and employees. A copy of the schema for this database can be found here.


Details

Part1: Bringing up mysql on faure

  1. Log into one of the department machines, either by working in one of the CS labs, or by ssh from home.
  2. run mysql by executing the following command:
       mysql -h faure.cs.colostate.edu -p
  3. This should drop you into mysql and request a password.
  4. Once you have entered the password (initially your CSU id), you can begin to nose around.
  5. Remember that if you are using faure, you do not have permissions to create a database. Instead, one by the same name as your user id has been made available for you to use.

Part 2: Create and/or use a provided database. You need to find a way to solve Question 4, 6, 7, and 8.

  1. Common mysql commands.  Before you start your operation on mysql database, it's good to know the following mysql commands.  Refer to the tutorial in the documentation for more detailed information.   Don't forget to terminate every command with a semicolon, otherwise you will get the mysql command continuation prompt: "->".  If this happens and you're done entering your command, simply enter a ";" and the command will execute. 
    • show databases;  This command shows all of the databases that your mysql server is managing. If you are running on faure, this will show your personal database available there.  Database in this sense refers to a collection of tables (relations) for supporting a complete conceptual model of the world.
    • use <database>;  This command changes the current database to <database>.
    • For security reasons, on faure we do not allow databases to be created, so you must use the database of your own name here.
    • show tables;  This command shows all of the tables (relations) for the current database (refer to the use command for changing the current database).
    • describe <table>;  This command describes the field names, types, and values for the table named <table>.
    • select * from <table>;  This command shows all of the data within the table named <table>.

  2. OK, now lets get to the assignment.
  3. Use the tee command to put your output in a text file to submit, Be sure to use a .txt extension, otherwise it won't be graded.
    • Use ? at the MariaDB prompt for help

  4. type in use [your database's name]; [your database's name] should be your existing database whose name is your cs department login name. Caps aren't important but spelling is.   Type in show tables;" and you should see nothing.
  5. Create the employees tables in the new database.  First think about what kind of information you would need to create a employee database.  Would you put all of it in a single table or separate it into multiple tables?  A script file is provided that you can run with mysql in batch mode to create the necessary database   Run  it by using the source command from within mysql. HINT: source file_name.sql in mysql prompt.
  6. Examine the tables.  Use the "describe" command to look at the structure of each table.  Use the documentation to help understand data types, syntax, etc.  Yes, you are being thrown into the deep end and having to swim, so start paddling.
  7. Load the database with some data, specifically the data provided in this script file.  Execute it the same way as you did in creating the tables. 

    Below are the data files that must be local to your account. A tar file can be found here if that is easier for you.

  8. Run some queries against the database.  Here is a script file that contains some sample queries. Examine the output to see if it makes sense.
    1. What is the difference between the first and second query? Why?
    2. What is the difference between the second and third query? Why?

  9. Type in notee to stop the tee command.


Submitting Your Assignment

Using the Canvas CS 430 Assignment Submission portal

  1. Upload the output file generated while you were executing the assigment.
  2. All text files MUST have a .txt extension (Canvas cannot handle them otherwise.
  3. Answer the 2 questions from #8 above in a .pdf document.
    Note: These must be done in the same submission. The TA will only grade the latest submission, if they are not together you will lose points
  4. Submit the assignment.

This is Lab Assignment 1a.


Late Policy

Assignments turned in during the late period are subject to a 20% late penalty.


For more information on SQL

In addition, refer to http://dev.mysql.com/doc/mysql/en/ for the online MySQL Reference Manual, as well as information on downloading MySQL onto your own machine.