CSCE 520
Homework 2
Hardcopy due at class start, e-submission due 11:59pm

Reading

Read through the rest of Chapter 2.

To submit

The purpose of this assignment is to get you to do some basic SQL.

Important Note: All references to numbered exercises in the textbook refer to the hardcover version of the text (ISBN-13: 978-0136006374). Other versions of a textbook (e.g., international versions) often renumber exercises, either through reordering or through omission. It is your responsibility to make sure you are doing the correct exercises. If you are not sure, you may consult my or the TA's textbook for comparison.

1. Do textbook Exercises 2.2.1 and 2.2.2 (hardcopy).
2. This is a MySQL exercise (essentially exercise 2.3.1 of the textbook). You will create a sales database.
   1. Open the MySQL client application as the root user and issue the command
      
        mysql> GRANT ALL ON sales.* TO 'whomever_you_are'@'localhost';
      
      
   2. Log out as root and log back in again to your regular user account (whomever_you_are). Type
      
        mysql> CREATE DATABASE sales;
      
      
   3. Log out of MySQL. When you log back in again later, you'll say (at the shell prompt)
      
        mysql -p sales
      
      which will automatically have you using the sales database.
   4. Now do textbook Exercise 2.3.1. Define each relational schema with a CREATE TABLE command. Do parts (e) and (f) with ALTER TABLE commands. Place these commands in a text file named ex2-3-1.sql. When you are done, you may execute this file inside MySQL by typing
      
        mysql> source ex2-3-1.sql
      
      You can check that your tables are created correctly by saying, e.g.,
      
      mysql> describe Product;
      
      and similarly for the other tables. NOTE: If you make a mistake, remember that your database is persistent. The mistake you make will last until you explicitly undo it. For now, the easiest way to fix things is to start from scratch: re-editing your ex2-3-1.sql file, immediately precede each CREATE TABLE command with a
      
          DROP TABLE IF EXISTS TableName;
      
      command, where TableName is the name of the table you create immediately after. The DROP TABLE command removes the table so that it is then created fresh from scratch. You can then re-run the source command above.
   5. Do Exercise 2.4.1(a-e):
      • To populate the tables, put INSERT INTO commands in a file ex2-4-1.sql, using the data given in Figures 2.20 and 2.21 to populate the tables. This is rather tedious, so you may start with the file ex2-4-1.sql, which has most of the INSERT commands already. I left a few out of each table, to allow you the joyful experience of adding these yourself. Run this file with the source command as above. Note that the file has two commands that undo the ALTER TABLE commands you did previously. You can check that your tables are populated correctly by saying, e.g.,
        
        SELECT * FROM Product;
        
        and similarly for the other tables.
      • Render the queries (a-e) as SELECT statements. Place these in text files ex2-4-1a.sql, ex2-4-1b.sql, etc. Check that they return the correct results. Also hand in your relational algebra queries, with answers, as hardcopy.

NOTE: Hardcopy is due on the podium in the classroom at or before the beginning of class. Any hardcopy turned in after that time (even by a few minutes) will be counted as late. This is a firm deadline.