* Bookbiz Database - (authors editors publishers roysched sales salesdetails titleauthors titleditors titles);



* Authors Table;



create table authors

        (au_id char(11),

        au_lname varchar(40),

        au_fname varchar(20),

        phone char(12),

        address varchar(40),

        city varchar(20),

        state char(2),

        zip char(5));



insert into authors

values('409-56-7008', 'Bennet', 'Abraham',

'415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705')

values ('213-46-8915', 'Green', 'Marjorie',

'415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618')

values('238-95-7766', 'Carson', 'Cheryl',

'415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705')

values('998-72-3567', 'Ringer', 'Albert',

'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152')

values('899-46-2035', 'Ringer', 'Anne',

'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152')

values('722-51-5454', 'DeFrance', 'Michel',

'219 547-9982', '3 Balding Pl.', 'Gary', 'IN', '46403')

values('807-91-6654', 'Panteley', 'Sylvia',

'301 946-8853', '1956 Arlington Pl.', 'Rockville', 'MD', '20853')

values('893-72-1158', 'McBadden', 'Heather',

'707 448-4982', '301 Putnam', 'Vacaville', 'CA', '95688')

values('724-08-9931', 'Stringer', 'Dirk',

'415 843-2991', '5420 Telegraph Av.', 'Oakland', 'CA', '94609')

values('274-80-9391', 'Straight', 'Dick',

'415 834-2919', '5420 College Av.', 'Oakland', 'CA', '94609')

values('756-30-7391', 'Karsen', 'Livia',

'415 534-9219', '5720 McAuley St.', 'Oakland', 'CA', '94609')

values('724-80-9391', 'MacFeather', 'Stearns',

'415 354-7128', '44 Upland Hts.', 'Oakland', 'CA', '94612')

values('427-17-2319', 'Dull', 'Ann',

'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301')

values('672-71-3249', 'Yokomoto', 'Akiko',

'415 935-4228', '3 Silver Ct.', 'Walnut Creek', 'CA', '94595')

values('267-41-2394', 'O''Leary', 'Michael',

'408 286-2428', '22 Cleveland Av. #14', 'San Jose', 'CA', '95128')

values('472-27-2349', 'Gringlesby', 'Burt',

'707 938-6445', 'PO Box 792', 'Covelo', 'CA', '95428')

values('527-72-3246', 'Greene', 'Morningstar',

'615 297-2723', '22 Graybar Rd.', 'Nashville', 'TN', '37215')

values('172-32-1176', 'White', 'Johnson',

'408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025')

values('712-45-1867', 'del Castillo', 'Innes',

'615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', '48105')

values('846-92-7186', 'Hunter', 'Sheryl',

'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301')

values('486-29-1786', 'Locksley', 'Chastity',

'415 585-4620', '18 Broadway Av.', 'San Francisco', 'CA', '94130')

values('648-92-1872', 'Blotchet-Halls', 'Reginald',

'503 745-6402', '55 Hillsdale Bl.', 'Corvallis', 'OR', '97330')

values('341-22-1782', 'Smith', 'Meander',

'913 843-0462', '10 Misisipi Dr.', 'Lawrence', 'KS', '66044')

;





* Information about Editos;



create table editors

        (ed_id char(11),

        ed_lname varchar(40),

        ed_fname varchar(20),

        ed_pos varchar(12),

        phone char(12),

        address varchar(40),

        city varchar(20),

        state char(2),

        zip char(5),

        ed_boss char(11) );



insert into editors

values ( '321-55-8906', 'DeLongue', 'Martinella', 'project',

'415 843-2222', '3000 6th St.', 'Berkeley', 'CA', '94710', '993-86-0420' )

values ( '527-72-3246', 'Greene', 'Morningstar', 'copy',

'615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN','37215', '826-11-9034' )

values ( '712-45-1867', 'del Castillo', 'Innes', 'copy',

'615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', '48105', '826-11-9034' )

values ('777-02-9831', 'Samuelson', 'Bernard', 'project',

'415 843-6990', '27 Yosemite', 'Oakland', 'CA', '94609', '993-86-0420' )

values ('777-66-9902', 'Almond', 'Alfred', 'copy',

'312 699-4177', '1010 E. Devon', 'Chicago', 'IL', '60018', '826-11-9034' )

values ('826-11-9034', 'Himmel', 'Eleanore', 'project',

'617 423-0552', '97 Bleaker', 'Boston', 'MA', '02210', '993-86-0420' )

values ('885-23-9140', 'Rutherford-Hayes', 'Hannah', 'project',

'301 468-3909', '32 Rockbill Pike', 'Rockbill', 'MD', '20852', '993-86-0420' )

