I really need help with A-K for this assignment 5. To complete this assignment,
ID: 3853352 • Letter: I
Question
I really need help with A-K for this assignment 5.
To complete this assignment, you must complete following instructions and provide detailed answers for each item: Follow the "Assessing Table Structure" procedure shown. List multivalued dependencies, functional dependencies, candidate keys, primary keys, and foreign keys. State your assumptions. A B C D List questions you would ask Phillip to verify your assumptions. Create tables as necessary to eliminate multivalued dependencies, if any 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 shipmentitem relationship. F Assume that Phillip wishes to create an updateable database from this data. Design tables you G Assume that Phillip wishes to create a read-only database from this data. Design tables you think H Do these data have the multivalue, multicolumn problem? If so, how will you deal with it? IDo these data have the inconsistent data problem? If so, how will you deal with it? JDo 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? think are appropriate. State all referential integrity constraints. are appropriate. State all referential integrity constraints. For part A, make sure you state your assumptions. For part B, list at least 6 questions to verify your assumptions stated in part A. For part C, describe the tables using the textbook format (the PK is underlined and the FK is italicized). For example, SKU_DATA (SKU SKU_Description, Department, Buyer) For part D, focus on the FK usage and the issue of data consistency when addressing this part. For Parts F and G,, describe the tables using the textbook format (underlined PK and italicized FK) with referential integrity statements. For example, SKU_DATA (SKU, SKU_Description, Department, Buyer) BUYER BuverName, Where SKU DATA.Buyer must exist in BUYER.BuyerName Include all of your answers for all parts in one MS Word document. See other useful hints document in module 1. The assignment is due Sunday 7/02/2017 at 11:59pm 2. Submit your assignment through D2LExplanation / 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.