Homework I

Tom Kelliher, CS 318

Due Feb. 13, 2002

Do the following:

1. Problem 2.1 from the text. The BrotherInLaw relation should be defined as either an SQL query or predicate logic statement using the other relations.

2. Problem 2.7 from the text. In addition, state three new valid integrity constraints for this modeling domain.

3. Design a database table, called JOBS, that will hold information about a collection of jobs. For each job, the table should hold the company, title, description, wage, experience level, job starting date, and tuple delete date. Pick the most appropriate type for each field. You may add additional columns to the table if you like. Write the CREATE TABLE statement used to create this table.
   1. Insert five rows into the JOBS table. Pick realistic values for each field, and include at least two jobs with a delete date of Feb. 1, 2002. Write the INSERT statements used to populate the table.

   2. Write an SQL query that returns the company and title of jobs due to be delete on Feb. 1, 2002.

   3. Write SQL queries that delete all the rows in the table and destroys the table.

   Turn in a printout of a shell session log in which you use psql to perform these SQL operations. See the man page for script for instructions on creating a session log.

4. Problem 4.2.

5. Problem 4.7.

6. Problem 4.9.