Database Storage & Design Chapter 8 Homework Cust invoice Customer And The Foreign Key Not

Chapter 8 – Database Redesign

Page 8-21

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

INVOICE table to CUST_INVOICE.

We will use the original dependency graph with just the tables. Based on the dependency graph,

we can see that INVOICE is dependent on CUSTOMER, and that INVOICE_ITEM is dependent

on INVOICE.

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 CUST_INVOICE (Note the foreign key constraint between CUSTOMER

and CUST_INVOICE can be created as part of the table creation process).

Page 8-22

(3) Copy all data from INVOICE to CUST_INVOICE.

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

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

between CUSTOMER and CUST_INVOICE at this time—we’re used to doing this when we

create a new table.

Chapter 8 – Database Redesign

Page 8-23

(2) Drop the foreign key constraints between CUSTOMER and INVOICE and between

INVOICE_ITEM and INVOICE.

(3) Copy all data from INVOICE to CUST_INVOICE.

INSERT INTO CUST_INVOICE (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal,

Tax, Total)

Chapter 8 – Database Redesign

Page 8-24

(4) Add the foreign key constraint for INVOICE_ITEM.

ALTER TABLE INVOICE_ITEM

ADD CONSTRAINT Item_CustInvoice_FK FOREIGN KEY(InvoiceNumber)

Chapter 8 – Database Redesign

Page 8-25

Note that the contents of the two tables are now identical:

Chapter 8 – Database Redesign

Page 8-26

Chapter 8 – Database Redesign

Page 8-27

D. Suppose that Marcia decides to allow multiple customers per order (for customers’

spouses, for example). Modify the design of these tables to accommodate this change.

We will assume that the name change from INVOICE to CUST_INVOICE described above has

(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 INVOICE_CUSTOMER_INT. Since we normally create foreign key constraints

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

INVOICE_CUSTOMER_INT and CUSTOMER and between INVOICE_CUSTOMER_INT and

CUST_INVOICE while creating the intersection table.

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

to question D.

INVOICE_CUSTOMER_INT (InvoiceNumber, CustomerSK )

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

as a separate step.

CREATE TABLE INVOICE_CUSTOMER_INT(

InvoiceNumber Int NOT NULL,

Chapter 8 – Database Redesign

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

there are existing foreign key matches from CUST_INVOICE to CUSTOMER and the foreign

key is not null.

INSERT INTO INVOICE_CUSTOMER_INT(InvoiceNumber, CustomerID)

Chapter 8 – Database Redesign

Page 8-29

Chapter 8 – Database Redesign

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

ALTER TABLE CUST_INVOICE

DROP CONSTRAINT CustINVOICE_Cust_FK;

ALTER TABLE CUST_INVOICE

DROP COLUMN CustomerID;

Chapter 8 – Database Redesign

Page 8-31

Chapter 8 – Database Redesign

Page 8-32

F. Suppose that Marcia considers changing the primary key of CUSTOMER to (FirstName,

LastName). Write correlated subqueries to display any data that indicate that this change is not

justifiable.

CUSTOMER (CustomerID, FirstName, LastName, Phone, EmailAddress)

If (FirstName, LastName) is going to be a primary key, then the following functional

dependencies must exist:

Chapter 8 – Database Redesign

Page 8-33

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:

For SQL Server:

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

FROM CUSTOMER AS C1

WHERE C1.LastName IN

Chapter 8 – Database Redesign

Page 8-34

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.

Given our results to Project Question F, this won’t work. SO, we’ll change the data set to allow

(FirstName, LastName) to be a possible primary key by changing the first name of CustomerID =

106 to “Betty”.

UPDATE CUSTOMER

SET FirstName = ‘Betty’

WHERE CustomerID = 106;

Chapter 8 – Database Redesign

Page 8-35

We will assume that both the changes described in all the preceding questions have been made.

Therefore the current table design (schema) is:

CUSTOMER (CustomerID, FirstName, LastName, Phone, EmailAddress)

CUST_INVOICE (InvoiceNumber, DateIn, DateOut, Subtotal, Tax, TotalAmount)

Chapter 8 – Database Redesign

Page 8-36

Without moving the locations of existing columns in the schema (table design), if we make

(FirstName, LastName) the primary key of CUSTOMER we would have:

CUSTOMER (CustomerID, FirstName, LastName, Phone, EmailAddress)

H. Code all SQL statements necessary to implement the changes described in question G.

(1) Add the new columns to INVOICE_CUSTOMER_INT to hold the foreign key values, but

allow these columns to be NULL at this point (there are many existing rows, and these columns

will initially be empty).

(2) Add foreign key data to INVOICE_CUSTOMER_INT.

Chapter 8 – Database Redesign

Page 8-37

First we’ll see what the current table data looks like, then add the data and finally look at the data

again.

SELECT * FROM INVOICE_CUSTOMER_INT;

Chapter 8 – Database Redesign

Page 8-38

UPDATE INVOICE_CUSTOMER_INT

SET INVOICE_CUSTOMER_INT.FirstName =

(SELECT C.FirstName

FROM CUSTOMER AS C

WHERE C.CustomerID = INVOICE_CUSTOMER_INT.CustomerID);

Chapter 8 – Database Redesign

Page 8-39

SELECT * FROM INVOICE_CUSTOMER_INT;

(3) Set the new columns to NOT NULL.

ALTER TABLE INVOICE_CUSTOMER_INT

ALTER COLUMN FirstName Char(25) NOT NULL;

ALTER TABLE INVOICE_CUSTOMER_INT

ALTER COLUMN LastName Char(25) NOT NULL;

Chapter 8 – Database Redesign

Page 8-40

(4) Drop the existing foreign key constraint between CUSTOMER and

INVOICE_CUSTOMER_INT.

(5) Drop the existing primary key constraint for CUSTOMER and set a new primary key

constraint.

ALTER TABLE CUSTOMER

DROP CONSTRAINT CustomerPK;

ALTER TABLE CUSTOMER

ADD CONSTRAINT CustomerPK_2

PRIMARY KEY(FirstName, LastName);