Need assistance in createing a database

Question

Need assistance in createing a database

deemurphy_us

Mr or Mrs. 500

Points: 595
More actions
January 27, 2012 at 7:26 am

#391418

In the database I want to create I want a table with regions in the US, states for the regions, their abreviations and their postal code (AR), the next table I want to have the largest cities for each of these states and their zip codes, the next table I want to be airports for these citiies and the airport abreviations, then I want a table that will have the top ten restaurants for each of these cities by Restaurant Type/Cuisine. I do not know what to make the primary key and what to make the function key. I am using SQL 2008 R2 and Visual Studio 2010 and would like use the entity framework.
Thank you
LadyDee

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

deemurphy_us (1/27/2012)
In the database I want to create I want a table with regions in the US, states for the regions, their abreviations and their postal code (AR), the next table I want to have the largest cities for each of these states and their zip codes, the next table I want to be airports for these citiies and the airport abreviations, then I want a table that will have the top ten restaurants for each of these cities by Restaurant Type/Cuisine. I do not know what to make the primary key and what to make the function key. I am using SQL 2008 R2 and Visual Studio 2010 and would like use the entity framework.
Thank you
LadyDee

What exactly is the question here?

I do not know what to make the primary key and what to make the function key.

Not sure what a function key is but your primary key will be different in each table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deemurphy_us Mr or Mrs. 500 Points: 595 More actions · Answer 2

I did not mean the function key sorry, I meant the foreign key. I realize I have to make something like regionID for the primary keys.

Thank you

LadyDee

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

So what is it that you need help with? Do you have an idea of what your Region table might look like?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deemurphy_us Mr or Mrs. 500 Points: 595 More actions · Answer 4

One region is DeepSouth, which would have Alabama, Ala - abbreviation and AL as postal Code.

LadyDee

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

OK. I still don't know what your question is. Given your description of what you want in the table what would the table look like?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deemurphy_us Mr or Mrs. 500 Points: 595 More actions · Answer 6

The table would look like this = RegionID, region, state, abbreviation, postal code. Hope this is what you need.

I have not created the tables yet.

LadyDee

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

deemurphy_us (1/27/2012)
Hope this is what you need.

I don't know what I need because I still have no idea what YOU need. What is it that you need help with?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deemurphy_us Mr or Mrs. 500 Points: 595 More actions · Answer 8

In all the table from my initial posting I want them to have a relationship and I and not sure how to do this.

LadyDe

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Something like this help? http://msdn.microsoft.com/en-us/library/ms177463.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deemurphy_us Mr or Mrs. 500 Points: 595 More actions · Answer 10

Yes, thank you so much, just what I needed. I appreciate your time in assisting me.

LadyDee

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

No problem. Sorry it took so long to get through my thick skull what you were looking for. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deemurphy_us Mr or Mrs. 500 Points: 595 More actions · Answer 12

NP. I did not explain what I needed well.

LadyDee - How do I mark this as solved

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

Around here these are never marked as "solved". The same problem you have may well come up again for someone else, or somebody else may stumble across this and the information provided may be relevant to them.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/