E47 Consider the following relation with sample data. 111 - 111 111 SHIPMENTS TA

Question

E47 Consider the following relation with sample data. 111 - 111 111 SHIPMENTS TABLE Quantity ShipmentID ShipmentDate TruckID TruckType ProductHD ProductType seem Trailer Tre That 180 T- Semi Trailer B Battery T222van Truck C Sparkplug 10,000 5,000 333 3-Jan T-333 Semi Trailer T333 semitrailer a 444 3-Jan T-222 Van Truckets 26,000 T-111 SemiTrailer B Battery 180 • The SHIPMENTS Table for a car part company captures the data about car shipments. • Each shipment has a unique Shipment ID and a shipment Date. • Each shipment is shipped on one truck. • Each truck has a unique Truck D, and a Truck Type. • Each shipment can ship multiple products. • Each product has a unique Product ID and a Product Type Each quantity of a product shipped in a shipment is re corded in a table in the column Quantity. E4.7a Depict full key functional dependencies, par- tial functional dependencies (if any), and tran sitive functional dependencies (if any) in the SHIPMENTS Table. E4.7b Show the result of normalizing the SHIPMENTS Table to 2NE E4.7c Show the result of normalizing the SHIPMENTS Table to 3NE

Explanation / Answer

Full Functional Dependancy in database is equivalent to 2NF of the table.

It is a state of table in whict it satisfies the 1NF(no repeating group of information) condition and and all the non-key attribute are functionally dependent on the primary key.

step 1: The above table has only atomic values so 1NFwas satisfied here.

Step 2: All non key attributes are not functionally dependent on the primary key,

Shipment date dependent on shipment id

Truck type dependent on truck id

Product type dependent on product id

To achieve 2NF with shipment table, it was further sub divided into

Shipment(ShipmentId, ShipmentDate, Quantity)

Truck_table(TruckID, TruckType)

Product_table(ProductId, ProductType)

Now the table above satisfies the second normal form.

Step 3: To achieve 3NF there should be no transitive functional dependency as well as the table has to be in 2NF.

Now there is no transitive dependency in the table above hence the shipment table is now in 3NF.

Shipment(ShipmentId, ShipmentDate, Quantity)

Truck_table(TruckID, TruckType)

Product_table(ProductId, ProductType)

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

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