Reading
- § 5.1
To Submit
Written
- Required
-
- Exercise 2.4.7
- Exercise 2.5.1(a,b,c)
- Graduate Students Only
-
- Exercise 2.4.8
SQL
Add the following constraints to your database schema from exercises 2.3.1 and 2.4.1
- The model attribute forms a primary key for Product, PC, Laptop, and Printer. (Four key constraints)
- The type of a product cannot be null.
- Every model that appears in PC, Laptop, or Printer must also appear as a model in Product. (Three referential integrity/foreign key constraints).
- Constrain the type of a product to be either ‘pc’, ‘laptop’, ‘printer’, ‘smartphone’, or ‘tablet’. (One domain/check constraint)
You should include these constraints with the CREATE TABLE commands and recreate the database from scratch, as you did in Homework 2. (You could add the constraints directly – without disturbing data – with ALTER TABLE commands, described in class, but then your file cannot be tested from scratch by the TA.)
Once you are satisfied that you have the correct commands, put them all in a single file hw3.sql along with the data insertion commands from HW2. Then try the following commands in MySQL (assuming the original data was entered exactly as in Figures 2.20 and 2.21:
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;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. Add these commands to the bottom of your hw3.sql file. Exit MySQL, and at the shell prompt, type
Submit a .zip file containing the two files hw3.sql and hw3.txt.