Question

[1] [30 points] Consider the relation R City, Street, Zip Code), where a tuple (c, s, z) is in R only if city c has a building with street s, and z is the zipcode for that address in that city. It is assumed that the nontrivial functional dependencies are: Street → Zip Code ZipCode → City City, [a] [b] [C] [d] [e] Determine all possible keys for R. Indicate all possible BCNF and 3NF violations. Decompose R as necessary into collections of relations that in BCNF. Decompose R as necessary into collections of relations that in 3NF. If we decompose R as R1(City, ZipCode) and Rz(City, Street), can the original relation be always recovered exactly through a natural join of the new relations. If not, explain why, giving an example to illustrate your answer.

Answer 1

R (City, Street, Zip Code) fDn City, Street Zip coole " Zip Code city. City street, zipcode [a] So, (b) keye aie city , Sheet) & ( Zip code, Street) All attributes are prime. The relation ix in auf, became there is no partial dependenty. The relation R also in 3NF b'ws there ix no transitive dependency But the relation R is not in BCNP it violates the condition of BCNA bicox Zip code here Zipcode i not a super key. city

BCNA [C] since the relation 'R' is not So; we de compoxe it. R. ( Zipcode , City) ER2( Street, Zipwde) by since the Relation R already is in 302 So, we don't need to de compose the relation. R City Street [Zip Code [e] Rol city, Zipcode) R2 [City, Street) Through this relation Ra & Riz the original relation Can not be recovered through a natural join because in these selations the common attribute for ir city & it is not the keys of any the relation Rq or Rs. Hence we say there not give the original relation R. example: Zip cole that R Street I y. 390.000 de b с 2 2 .Z w d .e www لیا Ri R2 City Zipcode City Street DOUDOU جمع کرا کرا کرا 13 JUDO xistN333 It we apply Natural Join on Pi & R2 we get some extra tiple lie, [ Cw 2 which is not in the original relation.
please give your valuable feedback and support

Thank you