Homework 5. Normalization Due on Question 1. A tabl A+B is the primary key The f
ID: 3729346 • Letter: H
Question
Homework 5. Normalization Due on Question 1. A tabl A+B is the primary key The following dependencies are identified: le contains columns A, B, C, D, E, F, G AD, E EG 1) Normalize this table to 2NF: underline the primary key of each table. 2) Then normalize to 3NF; underline the primary key of each table 3) Then normalize to BCNF underline the primary key of each table uestion2 Neighborhood Hardware is a hardware store. It orders inventory from various distributors. Neighborhood Hardware gives each distributor a DistID. Distributors may carry the same product. Each product has a universal UPC Number and product name. However each distributor has their own in-house distributor product ID (dist prod_id) for products they carry. For instance, Stanley Hammer-Pro has a universal UPC code 9785600021. Acer Inc. would refer to it by its product ID 576-183 HardwareKing would identify it as 4178 BBR Each purchase order has a purchase order date, and it is issued to one distributor. Neighborhood Hardware can purchase multiple products on a purchase order, each product has a purchase quantity 1) Identify a candidate key 2) Identify all FDs in the format of A B, C DistIDDistName U PC-Number ProdName Oty PurOrd PurOrd Num 25278 03/12 DistProdID Date 576-101 576-112 576-109 576-183 101 Acer Inc. 9785600001 Screwdriver 9785600016 Handsaw 9785600007Bolt cutter 9785600021 DOIT Distribution 0-34 9785600023 Chain saw, 16 in 9785600007 Bolt cutter 9785600015 2.75 in. bolt, 12 9785600016 9785600001 Screwdriver 9785600023 Chain saw, 16 in. 9785600021 9785600007 Boit cutter 9785600007 9785600023 Chain saw, 16 in 9785600015 2.75 in 978560002 Stanley Hammer-Pro 3 9785600023 Chainsaw, 16 in. 25279 03/12/12 102 12 0-345115 4-896101 152 BGR 177 BXR 4178 BB Hammer 2528 102 Distribution 0-345115 5115 25283 03/14/1 Bolt cutter 0-345111 4-896101 4178 BBR 4177 BXR 25284 03/14/12 103 HardwarekingExplanation / Answer
Solution:
The first question is done as per Chegg guidelines, please repost others.
1)
2NF
A relation/table which is in 2NF must not have any non-primary key column that can be determined by partial PK. i.e. we need to look for columns that depend on either A or B, but not both. Looking at the dependencies, we find D,E are such columns, hence we need to split table in following way:
R(A,D,E,G), A is PK. [you may think that G can be in 2nd table, but we want to keep E and G together, so in the next pass, i.e. 3NF we can take care of this dependency]
R(A,B,C,F), (A,B) is PK.
2)
3NF
In 3NF relations, there are no transitive dependencies and the tables are already in 2NF. Now we look into our tables to find transitive dependencies, i.e. X->Y->Z type of dependencies, we find that G is dependent on E, and E is dependent on A,
so our tables are:
R(A,D,E), A is PK.
R(E,G), E is PK.
R(A,B,C,F), (A,B) is PK.
3)
BCNF
in this form, in every function dependency X->Y, X must be superkey, i.e. guarantee uniqueness of a row in the table, if we look at the tables from previous answer, we find C->B is a dependency in which C is not a superkey, hence final tables are:
R(A,D,E), A is PK.
R(E,G), E is PK.
R(A,B,F), A,B is PK.
R(B,C), C is PK.
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.