Assume that Morgan has created a database with the tables described at the end of Chapter 7 (note that STORE uses the surrogate key StoreID): 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 throughout 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 the next slide.
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)
QUESTIONS:
1. 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.
2. Using your dependency graph, describe the tasks necessary to create and populate the ITEM table.
3. Write all SQL statements to make the name change described in question B.
4. Using your dependency graph, describe the tasks necessary to change the name of the SHIPMENT_ITEM table to SHIPMENT_LINE_ITEM and the needed changes to column names.
5. Write all SQL statements to make the name change described in question D.
6. Using your dependency graph, describe the tasks necessary to convert the sales order component of the MIPIS to the new configuration.
7. Code all SQL statements necessary to implement the changes described in question F.