Chapter 8 – Database Redesign
Page 8-92
MERGE INTO INVOICE AS I USING TEMP_INVOICE_SUBTOTAL AS TIS
ON I.InvoiceNumber = TIS.InvoiceNumber
Chapter 8 – Database Redesign
Page 8-93
UPDATE INVOICE
SET TaxUSD = (SubtotalUSD * 0.000);
It turns out that there are no INVOICES for our New York store, however!
Chapter 8 – Database Redesign
Page 8-94
UPDATE INVOICE
SET TotalUSD = (SubtotalUSD + TaxUSD);
SELECT * FROM INVOICE;
DROP TABLE TEMP_INVOICE_SUBTOTAL;
(3) Modify SHIPMENT_LINE_ITEM to use INVOICE_LINE_ITEM [InvoiceNumber,
LineNumber] instead of PURCHASE ITEM ID. This includes adding and populating new
columns, dropping the old referential integrity constraint.
Chapter 8 – Database Redesign
Now we’ll use another temporary table and the SQL MERGE command.
CREATE TABLE TEMP_INVOICE_DATA(
PurchaseItemID Int NOT NULL IDENTITY (500,5),
INSERT INTO TEMP_INVOICE_DATA
(InvoiceNumber, LineNumber)
SELECT InvoiceNumber, LineNumber
FROM INVOICE_LINE_ITEM;
SELECT * FROM TEMP_INVOICE_DATA;
Chapter 8 – Database Redesign
Page 8-96
MERGE INTO SHIPMENT_LINE_ITEM AS SLI USING TEMP_INVOICE_DATA AS TID
ON SLI.ItemID = TID.PurchaseItemID
Here is the resulting data in SHIPMENT_LINE_ITEM:
Chapter 8 – Database Redesign
Page 8-97
DROP TABLE TEMP_INVOICE_DATA;
Here is how the database looks at this point:
Now we’ll add the referential integrity constraints to SHIPMENT_LINE_ITEM (InvoiceNumber,
LineNumber) to INVOICE_LINE_ITEM.
ALTER TABLE SHIPMENT_LINE_ITEM
ADD CONSTRAINT SLI_ILI_FK
FOREIGN KEY(InvoiceNumber, LineNumber)
REFERENCES INVOICE_LINE_ITEM(InvoiceNumber, LineNumber)
ON UPDATE NO ACTION
ON DELETE CASCADE;
Chapter 8 – Database Redesign
Page 8-98
(4) Modify SHIPMENT_RECEIPT to use INVOICE_LINE_ITEM [InvoiceNumber,
LineNumber] instead of PURCHASE ITEM ID. This includes adding and populating new
columns, dropping the old referential integrity constraint, and adding a new referential integrity
constraint.
This is a reworking of what we did with the SHIPMENT_LINE_ITEM conversion. Here, in brief,
are the steps:
Chapter 8 – Database Redesign
Page 8-99
Now we’ll use another temporary table and the SQL MERGE command.
CREATE TABLE TEMP_SHIPMENT_RECEIPT_DATA (
PurchaseItemID Int NOT NULL IDENTITY (500,5),
InvoiceNumber Int NOT NULL,
LineNumber Int NOT NULL,
CONSTRAINT TID_PK PRIMARY KEY(PurchaseItemID)
);
Chapter 8 – Database Redesign
Page 8-100
MERGE INTO SHIPMENT_RECEIPT AS SR USING TEMP_SHIPMENT_RECEIPT_DATA AS TSRD
ON SR.PurchaseItemID = TSRD.PurchaseItemID
SELECT * FROM SHIPMENT_RECEIPT;
Now we drop the temporary table and add the new referential integrity constraint to
SHIPMENT_RECEIPT.
DROP TABLE TEMP_SHIPMENT_RECEIPT_DATA;
Chapter 8 – Database Redesign
ALTER TABLE SHIPMENT_RECEIPT
DROP CONSTRAINT Ship_Receipt_Item_FK;
(5) Finally, drop the PURCHASE_ITEM table:
DROP TABLE PURCHASE_ITEM;
Here is the final MI-CH08 database diagram, with the complete set of changes made. The tables
Chapter 8 – Database Redesign
Page 8-102