Database Storage & Design Chapter 8 Homework Supervisor in EMPLOYEE must exist in Employee ID

Chapter 8 – Database Redesign

(6) Create a new foreign key constraint between CUSTOMER and

INVOICE_CUSTOMER_INT. Note that since the primary key of CUSTOMER is no longer a

(7) Drop the primary key constraint on (InvoiceNumber, CustomerID) in

INVOICE_CUSTOMER_INT, and replace it with a primary key constraint on (InvoiceNumber,

FirstName, LastName).

Chapter 8 – Database Redesign

Page 8-42

(8) Drop the CustomerID column for INVOICE_CUSTOMER_INT and also in CUSTOMER.

Alter table INVOICE_CUSTOMER_INT drop column CustomerID;

Alter table CUSTOMER drop column CustomerID;

Chapter 8 – Database Redesign

Page 8-43

Chapter 8 – Database Redesign

❖ THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS

Assume that The Queen Anne Curiosity Shop has created a database with the tables described

at the end of Chapter 7:

CUSTOMER (CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword,

Address, City, State, ZIP, Phone, ReferredBy)

The referential integrity constraints are:

ReferredBy in CUSTOMER must exist in CustomerID in CUSTOMER

Supervisor in EMPLOYEE must exist in EmployeeID in EMPLOYEE

Assume that CustomerID of CUSTOMER, EmployeeID of EMPLOYEE, ItemID of ITEM, SaleID

of SALE, and SaleItemID of SALE_ITEM are all surrogate keys with values as follows:

CustomerID Start at 1 Increment by 1

Chapter 8 – Database Redesign

Page 8-45

A vendor may be an individual or a company. If the vendor is an individual, the CompanyName

field is left blank, while the ContactLastName and ContactFirstName fields must have data

values. If the vendor is a company, the company name is recorded in the CompanyName field,

and the name of the primary contact at the company is recorded in the ContactLastName and

ContactFirstName fields.

The diagram below shows the dependencies between the tables, and also shows the extensions for

view and functions. Triggers and stored procedures would be added as they are designed.

Chapter 8 – Database Redesign

B. Using your dependency graph, describe the tasks necessary to change the name of the

SALE table to CUSTOMER_SALE.

We will use just the tables in the dependency graph—every view, trigger and stored procedure

dependent on SALE would also have to be adjusted.

Based on the dependency graph, we can see that SALE is dependent on CUSTOMER and

EMPLOYEE, and that SALE_ITEM is dependent on SALE.

As described in Review Question 8.25 above, the process for changing a table name is:

(1) Create a new table with the new name.

So we would:

(1) Create the table CUSTOMER_SALE (Note—the foreign key constraints between

[CUSTOMER and CUSTOMER_SALE] and [CUSTOMER and EMPLOYEE] can be created as

part of the table creation process).

Chapter 8 – Database Redesign

Page 8-47

C. Write all SQL statements to make the name change described in part B.

(1) Create the table CUSTOMER_SALE. Note that we will also create the foreign key

constraints between [CUSTOMER and CUSTOMER_SALE] and [EMPLOYEE and

CUSTOMER_SALE] at this time—we’re used to doing this when we create a new table.

