Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

I don't understand where I would add this. HELP!

State

StateAbbrev   StateName     EnteredUnionOrder     StateBird      StatePopulation
    CT           Connecticut      5               American robin          7,458,116
    MI           Michigan             25                           Robin              10,229.277
    SD          South Dakota        40                 Pheasant              722,007
    TN           Tennessee           16               Mockingbird           4,999,002
    TX              Texas      23               Mockingbird          17,777.999

Capital

CapitalName     StateAbbrev  YearDesignated  PhoneAreaCode      CapitalPopulation
    Austin                   TX      1846                 627             777,222
  Hardford                   CT      1662                 899             139,999
   Lansing                   MI      1847                 517             122,332
  Nashville                   TN      1843                 615             477,388
    Pierre                   SD      1889`                 604               12,999

I need to add the attribute CountyName for the county or counties containing the state capital. I don’t understand how I could do this. The Primary Key for State is StateAbbrev and the Primary Key for Capital is CapitalName. The Foreign Key for Capital is StateAbbrev.
0
Potchitka
Asked:
Potchitka
  • 5
  • 5
1 Solution
 
PotchitkaAuthor Commented:
Clarification: I need to either add it to the Capital relation, the State relation or create a new relation. What should I do?
0
 
Arthur_WoodCommented:
Are you trying to keep an histrorical record of the state capitals- that is, are there any states in your Capitals table that have 2 capitals?  If not, then 1) why are you keeping two separate tables, since each state in the State table has EXACTLY 1 record in the Capitals Table (making this a one-to-one relations)

2) assuming that you have a good reason for keep the two tables separate, why don't you simply add a field in the Capital table to hold the County of the Capital (are there any instances of a State capital belonging to two counties?)

AW
0
 
PotchitkaAuthor Commented:
Arthur,

Thank you for responding so quickly. I would have to assume that the database will eventually store every county in the U.S., not just the counties that have state capitals. So, with this in mind, would it be better to create a new relation, add the CountyName attribute to it as the primary key and StateAbbrev as the foreign key? If so, why?

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Arthur_WoodCommented:
If that is the case, the  you would need a StateCounty Table to hold the StateAbbrev, CountyID, and CountName for each and every County in each and every state (that table should be available from some source, so that you do not need to create it yourself, as there are some 4800+ counties in the entire United States, and I doubt that you want to have to key in each entry.

Then you would add a field, in the Capital table to hold the CountyID of the County where the capital is located - since you already have the StateAbbrev in the Capital table, thus you can locate the County record in the StateCounty Table.

what do you mean by "If so, why?" you just answered that question.

AW
0
 
PotchitkaAuthor Commented:
AW,

Your answers make perfect sense, but I still don't know why creating a new relation is needed. Is that because the new attribute needs a separate relation and should not be added to either of the other relations? If this is so, then why? Why is it important to add a new relation? I must say that I am very new to MS-Access. Your help is very much appreciated. I promise that this is my last question.
0
 
Arthur_WoodCommented:
the 'new relation' as you call it (truly it is a new TABLE), is to allow you to have a list of ALL of the COUNTIES, in ALL of the states, and then simply add a field, in your Capitals table, to link to the SPECIFIC county record, in that new table, to identify which SPECIFIC record was the one that was tied to the Capital of that state.

If you are NOT going to have a list of all of the counties in each state, then, as I indicated, all you really need is to add a CountyName field to the Capitals tabel, and then simply have the CoutyName in the Capital table, without the need for another table.  It all depends on the intended application, and what you are trying to accomplish.  You can't just make things up as you go along.  It usually requires some planning, and a design of what it is you are attempting to accomplish.  In a formal development process, those are refered to as REQUIREMENTS - what is the application SUPPOSED to do, what is it going to be used for?

AW
0
 
PotchitkaAuthor Commented:
From this I wonder how I would be able to tell which counties have capitals in them?

State (StateAbbrev, StateName, EnteredUnionOrder, StateBird, StatePopulation)
Capital (CapitalName, CountyID, StateAbbrev, YearDesignated, PhoneAreaCode,
   CapitalPopulation)
   Foreign key: StateAbbrev to State relation
StateCounty (CountyID, StateAbbrev, CountyName)
   Forengn key: StateAbbrev to State relation
0
 
Arthur_WoodCommented:
The CountyID field in the Capitals table identifies, for each State Capital, the county of that city.

CountyID in the Capital Table is the Foreign Key for the StateCounty Table

AW
0
 
PotchitkaAuthor Commented:
Now it makes sense. Thanks AW!
0
 
Arthur_WoodCommented:
glad to be of assistance.

AW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now