/*****************************************************************************/
/* @(#) generic/localization/scripts/iso_1/%M%	%I%	%G%	*/
/* installinterpubs - script to install the international pubs database      */
/*****************************************************************************/
/*******                                                               *******/
/*******CAUTION:  Do not edit this file with an editor like /usr/ucb/vi!! ****/
/*******          It will strip off the 8th bit and you will no longer    ****/
/*******          have any of the accented characters.                    ****/
/*******                                                                  ****/
/*******          If you have already begun to edit this file, you should ****/
/*******          quit without saving (:q! in vi); otherwise, the data    ****/
/*******          will not reflect any of the international characters.   ****/
/*******                                                                  ****/
/*****************************************************************************/
set nocount on

if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	drop database interpubs
end
go
print 'Creating the "interpubs" database'
create database interpubs
go
set dateformat mdy
go
use interpubs
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	execute sp_addtype id, "varchar(11)", "not null"
	execute sp_addtype tid, "varchar(6)", "not null"
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table authors
	(au_id id,
	au_lname varchar(40) not null,
	au_fname varchar(20) not null,
	phone char(14),
	address varchar(40) null,
	city varchar(20) null,
	state char(2) null,
	country varchar(12) null,
	postalcode char(10) null)
end
go
grant select on authors to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table titleauthor
	(au_id id,
	title_id tid,
	au_ord tinyint null,
	royaltyper int null)
end
go
grant select on titleauthor to public
go
create unique clustered index taind
on titleauthor (au_id, title_id)
go
create nonclustered index auidind
on titleauthor (au_id)
go
create nonclustered index titleidind
on titleauthor (title_id)
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table sales
	(stor_id char(4),
	ord_num varchar(20),
	date datetime,
	qty smallint,
	payterms varchar(12),
	title_id tid)
end
go
grant select on sales to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table titles
	(title_id tid,
	title varchar(80) not null,
	type char(12),
	price money null,
	advance money null,
	royalty int null,
	ytd_sales int null,
	notes varchar(255) null,
	pubdate datetime)
end
go
grant select on titles to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table stores
	(stor_id char(4),
	stor_name varchar(40) null,
	stor_address varchar(40) null,
	city varchar(20) null,
	state char(2) null,
	country varchar(12) null,
	postalcode char(10) null)
end
go
grant select on stores to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table discounts
	(discounttype   varchar(40) not null,
	stor_id         char(4) null,
	lowqty          smallint null,
	highqty         smallint null,
	discount        float)
