|
The pubs database is used a sample database. It is a standard
sample database shipped with MS SQL Server. I exported the authors,
authortitle and the titles tables to
text and MS Access format. The text files use semi-colon separated
fields and new-line separated rows, with the first row containing
the column names.
The following definition of the database is copied from the MSDN documentation
about the pubs database.
authors Table
|
|
|
|
|
|
|
|
|
|
|
|
| Column_name |
Datatype |
Nullable |
Default |
Check |
Key/Index |
|
|
|
|
|
|
| au_id |
id |
no |
|
yes (1) |
PK, clust. |
| au_lname |
varchar(40) |
no |
|
|
Composite, nonclust. (3) |
| au_fname |
varchar(20) |
no |
|
|
Composite, nonclust. (3) |
| phone |
char(12) |
no |
'UNKNOWN' |
|
|
| address |
varchar(40) |
yes |
|
|
|
| city |
varchar(20) |
yes |
|
|
|
| state |
char(2) |
yes |
|
|
|
| zip |
char(5) |
yes |
|
yes (2) |
|
| contract |
bit |
no |
|
|
|
| (1) The au_id CHECK constraint is defined as (au_id LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]').
(2) The zip CHECK constraint is defined as (zip LIKE '[0-9][0-9][0-9][0-9][0-9]').
(3) The composite, nonclustered index is defined on au_lname, au_fname.
|
The following tables show the contents of the authors table. The first column (au_id) is repeated in the lower listing, along with columns #5 through #9. Column #1 is repeated for readability purposes only.
|
|
|
|
|
|
| au_id (#1) |
au_lname (#2) |
au_fname (#3) |
phone (#4) |
|
|
|
|
| 172-32-1176 |
White |
Johnson |
408 496-7223 |
| 213-46-8915 |
Green |
Marjorie |
415 986-7020 |
| 238-95-7766 |
Carson |
Cheryl |
415 548-7723 |
| 267-41-2394 |
O'Leary |
Michael |
408 286-2428 |
| 274-80-9391 |
Straight |
Dean |
415 834-2919 |
| 341-22-1782 |
Smith |
Meander |
913 843-0462 |
| 409-56-7008 |
Bennet |
Abraham |
415 658-9932 |
| 427-17-2319 |
Dull |
Ann |
415 836-7128 |
| 472-27-2349 |
Gringlesby |
Burt |
707 938-6445 |
| 486-29-1786 |
Locksley |
Charlene |
415 585-4620 |
| 527-72-3246 |
Greene |
Morningstar |
615 297-2723 |
| 648-92-1872 |
Blotchet-Halls |
Reginald |
503 745-6402 |
| 672-71-3249 |
Yokomoto |
Akiko |
415 935-4228 |
| 712-45-1867 |
del Castillo |
Innes |
615 996-8275 |
| 722-51-5454 |
DeFrance |
Michel |
219 547-9982 |
| 724-08-9931 |
Stringer |
Dirk |
415 843-2991 |
| 724-80-9391 |
MacFeather |
Stearns |
415 354-7128 |
| 756-30-7391 |
Karsen |
Livia |
415 534-9219 |
| 807-91-6654 |
Panteley |
Sylvia |
301 946-8853 |
| 846-92-7186 |
Hunter |
Sheryl |
415 836-7128 |
| 893-72-1158 |
McBadden |
Heather |
707 448-4982 |
| 899-46-2035 |
Ringer |
Anne |
801 826-0752 |
| 998-72-3567 |
Ringer |
Albert |
801 826-0752 |
|
|
|
|
|
|
| au_id (#1) |
address (#5) |
city (#6) |
state (#7) |
zip (#8) |
contract (#9) |
|
|
|
|
|
|
| 172-32-1176 |
10932 Bigge Rd. |
Menlo Park |
CA |
94025 |
1 |
| 213-46-8915 |
309 63rd St. #411 |
Oakland |
CA |
94618 |
1 |
| 238-95-7766 |
589 Darwin Ln. |
Berkeley |
CA |
94705 |
1 |
| 267-41-2394 |
22 Cleveland Av. #14 |
San Jose |
CA |
95128 |
1 |
| 274-80-9391 |
5420 College Av. |
Oakland |
CA |
94609 |
1 |
| 341-22-1782 |
10 Mississippi Dr. |
Lawrence |
KS |
66044 |
0 |
| 409-56-7008 |
6223 Bateman St. |
Berkeley |
CA |
94705 |
1 |
| 427-17-2319 |
3410 Blonde St. |
Palo Alto |
CA |
94301 |
1 |
| 472-27-2349 |
PO Box 792 |
Covelo |
CA |
95428 |
1 |
| 486-29-1786 |
18 Broadway Av. |
San Francisco |
CA |
94130 |
1 |
| 527-72-3246 |
22 Graybar House Rd. |
Nashville |
TN |
37215 |
0 |
| 648-92-1872 |
55 Hillsdale Bl. |
Corvallis |
OR |
97330 |
1 |
| 672-71-3249 |
3 Silver Ct. |
Walnut Creek |
CA |
94595 |
1 |
| 712-45-1867 |
2286 Cram Pl. #86 |
Ann Arbor |
MI |
48105 |
1 |
| 722-51-5454 |
3 Balding Pl. |
Gary |
IN |
46403 |
1 |
| 724-08-9931 |
5420 Telegraph Av. |
Oakland |
CA |
94609 |
0 |
| 724-80-9391 |
44 Upland Hts. |
Oakland |
CA |
94612 |
1 |
| 756-30-7391 |
5720 McAuley St. |
Oakland |
CA |
94609 |
1 |
| 807-91-6654 |
1956 Arlington Pl. |
Rockville |
MD |
20853 |
1 |
| 846-92-7186 |
3410 Blonde St. |
Palo Alto |
CA |
94301 |
1 |
| 893-72-1158 |
301 Putnam |
Vacaville |
CA |
95688 |
0 |
| 899-46-2035 |
67 Seventh Av. |
Salt Lake City |
UT |
84152 |
1 |
| 998-72-3567 |
67 Seventh Av. |
Salt Lake City |
UT |
84152 |
1 |
titleauthor Table
|
|
|
|
|
| Column_name |
Datatype |
Nullable |
Default |
Check |
Key/Index |
|
|
|
|
|
| au_id |
id |
no |
|
|
Composite PK, clust., (1) FK authors(au_id) (2) |
| title_id |
tid |
no |
|
|
Composite PK, clust., (1) FK titles(title_id) (3) |
| au_ord |
tinyint |
yes |
|
|
|
| royaltyper |
int |
yes |
|
|
|
| (1) The composite, Primary Key clustered index is defined on au_id, title_id.
(2) This Foreign Key also has a nonclustered index on au_id.
(3) This Foreign Key also has a nonclustered index on title_id.
|
|
|
|
|
|
| au_id |
title_id |
au_ord |
royaltyper |
|
|
|
|
|
| 172-32-1176 |
PS3333 |
1 |
100 |
| 213-46-8915 |
BU1032 |
2 |
40 |
| 213-46-8915 |
BU2075 |
1 |
100 |
| 238-95-7766 |
PC1035 |
1 |
100 |
| 267-41-2394 |
BU1111 |
2 |
40 |
| 267-41-2394 |
TC7777 |
2 |
30 |
| 274-80-9391 |
BU7832 |
1 |
100 |
| 409-56-7008 |
BU1032 |
1 |
60 |
| 427-17-2319 |
PC8888 |
1 |
50 |
| 472-27-2349 |
TC7777 |
3 |
30 |
| 486-29-1786 |
PC9999 |
1 |
100 |
| 486-29-1786 |
PS7777 |
1 |
100 |
| 648-92-1872 |
TC4203 |
1 |
100 |
| 672-71-3249 |
TC7777 |
1 |
40 |
| 712-45-1867 |
MC2222 |
1 |
100 |
| 722-51-5454 |
MC3021 |
1 |
75 |
| 724-80-9391 |
BU1111 |
1 |
60 |
| 724-80-9391 |
PS1372 |
2 |
25 |
| 756-30-7391 |
PS1372 |
1 |
75 |
| 807-91-6654 |
TC3218 |
1 |
100 |
| 846-92-7186 |
PC8888 |
2 |
50 |
| 899-46-2035 |
MC3021 |
2 |
25 |
| 899-46-2035 |
PS2091 |
2 |
50 |
| 998-72-3567 |
PS2091 |
1 |
50 |
| 998-72-3567 |
PS2106 |
1 |
100 |
titles Table
|
|
|
|
|
| Column_name |
Datatype |
Nullable |
Default |
Check |
Key/Index |
|
|
|
|
|
| title_id |
tid |
no |
|
|
PK, clust. |
| title |
varchar(80) |
no |
|
|
Nonclust. |
| type |
char(12) |
no |
'UNDECIDED' |
|
|
| pub_id |
char(4) |
yes |
|
|
FK publishers(pub_id) |
| price |
money |
yes |
|
|
|
| advance |
money |
yes |
|
|
|
| royalty |
int |
yes |
|
|
|
| ytd_sales |
int |
yes |
|
|
|
| notes |
varchar(200) |
yes |
|
|
|
| pubdate |
datetime |
no |
GETDATE( ) |
|
|
The following tables show the contents of the titles table. The first column (title_id) is repeated in the lower listing, along with columns #5 through #8, #9, and #10. Column #1 is repeated for readability purposes only.
|
|
|
|
|
| title_id (#1) |
title (#2) |
type (#3) |
pub_id (#4) |
price (#5) |
|
|
|
|
|
| BU1032 |
The Busy Executive's Database Guide |
business |
1389 |
19.99 |
| BU1111 |
Cooking with Computers: Surreptitious Balance Sheets |
business |
1389 |
11.95 |
| BU2075 |
You Can Combat Computer Stress! |
business |
0736 |
2.99 |
| BU7832 |
Straight Talk About Computers |
business |
1389 |
19.99 |
| MC2222 |
Silicon Valley Gastronomic Treats |
mod_cook |
0877 |
19.99 |
| MC3021 |
The Gourmet Microwave |
mod_cook |
0877 |
2.99 |
| MC3026 |
The Psychology of Computer Cooking |
UNDECIDED |
0877 |
NULL |
| PC1035 |
But Is It User Friendly? |
popular_comp |
1389 |
22.95 |
| PC8888 |
Secrets of Silicon Valley |
popular_comp |
1389 |
20.00 |
| PC9999 |
Net Etiquette |
popular_comp |
1389 |
NULL |
| PS1372 |
Computer Phobic and Non-Phobic Individuals: Behavior Variations |
psychology |
0877 |
21.59 |
| PS2091 |
Is Anger the Enemy? |
psychology |
0736 |
10.95 |
| PS2106 |
Life Without Fear |
psychology |
0736 |
7.00 |
| PS3333 |
Prolonged Data Deprivation: Four Case Studies |
psychology |
0736 |
19.99 |
| PS7777 |
Emotional Security: A New Algorithm |
psychology |
0736 |
7.99 |
| TC3218 |
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean |
trad_cook |
0877 |
20.95 |
| TC4203 |
Fifty Years in Buckingham Palace Kitchens |
trad_cook |
0877 |
11.95 |
| TC7777 |
Sushi, Anyone? |
trad_cook |
0877 |
14.99 |
|
|
|
|
|
| title_id (#1) |
advance (#6) |
royalty (#7) |
ytd_sales (#8) |
|
|
|
|
|
| BU1032 |
5,000.00 |
10 |
4095 |
| BU1111 |
5,000.00 |
10 |
3876 |
| BU2075 |
10,125.00 |
24 |
18722 |
| BU7832 |
5,000.00 |
10 |
4095 |
| MC2222 |
0.00 |
12 |
2032 |
| MC3021 |
15,000.00 |
24 |
22246 |
| MC3026 |
NULL |
NULL |
NULL |
| PC1035 |
7,000.00 |
16 |
8780 |
| PC8888 |
8,000.00 |
10 |
4095 |
| PC9999 |
NULL |
NULL |
NULL |
| PS1372 |
7,000.00 |
10 |
375 |
| PS2091 |
2,275.00 |
12 |
2045 |
| PS2106 |
6,000.00 |
10 |
111 |
| PS3333 |
2,000.00 |
10 |
4072 |
| PS7777 |
4,000.00 |
10 |
3336 |
| TC3218 |
7,000.00 |
10 |
375 |
| TC4203 |
4,000.00 |
14 |
15096 |
| TC7777 |
8,000.00 |
10 |
4095 |
|
|
|
|
|
| title_id (#1) |
notes (#9) |
pubdate (#10) |
|
|
|
|
|
| BU1032 |
An overview of available database systems
with emphasis on common business applications. Illustrated. |
Jun 12 1991 12:00AM |
| BU1111 |
Helpful hints on how to use your electronic
resources to the best advantage. |
Jun 9 1991 12:00AM |
| BU2075 |
The latest medical and psychological techniques for living
with the electronic office. Easy-to-understand explanations. |
Jun 30 1991 12:00AM |
| BU7832 |
Annotated analysis of what computers can do for you:
a no-hype guide for the critical user. |
Jun 22 1991 12:00AM |
| MC2222 |
Favorite recipes for quick, easy, and elegant meals. |
Jun 9 1991 12:00AM |
| MC3021 |
Traditional French gourmet recipes adapted for modern
microwave cooking. |
Jun 18 1991 12:00AM |
| MC3026 |
NULL |
Apr 28 1995 10:36AM |
| PC1035 |
A survey of software for the naive user, focusing on the
"friendliness" of each. |
Jun 30 1991 12:00AM |
| PC8888 |
Muckraking reporting on the world's largest computer
hardware and software manufacturers. |
Jun 12 1994 12:00AM |
| PC9999 |
A must-read for computer conferencing. |
Apr 28 1995 10:36AM |
| PS1372 |
A must for the specialist, examining the difference between
those who hate and fear computers and those who don't. |
Oct 21 1991 12:00AM |
| PS2091 |
Carefully researched study of the effects of strong
emotions on the body. Metabolic charts included. |
Jun 15 1991 12:00AM |
| PS2106 |
New exercise, meditation, and nutritional techniques that
can reduce the shock of daily interactions. Popular audience.
Sample menus included, exercise video available separately. |
Oct 5 1991 12:00AM |
| PS3333 |
What happens when the data runs dry? Searching evaluations
of information-shortage effects. |
Jun 12 1991 12:00AM |
| PS7777 |
Protecting yourself and your loved ones from undue
emotional stress in the modern world. Use of computer and
nutritional aids emphasized. |
Jun 12 1991 12:00AM |
| TC3218 |
Profusely illustrated in color, this makes a wonderful gift
book for a cuisine-oriented friend. |
Oct 21 1991 12:00AM |
| TC4203 |
More anecdotes from the Queen's favorite cook describing
life among English royalty. Recipes, techniques, tender
vignettes. |
Jun 12 1991 12:00AM |
| TC7777 |
Detailed instructions on how to make authentic Japanese
sushi in your spare time. |
Jun 12 1991 12:00AM |
|
