system IS422ABC@dbs

1. systemIS422ABC@dbs

2. Create user Create user tot identified by my_password; Grant connect, resource to tot ; Grant create table to tot ; Alter user tot quota 1M on system;

3. categories create table categories( cat_id char(5), cat_name varchar2(20) not null unique, created_date date not null, constraint cat_pk primary key (cat_id) );

4. buyer create table buyer( buyer_id char(5), buyer_name varchar2(10) not null , buyer_userid varchar2(20) not null unique , buyer_password varchar2(8) not null unique, buyer_address varchar2(20) not null, buyer_phon number(10) not null, buyer_fax number(7), buyer_dob date not null, buyer_status varchar2(9) not null, buyer_email varchar2(20) not null, constraint buyer_pk primary key (buyer_id), constraint buyer_stat check (buyer_status in ('locked ','unlocked') ) );

5. sellers create table sellers( seller_id char(5), seller_name varchar2(10) not null, seller_userid varchar2(20) not null unique, seller_password varchar2(8) not null unique, seller_address varchar2(30) not null, seller_phon number(10) not null , seller_fax number(7), seller_dob date not null, seller_status varchar2(9) not null, seller_email varchar2(20), constraint seller_pk primary key (seller_id), constraint seller_stat check (seller_status in ('locked ','unlocked') ) );

6. subscription_fee create table subscription_fee( sub_fee_id char(5), sub_fee_affectivedate date not null, sub_fee number(6,2) not null, constraint sub_fee_pk primarykey (sub_fee_id) );

7. user_type create table user_type( usertype_id char(5), usertye_name varchar2(10) not null, constraint usertype_pk primary key (usertype_id) );

8. book create table book( book_id char(5), cat_id char(5), seller_id char(5), book_name varchar2(20) not null, book_isbn char(4) unique, book_author varchar2(30) not null, book_price number(5,2) not null , shipment_cost number(5,2) not null , book_status varchar2(9) not null, book_image varchar2(200), constraint book_pk primary key (book_id), constraint book_fk1 foreign key (cat_id) references categories(cat_id) on delete cascade, constraint book_fk2 foreign key (seller_id) references sellers(seller_id) on delete cascade, constraint status check (book_status in ('available','sold ')) );

9. sales create table sales( invoice_id char(5), invoice_date date not null, buyer_id char(5), sal_tax_rate number(3) not null, ship_address varchar2(20) not null, ship_phon number(10) not null, ship_fax number(7), ship_deliver varchar2(4) not null, ship_note varchar2(20), constraint sales_pk primary key (invoice_id), constraint sales_fk foreign key (buyer_id) references buyer (buyer_id) on delete cascade , constraint deliver check (ship_deliver in ('yes ','no') ));

10. sales_details create table sales_details( invoice_id char(7), book_id char(5), price number(5,2) not null, discount number(3), shipment_cost number(5,2) not null, constraint sales_details_fk1 foreign key (invoice_id) references sales(invoice_id) on delete cascade , constraint sales_details_fk2 foreign key (book_id) references book(book_id) on delete cascade) ;

11. seller_subscription create table seller_subscription( sub_id char(5), seller_id char(5), constraint seller_sub_pk primary key (sub_id), constraint seller_sub_fk foreign key (seller_id) references seller(seller_id) on delete cascade );

12. subscription_detail create table subscription_detail( sub_id char(5), sub_date date not null, sub_amount number(6,2) not null, sub_valid_upto date not null, constraint sub_detail_fk foreign key (sub_id) references seller_subscription(sub_id) ondelete cascade );

13. users create table users( user_id char(5), user_userid varchar2(10) not null unique, user_password varchar2(8) not null unique, usertype_id char(5), users_status varchar2(9) not null, constraint user_pk primary key (user_id), constraint user_fk foreign key (usertype_id) references user_type(usertype_id ) on delete cascade, constraint users_status check (users_status in ('locked ','unlocked') ));