The goal is to practice database design using normalization. Work the Morgan Imp

Question

The goal is to practice database design using normalization. Work the Morgan Importing Problem described as described below. No database development is required for this assignment, just the table design. The assignment involves concepts covered in chapter 4 and its slides. Pay attention to the following guidelines for Assessing Table Structure from Figure 4-1 of Chapter 4. Count rows and examine columns Examine data values and interview users to determine: Multivalued dependencies Functional dependencies Candidate keys Primary keys Foreign keys Assess validity of assumed referential integrity constraints Assessing Table Structure 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.

Explanation / Answer

E:
Shipment contains contents column which can be made with referential Integrity (foreign Key) by replacing with ItemID as shown below:

Items
ItemID int
Item nvarchar(128)


Shipments
ShipmentID int
ShipmentNumber int
Shipper nvarchar(128)
Phone nvarchar(128)
From nvarchar(128)
Departure nvarchar(128)
Arrival nvarchar(128)
ItemID int FOREIGN KEY REFERENCES Items(ItemID)--replaced
InsuredValue currency
OrderID int FOREIGN KEY REFERENCES Orders(OrderID)--added

F:
Items
ItemID int Primary key,identity
Item nvarchar(128)

Items(ItemID,Item)  
  
Store
StoreID int Primary key,identity
Store nvarchar(128)
City nvarchar(128)
SalesPersonID int FOREIGN KEY REFERENCES SalesPersons (SalesPersons ID)

Store(StoreID,Store,City,SalesPersonID)
SalesPerson(SalesPersonID,SalesPersonName)
where Store.StoreID must exist in SalesPersons.SalesPersonID

SalesPersons
SalesPersonID int Primary key,identity
SalesPersonName nvarchar(128)

SalesPersons(SalesPersonID,SalesPersonName)  
  
Orders
OrderID int Primary key,identity
ItemID int FOREIGN KEY REFERENCES Items(ItemID)
StoreId int
SalesPersonID int FOREIGN KEY REFERENCES SalesPersons (SalesPersonID)
Price currency

Orders(OrderID,ItemID,StoreId,SalesPersonID,Price)
Items(ItemID,Item)
Store(StoreID,Store,City,SalesPersonID)
SalesPersons(SalesPersonID,SalesPersonName)
where Orders.ItemId must exist in Items.ItemID and Orders.StoreId must exist in Store.StoreID
and orders.SalesPersonID must exist in SalesPersons.SalesPersonID

Shipments
ShipmentID int Primary key,identity
ShipmentNumber int
Shipper nvarchar(128)
Phone nvarchar(128)
From nvarchar(128)
Departure nvarchar(128)
Arrival nvarchar(128)
ItemID int FOREIGN KEY REFERENCES Items(ItemID)
InsuredValue currency
OrderID int FOREIGN KEY REFERENCES Orders(OrderID)

Shipments(above columns)
Items(itemid,item)
Orders(OrderID,ItemID,StoreId,SalesPersonID,priceid)
where shipments.OrderID must exist in Orders.OrderID
and shipments.ItemID must exist in Items.ItemID

G:
Create Database Imports

GRANT SELECT ON OBJECT::Imports TO User_Name;

H:

There is a multiValue problem in Shipments table of column Contents which holds multiple Items which is will be resolved when we use the normalized tables described in F.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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