values ('993-86-0420', 'McCann', 'Dennis', 'acquisition',

'301 468-3909', '32 Rockbill Pike', 'Rockbill', 'MD', '20852', null )

values ('943-88-7920', 'Kaspchek', 'Christof', 'acquisition',

'415 549-3909', '18 Severe Rd.', 'Berkeley', 'CA', '94710', null)

;





* Publishers Table;



create table publishers

        (pub_id char(4),

        pub_name varchar(40),

        address varchar(40),

        city varchar(20),

        state char(2));



insert into publishers

values('0736', 'New Age Books', '1 1st St','Boston', 'MA')

values('0877', 'Binnet & Hardley','2 2nd Ave.', 'Washington', 'DC')

values('1389', 'Algodata Infosystems', '3 3rd Dr.','Berkeley', 'CA')

;





* Lookup Table for Calculating Author Royalties;



create table roysched

        (title_id char(6),

        lorange int,

        hirange int,

        royalty dec(5,2) format=5.2);



insert into roysched

values('BU1032', 0, 5000, .10)

values('BU1032', 5001, 50000, .12)

values('PC1035', 0, 2000, .10)

values('PC1035', 2001, 4000, .12)

values('PC1035', 4001, 50000, .16)

values('BU2075', 0, 1000, .10)

values('BU2075', 1001, 5000, .12)

values('BU2075', 5001, 7000, .16)

values('BU2075', 7001, 50000, .18)

values('PS9999', 0, 50000, .10)

values('PS2091', 0, 1000, .10)

values('PS2091', 1001, 5000, .12)

values('PS2091', 5001, 50000, .14)

values('PS2106', 0, 2000, .10)

values('PS2106', 2001, 5000, .12)

values('PS2106', 5001, 50000, .14)

values('MC3021', 0, 1000, .10)

values('MC3021', 1001, 2000, .12)

values('MC3021', 2001, 6000, .14)

values('MC3021', 6001, 8000, .18)

values('MC3021', 8001, 50000, .20)

values('TC3218', 0, 2000, .10)

values('TC3218', 2001, 6000, .12)

values('TC3218', 6001, 8000, .16)

values('TC3218', 8001, 50000, .16)

values('PC8888', 0, 5000, .10)

values('PC8888', 5001, 50000, .12)

values('PS7777', 0, 5000, .10)

values('PS7777', 5001, 50000, .12)

values('PS3333', 0, 5000, .10)

values('PS3333', 5001, 50000, .12)

values('MC3026', 0, 1000, .10)

values('MC3026',1001, 2000, .12)

values('MC3026', 2001, 6000, .14)

values('MC3026', 6001, 8000, .18)

values('MC3026', 8001, 50000, .20)

values('BU1111', 0, 4000, .10)

values('BU1111', 4001, 8000, .12)

values('BU1111', 8001, 50000, .14)

values('MC2222', 0, 2000, .10)

values('MC2222', 2001, 4000, .12)

values('MC2222', 4001, 8000, .14)

values('MC2222', 8001, 12000, .16)

values('TC7777', 0, 5000, .10)

values('TC7777', 5001, 15000, .12)

values('TC4203', 0, 2000, .10)

values('TC4203', 2001, 8000, .12)

values('TC4203', 8001, 16000, .14)

values('BU7832', 0, 5000, .10)

values('BU7832', 5001, 50000, .12)

values('PS1372', 0, 50000, .10)

;





* The Sales Order Information;



create table sales

        (sonum int,

        stor_id char(4),

        ponum varchar(20),

        sdate num informat=date9. format=date9.);



insert into sales

values(1,'7066', 'QA7442.3','13Sep1998'd)

values(2,'7067', 'D4482','14Sep1998'd)

values(3,'7131', 'N914008','14Sep1998'd)

values(4,'7131', 'N914014','14Sep1998'd)

values(5,'8042', '423LL922','14Sep1998'd)

values(6,'8042', '423LL930','14Sep1998'd)

values(7, '6380', '722a','13Sep1998'd)

values(8,'6380', '6871','14Sep1998'd)

values(9,'8042','P723','11Mar2001'd)

values(19,'7896','X999','21Feb2001'd)

values(10,'7896','QQ2299','28Oct2000'd)

values(11,'7896','TQ456','12Dec2000'd)

values(12,'8042','QA879.1','22May2000'd)

values(13,'7066','A2976','24May2000'd)

values(14,'7131','P3087a','29May2000'd)

values(15,'7067','P2121','15Jun2000'd)

;





* The  Line-item Entries for Each Sales Order;



create table salesdetails

        (sonum int,

        qty_ordered smallint,

        qty_shipped smallint,

        title_id char(6),

        date_shipped num informat=date9. format=date9.);



insert into salesdetails

values(1, 75, 75,'PS2091',  '15Sep1998'd)

