MPCS 53001: Assignment 3

Step 2 of Your TBP

1. Request a MySQL account. For specific instructions on how to get a MySQL account, login to MySQL, create relations, etc. please, refer to Getting Started With MySQL. Get acquainted with MySQL, change your initial password, experiment with some simple commands, and check out the help command.

2. Create relations for your TBP based on your final relational schema from Step 1. In addition to creating the appropriate attributes and types, please declare primary keys for your relations. The attribute types supported by MySQL are described in the MySQL online documentation. Please, generate a script file called create_db.sql that creates all of your tables.

3. Develop a small amount (10-20 tuples per relation) of real data and load it into your relations using the SQL load command. See Loading Data in MySQL for information on how to bulk-load data. Please, generate a script file called populate_db.sql that loads all of your tables. Note that the script will likely reference several data files in your directory. Create a directory data inside your hw3 directory and move all of your data files there. Also, reference these files in your populate_db.sql script by relative unix paths. For example, if you have a data file tweets.txt, there will be a line inside populate_db.sql that looks like:
   LOAD DATA LOCAL INFILE "data/tweets.txt" ...

4. Develop and test at least six SQL data retrieval (select) commands.

5. Please, generate a script file called query2.sql with all of your queries.

Please note:

• You can invoke your SQL commands interactively through mysql, as described in the document Getting Started With MySQL.

• Please write "interesting" queries. You should try to use most or all of the SQL constructs discussed in class and in the textbook. You will not receive full credit if your queries are all extremely simple.

Please, create a new directory in your project directory on the CS server called hw3 and move all of the relevant files there including create_db.sql, populate_db.sql, query2.sql as well as all data files referenced by create_db.sql.

The instructions on how to submit your project can be found here. Please make sure that all of the relevant files and the data directory are in the submitted directory.

Problem Set

The name of the homework is MPCSDB-Aut13 HW3. There are 11 questions in this homework - 10 regular questions and one extra credit one (relational division). All questions in this problem set are multiple choice. However, to answer them correctly you will need to work out their long (general) answers. A correct answer is worth 3 points. You lose a point for each incorrect answer. You can attempt the problem set as many times as you like; only your highest score will count. Note that you will probably get slightly different questions each time you take it.

The due date for the Gradiance part of the homework is 11:59pm on Tuesday, October 22, 2013.