MySQL database

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.

Project Overview:

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.

MySQL Administrare baze de date

ID Proiect: #30197485

Detalii despre proiect