titles Table

The titles table contains the title id, title, type, publisher ID, price, and other information about titles.

titles is defined as:

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 uses this 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 this 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