Q2: Create relational tables that solve the update, insert, and delete anomalies
ID: 3869452 • Letter: Q
Question
Q2: Create relational tables that solve the update, insert, and delete anomalies in
Table 4-18. Create all the relational tables with PK, FK (labeled) and with the records for each table.
Purchase
Order #
Purchase Order Date
Part #
Description
Unit
Price
Quantity Ordered
Vendor #
Vendor Name
Vendor Address
2
3/9/18
334
XYZ
$30
3
504
KL Supply
75 Stevens Dr.
2
3/9/18
231
PDQ
$50
5
504
KL Supply
75 Stevens Dr.
2
3/9/18
444
YYM
$80
6
504
KL Supply
75 Stevens Dr.
3
4/5/18
231
PDQ
$50
2
889
Oscan Inc
55 Cougar Cir.
Purchase
Order #
Purchase Order Date
Part #
Description
Unit
Price
Quantity Ordered
Vendor #
Vendor Name
Vendor Address
2
3/9/18
334
XYZ
$30
3
504
KL Supply
75 Stevens Dr.
2
3/9/18
231
PDQ
$50
5
504
KL Supply
75 Stevens Dr.
2
3/9/18
444
YYM
$80
6
504
KL Supply
75 Stevens Dr.
3
4/5/18
231
PDQ
$50
2
889
Oscan Inc
55 Cougar Cir.
Explanation / Answer
Solution :
Table <PurchaseOrder#, PurchaseOrderDate, Part#,Description, UnitPrice, QuantityOrdered, Vender#, VenderName, VendorAddress>
Now we can have anomalies while inserting deleting or updating recodrs in the above table like while inserting we could misspell some columns and also we need to maintain redundant data in the table, while deleting we could loose some information and while updating we might miss out some records to update and there could be inconsistency in the database.
So, we need to normalize the give tables to remove these anomalies.
We can break the given table into 3 smaller tables which will remove all the anomalies.
Vendor <Vender#, VenderName, VendorAddress>
Part <Part#, Description, UnitPrice>
Order <Order#, PurchaseOrder#, PurchaseOrderDate, QuantityOrdered, Part#, Vendor#>
Primary Key :
Vendor : Vendor#
Part : Part#
Order : Order#
Foreign Key :
Order : Part# References Part, Vendor# References Vendor
Please let me know if you face any difficulty in understanding the answer.
Please vote up if satisfied.
Vendor Vendor# VenderName VendorAddress 504 KL Supply 75 Stevens Dr. 889 Oscan Inc 55 Cougar Cir. Part Part# Description UnitPrice 334 XYZ 30 231 PDQ 50 444 YYM 80 Order Order# PurchaseOrder# PurchaseOrderDate QuantityOrdered Part# Vendor# 1 2 3/9/18 3 334 504 2 2 3/9/18 5 231 504 3 2 3/9/18 6 444 504 4 3 4/5/18 2 231 889Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.