Relational Dependencies: You make the Call....

  • Thread starter Thread starter WWGD
  • Start date Start date
AI Thread Summary
The discussion centers on the normalization of a database involving the fields CompanyCity, CompanyState, and CompanyZip. It is established that the dependency CompanyCity + CompanyState leads to CompanyZip, while CompanyCity alone does not, due to cities sharing names across different states. Participants highlight that some cities, like New York City and Los Angeles, have multiple ZIP codes, necessitating a more complex table structure to accurately represent these relationships. To fully normalize the database, a suggested approach includes creating separate tables for city and ZIP code data, ensuring all fields depend on the primary key. The conversation emphasizes the importance of adhering to normalization rules to avoid data redundancy and maintain integrity.
WWGD
Science Advisor
Homework Helper
Messages
7,700
Reaction score
12,698
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.
 
Technology news on Phys.org
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.)
 
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+
 
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.
 
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).
 
  • Like
Likes WWGD
Thread 'Is this public key encryption?'
I've tried to intuit public key encryption but never quite managed. But this seems to wrap it up in a bow. This seems to be a very elegant way of transmitting a message publicly that only the sender and receiver can decipher. Is this how PKE works? No, it cant be. In the above case, the requester knows the target's "secret" key - because they have his ID, and therefore knows his birthdate.
I tried a web search "the loss of programming ", and found an article saying that all aspects of writing, developing, and testing software programs will one day all be handled through artificial intelligence. One must wonder then, who is responsible. WHO is responsible for any problems, bugs, deficiencies, or whatever malfunctions which the programs make their users endure? Things may work wrong however the "wrong" happens. AI needs to fix the problems for the users. Any way to...
Back
Top