Chapter 8 – Database Redesign
Page 8-81
F. Using your dependency graph, describe the tasks necessary to convert the sales order
component of the MIPIS to the new configuration.
The new MIPIS configuration with the INVOICE and INVOICE_LINE_ITEM tables is shown in
Figure 8-7 on the next page. We will assume that the ITEM table has been created and populated
as above, and that the SHIPMENT_LINE_ITEM table rename was completed. These need to be
done to facilitate the final steps. We need to:
Chapter 8 – Database Redesign
(1) Create the INVOICE and INVOICE_LINE_ITEM tables, with an initial foreign key
(2) Populate the INVOICE and INVOICE_LINE_ITEM tables with existing data from
PURCHASE_ITEM. We will need to examine the data when assigning
INVOICE_LINE_ITEM.LineNumber values (were multiple items purchased from the same store
on the same date?) and assign appropriate ITEM.ItemID values.
(3) Modify SHIPMENT_LINE_ITEM to use INVOICE_LINE_ITEM [InvoiceNumber,
LineNumber] instead of PurchaseItemID. This includes adding and populating new columns,
dropping the old referential integrity constraint, and adding a new referential integrity constraint.
Chapter 8 – Database Redesign
Page 8-83
G. Code all SQL statements necessary to implement the changes described in question F.
(1) Create the INVOICE and INVOICE_LINE_ITEM tables, with an initial foreign key
relationship between them.
For SQL Server:
CREATE TABLE INVOICE (
InvoiceNumber Int NOT NULL IDENTITY (500,1),
InvoiceDate Date NOT NULL,
CREATE TABLE INVOICE_LINE_ITEM (
InvoiceNumber Int NOT NULL,
LineNumber Int NOT NULL,
ItemID Int NULL,
Quantity Int NOT NULL,
UnitPriceUSD Numeric(12,2) NOT NULL,
ExtendedPriceUSD Numeric(12,2) NULL,
The allowable NULL values let us provide initial data, and then use triggers or stored procedure
to calculate the other needed values later. Note that ITEM values must be provided when an item
Chapter 8 – Database Redesign
Page 8-84
Here is the current configuration of the database:
Chapter 8 – Database Redesign
(2) Populate the INVOICE and INVOICE_LINE_ITEM tables with existing data from
PURCHASE ITEM. We will need to examine the data when assigning
INVOICE_LINE_ITEM.LineNumber values (were multiple items purchased from the same store
on the same date?) and assign appropriate ITEM.ItemID values.
Chapter 8 – Database Redesign
Page 8-86
We need to examine the data for items purchased from the same store on the same date—those
should show up on one INVOICE. In the data, the only time this happened was on May 20, 2018,
when two items were purchased from the store with StoreID 1100 by Purchasing Agent 104.
Based on this, here are the INSERT statements to populate the tables:
INSERT INTO INVOICE
(StoreID, PurchasingAgentID, InvoiceDate)
VALUES
(1050, 101, ’10-Dec-17′),
(1050, 102, ’12-Dec-17′),
(1200, 104, ’15-Dec-17′),
(1200, 104, ’16-Dec-17′),
(1050, 102, ’07-Apr-18′),
Chapter 8 – Database Redesign
Page 8-87
Chapter 8 – Database Redesign
Page 8-88
Ideally, we would like to use bulk inserts, but this data is too complex at this point, so we will
have to do it manually. Further, we do not have quantity and unit price data, so for existing data
we have to assume a quantity of 1 and a unit price equal to PriceUSD. We can, however,
calculate the new extended priceUSD.
Here is the current ITEM data:
Therefore, the INSERTs into INVOICE_LINE_ITEM are:
INSERT INTO INVOICE_LINE_ITEM
(InvoiceNumber, LineNumber, ItemID, Quantity, UnitPriceUSD)
VALUES
(500, 1, 1, 1, 13415),
(501, 1, 9, 1, 13300),
(502, 1, 4, 1, 38500),
(503, 1, 5, 1, 3200),
(504, 1, 10, 1, 14300),
Chapter 8 – Database Redesign
Page 8-89
Chapter 8 – Database Redesign
Page 8-90
Now we need to SubtotalUSD the INVOICE_LINE_ITEM for each order, determine TaxUSD,
and calculate the TotalUSD. Checking the STORE data, the only STORE in the US is European
Imports in New York City. Although there are no INVOICES for the New York store, we will
show the steps required if there were such invoices. We will assume that we pay TaxUSD = .087
Chapter 8 – Database Redesign
Page 8-91
CREATE TABLE TEMP_INVOICE_SUBTOTAL(
InvoiceNumber Int NOT NULL,
SumExtendedPriceUSD Numeric(12,2) NOT NULL,
CONSTRAINT TIS_PK PRIMARY KEY(InvoiceNUmber)
);