Answer questions A through G of the Morgan Importing Project found on page 450.
Create and set up the database with the given information.
Deliver a single sql file, along with a document that contains all the content from the sql file.
Each query should start with a comment line that looks like:
/* *** CS8-<MI.Q> *** */
Example for Question A:
/* *** CS8-MI.A *** */
Do not include the result table unless specifically directed to.
Include at least one line of white space between answers.

Database Information

Tables

EMPLOYEE (EmployeeID, LastName, FirstName, Department, Position,Supervisor, OfficePhone, OfficeFax, 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)

Referential Integrity Constraints

Supervisor in EMPLOYEE must exist in EmployeeID in EMPLOYEE
StoreID in PURCHASE_ITEM must exist in StoreID in STORE
PurchasingAgentID in PURCHASE_ITEM must exist in EmployeeID in EMPLOYEE
ShipperID in SHIPMENT must exist in ShipperID in SHIPPER
PurchasingAgentID in SHIPMENT must exist in EmployeeID in EMPLOYEE
PurchaseItemID in SHIPMENT_ITEM must exist in PurchaseItemID in PURCHASE_ITEM
ShipmentID in SHIPMENT_RECEIPT must exist in ShipmentID in SHIPMENT
PurchaseItemID in SHIPMENT_RECEIPT must exist in PurchaseItemID in PURCHASE_ITEM
ReceivingAgentID in SHIPMENT_RECEIPT must exist in EmployeeID in EMPLOYEE

Keys and Value Restrictions

EmployeeID of EMPLOYEE, PurchaseItemID of PURCHASE_ITEM, ShipperID of SHIPPER, ShipmentID of SHIPMENT, and ReceiptNumber of SHIPMENT_ RECEIPT are all surrogate keys with values as follows:

Column	Initial	Increment
StoreID	Start at 100	Increment by 50
EmployeeID	Start at 101	Increment by 1
PurchaseItemID	Start at 500	Increment by 5
ShipperID	Start at 1	Increment by 1
ShipmentID	Start at 100	Increment by 1
ReceiptNumber	Start at 200001	Increment by 1

Values of the Country column in the STORE table are restricted to:

Hong Kong
India
Japan
Peru
Philippines
Singapore
United States

Motivation

James Morgan wants to modify the database design of the Morgan Importing 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 Figure 8-7 (compare this figure to Figure 6-18(b)).

Figure 8.7: MIPIS Modified SALES_ ORDER Configuration

Figure 6.18b: Transformation of the Mixed Entity Pattern

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 MI.A - MI.G

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.

D.) 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.

E.) Write all SQL statements to make the name change described in part D.

F.) Using your dependency graph, describe the tasks necessary to convert the sales order component of the MIPIS to the new configuration.

G.) Code SQL statements to implement your redesign recommendations in your answer to part F.