Does City and Zip in Address Table Violate BCNF

235 Views Asked by At

Let's say you have a the following

 ---------------------------
  attribute      constraints
| --------       -----------   
| id             (PK)
| location_name  **
| street         
| zipcode        
| city           

** realistically Unique, but not going to use for future proofing? 

Would this violate BCNF as zipcode can be used to find the city? Although cities can share zipcodes and vise versa, a city can't be in two separate zipcodes where another city is part of that zipcode? (zipcode1 --> city1) and (zipcode2 --> city1 and city2)

(note that zipcode and city are not a composite superkey as multiple locations can be associated with the same zipcode and city). Is BCNF suggesting that you should a completely separate table JUST for pairing cities and zipcodes?

States are omitted because this database is for a single state. Although in that case would you have to have 3 tables since a zipcode cannot be in multiple states (edit: apparently there are, but assuming there aren't). Seems too dumb to me true and that wayy too many unions would be needed.

I honestly dont understand much of anything regarding key terms and have just been left confused (if you could answer in layman's terms and/or technically that's highly appreciated). I tried searching for an answer because I figured it would be common, but couldn't find anything. Given my inability to organize and process mathematical logic, i'm starting to wonder if I picked the wrong field to enter..

1

There are 1 best solutions below

0
Walter Mitty On

What does a five digit zipcode actually determine? As I understand it, it determines a Post Office. This is enough to route every piece of mail from wherever it is to a destination post office. That post office then deliver it locally.

Figuring out what the dependencies are between zip code and state or zip code and city, or zip code and street plus number or apartment number can be the devil's own business.

The area served by a post office is generally part of some community that the Post Office is in, like a town. But there are quirks. The residents of Magalloway, ME are served by the post office in nearby Errol, NH. They therefore use zipcode 03579, the same as the residents of downtown Errol. The letters get forwarded to the Errol post office, then delivered to them in Maine. This may seem very strange, but it works out well in terms of driving miles.

map of 03579