create table tblAuthor(A_id varchar2(4) primary key, aut_name varchar2(25) not null, city varchar2(20) not null, state varchar2(20) not null, subject varchar2(20), phone number(7) ,email varchar2(40)); 
   
create table tblPublisher(p_id varchar2(4) primary key, p_name varchar2(25) not null, city varchar2(20) not null, state varchar2(20) not null, phone number(7) ,email varchar2(40));    

create table tblcustomer(c_id varchar2(4) primary key, c_name varchar2(25) not null, city varchar2(20) not null, state varchar2(20) not null, phone number(7) ,email varchar2(40));    
 
  

create table tblBooks (b_id varchar2(4) primary key, title varchar2(25) not null, 
			type varchar2(20), edition varchar2(20) not null,
 			print varchar2(20) not null, copies number(5),
 			cost number(6,3) not null, p_Id varchar2(4) references	tblpublisher(p_id), date_of_pub date);   
 
create table tblBook_Auth (b_id varchar2(4) references tblbooks(b_id),
			   a_id varchar2(4) references tblauthor(a_id));

create table tblSales_Master(s_id varchar2(4) primary key,sales_date date, 
			     c_id varchar2(4) references tblcustomer(c_id) on delete cascade,
			     tot_cost number(8,3) not null);	

create table tblSales_Detail(s_id varchar2(4) references tblSales_master(s_id) on delete cascade,
			     b_id varchar2(4) references tblbooks(b_id),quantity number(5) not null,price number(8,3) not null);	

create table tblStock_Master(st_id varchar2(4) primary key,st_date date, 
			     p_id varchar2(4) references tblpublisher(p_id),
			     tot_cost number(8,3) not null);	

create table tblStock_Detail(st_id varchar2(4) references tblStock_master(st_id) ,
			     b_id varchar2(4) references tblbooks(b_id),quantity number(5) not null,rate number(8,3) not null);	

alter table tblAuthor drop primary key cascade;

alter table tblAuthor add constraint pas primary key(A_ID);

alter table tblbook_auth add constraint fk_c foreign key(a_ID) references tblAuthor(A_ID) on delete cascade;

alter table tblPublisher  drop primary key cascade;

alter table tblPublisher  add constraint p1  primary key(P_ID);

alter table tblBooks add constraint fk1 foreign key(p_ID)  references tblPublisher(P_ID) on de
ete cascade;

alter table tblBooks drop primary key cascade;

alter table tblBooks add constraint p2 primary key(B_ID);

alter table tblbook_auth add constraint fk2 foreign key(B_ID) references  tblBooks(B_ID) on de
ete cascade;

alter table tblStock_master drop primary key cascade;

 alter table tblbook_auth add constraint b unique(b_id,a_id);
alter table tblStock_master add constraint p3 primary key(st_id);

alter table tblStock_detail add constraint fk3 foreign key(st_id)  references  tblStock_maste
(st_id) on delete cascade;

alter table tblStock_master add constraint fk4  foreign key(p_ID)  references tblPublisher(P_Id) on delete cascade;



alter table tblBooks add constraint poscons3 check(copies >= 0);
alter table tblAuthor add constraint poscons check(phone >= 0);
alter table tblcustomer add constraint  poscons1 check(phone >= 0);
alter table tblPublisher  add constraint  poscons2  check(phone >= 0);

create table tblpassword(usrname varchar2(8), pwrd varchar2(8));

insert into tblpassword values('bms', 'cse');
