CSPP 53001: Assignment 3
Online homework and project due by 11:59pm on Wednesday, October 31, 2012
Step 2 of Your TBP
The second part of your TBP project is to create a database in
MySQL, populate it with some real data and run several interesting queries.
- Get 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.
- 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.
- 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.
- Develop and test at least six SQL data retrieval (select) commands.
- 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.
You will submit your homework using the hwsubmit command.
Please, create a new directory called hw2_<your_username>,
e.g. hw2_evtimov, and move all of the relevant scripts and data files
there. Then, you can submit your work by typing (<path_to> is the directory path to your hw2 directory).
hwsubmit cspp53001 <path_to>/hw2_<your_username>
Please make sure that all of the relevant files and no others are in that directory.
Problem Set
You will
complete the problem set using Gradiance (http://www.newgradiance.com/).
The name of the homework is CSPPDB-Aut12 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 Wednesday, October 31, 2012.