A. Follow the procedure shown in Figure 4-1 to assess these data. 1. List all fu

Question

A. Follow the procedure shown in Figure 4-1 to assess these data.

1. List all functional dependencies.

2. List any multivalued dependencies.

3. List all candidate keys.

4. List all primary keys

5. List all foreign keys.

6. State any assumptions you make as you list these components.

B. List questions you would ask Phillip to verify your assumptions.

C. If there are any multivalued dependencies, create the tables needed to eliminate these dependencies.

D. The relationship between shipment and item data could possibly 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 updatable database from these data. Design tables you think are appropriate. State all referential integrity constraints. Figure 4-9 Spreadsheets from Morgan Importing 154 Part 2 Database Design

G. Assume that Phillip wishes to create a read-only database from these 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?

E. Describe a change to this spreadsheet that does express the shipment-item relationship F Assume that Phillip wishes to create an updatable database from these data. Design igure: 4-9: tables you think are appropriate. State all referential integrity constraints preadsheets from Morgan mporting 1 ShipmentNumbe Shippe Contact Fio DepartureAiva Contents InsuredValue Pliont QE dining set, large bureau, 49100300 Wordwide 800-123-4567 Jose Philippines 5/5/2011 6/17/1999 porcelain lamps $27.500 Miscellaneous linen, large masks 14 setting Woven goods, antique leather 488955 Intenational 800-123-8898 Mrilyn Singapore 6/2/2011 Willow design china $7,500 84899440 Wordwide 800-123-4567 lose 73/2011 7128/2011 chairs Large burcau, brass lamps, willow 399400 Inlenaliurial 800-123-8898 Marily Singapureee 52011 9/11/2011 design servinng disthes $18,000 City Salesperson Price Willow Serving Dishes /15/2009 Singapore Jade Antiques Swee Lai Large bureau Brass lamps QE Dining Set 54,500 $9,500 $1,200 14,300 10 7/17/2009 Singapore Eastern Sales Jeremey 7/20/2009 Singapore Jade Antiques Mr. James 4/7/2009 ManilaE. Treasures Cracielle 12 13 14 15

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:-

In this Shipment table the attributes Shipper,Phone,Contact,From,Departure,Arrival,Contents are all functionally dependent on the attribute ShipmentNumber.

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

Shipper -> Phone,Contact

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:-

Itemà City,Store

Salesperson à Item,City,Store

3.Candidate Keys:-

ShipmentNumber

Item

Date

4.Primary Keys:-

ShipmentNumber

Item

5.Foreign Keys:-

Item.Item

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.

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 A
• Subjects

---

---

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