------------
-- create-bks.sql
--
-- create a bookstore database
--
-- originally from: Ann Burroughs
-- modified by: Sharon Tuttle
-- last modified: 01-28-11 - now create-bks.sql
------------

drop table return;
drop table receipts;
drop table order_needed;
drop table order_detail;
drop table order_sum;
drop table title;
drop table publisher;

create table publisher 
(pub_no	     	number(3)	NOT NULL, 
 pub_name	varchar2(30)	NOT NULL, 
 pub_city	varchar2(15)	NOT NULL, 
 pub_state	char(2)		NOT NULL, 
 order_min	number(7,2)	NOT NULL, 
 over_min_disc  number(3,2)     NOT NULL,
 CONSTRAINT publisher_pk PRIMARY KEY (pub_no), 
 CONSTRAINT publisher_check1 CHECK (over_min_disc BETWEEN .0 and .99)
);

create table title 
(isbn  	     varchar2(10)	NOT NULL, 
 pub_no	     number(3) 		NOT NULL, 
 title_name  varchar2(35)  	NOT NULL, 
 author      varchar2(30)    	NOT NULL,
 cost	     number(7,2)	NOT NULL, 
 price	     number(7,2)	NOT NULL, 
 qty_on_hand number(3)		NOT NULL, 
 order_point number(3)		NOT NULL, 
 order_qty   number(3)		NOT NULL, 
 on_order    char(1)		NOT NULL, 
 CONSTRAINT title_pk PRIMARY KEY (isbn), 
 CONSTRAINT title_fk_pub_no FOREIGN KEY (pub_no) REFERENCES publisher(pub_no),
 CONSTRAINT title_check1 CHECK (on_order in ('T', 'F'))
);

create table order_needed 
(on_key         number		NOT NULL, 
 isbn		varchar2(10)	NOT NULL, 
 order_qty	number(3) 	NOT NULL, 
 date_created	date    	NOT NULL, 
 date_placed	date 		NULL, 
 CONSTRAINT order_needed_pk PRIMARY KEY (on_key),
 CONSTRAINT order_needed_fk_isbn FOREIGN KEY (isbn) REFERENCES title(isbn)
);

create table order_sum 
(ord_no	     	number(6)	NOT NULL, 
 pub_no		number(3)	NOT NULL, 
 date_placed	date	      	NOT NULL, 
 date_complete  date    	NULL,
 CONSTRAINT order_sum_pk PRIMARY KEY (ord_no), 
 CONSTRAINT order_sum_fk_pub_no FOREIGN KEY (pub_no) REFERENCES publisher(pub_no)
);

create table order_detail
(ord_no		number(6)	NOT NULL, 
 line_no	number(5)    	NOT NULL, 
 isbn		varchar2(10)	NOT NULL, 
 order_qty	number(3)       NOT NULL, 
 qty_rcvd_todt  number(3)       NULL, 
 CONSTRAINT order_detail_pk PRIMARY KEY (ord_no, line_no),
 CONSTRAINT order_detail_fk_ord_no FOREIGN KEY (ord_no) REFERENCES order_sum (ord_no),
 CONSTRAINT order_detail_fk_isbn FOREIGN KEY (isbn) REFERENCES title(isbn)
);

create table receipts 
(rcpt_no     number(7)	NOT NULL, 
 ord_no	     number(6) 	NOT NULL, 
 line_no     number(5) 	NOT NULL, 
 qty_rcvd    number(3) 	NOT NULL,  
 date_rcvd   date	NOT NULL, 
 date_posted date	NULL,
 CONSTRAINT receipts_pk PRIMARY KEY (rcpt_no),
 CONSTRAINT receipts_fk_ord_no_line_no FOREIGN KEY (ord_no, line_no) REFERENCES order_detail (ord_no, line_no)
);

create table return
(return_id      number(6)       NOT NULL,
 pub_no         number(3)       NOT NULL,
 ord_no         number(6)       NOT NULL,
 isbn           varchar2(10)    NOT NULL,
 return_qty     number(3)       NOT NULL,
 date_returned  date            NOT NULL,
 CONSTRAINT return_pk PRIMARY KEY (return_id),
 CONSTRAINT return_fk_pub_no FOREIGN KEY (pub_no) REFERENCES publisher(pub_no),
 CONSTRAINT return_fk_ord_no FOREIGN KEY (ord_no) references order_sum(ord_no),
 CONSTRAINT return_fk_isbn FOREIGN KEY(isbn) references title(isbn)
);

commit;