------------
-- authors.sql - a little authors database
--
-- original version from: Ann Burroughs
-- modified by: Sharon Tuttle
-- last modified: 01-30-06
------------

-- 1-30-06: added cascade constraints

drop table author cascade constraints;
drop table sales_history cascade constraints;
drop table royalty cascade constraints;
drop table titles cascade constraints;

create table author
(author_num number,
 name       varchar2(10),
 primary key(author_num));

create table titles
(title_num       number,
 book_name       varchar2(10) unique,
 royalty_percent number(5,3),
 primary key(title_num));

create table sales_history
(sales_key number,
 title_num number,
 amount    number(7,2),
 primary key(sales_key),
 foreign key(title_num) references titles);

create table royalty
(royalty_key number,
 author_num  number,
 title_num   number,
 split       number(5,3),
 primary key(royalty_key),
 foreign key(author_num) references author,
 foreign key(title_num) references titles);

insert into author values (1, 'Peabody');
insert into author values (2, 'George');
insert into author values (4, 'Catton');
insert into author values (5, 'Bookless');

insert into titles values (1, 'Book 1', 0.10);
insert into titles values (2, 'Book 2', 0.145);
insert into titles values (6, 'Book 3', 0.12);
insert into titles values (10, 'Book 4', 0.105);

insert into sales_history values (1, 1, 10.95);
insert into sales_history values (2, 1, 9.95);
insert into sales_history values (3, 2, 14.95);
insert into sales_history values (4, 1, 9.95);
insert into sales_history values (5, 1, 10.50);
insert into sales_history values (6, 6, 4.95);
insert into sales_history values (7, 6, 4.95);
insert into sales_history values (8, 6, 4.95);
insert into sales_history values (9, 6, 4.95);
insert into sales_history values (11, 6, 4.95);
insert into sales_history values (12, 6, 4.95);
insert into sales_history values (13, 6, 4.95);
insert into sales_history values (14, 6, 4.95);
insert into sales_history values (15, 6, 4.95);
insert into sales_history values (16, 6, 4.95);
insert into sales_history values (17, 10, 29.95);
insert into sales_history values (18, 10, 29.95);
insert into sales_history values (19, 10, 29.95);
insert into sales_history values (20, 10, 29.95);
insert into sales_history values (21, 10, 29.95);
insert into sales_history values (22, 10, 29.95);
insert into sales_history values (23, 10, 29.95);
insert into sales_history values (24, 10, 29.95);

insert into royalty values (1, 1, 1, .60);
insert into royalty values (2, 2, 1, .40);
insert into royalty values (3, 1, 2, 1.00);
insert into royalty values (4, 4, 10, 1.00);
insert into royalty values (5, 4, 6, .20);
insert into royalty values (6, 2, 6, .80);

commit;

-- end of authors.sql