Database Redesign - Database Processing: Fundamentals, Design, and Implementation - page 391

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.
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.
h. Code all SQL statements necessary to implement the changes described in part G.
Assume that Morgan has created a database with tables similar to, but not identical to,
those described at the end of Chapter 7 (note that STORE uses the surrogate key StoreID
[this is now also used as the foreign key as needed in other tables], and that the table
named ITEM in Chapter 7 is named PURChASE_ITEM here [with a primary key named
PurchaseItemID, which is also used as the foreign key as needed in other tables]):
EMPLOYEE ( EmployeeID , LastName, FirstName, Department, Phone, Fax,
EmailAddress)
STORE ( StoreID , StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
PURCHASE_ITEM ( PurchaseItemID , StoreID , PurchasingAgentID , PurchaseDate,
ItemDescription, Category, PriceUSD)
SHIPMENT ( ShipmentID , ShipperID , PurchasingAgentID , ShipperInvoiceNumber,
Origin, Destination, ScheduledDepartureDate, ActualDepartureDate,
EstimatedArrivalDate)
SHIPMENT_ITEM ( ShipmentID , ShipmentItemID , PurchaseItemID , InsuredValue)
SHIPPER ( ShipperID , ShipperName, Phone, Fax, Email, Contact)
SHIPMENT_RECEIPT ( ReceiptNumber , ShipmentID , PurchaseItemID , ReceivingAgent ,
ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
Assume that all relationships have been defined as implied by the foreign keys in
this table list.
James Morgan wants to modify the database design of the Morgan Imports
procurement information system (MIPIS) to separate the items in PURChASE_ITEM
in a separate table named ITEM. This will allow each item to be tracked as a unique
entity thoughout its acquisition and sale. The schema for the ITEM table is:
ITEM ( ItemID , ItemDescription, Category)
PURChASE_ITEM will then be replaced by two tables named INVOICE and INVOICE_
LINE_ITEM, linked in a modified sales order configuration as shown in Figure 8-6
(compare this figure to Figure 6-18(b)).
Similarly, the shipping part of the MIPIS will be modified by changes to the
ShIPMENT_ITEM tables as follows:
SHIPMENT_LINE_ITEM ( ShipmentID , ShipmentLineNumber , ItemID ,
InsuredValue)
If you want to run these solutions in a DBMS product, first create a version of the
of the MI database described in Chapter 7 and name it MI-Ch08.
A. Create a dependency graph that shows dependencies among the original set of tables.
Explain how you need to extend this graph for views and other database constructs,
such as stored procedures.
B. Using your dependency graph, describe the tasks necessary to create and populate the
ITEM table.
C. Write all SQL statements to make the name change described in part B.