CREATE TABLE CUSTOMER_SALE(

SaleID Int NOT NULL IDENTITY (1, 1),

CustomerID Int NOT NULL,

EmployeeID Int NOT NULL,

Chapter 8 – Database Redesign

Page 8-48

(2) Drop the foreign key constraints between CUSTOMER and SALE, between EMPLOYEE

and SALE, and between SALE_ITEM and SALE.

ALTER TABLE SALE

DROP CONSTRAINT SALE_CUSTOMER_FK;

Chapter 8 – Database Redesign

Page 8-49

(3) Copy all data from CUSTOMER to CUSTOMER_SALE (note use of SET

IDENTITY_INSERT because SaleID is a surrogate key).

The SQL INSERT statement will have a problem, and the problem is the surrogate key for

SaleID. SQL Server will not let us insert values into a surrogate key field that uses the

IDENTITY function without explicitly stating that we want to. This requires the use of the SQL

Server SQL command IDENTITY_INSERT. We will use the command (don’t run it yet):

SET IDENTITY_INSERT dbo.CUSTOMER_SALE ON

Chapter 8 – Database Redesign

Page 8-50

(4) Add the foreign key constraint for SALE_ITEM.

ALTER TABLE SALE_ITEM

ADD CONSTRAINT Item_CustSale_FK FOREIGN KEY(SaleID)

REFERENCES CUSTOMER_SALE(SaleID)

ON UPDATE CASCADE

ON DELETE CASCADE;

Chapter 8 – Database Redesign

Page 8-51

Chapter 8 – Database Redesign

Page 8-52

If we look at the database diagram in the Microsoft SQL Server Management Studio, we can see

that CUSTOMER_SALE is correctly inserted into the database, and SALE is now disconnected.

Page 8-53

(5) Drop the SALE table.

DROP TABLE SALE;

D. Suppose that The Queen Anne Curiosity Shop owners decide to allow multiple

customers per order (e.g., for customers’ spouses). Modify the design of these tables to

accommodate this change.

(1) Create the intersection table. Foreign key constraints can be done while creating the table or

as a separate step.

So we would:

(1) Create the CUSTOMER_CUSTOMER_SALE_INT intersection table (yes, that is an

awkward name, and we might want to name it something else!). Since we normally create

foreign key constraints when we create a table, we will do the foreign key constraints between

Chapter 8 – Database Redesign

Page 8-54

E. Code SQL statements necessary to redesign the database, as described in your answer

to part D.

CUSTOMER_CUSTOMER_SALE_INT (CustomerID, SaleID)

(1) Create the intersection table. Foreign key constraints can be done while creating the table or

as a separate step.

CREATE TABLE CUSTOMER_CUSTOMER_SALE_INT(

CustomerID Int NOT NULL,

Chapter 8 – Database Redesign

Page 8-55

(2) Copy the values of primary keys from the tables of the original child table for rows in which

there is an existing foreign key match from CUSTOMER_SALE to

CUSTOMER_CUSTOMER_SALE_INT and the foreign key is not null.

INSERT INTO CUSTOMER_CUSTOMER_SALE_INT(CustomerID, SaleID)

Chapter 8 – Database Redesign

If we look at the database diagram in the Microsoft SQL Server Management Studio, we can see

that INVOICE_CUSTOMER_INT is correctly inserted into the database, but that the foreign key

relationship between CUSTOMER_SALE and CUSTOMER still exists.

(3) Drop the original foreign key constraint and foreign key column for CUSTOMER_SALE.

ALTER TABLE CUSTOMER_SALE

DROP CONSTRAINT C_SALE_CUSTOMER_FK;

Chapter 8 – Database Redesign

Page 8-57

With a final look at the database diagram in the Microsoft SQL Server Management Studio, we

can see that CUSTOMER_CUSTOMER_SALE_INT is correctly inserted into the database.

Chapter 8 – Database Redesign

Page 8-58

F. Suppose that The Queen Anne Curiosity Shop owners are considering changing the

primary key of CUSTOMER to (FirstName, LastName). Write correlated subqueries to

display any data that indicate that this change is not justifiable.

Chapter 8 – Database Redesign

Page 8-59

(FirstName, LastName) → EmailAddress

(FirstName, LastName) → EncryptedPassword

(FirstName, LastName) → Address

Which can be combined as:

(FirstName, LastName) → (EmailAddress, EncryptedPassword, Address, City, State, ZIP,

Phone, ReferredBy)

A quick look at the data in the table (which only works because we have very little data) shows

that (FirstName, LastName) is indeed currently unique.

Since the table still has CustomerID as a primary key, the easiest way to check is to test

uniqueness for (FirstName, LastName). We did something similar is Review Question 8.7. Here

is a similar SQL statement:

Chapter 8 – Database Redesign

For SQL Server:

SELECT C1.CustomerID, C1.FirstName, C1.LastName

FROM CUSTOMER AS C1

WHERE C1.LastName IN

G. Suppose that (FirstName, LastName) can be made the primary key of CUSTOMER.

Make appropriate changes to the table design with this new primary key.