Hi everyone, i am currently working on a personal project that i need help with using MySQL and knowledge of databases. I have provided below all the instructions in attatchments.
James Morgan owns and operates Morgan Importing, which purchases antiques and home furnishings in Asia, ships those items to a warehouse facility in Los Angeles, and then sells these items in the United States. James has hired you to design a database that tracks the Asian purchases and subsequent shipments of these items to Los Angeles and keeps a list of items purchased, shipments of the purchased items, and the items in each shipment. You have chosen to include the following 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)
SHIPPER (ShipperID, ShipperName, Phone, Fax, EmailAddress, Contact)
SHIPMENT (ShipmentID, ShipperID, PurchasingAgentID, ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
SHIPMENT_ITEM (ShipmentID, ShipmentItemID, PurchaseItemID, InsuredValue)
SHIPMENT_RECEIPT (ReceiptNumber, ShipmentID, PurchaeItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
The referential integrity constraints are:
Supervisor in EMPLOYEE must exist in EmployeeID in EMPLOYEE
StoreName in PURCHASE_ITEM must exist in StoreName 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
Furthermore, you have decided that EmployeeID of EMPLOYEE, PurchaseItemID of PURCHASE_ITEM, ShipperID of SHIPPER, ShipmentID of SHIPMENT, and ReceiptNumber of SHIPMENT_RECEIPT are all surrogate keys.
The values of the Country column in the STORE table are restricted to: Hong Kong, India, Japan,Peru, Philippines, Singapore, and United States.