titleauthor table

The titleauthor table contains the title and author ids, royalty percentages, and other information about titles and authors in the pubs3 database.

titleauthor is defined as follows:

create table titleauthor
(au_id id not null
    references authors(au_id),
title_id tid not null
    references titles(title_id),
au_ord tinyint null,
royaltyper int null)

Its nonclustered index for the au_id column is defined as:

create nonclustered index auidind
on titleauthor(au_id)

Its nonclustered index for the title_id

create nonclustered index titleidind
on titleauthor(title_id)

The following view uses titleauthor:

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

The following procedure uses titleauthor:

create procedure byroyalty @percentage int
as
select au_id from titleauthor
where titleauthor.royaltyper = @percentage

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF