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