Homework 2
CS122, Summer 2007
Due: Tuesday, August 14
Please put your SQL statements in three script files hw2-1.sql, hw2-2, and hw2-3.sql, and upload them to CSNS.
Note that file
uploading will be disabled automatically after 11:59PM
of the due date, so please turn in your work on time.
1. (10pt) Chapter 8, Exercise 1: (a), (c), (d), (e), (g).
Note that you may either specify the constraints as part of the
CREATE TABLE statement, or add the constraints after the table is
created using ALTER TABLE statements.
For +2 extra credit, read the MySQL documentation about AUTO_INCREMENT and use it for the OrderID column.
2. (10pt) Chapter 8, Exercise 2. For this exercise, you will need
to use an ALTER TABLE statement to add the foreign key constraint. And
unlike what the textbook says, this can be done with MySQL (version 5
or above).
3. (+5pt) Chapter 8, Exercise 3.
- You must use only ALTER TABLE statements for this exercise, i.e.
you can't simply drop the Contracts and ContractType table then
recreate them, because you would lose all the data in the tables.
- You don't have to complete the operations in the order described
in the exercise, e.g. you may want to do (b) before (a), especially if
you want to make TypeID an identity column.
- For (c), simply make sure that different contract types have different TypeID values.
- Again, you should be able to do everything with MySQL.