Database Theory

# Thread: Database Theory

1. Junior Member
Join Date
Dec 1969
Posts
8

## Database Theory

&#039;m new to working with databases but I have some qustions about primary and foreign keys. I was reading a book that said "You can search for information in a relational database by using information in one table&#039;s promary key to find information in another table. A foreign key is a column in one table that corresponds to a primary key in another table. In order for you to search for information in a database, the primary key of one table must match a foreign key in another table" Ok but can someone explain to me whats going on behind the scenes why is this happening.

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Actually, nothing is going on...

Oh, databases may optimize queries for this situation, but there&#039;s nothing magic about it.<BR><BR>You could write the same kind of stuff, yourself, using arrays in VBS code:<BR><BR>&#060;%<BR>tableNames = Array("1;John", "2;Bob", "3;Mary")<BR>tableSales = Array("1;14.95", "3;17.85", "1;12.40", "1;19.50", "3;24.50")<BR><BR>&#039; query: Find the total sales for each person<BR>&#039;<BR>For pIndex = 0 To UBound( tableNames )<BR>&nbsp; &nbsp; person = Split( tableNames(pIndex), ";" )<BR>&nbsp; &nbsp; pnum = person(0)<BR>&nbsp; &nbsp; pname = person(1)<BR>&nbsp; &nbsp; total = 0.0<BR>&nbsp; &nbsp; For sIndex = 0 To UBound( tableSales )<BR>&nbsp; &nbsp; &nbsp; &nbsp; sale = Split( tableSales(sIndex), ";" )<BR>&nbsp; &nbsp; &nbsp; &nbsp; spnum = sale(0)<BR>&nbsp; &nbsp; &nbsp; &nbsp; samt = sale(1)<BR>&nbsp; &nbsp; &nbsp; &nbsp; If pnum = spnum Then total = total + samt<BR>&nbsp; &nbsp; Next &#039; next tableSales entry<BR>&nbsp; &nbsp; Response.Write pname & " sold " & total & "&#060;P&#062;"<BR>Next &#039; next tableNames entry<BR>%&#062;<BR><BR>Do you see it? All we have done is recorded multiple sales by using the "ID" of the person who made the sale. Then, when we need to extract info we can "join" the two tables by making matches on the "ID" values in each table.<BR><BR>A database engine does this same thing, effectively, only very very much faster and more efficient thanks to coding techniques and data organization that you really don&#039;t care about, for now.<BR><BR>It&#039;s just a way of matching up records in one table with records in another. And the important point is the in *one* of the tables the ID (or primary key) must be *unique*. (We&#039;d be in trouble if that first tableNames looked like this:<BR>&nbsp; &nbsp; tableNames = Array("1;John", "2;Bob", "3;Mary", "1;Bill")<BR>both John and Bill would get credit for the same sales, yes?)<BR><BR>Oh, and why do we use a unique autonumber value as the primary key, usually? Just to avoid possible "collisions" on other fields! After all, there are lots of "John Smith"s in the world, so you wouldn&#039;t want to rely on name alone, would you? (But you might use Social Security Number as a primary key, mightn&#039;t you? And don&#039;t lots of companies do so?)<BR><BR><BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
8

## RE: Actually, nothing is going on...

So by using a primary key in one table and referencing it as a foregin key in another table allows us to update more than one table.Basically the table that you are realted to.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well, yes and no...

Most DBs won&#039;t let you update more than one table at a time. And, truthfully, it isn&#039;t that common that you *need* to do so. (After all, if you had a Personnel table and a Sales table, why would you need to update the Sales table just because John Doe&#039;s phone number changed?)<BR><BR>If you meant, though, that we use this so that additions we make to the "dependent" table can be "viewed" by way of the "master" table... yes.<BR><BR>The PK-FK relationship stuff is most often used in making queries, of course. Such as the one I demoed.<BR><BR>You might want to find a good reference on "NORMALIZATION" and read it. At a minimum, understand second form normalization. It *is* important.<BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
8

## RE: Well, yes and no...

what is NORMALIZATION also what books would you suggest for someone thats just starting out using databases. I want to learn good design techniques. Also in sql server 2000 is there an autonumber. I&#039;m working on some homework for class and I did not see one listed maybe its named something else

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## I'd just start on the web...

And even in the SQL Server and Access on-line docs, there&#039;s a lot of reasonable material on relationships and normalization. You just kind of have to keep poking around and exploring the various links.<BR><BR>Look at @@IDENTITY for the autonumber capability in SQL Server. If you look in the ASPFAQs and search for that you&#039;ll find a link to a few articles that are relevant.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
10,852

## I disagree.

While yes, the on-line Access, (and particularly SQL Server) docs *do* have lots of info....They aren&#039;t the easiest things for a beginner to understand. Too piecemeal, and technical. <BR><BR>I&#039;d start with something like Wrox&#039;s "Beginning ASP databases".<BR>http://www.amazon.com/exec/obidos/ASIN/1861002726/qid=1016591392/sr=2-2/ref=sr_2_2/102-7007037-4914513<BR><BR>Or Beginning SQL Server 2000 Programming. <BR><BR>A good beginning book will give a much better introduction, form the ground-up, in a far more sequential path than any on-line docs. (At least that I&#039;ve found)<BR><BR>I dunno...Just my two cents.

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## I didn't mean no books at all!

Just that he could start getting *concepts* and then decide what book to buy.<BR><BR>Too often, if you know nothing at all when you go looking at books, you end up getting one that ends up being either too basic or too incomplete. <BR><BR>I *love* books. I&#039;d much rather learn from a book than from HTML pages. But in the absence of any experience with more modern SQL books, what I suggested is what I would do.<BR><BR>If you can endorse those two books unreservedly then by all means, I hope he will get one (or both).<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
10,852

## Ah ha....I misunderstood you.

Mea culpa. &#060;ToBorrowAPhrase /&#062; :-)<BR><BR>And yes, I can recommend those books unreservedly. I don&#039;t own either of them, but have spent a great deal of time looking through both, and I think that they offer a great starting place.<BR><BR>I also *do* own the older brother of the second, "Professional SQL Server 2000 Programming", and it has been invaluable to me.<BR><BR>And I *do* concur wholeheartedly with that sentiment...Books aside, the web...Particularly sqlteam.com, and SQL Server&#039;s Books Online have been indespesible to me. They provide resources that can&#039;t be matched by any book that I have ever found.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•