208 10-3. a. Create or re-create if necessary, Test4 from Exercise 10-1, which does not specify the PRIMARY KEY. Insert data into the table with at least one duplicate ssn. Then, try to impose the PRIMARY KEY constraint with an ALTER TABLE command. What happens? b. Re-create the table Test4 from Exercise 10-1, but this time add a PRIMARY KEY of ssn. If you still have Test4 from Exercise 10-3a, you may be able to delete offending rows and add the PRIMARY KEY constraint. Enter two more rows to your table one containing a new ssn and one with a duplicate ssn. What happens? 10-4. Create the Department and Employees tables as per the examples in the chapter with all the constraints (PRIMARY KEYs, referential and UNIQUE constraints, CHECK constraints). You can add the constraints at create time or you can use ALTER TABLE to add the constraints. Load the Department table first with departments 1,2, and 3. Then load the Employees table. Note: Before doing the next few exercises, it is prudent to create two tables called Deptbak and Empbak, which will contain the data you load. This is because you will be deleting, inserting, dropping, re-creating, and so on. You can create the Deptbak and Empbak tables with the data we have been using with a command like this: CREATE TABLE Deptbak AS SELECT FROM Dept Then, when you have added, deleted, updated, and so on, and you want the original table from the start of this problem, you simply run the commands: DROP TABLE Deptbak; CREATE TABLE Deptbak AS SELECT FROM Dept; Create a violation of insertion integrity by adding an employee to a nonexistent department. What happens? a. b. Create an UPDATE violation by trying to change: (i) an existing employee to a nonexistent department (ii) a referenced department number. Try to DELETE a department for which there is an employee. What happens? What happens if you try to DELETE a department where no employee has yet to be assigned to it? Re-do this entire experiment (starting with Exercise 10-4a) except when you create the Employees table, specify the DELETE constraint as CASCADE. c. d