VB2008从入门到精通(PDF格式英文版)-第106部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
2000。06。10 1 3 12 23 29 33 27
2000。06。14 2 4 13 19 39 45 26
2000。06。17 3 8 17 19 21 25 35
The data is arranged as a table； and thus could be easily converted into a relational data
base table。 In relational database…speak； each row in the file corresponds to a row of data。 But
to create a row of data； you need fields； each of which is also in a column。 A relational database
table is a grid of rows and columns。 When working with a relational database； you don’t
manipulate columns； you manipulate individual rows。
What distinguishes a relational database is that you can bine tables and create more
plicated data structures。 It is the relationships （called relations） between pieces of data
that make a relational database so powerful。 For the lottery data； a relation could be who won
the jackpot on the draw dates。 Modifying the table to include the person who won would look
this：
2000。05。31 nobody 5 6 13 23 25 37 43
2000。06。03 nobody 7 10 11 18 32 41 5
2000。06。07 nobody 15 23 24 28 38 39 45
2000。06。10 jack 1 3 12 23 29 33 27
2000。06。14 nobody 2 4 13 19 39 45 26
2000。06。17 nobody 3 8 17 19 21 25 35
For the most part； no one won the jackpot。 But on June 10； 2000 （2000。06。10）； a person
named Jack won the jackpot。 You might know a Jack。 I might know a Jack。 But would he be the
same Jack？ Probably not。 So to distinguish winner Jack from another Jack； you would need his
full name； address； and other details。 But where do you put this extra information？
Do you put the information about Jack into the lottery data table？ Or do you create another
table？ The answer is that you create another table。 But since you are working with files； it will
be a file； not a table。 The file will be called jack。txt because the winner of the lottery jackpot
was Jack。 The jack。txt file could contain the following information。
Jack Smith
Address
City
Country
The information in jack。txt and the information in the lottery data file have a relation。 The
relation is the winner of the jackpot。 This is what a relational database is all about。 The reason you
use a relational database and not files that cross…reference each other is because a relational
database is extremely efficient at managing tables and relations。 The techniques demonstrated
in Chapter 10 are nowhere near as efficient and effective as using a relational database。
Relational databases are quite capable of handling terabytes of data。 Additionally； relational
databases are capable of building efficient relations that span terabytes of data。
…………………………………………………………Page 395……………………………………………………………
C HA P TE R 1 4 ■ L E AR N I N G AB O U T R E L AT IO N A L D AT AB A SE D A TA 373
Database Relations
Relations are very powerful； but they can also make things extremely plicated。 When you
split tables and create relations between the tables； you are normalizing the data。 Database
normalization is defined as follows （http：//en。wikipedia。org/wiki/Database_normalization）：
Database normalization is a technique for designing relational database tables to
minimize duplication of information and； in so doing； to safeguard the database
against certain types of logical or structural problems； namely data anomalies。 For
example； when multiple instances of a given piece of information occur in a table； the
possibility exists that these instances will not be kept consistent when the data within
the table is updated； leading to a loss of data integrity。 A table that is sufficiently
normalized is less vulnerable to problems of this kind； because its structure reflects the
basic assumptions for when multiple instances of the same information should be
represented by a single instance only。
For example； the data on the winner and the data on the lottery ticket are kept in separate
tables； making the overall data structure more efficient from a maintenance and performance
point of view。
Creating relations between tables is a form of referencing； where one table says informa
tion here references information in another table。 The referencing is carried out using columns
of one table that are cross…referenced with columns of another table。
The lottery ticket example is interesting because of the relations between the lottery draws
and the winners； which could be of two types：
o One draw to many winners： When a lottery ticket is drawn； there can be many winners。
o One winner to many draws： Even though it is improbable； one winner could win
multiple draws。
In the file example； you saw the one…winner…to…many…draws relation。 You probably did
not think it was that type of relation； and more likely thought it was a one…draw…to…one…winner
relation。 But consider the following text file； where jack is referenced twice。
2000。05。31 nobody 5 6 13 23 25 37 43
2000。06。03 jack 7 10 11 18 32 41 5
2000。06。07 nobody 15 23 24 28 38 39 45
2000。06。10 jack 1 3 12 23 29 33 27
2000。06。14 nobody 2 4 13 19 39 45 26
2000。06。17 nobody 3 8 17 19 21 25 35
The table of draws has the ability to reference the same winner twice； which means the
relation of one winner to many draws is possible。 To add the relation of one draw to many
winners； the table would need to be modified like this：
2000。05。31 nobody 5 6 13 23 25 37 43
2000。06。03 jack jill 7 10 11 18 32 41 5
2000。06。07 nobody 15 23 24 28 38 39 45
2000。06。10 jack 1 3 12 23 29 33 27
2000。06。14 nobody 2 4 13 19 39 45 26
2000。06。17 nobody 3 8 17 19 21 25 35
…………………………………………………………Page 396……………………………………………………………
374 CH AP T E R 1 4 ■ L E A R N I N G A B OU T R E L A TI O N AL DA TA B AS E D AT A
Here； another field indicates Jill as the second winner of the draw。 Adding another field
throws a monkey wrench into the entire table structure and makes processing much more
plicated； because the parsing routines will need to verify if another field is present。 This
breaks the nice grid structure and is plain wrong。
Another approach using the text file would be to create a third file that cross…references the
winners with the dates。 So the lottery file would go back to the original version：
2000。05。31 5 6 13 23 25 37 43
2000。06。03 7 10 11 18 32 41 5
2000。06。07 15 23 24 28 38 39 45
2000。06。10 1 3 12 23 29 33 27
2000。06。14 2 4 13 19 39 45 26
2000。06。17 3 8 17 19 21 25 35
And a winners table would be created：
2000。06。03 jack
2000。06。03 jill
2000。06。10 jack
The winners table is a grid of draw dates and winners on those dates。 Notice how there is
no entry for nobody； so only draw dates with winners are included。
■Note These three tables are an example of correctly normalized data。 When the data is well normalized；
each table contains unique data。 In this example； one table contains all of the lottery drawings； but who the
winners are is stored in another table。 Using database relations； the winners and lottery data are related； yet
neither table needs to know about the other table。
Now what happens if two different people named Jack are lottery winners？ The data might
look like this：
2000。05。31 nobody 5 6 13 23 25 37 43
2000。06。03 nobody 7 10 11 18 32 41 5
2000。06。07 nobody 15 23 24 28 38 39 45
2000。06。10 jack 1 3 12 23 29 33 27
2000。06。14 jack 2 4 13 19 39 45 26
2000。06。17 nobody 3 8 17 19 21 25 35
We know the two jack entries are not for the same Jack。 So now we have an additional
problem of uniqueness。 Uniqueness is not unusual when dealing with relational databases；
and the mon technique is to identify each Jack with a unique key。 For example； a unique
key could be jack_1 or jack_2。 The problem with using jack_1 and jack_2 is that you need to
search the database to see if there is a jack entry； and then find out the last jack entry。 Those
steps are resource…intensive and typically avoided。 Another solution is to use a database
provided field that generates a unique key； which could be a row number or globally unique
identifier （GUID）。 If the unique identifier were to be puter…generated； the table might look
as follows：
…………………………………………………………Page 397……………………………………………………………
C HA P TE R 1 4 ■ L E AR N I N G AB O U T R E L AT IO N A L D AT AB A SE D A TA 375
2000。05。31 1877_ds 5 6 13 23 25 37 43
2000。06。03 1877_ds 7 10 11 18 32 41 5
2000。06。07 1877_ds 15 23 24 28 38 39 45
2000。06。10 1023_ad 1 3 12 23 29 33 27
2000。06。14 1022_xy 4 13 19 39 45 26
2000。06。17 1877_ds 3 8 17 19 21 25 35
In the modified table； you would have no idea who the identifiers represent。 The way to
find out is to take a key and open its associated field—say 1877_ds and the file 1877_ds。txt。
Upon opening the file； you woul
