Appendix A: The pubs2 Database

titles table

The titles table contains the title id, title, type, publisher id, price, and other information on titles in the pubs2 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,
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

View this book as PDF