titles table

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,
price money null,
advance money null,
total_sales int null,
notes varchar(200) null,
pubdate datetime not null,
contract bit not null)

Its primary key is title_id:

sp_primarykey titles, title_id

Its pub_id column is a foreign key to the publishers table:

sp_foreignkey titles, publishers, pub_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, total_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id

Table A-3 lists the contents of titles:

Table A-3: titles table

title- _id

title

type

pub- _id

price

ad- vance

total_- sales

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 appli- cations. 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-understand 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

TraditionalFrench 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 Psychology of 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

Muckrak-ing reporting by two courag-eous 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 Individ-uals: 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 informationshortage 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