end
go
grant select on discounts to public
go
execute sp_primarykey titles, title_id
execute sp_primarykey titleauthor, au_id, title_id
execute sp_primarykey authors, au_id
execute sp_primarykey sales, stor_id, title_id, ord_num
execute sp_primarykey stores, stor_id
execute sp_primarykey discounts, discounttype, stor_id  
go
execute sp_foreignkey titleauthor, titles, title_id
execute sp_foreignkey titleauthor, authors, au_id
execute sp_foreignkey sales, titles, title_id
execute sp_foreignkey sales, stores, stor_id
execute sp_foreignkey discounts, stores, stor_id
go
create unique clustered index auidind
on authors (au_id)
go
create nonclustered index aunmind
on authors (au_lname, au_fname)
go
create unique clustered index titleidind
on titles (title_id)
go
create nonclustered index titleind
on titles (title)
go
create nonclustered index titleidind
on sales (title_id)
go
create default typedflt
as "UNDECIDED"
go
sp_bindefault typedflt, "titles.type"
go
create default datedflt
as getdate()
go
sp_bindefault datedflt, "titles.pubdate"
go
create default phonedflt
as "UNKNOWN"
go
sp_bindefault phonedflt, "authors.phone"
go
insert authors
values('409-56-7008', 'Bennet', 'Abraham',
'415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', 'USA', '94703')
go
insert authors
values ('213-46-8915', 'Green', 'Marjorie',
'415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', 'USA','94618')
go
insert authors
values('238-95-7766', 'Carson', 'Cheryl',
'415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', 'USA', '94705')
go
insert authors
values('998-72-3567', 'Ringer', 'Albert',
'801 826-0752', '67 Seventh Ave.', 'Salt Lake City', 'UT', 'USA', '84152')
go
insert authors
values('899-46-2035', 'Ringer', 'Anne',
'801 826-0752', '67 Seventh Ave.', 'Salt Lake City', 'UT', 'USA', '84152')
go
insert authors
values('020-83-5948', 'Döblin', 'Alfred',
'010 223 2455', '12, Goethestraße', 'Berlin', 'Berlin','BRD','P-1124')
go
insert authors
values('011-112-1211', 'Dürrenmatt','Friedrich',
'011 255 29 24', '24, Schönplatz','Bern','Bern','Schweiz','S-114')
go
insert authors
values('018-913-9422', 'Böll', 'Heinrich',
'011 442 23 90', '18, Einsteingaße', 'Bonn', 'Bonn', 'BRD', 'V-222')
go
insert authors 
values('080-92-2323', 'Borchert', 'Wolfgang',
'020 828 09 93', '24, Türingenstraße', 'Wien', 'Wien', 'Österreich', 'A-1253')
go
insert authors
values('011-35-8631', 'Dutschke', 'Rudi',
'399-09-8380', '811, Lindenstraße', 'München', 'München','BRD', 'B-8263')
go
insert authors
values('136-85-3857', 'Césaire', 'Aimé',
'010 244 38 95','8, Rue de la Campagne','Nice','Nice','France','N-1382')
go
insert authors
values('852-22-3431', 'Céline', 'Louis-Ferdinand',
'010 344 24 28', '9, Avenue Foch', 'Paris', 'Paris', 'France', 'X-1124')
go
insert authors
values('258-82-1314', 'Cendrars', 'Blaise',
'023 392 29 40', '12, Rue de Gaulle', 'Lyons', 'Lyons', 'France', 'Y-1388')
go
insert authors
values('010-08-1946', 'Lévi-Strauss', 'Claude',
'099 998 23 84', '10, Avenue Saint Martin', 'Paris', 'Paris', 'France', 'P-2832')
go
insert authors
values('330-29-9328', 'Prévost', 'Antoine-François',
'011 203 83 27', "82, Rue de l'Église", 'Nancy', 'Nancy', 'France', 'N-3289')
go
insert authors
values('828-39-2382', 'Ségalen', 'Victor',
'011 210 28 21', '104, Avenue Saint Michel', 'Nernier','Haute-Savoie', 'France', 'H-1383')
go
insert authors
values('831-11-9825', 'Aymé', 'Marcel',
'011 223 28 22', '112, Rue Micheline', 'Le Mans', 'Le Mans', 'France', 'A-1211')
go
insert authors
values('030-23-1432', 'Ariès', 'Philippe',
'012 822 28 02', '84, Rue Charpentier', 'Paris', 'Paris', 'France', 'P-8224')
insert sales
values('7066', 'QA7442.3', '09/13/85', 75, 'On invoice','PS2091')
go
insert sales
values('7067', 'D4482', '09/14/85', 10, 'Net 60','PS2091')
go
insert sales
values('7131', 'N914008', '09/14/85', 20, 'Net 30','PS2091')
go
insert sales
values('7131', 'N914014', '09/14/85', 25, 'Net 30','MC3021')
go
insert sales
values('8042', '423LL922', '09/14/85', 15, 'On invoice','MC3021')
go
insert sales
values('8042', '423LL930', '09/14/85', 10, 'On invoice','BU1032')
go
insert sales
values('6380', '722a', '09/13/85', 3, 'Net 60','PS2091')
go
insert sales
values('6380', '6871', '09/14/85', 5, 'Net 60','BU1032')
go
insert sales 
values('8042','P723', '03/11/88', 25, 'Net 30', 'BU1111')
go
insert sales 
values('7896','X999', '02/21/88', 35, 'On invoice', 'BU2075')
go
insert sales 
values('7896','QQ2299', '10/28/87', 15, 'Net 60', 'BU7832')
go
insert sales 
values('7896','TQ456', '12/12/87', 10, 'Net 60', 'MC2222')
go
insert sales 
values('8042','QA879.1', '5/22/87', 30, 'Net 30', 'PC1035')
go
insert sales 
values('7066','A2976', '5/24/87', 50, 'Net 30', 'PC8888')
go
insert sales 
values('7131','P3087a', '5/29/87', 20, 'Net 60', 'PS1372')
go
insert sales 
values('7131','P3087a', '5/29/87', 25, 'Net 60', 'PS2106')
go
insert sales 
values('7131','P3087a', '5/29/87', 15, 'Net 60', 'PS3333')
go
insert sales 
values('7131','P3087a', '5/29/87', 25, 'Net 60', 'PS7777')
go
insert sales 
values('7067','P2121', '6/15/87', 40, 'Net 30', 'TC3218')
go
insert sales 
values('7067','P2121', '6/15/87', 20, 'Net 30', 'TC4203')
go
insert sales 
values('7067','P2121', '6/15/87', 20, 'Net 30', 'TC7777')
go
insert sales
values('7067','P2123', '6/20/87', 20, 'Net 30', 'HI1789')
go
insert sales
values('7067','P2123', '6/20/87', 10, 'Net 30', 'HI1897')
go
insert sales
values('7067','P2123', '6/20/87', 5, 'Net 30', 'HI1983')
go
insert sales
values('7067','P2123', '6/20/87', 50, 'Net 30', 'PH8103')
go
insert sales
values('8042', '483LL821', '09/14/87', 10, 'On invoice','MY1012')
go
insert sales
values('8042', '483LL821', '09/14/87', 20, 'On invoice','MY1011')
go
insert sales
values('8042', '483LL821', '09/14/87', 20, 'On invoice','MY8332')
go
insert sales
values('8042', '483LL821', '09/14/87', 10, 'On invoice','SO1832')
go
insert sales
values('8042', '483LL821', '09/14/87', 10, 'On invoice','HI1897')
go
insert sales
values('8042', '483LL821', '09/14/87', 10, 'On invoice','DR1818')
go
insert sales
values('7067', 'R4482', '10/17/87', 10, 'Net 60','PH1024')
go
insert sales
values('7067', 'R4482', '10/17/87', 10, 'Net 60','MY1012')
go
insert sales
values('7067', 'R4482', '10/17/87', 50, 'Net 60','PO1813')
go
insert sales
values('7067', 'R4482', '10/17/87', 20, 'Net 60','PH9823')
go
insert sales
values('7067', 'R4482', '10/17/87', 20, 'Net 60','MY1011')
go
insert titleauthor
values('409-56-7008', 'BU1032', 1, 60)
go
insert titleauthor
values('213-46-8915', 'BU1032', 2, 40)
go
insert titleauthor
values('238-95-7766', 'PC1035', 1, 100)
go
insert titleauthor
values('213-46-8915', 'BU2075', 1, 100)
go
insert titleauthor
values('998-72-3567', 'PS2091', 1, 50)
go
insert titleauthor
values('899-46-2035', 'PS2091', 2, 50)
go
insert titleauthor
values('998-72-3567', 'PS2106', 1, 100)
go
insert titleauthor
values('899-46-2035', 'MC3021', 2, 25)
go
insert titleauthor
values('020-83-5948', 'MY1011', 1, 100)
go
insert titleauthor
values('011-112-1211', 'MY1012', 1, 100)
go
insert titleauthor
values('018-913-9422', 'PH1024', 1, 100)
go
insert titleauthor
values('080-92-2323', 'DR1818', 1, 100)
go
insert titleauthor
values('011-35-8631', 'PO1813', 1, 100)
go
insert titleauthor
values('136-85-3857', 'DR0223', 1, 100)
go
insert titleauthor
values('852-22-3431', 'PH9823', 1, 100)
go
insert titleauthor
values('258-82-1314', 'HI1789', 1, 100)
go
insert titleauthor
values('010-08-1946', 'PH8103', 1, 100)
go
insert titleauthor
values('330-29-9328', 'HI1897', 1, 100)
go
insert titleauthor
values('828-39-2382', 'SO1832', 1, 100)
go
insert titleauthor
values('831-11-9825', 'MY8332', 1, 100)
go
insert titleauthor
values('030-23-1432', 'HI1983', 1, 100)
go
insert titles
values ('BU1032', "The Busy Executive's Database Guide",
'business', $19.99, $5000.00, 10, 4095,
"An overview of available database systems with emphasis on common business applications.  Illustrated.",
'06/12/85')
go
insert titles
values ('PC1035', 'But Is It User Friendly?',
'popular_comp', $22.95, $7000.00, 16, 8780,
"A survey of software for the naive user, focusing on the 'friendliness' of each.",
'06/30/85')
go
insert titles
values('BU2075', 'You Can Combat Computer Stress!',
'business', $2.99, $10125.00, 24, 18722,
'The latest medical and psychological techniques for living with the electronic office.  Easy-to-understand explanations.',
'06/30/85')
go
insert titles
values('PS2091', 'Is Anger the Enemy?',
'psychology', $10.95, $2275.00, 12, 2045,
'Carefully researched study of the effects of strong emotions on the body.  Metabolic charts included.',
'06/15/85')
go
insert titles
values('PS2106', 'Life Without Fear',
'psychology', $7.00, $6000.00, 10, 111,
'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience.  Sample menus included, exercise video available separately.',
'10/05/85')
go
insert titles
values('MC3021', 'The Gourmet Microwave',
'mod_cook', $2.99, $15000.00, 24, 22246,
'Traditional French gourmet recipes adapted for modern microwave cooking.',
'06/18/85')
go
insert titles
values('MY1011', 'Berlin Alexanderplatz',
'mystery', $8.95, $10000.00, 24, 22246,
'Die Geschichte des Transportarbeiters Franz Biberkopf, der erste deutsche Großstadtroman von literarischen Rang.',
'06/18/85')
go
insert titles
values('MY1012', 'Der Auftrag',
'mystery', $8.95, $10000.00, 24, 22329,
'Eine Kriminalstory, aber eine von der metaphysischen Sorte, ein Gedankengebäude.',
'05/23/71')
go
insert titles 
values('PH1024', 'Vermintes Gelände',
'philosophy', $12.95, $10000.00, 12, 12829, 
'Dieser jüngste Band mit Schriften Heinrich Bölls aus den Jahren 1977-1981 zeigt eine deutliche Verschiebung des Interesses von Literatur zur zeitkritischen Analyse und Stellungnahme.',
'03/21/82') 
go 
insert titles  
values('DR1818', 'Draußen vor der Tur', 
'drama', $18.95, $15000.00, 14, 52829,  
'Das einzige Drama des früh verstorbenen Dichters ist ein verzweifelter Protestschrei gegen die zerstörische und verderbnisträchtige Macht des Krieges.',
'12/18/85')  
go
insert titles   
values('PO1813', 'Die Revolte',  
'politics', $8.95, $5000.00, 10, 52923,   
'Dutschke denkt, daß sich heute derjenige als Revolutionär begreifen müß, der durch intellektuelle Arbeit und sinnvolle Erfahrung zu der Erkenntnis kommt:  Diese Gesellschaft kann und soll verändert werden.',
'2/8/82')   
go 
insert titles    
values('DR0223', 'Une tempête',  
'drama', $12.95, $7500.00, 12, 51823,    
"Adaptée pour un théâtre nègre, <<La Tempête>> de Shakespeare donne un relief accru aux rapports de Prospéro et de Caliban; le maître est blanc, l'esclave est noir.",
'10/18/86')      
go  
insert titles     
values('PH9823', "D'un château l'autre",  
'philosophy', $22.95, $7500.00, 12, 81201,     
'Les châteaux dont parle le titre sont en effet douloureux, agités de spectres qui se nomment la guerre, la haine, la misère.',
'8/12/76')       
go  
insert titles
values('HI1789', "L'or",
'history', $7.95, $7500.00, 10, 8201,
'La merveilleuse histoire du général Johann August Suter.',
'5/10/70')
go    
insert titles 
values('PH8103', 'La pensée sauvage', 
'philosophy', $27.95, $17500.00, 12, 18201, 
"La pensée sauvage a trouvé la matière et l'inspiration d'une logique dont les lois se bornent à transposer les propriétés du réel, et qui, pour cette raison même, a pu permettre aux hommes d'avoir prise sur lui.",
'10/20/86') 
go     
insert titles  
values('HI1897', 'Manon Lescaut',  
'history', $12.95, $7500.00, 12, 1800,  
'Histoire du chevalier des Grieux.',
'10/12/70')  
go      
insert titles   
values('HI1983', "L'homme devant la mort",   
'history', $32.95, $15000.00, 12, 2800,   
"Ariès a contribué magistralement au renouvellement de l'historiographie française.",
'3/22/87')   
go       
insert titles    
values('SO1832', 'Les immémoriaux',    
'sociology', $22.95, $5000.00, 10, 8110,    
'Le seul ouvrage sur les îles du Pacifique.  Le récit du drame que provoque le choc de deux civilisations.',
'4/12/85')    
go        
insert titles     
values('MY8332', 'Le moulin de la Sourdine',    
'mystery', $7.95, $5000.00, 12, 8800,     
'Une crime révoltant vien de mettre en émoi la population de notre paisible cité.',
'2/2/82')     
go         
insert stores
values('7066',"Barnum's",'567 Pasadena Ave.','Tustin','CA', 'USA','92789')
go
insert stores
values('7067','News & Brews','577 First St.','Los Gatos','CA','USA','96745')
go
insert stores
values('7131','Doc-U-Mat: Quality Laundry and Books','24-A Avrogado Way','Remulade','WA','USA','98014')
go
insert stores
values('8042','Bookbeat','679 Carson St.','Portland','OR','USA','89076')
go
insert stores
values('6380',"Eric the Read Books",'788 Catamaugus Ave.','Seattle','WA','USA','98056')
go
insert stores
values('7896','Fricative Bookshop','89 Madison St.','Fremont','CA','USA','90019')
go
insert discounts
values('Initial Customer', NULL, NULL, NULL, 10.5)
go
insert discounts 
values('Volume Discount', NULL, 100, 1000, 6.7)
go
insert discounts 
values('Customer Discount', '8042', NULL, NULL, 5.0)
go
create trigger deltitle
on titles
for delete
as
if (select count(*) from deleted, sales
where sales.title_id = deleted.title_id) >0
begin
	rollback transaction
	print "You can't delete a title with sales."
end
go
create view titleview
as
select title, au_ord, au_lname,
price, ytd_sales
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
go
create procedure byroyalty @percentage int
as
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
go
grant execute on byroyalty to public
go
grant create procedure to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	execute sp_adduser guest
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	grant all on titles to guest
	grant all on authors to guest
	grant all on titleauthor to guest
	grant all on sales to guest
	grant all on stores to guest
	grant all on discounts to guest
	grant exec on byroyalty to guest
	grant create table to guest
	grant create view to guest
	grant create rule to guest
	grant create default to guest
	grant create procedure to guest
end
go
use sybsystemprocs
go
grant exec on sp_bindefault to guest
grant exec on sp_unbindefault to guest
grant exec on sp_bindrule to guest
grant exec on sp_unbindrule to guest
grant exec on sp_addtype to guest
grant exec on sp_droptype to guest
grant exec on sp_spaceused to guest
grant exec on sp_help to guest
grant exec on sp_helpgroup to guest
grant exec on sp_helpindex to guest
grant exec on sp_helprotect to guest
go