values(2, 10, 10,'PS2091',  '15Sep1998'd)

values(3, 20, 720,'PS2091', '18Sep1998'd)

values(4, 25, 20,'MC3021',  '18Sep1998'd)

values(5, 15, 15,'MC3021',  '14Sep1998'd)

values(6, 10, 3,'BU1032',   '22Sep1998'd)

values(7, 3, 3,'PS2091',    '20Sep1998'd)

values(8, 5, 5,'BU1032',    '14Sep1998'd)

values(9, 25, 5,'BU1111',   '28Mar2001'd)

values(19, 35, 35,'BU2075', '15Mar2001'd)

values(10, 15, 15,'BU7832', '29Oct2000'd)

values(11, 10, 10,'MC2222', '12Jan2001'd)

values(12, 30, 30,'PC1035', '24May2000'd)

values(13, 50, 50,'PC8888', '24May2000'd)

values(14, 20, 20,'PS1372', '29May2000'd)

values(14, 25, 25,'PS2106', '29Apr2000'd)

values(14, 15, 10,'PS3333', '29May2000'd)

values(14, 25, 25,'PS7777', '13Jun2000'd)

values(15, 40, 40,'TC3218', '15Jun2000'd)

values(15, 20, 20,'TC4203', '30May2000'd)

values(15, 20, 10,'TC7777', '17Jun2000'd)

;





* Link Table: the Relationship between Authors and Books;



create table titleauthors

        (au_id char(11),

        title_id char(6),

        au_ord smallint,

        royaltyshare dec(5,2) format=5.2);



insert into titleauthors

values('409-56-7008', 'BU1032', 1, .60)

values('486-29-1786', 'PS7777', 1, 1.00)

values('486-29-1786', 'PC9999', 1, 1.00)

values('712-45-1867', 'MC2222', 1, 1.00)

values('172-32-1176', 'PS3333', 1, 1.00)

values('213-46-8915', 'BU1032', 2, .40)

values('238-95-7766', 'PC1035', 1, 1.00)

values('213-46-8915', 'BU2075', 1, 1.00)

values('998-72-3567', 'PS2091', 1, .50)

values('899-46-2035', 'PS2091', 2, .50)

values('998-72-3567', 'PS2106', 1, 1.00)

values('722-51-5454', 'MC3021', 1, .75)

values('899-46-2035', 'MC3021', 2, .25)

values('807-91-6654', 'TC3218', 1, 1.00)

values('274-80-9391', 'BU7832', 1, 1.00)

values('427-17-2319', 'PC8888', 1, .50)

values('846-92-7186', 'PC8888', 2, .50)

values('756-30-7391', 'PS1372', 1, .75)

values('724-80-9391', 'PS1372', 2, .25)

values('724-80-9391', 'BU1111', 1, .60)

values('267-41-2394', 'BU1111', 2, .40)

values('672-71-3249', 'TC7777', 1, .40)

values('267-41-2394', 'TC7777', 2, .30)

values('472-27-2349', 'TC7777', 3, .30)

values('648-92-1872', 'TC4203', 1, 1.00)

;





* Track of Association between Editors and the Books;



create table titleditors

        (ed_id char(11),

        title_id char(6),

        ed_ord smallint);



insert into titleditors 

values('826-11-9034', 'BU2075', 2)

values('826-11-9034', 'PS2091', 2)

values('826-11-9034', 'PS2106', 2)

values('826-11-9034', 'PS3333', 2)

values('826-11-9034', 'PS7777', 2)

values('826-11-9034', 'PS1372', 2)

values('885-23-9140', 'MC2222', 2)

values('885-23-9140', 'MC3021', 2)

values('885-23-9140', 'TC3281', 2)

values('885-23-9140', 'TC4203', 2)

values('885-23-9140', 'TC7777', 2)

values('321-55-8906', 'BU1032', 2)

values('321-55-8906', 'BU1111', 2)

values('321-55-8906', 'BU7832', 2)

values('321-55-8906', 'PC1035', 2)

values('321-55-8906', 'PC8888', 2)

values('321-55-8906', 'BU2075', 3)

values('777-02-9831', 'PC1035', 3)

values('777-02-9831', 'PC8888', 3)

values('943-88-7920', 'BU1032', 1)

values('943-88-7920', 'BU1111', 1)

values('943-88-7920', 'BU2075', 1)

values('943-88-7920', 'BU7832', 1)

values('943-88-7920', 'PC1035', 1)

values('943-88-7920', 'PC8888', 1)

values('993-86-0420', 'PS1372', 1)

values('993-86-0420', 'PS2091', 1)

values('993-86-0420', 'PS2106', 1)

values('993-86-0420', 'PS3333', 1)

values('993-86-0420', 'PS7777', 1)

