Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-10-09
10
Medium Priority
?
357 Views
Last Modified: 2012-08-14
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
Comment
Question by:Potchitka
  • 5
  • 5
10 Comments
 

Author Comment

by:Potchitka
ID: 12268579
Clarification: I need to either add it to the Capital relation, the State relation or create a new relation. What should I do?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12268614
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
 

Author Comment

by:Potchitka
ID: 12268667
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12268694
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
 

Author Comment

by:Potchitka
ID: 12268796
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
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 2000 total points
ID: 12271018
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
 

Author Comment

by:Potchitka
ID: 12305329
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12313128
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
 

Author Comment

by:Potchitka
ID: 12328721
Now it makes sense. Thanks AW!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12330457
glad to be of assistance.

AW
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question