Answer to Chapter 11 of O'Reilly Learning Sql on SQL Server 2005 .

 

11.5. Review Questions
1. What is an index?
An index is an on-disk structure associated with a table or view that speeds retrieval of rows

from the table or view.

2. Does an index slow down updates on indexed columns?
YES

3. What is a constraint?
constraints can be added to tables. As explained previously, constraints are added to give

tables more integrity.

4. How many indexes does SQL Server 2005 allow you to have on a table?
not limit

5. What command would you use to create an index?
CREATE INDEX index_name
ON Tablename (column [ASC | DESC])

6. Is there a difference between an index and a constraint?
index is use to optimaize the speed. Constrain is used to keep data integrity.

7. What is the default ordering that will be created by an index (ascending or

descending)?
ascending

8. When can the UNIQUE option be used?
UNIQUE
Creates a unique index on a table or view. A unique index is one in which no two rows are

permitted to have the same index key value. A clustered index on a view must be unique

9. What does the IGNORE NULL option do?
Prevent records with Null values in the indexed field or fields from being included in the

index by using the IGNORE NULL option.

10. How do you delete an index?
DROP INDEX Table_name.index_name

11. What does the NOT NULL constraint do?
The NOT NULL constraint is an integrity CONSTRAINT that allows the database creator to deny

the creation of a row where a column would have a null value.

12. What command must you use to include the NOT NULL constraint after a table has already

been created?
ALTER TABLE Tablename
ALTER COLUMN column_name column_type(size) NOT NULL

 

13. When a PRIMARY KEY constraint is included in a table, what other constraints does this

imply?
NOT NULL

14. What is a concatenated primary key?
When more than one column makes up a primary key, it is called a concatenated primary key.

15. How are the UNIQUE and PRIMARY KEY constraints different?
UNIQUE keys can exist in addition to (or without) the PRIMARY KEY.

UNIQUE does not necessitate NOT NULL, whereas PRIMARY KEY does.

There can be more than one UNIQUE key, but only one PRIMARY KEY.

16. What is a referential integrity constraint? What two keys does the referential

integrity constraint usually include?
A referential integrity constraint is one in which a row in one table (with a foreign key)

cannot exist unless a value (column) in that row refers to a primary key value (column) in

another table.
This is a primary key-foreign key relationship between two tables.

17. What is a foreign key?

A foreign key is a column in one table that is used to link that table to another table in

which that column is a primary key.

18. What does the ON DELETE CASCADE option do?
CASCADE will allow the deletions in the dependent table that are affected by the deletions of

the tuples in the referenced table .

19. What does the ON UPDATE NO ACTION do?
we try to update a row from the parent table  that has a referencing row in the dependent

table , then SQL Server 2005 will raise an error and the update action on the row in the

parent table will be rolled back.

20. Can you use the ON DELETE and ON UPDATE in the same constraint?
YES

11.6. Exercises
Unless specified otherwise, use the Student_course database to answer the following questions.

Unless otherwise directed, name all CONSTRAINTs.
1. 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

'xx','yyyy',2,1234567.89 could not be inserted, because the 1234567.89 is too large for

SMALLMONEY)

2. 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?
CREATE INDEX idx_ssn ON Test3 (ssn ASC)

ssn colum allow null.

a. Does your ssn column take duplicates? If so, how can you prevent this column from

taking duplicates?
 Yes.
Add UNIQUE constraint.
ALTER TABLE Test3
ADD CONSTRAINT uk_ssn UNIQUE(ssn)

b. 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?
ALTER TABLE Test3
ALTER COLUMN ssn  CHAR(9) NOT NULL
-- This statement is failed since we add some other index and contraint on the same column

already.

ALTER TABLE Test3
ADD CONSTRAINT ck_NULL CHECK (ssn IS NOT NULL );
-- This could be used as alternative.

c. 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.
No, if we only concern the data integrity.

d. 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?
statament refused by sql server

e. Include the NOT NULL constraint in the acct_balance column. What happens?
Delete Test3.

3. 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."

4. 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?
failed.

a. 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?
failed

5. 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

a. Create a violation of insertion integrity by adding an employee to a nonexistent

department. What happens?

b. 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.

c. 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?

d. 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.

e. Redo exercises 5a-5c, except that this time, create the Employee table with the ON

DELETE NO ACTION.

f. Redo exercises 5a-5c, except that this time, create the Employee table with the ON

UPDATE CASCADE.

g. Redo exercises 5a-5c, except that this time, create the Employee table with the ON

UPDATE NO ACTION.

h. Redo exercises 5a-5c, except that this time, create the Employee table with the ON

UPDATE NO ACTION and ON DELETE CASCADE together.

6. 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.

7. 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?