values('993-86-0420', 'MC2222', 1)

values('993-86-0420', 'MC3021', 1)

values('993-86-0420', 'TC3218', 1)

values('993-86-0420', 'TC4203', 1)

values('993-86-0420', 'TC7777', 1)

;





* Information about books;



create table titles

        (title_id char(6) ,

        title varchar(80),

        type char(12) ,

        pub_id char(4) ,

        price numeric(8,2),

        advance numeric(10,2),

        ytd_sales int,

        contract int,

        notes varchar(200) ,

        pubdate num informat=date9. format=date9.);



insert into titles

values ('PC8888', 'Secrets of Silicon Valley',

'popular_comp', '1389', 40.00, 8000.00, 4095,1,

'Muckraking reporting on the world''s largest computer hardware and software manufacturers.',

'12jun1998'd)

values ('BU1032', 'The Busy Executive''s Database Guide',

'business', '1389', 29.99, 5000.00, 4095, 1,

'An overview of available database systems with emphasis on common business applications.  Illustrated.',

'12jun1998'd)

values ('PS7777', 'Emotional Security: A New Algorithm',

'psychology', '0736', 17.99, 4000.00, 3336, 1,

'Protecting yourself and your loved ones from undue emotional stress in the modern world.  Use of computer and nutritional aids emphasized.',

'12jun1998'd)

values ('PS3333', 'Prolonged Data Deprivation: Four Case Studies',

'psychology', '0736', 29.99, 2000.00, 4072,1,

'What happens when the data runs dry?  Searching evaluations of information-shortage effects.',

'12jun1998'd)

values ('BU1111', 'Cooking with Computers: Surreptitious Balance Sheets',

'business', '1389', 21.95, 5000.00, 3876, 1,

'Helpful hints on how to use your electronic resources to the best advantage.',

'09jun1998'd)

values ('MC2222', 'Silicon Valley Gastronomic Treats',

'mod_cook', '0877', 29.99, 0.00, 2032, 1,

 'Favorite recipes for quick, easy, and elegant meals tried and tested by people who never have time to eat, let alone cook.',

'09jun1998'd)

values ('TC7777', 'Sushi, Anyone?',

'trad_cook', '0877', 29.99, 8000.00, 4095, 1,

'Detailed instructions on improving your position in life by learning how to make authentic Japanese sushi in your spare time. 5-10% increase in number of friends per recipe reported from beta test. ',

'12jun1998'd)

values ('TC4203', 'Fifty Years in Buckingham Palace Kitchens',

'trad_cook', '0877', 21.95, 4000.00, 15096, 1,

'More anecdotes from the Queen''s favorite cook describing life among English royalty.  Recipes, techniques, tender vignettes.',

'12jun1998'd)

values ('PC1035', 'But Is It User Friendly?',

'popular_comp', '1389', 42.95, 7000.00, 8780, 1,

'A survey of software for the naive user, focusing on the ''friendliness'' of each.',

'30jun1998'd)

values('BU2075', 'You Can Combat Computer Stress!',

'business', '0736', 12.99, 10125.00, 18722, 1,

'The latest medical and psychological techniques for living with the electronic office.  Easy-to-understand explanations.',

'30jun1998'd)

values('PS2091', 'Is Anger the Enemy?',

'psychology', '0736', 21.95, 2275.00, 2045, 1,

'Carefully researched study of the effects of strong emotions on the body. Metabolic charts included.',

'15jun1998'd)

values('PS2106', 'Life Without Fear',

'psychology', '0736', 17.00, 6000.00, 111, 1,

'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience.  Sample menus included, exercise video available separately.',

'05oct1998'd)

values('MC3021', 'The Gourmet Microwave',

'mod_cook', '0877', 12.99, 15000.00, 22246, 1,

'Traditional French gourmet recipes adapted for modern microwave cooking.',

'18jun1998'd)

values('TC3218','Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean',

'trad_cook', '0877', 40.95, 7000.00, 375, 1,

'Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.',

'10oct1998'd)

values('MC3026', 'The Psychology of Computer Cooking','.' , '0877', ., ., ., 0, '.', .)

values ('BU7832', 'Straight Talk About Computers',

'business', '1389', 29.99, 5000.00, 4095, 1,

'Annotated analysis of what computers can do for you: a no-hype guide for the critical user.',

'22jun1998'd)

values('PS1372',

'Computer Phobic and Non-Phobic Individuals: Behavior Variations',

'psychology', '0736', 41.59, 7000.00, 375, 1,

'A must for the specialist, this book examines the difference between those who hate and fear computers and those who think they are swell.',

'10oct1998'd)

values('PC9999', 'Net Etiquette', 'popular_comp', '1389', ., ., ., 0,

'A must-read for computer conferencing debutantes!.', .)

;