CREATE TABLE  "AUTHOR" 
   (	"AUTHORID" NUMBER(5,0) NOT NULL ENABLE, 
	"LASTNAME" VARCHAR2(100) NOT NULL ENABLE, 
	"FIRSTNAME" VARCHAR2(100) NOT NULL ENABLE, 
	 CONSTRAINT "AUTHOR_PK" PRIMARY KEY ("AUTHORID") ENABLE
   )
/
CREATE TABLE  "CATEGORY" 
   (	"CATEGORYID" NUMBER(2,0) NOT NULL ENABLE, 
	"CATEGORYDESC" VARCHAR2(500) NOT NULL ENABLE, 
	 CONSTRAINT "CATEGORY_PK" PRIMARY KEY ("CATEGORYID") ENABLE
   )
/
CREATE TABLE  "SUBJECT" 
   (	"SUBJECTID" NUMBER(3,0) NOT NULL ENABLE, 
	"CATEGORYID" NUMBER(2,0) NOT NULL ENABLE, 
	"SUBJECTDESC" VARCHAR2(500) NOT NULL ENABLE, 
	"KEYWORDS" VARCHAR2(1000), 
	 CONSTRAINT "SUBJECT_PK" PRIMARY KEY ("SUBJECTID") ENABLE, 
	 CONSTRAINT "SUBJECT_FK" FOREIGN KEY ("CATEGORYID")
	  REFERENCES  "CATEGORY" ("CATEGORYID") ON DELETE CASCADE ENABLE
   )
/
CREATE TABLE  "BOOK" 
   (	"BOOKID" NUMBER(7,0) NOT NULL ENABLE, 
	"AUTHORID" NUMBER(5,0) NOT NULL ENABLE, 
	"SUBJECTID" NUMBER(3,0) NOT NULL ENABLE, 
	"TITLE" VARCHAR2(500) NOT NULL ENABLE, 
	"PRICE" NUMBER(7,2) NOT NULL ENABLE, 
	"PUBDATE" DATE NOT NULL ENABLE, 
	 CONSTRAINT "BOOK_PK" PRIMARY KEY ("BOOKID") ENABLE, 
	 CONSTRAINT "BOOK_FK1" FOREIGN KEY ("AUTHORID")
	  REFERENCES  "AUTHOR" ("AUTHORID") ENABLE,
	 CONSTRAINT "BOOK_FK2" FOREIGN KEY ("SUBJECTID")
	  REFERENCES  "SUBJECT" ("SUBJECTID") ENABLE
   )
/
CREATE TABLE  "CUSTOMER" 
   (	"CUSTOMERID" NUMBER(7,0) NOT NULL ENABLE, 
	"LASTNAME" VARCHAR2(100) NOT NULL ENABLE, 
	"FIRSTNAME" VARCHAR2(100) NOT NULL ENABLE, 
	"ADDRESS" VARCHAR2(200), 
	"PHONE" VARCHAR2(30), 
	 CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUSTOMERID") ENABLE
   )
/
CREATE TABLE  "CUSTORDER" 
   (	"ORDERID" NUMBER(7,0) NOT NULL ENABLE, 
	"CUSTOMERID" NUMBER(7,0) NOT NULL ENABLE, 
	"ORDERDATE" DATE NOT NULL ENABLE, 
	 CONSTRAINT "CUSTORDER_PK" PRIMARY KEY ("ORDERID") ENABLE, 
	 CONSTRAINT "CUSTORDER_FK" FOREIGN KEY ("CUSTOMERID")
	  REFERENCES  "CUSTOMER" ("CUSTOMERID") ENABLE
   )
/
CREATE TABLE  "ORDERDETAILS" 
   (	"ORDERID" NUMBER(7,0) NOT NULL ENABLE, 
	"BOOKID" NUMBER(7,0) NOT NULL ENABLE, 
	"QUANTITY" NUMBER(3,0) NOT NULL ENABLE, 
	"UNITPRICE" NUMBER(7,2) NOT NULL ENABLE, 
	"DISCOUNT" NUMBER(4,2), 
	 CONSTRAINT "ORDERDETAILS_FK" FOREIGN KEY ("ORDERID")
	  REFERENCES  "CUSTORDER" ("ORDERID") ON DELETE CASCADE ENABLE, 
	 CONSTRAINT "ORDERDETAILS_FK2" FOREIGN KEY ("BOOKID")
	  REFERENCES  "BOOK" ("BOOKID") ENABLE
   )
/
CREATE INDEX  "CUSTOMER_IDX1" ON  "CUSTOMER" ("LASTNAME", "FIRSTNAME")
/
CREATE INDEX  "BOOK_IDX1" ON  "BOOK" ("TITLE")
/
 CREATE SEQUENCE   "SUBJECT_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "CUSTORDER_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "CUSTOMER_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "CATEGORY_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "BOOK_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "AUTHOR_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/
CREATE OR REPLACE TRIGGER  "BI_SUBJECT" 
  before insert on "SUBJECT"               
  for each row  
begin   
    select "SUBJECT_SEQ".nextval into :NEW.SUBJECTID from dual; 
end; 

/
ALTER TRIGGER  "BI_SUBJECT" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_CUSTORDER" 
  before insert on "CUSTORDER"               
  for each row  
begin   
    select "CUSTORDER_SEQ".nextval into :NEW.ORDERID from dual; 
end; 

/
ALTER TRIGGER  "BI_CUSTORDER" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_CUSTOMER" 
  before insert on "CUSTOMER"               
  for each row  
begin   
    select "CUSTOMER_SEQ".nextval into :NEW.CUSTOMERID from dual; 
end; 

/
ALTER TRIGGER  "BI_CUSTOMER" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_CATEGORY" 
  before insert on "CATEGORY"               
  for each row  
begin   
    select "CATEGORY_SEQ".nextval into :NEW.CATEGORYID from dual; 
end; 

/
ALTER TRIGGER  "BI_CATEGORY" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_BOOK" 
  before insert on "BOOK"               
  for each row  
begin   
    select "BOOK_SEQ".nextval into :NEW.BOOKID from dual; 
end; 

/
ALTER TRIGGER  "BI_BOOK" ENABLE
/
CREATE OR REPLACE TRIGGER  "BI_AUTHOR" 
  before insert on "AUTHOR"               
  for each row  
begin   
    select "AUTHOR_SEQ".nextval into :NEW.AUTHORID from dual; 
end; 

/
ALTER TRIGGER  "BI_AUTHOR" ENABLE
/