Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Relational Dependencies: You make the Call...

  1. May 19, 2016 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  2. jcsd
  3. May 19, 2016 #2

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    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.)
     
  4. May 19, 2016 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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+
     
  5. May 19, 2016 #4

    QuantumQuest

    User Avatar
    Gold Member

    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.
     
  6. May 19, 2016 #5

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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).
     
  7. May 20, 2016 #6
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: Relational Dependencies: You make the Call...
  1. Call by reference (Replies: 11)

Loading...