Appendix B: The pubs3 Database

titles table

The titles table contains the name, title id, type, and other information about titles in the pubs3 database.

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

View this book as PDF