-- create tables create table ch18_user ( id number generated by default on null as identity constraint ch18_user_id_pk primary key, user_type varchar2(15 char) constraint ch18_user_user_type_ck check (user_type in ('MEMBER','ADMINISTRATOR')), firstname varchar2(30 char), lastname varchar2(30 char), address varchar2(50 char), email varchar2(80 char), phone varchar2(20 char), date_of_birth date, intests varchar2(256 char), user_status varchar2(10 char) constraint ch18_user_user_status_ck check (user_status in ('ACTIVE','SUSPENDED','INACTIVE')), username varchar2(50 char), user_passwd varchar2(50 char) ) ; create table ch18_type_of_plant ( id number generated by default on null as identity constraint ch18_type_of_plant_id_pk primary key, name_of_type_of_plant varchar2(30 char), ch18_is_subtype_of number constraint ch18_type_of_ch18_is_subty_fk references ch18_type_of_plant on delete cascade ) ; -- table index create index ch18_type_of_plant_i1 on ch18_type_of_plant (ch18_is_subtype_of); create table ch18_plants ( id number generated by default on null as identity constraint ch18_plants_id_pk primary key, ch18_plant_type_id number constraint ch18_plants_ch18_plant_type_fk references ch18_type_of_plant on delete cascade, names_local varchar2(100 char), names_english varchar2(100 char), name_latin varchar2(40 char), description varchar2(200 char), habitat varchar2(50 char), cultivation varchar2(100 char), use varchar2(100 char), photo blob, hyperlinks varchar2(100 char), status varchar2(20 char) ) ; -- table index create index ch18_plants_i1 on ch18_plants (ch18_plant_type_id); create table ch18_offer ( id number generated by default on null as identity constraint ch18_offer_id_pk primary key, ch18_offer_plant_id number constraint ch18_offer_ch18_offer_plant_fk references ch18_plants on delete cascade, ch18_offer_user_id number constraint ch18_offer_ch18_offer_user_fk references ch18_user on delete cascade, bid_or_ask varchar2(100 char), quantity number, location varchar2(100 char), bidder varchar2(50 char), comments varchar2(200 char), offer_date date, valid_to varchar2(100 char) ) ; -- table index create index ch18_offer_i1 on ch18_offer (ch18_offer_plant_id); create index ch18_offer_i132 on ch18_offer (ch18_offer_user_id); create table status_vc50 ( id number generated by default on null as identity constraint status_vc50_id_pk primary key, ch18_offer_id number constraint status_vc50_ch18_offer_id_fk references ch18_offer on delete cascade ) ; -- table index create index status_vc50_i1 on status_vc50 (ch18_offer_id); create table ch18_message ( id number generated by default on null as identity constraint ch18_message_id_pk primary key, ch18_message_user_id1 number constraint ch18_message_ch18_message_u_fk references ch18_user on delete cascade, ch18_message_user_id2 number constraint ch18_message_ch18_message_fk19 references ch18_user on delete cascade, ch18_message_offer_id number constraint ch18_message_ch18_message_o_fk references ch18_offer on delete cascade, visibility varchar2(100 char), message_date date, content varchar2(200 char), status varchar2(50 char) ) ; -- table index create index ch18_message_i1 on ch18_message (ch18_message_offer_id); create index ch18_message_i222 on ch18_message (ch18_message_user_id1); create index ch18_message_i233 on ch18_message (ch18_message_user_id2);