titleauthor table

the titleauthor table shows the author id, title id, and royalty of titles by percentage in titles in the pubs2 database.

titleauthor is defined as follows:

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

Its primary keys are au_id and title_id:

sp_primarykey titleauthor, au_id, title_id

Its title_id and au_id columns are foreign keys to titles and authors:

sp_foreignkey titleauthor, titles, title_id
sp_foreignkey titleauthor, authors, au_id

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 column is defined as:

create nonclustered index titleidind
on titleauthor(title_id)

The following view uses titleauthor:

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

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