Liren Large Software Subsidy 5/05.iso/a/a063/4.img/SCRIPTS/INSTIPUB.SQL

Text File | 1992-05-17 | 18.4 KB | 624 lines

/* $Header:   S:/DBMS/OS2/SCRIPTS/PVCS/INSTIPUB.SQV   5.1   09 Mar 1992 18:20:16   YUMEI  $ */
/********************************************************************************/
/* installinterpubs    28.1.1.1    12/20/90 */
/* installinterpubs - script to install the international pubs database    1/3/90  */
/********************************************************************************/
/*******                                                                  *******/
/*******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
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', 'Dvblin', '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, Schvnplatz','Bern','Bern','Schweiz','S-114')
go
insert authors
values('018-913-9422', 'Bvll', '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', 'Vsterreich', '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', 'Cisaire', 'Aimi',
'010 244 38 95','8, Rue de la Campagne','Nice','Nice','France','N-1382')
go
insert authors
values('852-22-3431', 'Ciline', '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', 'Livi-Strauss', 'Claude',
'099 998 23 84', '10, Avenue Saint Martin', 'Paris', 'Paris', 'France', 'P-2832')
go
insert authors
values('330-29-9328', 'Privost', 'Antoine-Frangois',
'011 203 83 27', "82, Rue de l'Iglise", 'Nancy', 'Nancy', 'France', 'N-3289')
go
insert authors
values('828-39-2382', 'Sigalen', 'Victor',
'011 210 28 21', '104, Avenue Saint Michel', 'Nernier','Haute-Savoie', 'France', 'H-1383')
go
insert authors
values('831-11-9825', 'Aymi', 'Marcel',
'011 223 28 22', '112, Rue Micheline', 'Le Mans', 'Le Mans', 'France', 'A-1211')
go
insert authors
values('030-23-1432', 'Arihs', '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 Gedankengebdude.',
'05/23/71')
go
insert titles
values('PH1024', 'Vermintes Geldnde',
'philosophy', $12.95, $10000.00, 12, 12829,
'Dieser j|ngste Band mit Schriften Heinrich Bvlls 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 zerstvrische und verderbnistrdchtige 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 Revolutiondr begreifen m|_, der durch intellektuelle Arbeit und sinnvolle Erfahrung zu der Erkenntnis kommt:  Diese Gesellschaft kann und soll verdndert werden.',
'2/8/82')
go
insert titles
values('DR0223', 'Une tempjte',
'drama', $12.95, $7500.00, 12, 51823,
"Adaptie pour un thibtre nhgre, +La Tempjte; de Shakespeare donne un relief accru aux rapports de Prospiro et de Caliban; le mantre est blanc, l'esclave est noir.",
'10/18/86')
go
insert titles
values('PH9823', "D'un chbteau l'autre",
'philosophy', $22.95, $7500.00, 12, 81201,
'Les chbteaux dont parle le titre sont en effet douloureux, agitis de spectres qui se nomment la guerre, la haine, la mishre.',
'8/12/76')
go
insert titles
values('HI1789', "L'or",
'history', $7.95, $7500.00, 10, 8201,
'La merveilleuse histoire du giniral Johann August Suter.',
'5/10/70')
go
insert titles
values('PH8103', 'La pensie sauvage',
'philosophy', $27.95, $17500.00, 12, 18201,
"La pensie sauvage a trouvi la matihre et l'inspiration d'une logique dont les lois se bornent ` transposer les propriitis du riel, et qui, pour cette raison mjme, 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,
"Arihs a contribui magistralement au renouvellement de l'historiographie frangaise.",
'3/22/87')
go
insert titles
values('SO1832', 'Les immimoriaux',
'sociology', $22.95, $5000.00, 10, 8110,
'Le seul ouvrage sur les nles du Pacifique.  Le ricit 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 rivoltant vien de mettre en imoi la population de notre paisible citi.',
'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 master
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