CSCE 520
Homework 3
Due September 19, 2017: hardcopy by 2:50pm, Oracle by 11:59pm

Reading

Finish Chapter 2. Read Section 5.1.

To submit

The hardcopy exercises together are worth 70% and the Oracle exercise 30%.

Undergrads and graduate students
Do Exercises 2.4.7, 2.4.8 (undergrad extra credit), 2.5.1(a,b,c) (hardcopy).
Oracle exercise (dropbox): Add the following constraints to your database schema from Homework2, exercises 2.3.1 and 2.4.1:

  1. The model attribute forms a primary key for Product, PC, Laptop, and Printer. (Four key constraints)
  2. The type of a product cannot be null.
  3. Every model that appears in PC, Laptop, or Printer must also appear as a model in Product. (Three referential integrity/foreign key constraints). For the Printer schema, include the policy "on delete cascade", but don't include this in the PC or Laptop schemas.
  4. Constrain the type of a product to be either 'pc', 'laptop', 'printer', 'smartphone', or 'tablet'. (One domain/check constraint)
You may include these constraints with the CREATE TABLE commands and recreate the database from scratch, as you did in Homework 2, or, you can add these constraints directly (without disturbing data) with ALTER TABLE commands (described in class).

After altering the database schema as above, try the following commands in Oracle (assuming the original data was entered exactly as in Figures 2.20 and 2.21):

spool hw3
insert into Product(maker, model) values('I', 1014);
insert into Product values('J', 4001, 'toaster');
insert into Laptop values (2001, 3.00, 1024, 480, 15.6, 1995);
delete from Product where model=1001;
select * from Printer;
delete from Product where model=3001;
select * from Printer;
rollback;
spool off

All the data-altering commands above should be disallowed, with an error message given, except for the last one, which should also alter the Printer table instance.

Submit a .zip file containing any files you executed within Oracle (including any files from Homework 2 you used), as well as the log file of your Oracle session.

Graduate students only
Do Exercises 2.4.8 (for full credit), 2.5.1(d,e) (hardcopy)