titles is defined as follows:
create table titles (title_id tid not null, title varchar(80) not null, type char(12) not null, pub_id char(4) null references publishers(pub_id), price money null, advance numeric(12,2) null, num_sold int null, notes varchar(200) null, pubdate datetime not null, contract bit not null, unique nonclustered (title_id))
Its nonclustered index for the title column is defined as:
create nonclustered index titleind on titles (title)
Its title_idrule is defined as:
create rule title_idrule as @title_id like "BU[0-9][0-9][0-9][0-9]" or @title_id like "[MT]C[0-9][0-9][0-9][0-9]" or @title_id like "P[SC][0-9][0-9][0-9][0-9]" or @title_id like "[A-Z][A-Z]xxxx" or @title_id like "[A-Z][A-Z]yyyy"
The type column has the following default:
create default typedflt as "UNDECIDED"
sp_bindefault typedflt, "titles.type"
The pubdate column has this default:
create default datedflt as getdate()
sp_bindefault datedflt, "titles.pubdate"
titles uses the following trigger:
create trigger deltitle on titles for delete as if (select count(*) from deleted, salesdetail where salesdetail.title_id = deleted.title_id) >0 begin rollback transaction print "You can’t delete a title with sales." end
The following view uses titles:
create view titleview as select title, au_ord, au_lname, price, num_sold, pub_id from authors, titles, titleauthor where authors.au_id = titleauthor.au_id and titles.title_id = titleauthor.title_id
Table B-3 lists the contents of titles:
title_id |
title |
type |
pub- _id |
price |
ad- vance |
num_- sold |
notes |
pub- date |
con- tract |
---|---|---|---|---|---|---|---|---|---|
BU1032 |
The Busy Executive’s Database Guide |
business |
1389 |
19.99 |
5000.00 |
4095 |
An overview of available database systems with emphasis on common business applica-tions. Illustrated. |
Jun 12, 1986 |
1 |
BU1111 |
Cooking with Computers: Surrepti-tious Balance Sheets |
business |
1389 |
11.95 |
5000.00 |
3876 |
Helpful hints on how to use your electronic resources to the best advantage. |
Jun 9, 1988 |
1 |
BU2075 |
You Can Combat Computer Stress! |
business |
0736 |
2.99 |
10125.00 |
18722 |
The latest medical and psycho-logical techniques for living with the electronic office. Easy-to-under-stand explana-tions. |
Jun 30, 1985 |
1 |
BU7832 |
Straight Talk About Computers |
business |
1389 |
19.99 |
5000.00 |
4095 |
Annotated analysis of what computers can do for you: a no-hype guide for the critical user. |
Jun 22, 1987 |
1 |
MC2222 |
Silicon Valley Gastro-nomic Treats |
mod_- cook |
0877 |
19.99 |
0.00 |
2032 |
Favorite recipes for quick, easy, and elegant meals; tried and tested by people who never have time to eat, let alone cook. |
Jun 9, 1989 |
1 |
MC3021 |
The Gourmet Microwave |
mod_- cook |
0877 |
2.99 |
15000.00 |
22246 |
Traditional French gourmet recipes adapted for modern microwave cooking. |
Jun 18, 1985 |
1 |
PC1035 |
But Is It User Friendly? |
popular_comp |
1389 |
22.95 |
7000.00 |
8780 |
A survey of software for the naive user, focusing on the “friendli-ness” of each. |
Jun 30, 1986 |
1 |
MC3026 |
The Psychologyof Computer Cooking |
UNDE- CIDED |
0877 |
NULL |
NULL |
NULL |
NULL |
Jul 24, 1991 |
0 |
PC8888 |
Secrets of Silicon Valley |
popular_comp |
1389 |
20.00 |
8000.00 |
4095 |
Muck-raking reporting by two coura-geous women on the world’s largest computer hardware and software manufact-urers. |
Jun 12, 1987 |
1 |
PC9999 |
Net Etiquette |
popular_comp |
1389 |
NULL |
NULL |
NULL |
A must-read for computer conferenc-ing debu-tantes! |
Jul 24, 1996 |
0 |
PS1372 |
Computer Phobic and Non-Phobic Indivi-duals: Behavior Variations |
psycho- logy |
0877 |
21.59 |
7000.00 |
375 |
A must for the specialist, this book examines the difference between those who hate and fear computers and those who think they are swell. |
Oct 21, 1990 |
1 |
PS2091 |
Is Anger the Enemy? |
psycho- logy |
0736 |
10.95 |
2275.00 |
2045 |
Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. |
Jun 15, 1989 |
1 |
PS2106 |
Life Without Fear |
psycho- logy |
0736 |
7.00 |
6000.00 |
111 |
New exercise, meditation, and nutritional techniques that can reduce the shock of daily inter-actions. Popular audience. Sample menus included. Exercise video available separately. |
Oct 5, 1990 |
1 |
PS3333 |
Prolonged Data Depriva-tion: Four Case Studies |
psycho- logy |
0736 |
19.99 |
2000.00 |
4072 |
What happens when the data runs dry? Searching evaluations of infor- mation-shortage effects on heavy users. |
Jun 12, 1988 |
1 |
PS7777 |
Emotional Security: A New Algorithm |
psycho- logy |
0736 |
7.99 |
4000.00 |
3336 |
Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids empha-sized. |
Jun 12, 1988 |
1 |
TC3218 |
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterra- nean |
trad_cook |
0877 |
20.95 |
7000.00 |
375 |
Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend. |
Oct 21, 1990 |
1 |
TC4203 |
Fifty Years in Bucking-ham Palace Kitchens |
trad_cook |
0877 |
11.95 |
4000.00 |
15096 |
More anecdotes from the Queen’s favorite cook describing life among English royalty. Recipes, techniques, tender vignettes. |
Jun 12, 1985 |
1 |
TC7777 |
Sushi, Anyone? |
trad_cook |
0877 |
14.99 |
8000.00 |
4095 |
Detailed instructions on improving your position in life by learning how to make authentic Japanese sushi in your spare time. 5-10% increase in number of friends per recipe reported from beta test. |
Jun 12, 1987 |
1 |