Section 11.6. Exercises

Unless specified otherwise, use the Student_course database to answer the following questions. Unless otherwise directed, name all CONSTRAINTs.

To test choices of data types, create a table with various data types like this:
```
CREATE TABLE Test3
  (name                VARCHAR(20),
   ssn                 CHAR(9),
   dept_number         INTEGER,
   acct_balance        SMALLMONEY)
```
Then insert values into the table to see what will and will not be accepted. The following data may or may not be acceptable. You are welcome to try other choices.
```
'xx','yy',2,5
'xx','yyy',2000000000,5
'xx','yyyy',2,1234567.89
```
Create an index of ssn in ascending order of ssn. TRy to insert some new data in the ssn column. Does your ssn column take nulls?
1. Does your ssn column take duplicates? If so, how can you prevent this column from taking duplicates?
2. Include a NOT NULL constraint on the ssn column. Now try to insert some new data in the ssn column with nulls in the ssn column. What happens?
3. With this NOT NULL constraint, is it necessary to include the PRIMARY KEY constraint? Why or why not? Now include the PRIMARY KEY constraint and see whether there is any difference in the types of values it accepts.
4. Include some data with null values in the dept_number and acct_balance columns. Now include the NOT NULL constraint in the acct_balance column. What happens?
5. Include the NOT NULL constraint in the acct_balance column. What happens?
Delete Test3.
To test the errors generated when NOT NULL is used, create a table called Test4, which looks like this:
```
CREATE TABLE Test4
  (a    CHAR(2)      NOT NULL,
   b    CHAR(3))
```
Input some data and try to enter a null value for A. Acceptable input data for a null is "null."
Create or recreate, if necessary, Test3, which does not specify a primary key. Populate the table with at least one duplicate ssn. Then, try to impose the PRIMARY KEY constraint with an ALTER TABLE command. What happens?
1. Recreate the Test3 table, but this time add a primary key of ssn. If you still have the Test3 table from Exercise 4, you may be able to delete offending rows and add the PRIMARY KEY constraint. Enter two more rows to your tableone containing a new ssn and one with a duplicate ssn. What happens?
Create the Department and Employee tables, as per the examples earlier in the chapter, with all the constraints (PRIMARY KEYs, referential and UNIQUE constraints). You can add the constraints at create time or you can use ALTER TABLE to add the constraints. Populate the Department table first with departments 1, 2, and 3. Then populate the Employee table.
Note: before doing the next few exercises, it is prudent to create two tables, called Deptbak and Empbak, to contain the data you load, because you will be deleting, inserting, dropping, recreating, and so on. You can create Deptbak and Empbak tables (as temporary tables) with the data we have been using with a query like:
```
SELECT *
INTO Deptbak
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 following commands:
```
DROP TABLE Dept
SELECT *
INTO Dept
FROM  Deptbak
```
1. Create a violation of insertion integrity by adding an employee to a nonexistent department. What happens?
2. Create an UPDATE violation by trying to change an existing employee to a nonexistent department, and then by trying to change a referenced department number.
3. Try to delete a department for which there is an employee. What happens? What happens if you try to DELETE a department to which no employee has yet been assigned?
4. Redo this entire experiment (starting with Exercise 5a), except that this time create the Employee table with the ON DELETE CASCADE. View the table definition of the Employee table.
5. Redo exercises 5a-5c, except that this time, create the Employee table with the ON DELETE NO ACTION.
6. Redo exercises 5a-5c, except that this time, create the Employee table with the ON UPDATE CASCADE.
7. Redo exercises 5a-5c, except that this time, create the Employee table with the ON UPDATE NO ACTION.
8. Redo exercises 5a-5c, except that this time, create the Employee table with the ON UPDATE NO ACTION and ON DELETE CASCADE together.
Create a table (your choice) with a PRIMARY KEY and a UNIQUE constraint. Insert data into the table and, as you do, enter a good row and a bad row (the bad row violates a constraint). Demonstrate a violation of each of your constraints one at a time. Show the successes and the errors as you receive them.
In this chapter, the Employee table was referenced to (depended on) the Department table. Suppose that there were another table that depended on the Employee table, such as Dependent, where the Dependent table contained the columns name and empnum. Create the Dependent table. Then add the referential constraint where empnum references the Employee table, with ON DELETE CASCADE (and note that the Employee table also has an ON DELETE CASCADE option). You are creating a situation in which the Dependent table references the Employee table, which references the Department table. Will SQL Server let you do this? If so, and if you delete a tuple from the Department table, will it cascade through the Employee table and on to the Dependent table?