CSCE 520
Homework 2
Due September 14, 2017. Hardcopy due by 2:50pm, e-files due 11:59pm


Read through the rest of Chapter 2.

To submit

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

  1. Do textbook Exercises 2.2.1 and 2.2.2 (hardcopy).
  2. Do textbook Exercise 2.3.1. Define each schema with a CREATE TABLE command. Do parts (e) and (f) with an 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 Oracle by typing

      SQL> @ex2-3-1

    The at-sign executes the commands in the given file (with .sql extension). 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: immediately precede each CREATE TABLE command with a

        DROP TABLE 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.
  3. Do Exercise 2.4.1(a-e). To populate the tables, put INSERT INTO commands in a file ex2-4-1.sql. Use the data given in Figures 2.20 and 2.21 to populate the tables. Render the queries (a-e) as SELECT statements. Place these in text files ex2-4-1a.sql, ex2-4-1b.sql, etc. Also hand in your relational algebra queries, with answers, as hardcopy.
Place all your files in a single directory hw2. Create a zip of this directory: in the parent directory, type

zip -r hw2 hw2

Upload the created file to Dropbox.

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