Relational Dependencies: You make the Call...

WWGD · May 19, 2016

Hi all,
I am normalizing a database and I have the three fields: CompanyCity, CompanyState, CompanyZip
It seems clear to me that there is a dependency :

CompanyCity+ CompanyState --> CompanyZip

But CompanyCity --> CompanyZip is not a dependency, since there are cities with the same name in different states. My colleague says this says both are wrong. Just want to have someone impartial clear up this disagreement.
Thanks.

Samy_A · May 19, 2016

Are there cities in the US with more than one zipcode? If there are, the first dependency is wrong. You would need a table with CityName, CityState, CityZip, CityPK, and then have a foreign key in the Companies table referring to CityPK.

(And to fully normalize, you could make two tables, one with CityName, CityState, CityPK, and a second table listing all the zip codes per city. Maybe overkill in most circumstances.)

WWGD · May 19, 2016

Samy_A said: ↑

Are there cities in the US with more than one zipcode? If there are, the first dependency is wrong. You would need a table with CityName, CityState, CityZip, CityPK, and then have a foreign key in the Companies table referring to CityPK.

(And to fully normalize, you could make two tables, one with CityName, CityState, CityPK, and a second table listing all the zip codes per city. Maybe overkill in most circumstances.)

Actually, now that you mention it, yes, in NYC, for one, there are different ZIPs for different parts of the city. So now I am thinking. And I know there is a similar issue in L.A , you know, the 90210 thing and all (though maybe, unlike me, you actually have a life and you don't know ;)) :

CompanyStAddress+CompanyCity+ CompanyState --> CompanyZip

Since once you know a region within a city, the ZIP is determined. But , as you correctly point out, CompanyCity+

QuantumQuest · May 19, 2016

In order to be sure that you're normalizing the tables of a database correctly, you must begin with the 1st NF and go your way, keeping track of what stage of normalizing you're at. I give the general guidelines from my own experience, just in case they could be of some help:
In order to get a table in 1NF, you have to get rid of repeated groups of fields (columns) so that the intersection of a raw and a column, always corresponds to a single value. If you are on 1st NF - if not, you have to go first to 1st NF, then to go to 2nd NF, you must get rid of all partial functional dependencies that exist in table fields. All fields not belonging to primary key, are solely dependent on the primary key, which in the usual case is comprised by more than one fields. With 2nd NF we effectively avoid field repetitions. In order to get to 3rd NF - table has to be already in 2nd NF, we must get rid of all the transitional dependencies that exist among its fields. In other words, in each and every table all fields must depend on the primary key - not through other fields. Now in order to go to 4th NF - provided you are on 3rd NF, we have to get rid of all functional dependencies that contain multi-valued fields. Finally, to go to 5th NF - again provided you are on 4th NF, you must get rid of all the remaining dependencies that block the breakup of the table in smaller tables, which if in turn breakup in even smaller ones and get combined appropriately, they can recreate the initial tables.

WWGD · May 19, 2016

QuantumQuest said: ↑

In order to be sure that you're normalizing the tables of a database correctly, you must begin with the 1st NF and go your way, keeping track of what stage of normalizing you're at. I give the general guidelines from my own experience, just in case they could be of some help:
In order to get a table in 1NF, you have to get rid of repeated groups of fields (columns) so that the intersection of a raw and a column, always corresponds to a single value. If you are on 1st NF - if not, you have to go first to 1st NF, then to go to 2nd NF, you must get rid of all partial functional dependencies that exist in table fields. All fields not belonging to primary key, are solely dependent on the primary key, which in the usual case is comprised by more than one fields. With 2nd NF we effectively avoid field repetitions. In order to get to 3rd NF - table has to be already in 2nd NF, we must get rid of all the transitional dependencies that exist among its fields. In other words, in each and every table all fields must depend on the primary key - not through other fields. Now in order to go to 4th NF - provided you are on 3rd NF, we have to get rid of all functional dependencies that contain multi-valued fields. Finally, to go to 5th NF - again provided you are on 4th NF, you must get rid of all the remaining dependencies that block the breakup of the table in smaller tables, which if in turn breakup in even smaller ones and get combined appropriately, they can recreate the initial tables.

I understand, that is precisely what I am trying to do. I believe the above is a dependency on non-keys: Company Name is the PK for Company, but CompanyZip is dependent on the three fields (We are restricting to major companies so that two companies having the same name is not an issue).

Fooality · May 20, 2016

Interesting question: just googled it, relation between zip codes and cities is many to many. Many zips for city, many towns for zip:
https://www.melissadata.com/lookups/cityzip.asp