Morgan Importing Problem: Phillip Morgan, the owner of Morgan Importing, makes p

Question

Morgan Importing Problem:
Phillip Morgan, the owner of Morgan Importing, makes periodic buying trips to various countries. During the trips, he keeps notes about the items he purchases and basic data about their shipments. He hired a college student as an intern, and she transformed his notes into the spreadsheets in Figure 4-9. This is just sample data. Phillip has purchased hundreds of items over the years, and they have been shipped in dozens of different shipments.
Phillip wants to enter the information age, thus he has decided to develop a database of his inventory. He wants to keep track of the items he has purchased, their shipments, and eventually customers and sales. To get started, he has asked you to create a database for the data in Figure 4-9.
Figure 4-9 – Spreadsheets fromMorgan Importing
To complete this assignment, you must complete following instructions and provide detailed answers for each item:
A Follow the “Assessing Table Structure” procedure shown. List multivalued dependencies,
functional dependencies, candidate keys, primary keys, and foreign keys. State your
assumptions.
B List questions you would ask Phillip to verify your assumptions.
C Create tables as necessary to eliminate multivalued dependencies, if any.
D The relationship between shipment and item data could be inferred by matching values in the
From cells to values in the City cells. Describe two problems with that strategy.
E Describe a change to this spreadsheet that does express the shipment/item relationship.
F Assume that Phillip wishes to create an updateable database from this data. Design tables you
think are appropriate. State all referential integrity constraints.
G Assume that Phillip wishes to create a read-only database from this data. Design tables you think
are appropriate. State all referential integrity constraints.
H Do these data have the multivalue, multicolumn problem? If so, how will you deal with it?
I Do these data have the inconsistent data problem? If so, how will you deal with it?
J Do these data have a null value data problem? If so, how will you deal with it?
K Do these data have the general-purpose remarks problem? If so, how will you deal with it?

Explanation / Answer

A.Answer

SHIPMENT (ShipmentNumber, Shipper, Phone, Contact, From, Departure, Arrival, Contents, InsuredValue)
ITEM (Item, Date, City, Store, Salesperson, Price)

Multivalued dependencies
Item
City -> Store
(City, Store) -> Item
(City, Store) -> Salesperson

Functional Dependencies
In Shipment
ShipmentNumber -> Shipper
ShipmentNumber -> Phone
ShipmentNumber -> Contact
ShipmentNumber -> From
ShipmentNumber -> Departure
ShipmentNumber -> Arrival
ShipmentNumber -> Contents
Shipper -> Phone
Shipper -> Contact

Candidate Keys
- ShipmentNumber

ITEM (Item, Date, City, Store, Salesperson, Price)

Multivalued Dependencies
City ->-> Store
(City,Store) ->-> Item
(City, Store) ->-> SalesPerson

Functional Dependencies
(Item, Date, City, Store) -> SalesPerson
(Item, Date, City, Store) -> Price

Candidate Keys:
(Item, Date, City, Store)

B. List all question
1. Can the same item be brought from different stores?
2. Does each shipper has just one phone?
3. Can more than one store in same country , but in a different city have the same name?
4. Is there the same city name in more than one country?
do you always use just one contact at each shipper?
5. Can you buy item from more than one store in each city in each country?
6. Same type of item can be purchased from different stores?

C)Create tables as necessary to eliminate multivated dependencies
In Item
Suppose more than one item purchased from each store
City ->-> Store
City_Store(City, Store)
(City, Store) ->-> Item

In a case where an item may bbe available for many store
Item ->-> (City, Store)
ITEM(Item, Date, City, Store, Salesperson, Price)

To track data on items purchased for import we need to create a separate ITEM table

CITY_STORE_ITEM(Item, City, Store)
(City, Store) ->-> Salesperson
CITY_STORE_SALESPERSON(City, Store, Salesperson)
ITEM (Item, Date, City, Store, Salesperson, Price)

F. Answer
SHIPMENT. Contents has been replaced with the SHIPMENT_ITEM table
ITEM (Item, {additional columns - Description, Weight, etc})

CITY_STORE(City, Store,{additional columns - Country, Address, etc})

CITY_STORE_SALESPERSON(City, Store, Salesperson WHERE CITY_STORE_SALESPERSON.(City,Store))
must exist in CITY_STORE.(City,Store)

CITY_STORE_ITEM(Item,City,Store)
WHERE CITY_STORE_ITEM.(City,Store) must exist in CITY_STORE.(City, Store), AND
WHERE CITY_STORE_ITEM. Item must exist in ITEM.Item

ITEM_PURCHASE(Item, Date, CITY, Store, Salesperson, Price)
WHERE ITEM_PURCHASE(Item, City, Store) must exist in CITY_STORE_ITEM. (Item, City, Store)

SHIPPER(Shipper, Phone, Contact)

SHIPMENT(ShipmentNumber, Shipper, From, Departure, Arrival, Contents, InsuredValue)
WHERE SHIPMENT.Shipper must exist in SHIPPER.Shipper

SHIPMENT_ITEM(ShipmentNumber, Item, Date, City, Store)
WHERE SHIPMENT_ITEM.sHIPMENTNumber must exist in SHIPMENT.ShipmentNumber, AND
WHERE SHIPMENT_ITEM(Item, Date,City,Store) must exist in ITEM_PURCHASE.(Item, Date,City,Store)

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.