97 lines
4.6 KiB
Markdown
97 lines
4.6 KiB
Markdown
# Case Study 8
|
|
|
|
## Instructions
|
|
- 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 (<u>EmployeeID</u>, LastName, FirstName, Department, Position,_Supervisor_, OfficePhone, OfficeFax, EmailAddress)
|
|
|
|
STORE (<u>StoreID</u>, StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
|
|
|
|
PURCHASE_ITEM (<u>PurchaseItemID</u>, _StoreID_, _PurchasingAgentID_,PurchaseDate, ItemDescription, Category, PriceUSD)
|
|
|
|
SHIPMENT (<u>ShipmentID</u>, _ShipperID_, _PurchasingAgentID_,ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate,ActualDepartureDate, EstimatedArrivalDate)
|
|
|
|
SHIPMENT_ITEM (<u>ShipmentID</u>, <u>ShipmentItemID</u>, _PurchaseItemID_,InsuredValue)
|
|
|
|
SHIPPER (<u>ShipperID</u>, ShipperName, Phone, Fax, Email, Contact)
|
|
|
|
SHIPMENT_RECEIPT (<u>ReceiptNumber</u>, _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 (<u>ItemID</u>, 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 (<u>ShipmentID</u>, <u>ShipmentLineNumber</u>, _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.
|