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

A.

Here there are two tables given in the spread sheet there are

Shipment(ShipmentNumber,Shipper,Phone,Contact,From,Departure,Arrival,Contents,Insuredvalue)

Item(Item,Date,city,store,salesperson ,price)

Functional Dependencies For the above tables are:- Functional Dependency means that one attribute is functionally dependent on another attribute in the table. Which means without the main attribute the second one is meaningless.

1.Functional Dependencies:-

For the Shipment table the functional dependencies are:-

In this Shipment table the attributesShipper,Phone,Contact,From,Departure,Arrival,Contents are all functionally dependent on the attributeShipmentNumber.

ShipmentNumber-> Shipper,Phone,Contact,From,Departure,Arrival,Contents

Shipper -> Phone,Contact

For the Item table the functional dependencies are:-

In this Item table salesperson is functionally dependent on multiple attributes Item,city,store.

Item,city,store -> Salesperson

Item,Store -> Price

2.Multi values Dependencies:-

In the Shipment table there are no multivalued dependencies because there are no attribute which contains the multiple values to store.
In the Item table the multivalued dependencies are

Itemà City,Store

Salesperson à Item,City,Store

3.Candidate Keys:-

In the shipment table the candidate keys are

ShipmentNumber

In the Item table

Item

Date

4.Primary Keys:-

In Shipment table

ShipmentNumber

In Item Table

Item

5.Foreign Keys:-

In Shipment table

Item.Item

In Item Table

It is not required because the item data is being referenced in the shipment table.

6. Assumptions:-

I have that I am working with this two tables only. But in reality inorder to make this database workout we need to create many new other tables by breaking up somedata with in this tables.

B.

I may ask the Philip the below questions inorder to make the database more clear.

Does you use only one contact number all the time in shipping address.
Does every time you use only one shipper or different shippers
Don’t they have alternate contact numbers like the delivery boy number and don’t you store that in table.
Did you bought the same item from different stores anytime.
Don’t you came across the same state or city name in different countries

C.

In order to eliminate the multivalued dependencies in Item table we need to create the below tables

Item ( Item, store,city)

Item_salesperson(salesperson,item,store,city)

D.

One Problem will be is there is not all the times possible to ship the item from the same country where we ordered. That means to make transportation easy or the item will be made in some other country in these cases the items will be shipped directly from the manufacturers or the country which is nearest to the destination. Here in shipment the from column refers from where the shipment started and in the item table from refers where the item is ordered. So this will be the problem.

The second problem is in shipment table from column refers the countries and the item table from refers the cities in the countries. So this is lot bigger problem than one.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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