Sample script for above diagram for auction house software
this is shared script samples some need modification to run!
tables is 100% accurate to what you need
— tables
— Table: bid_coin_bag
CREATETABLE bid_coin_bag (
id number NOTNULL,
bag_name varchar2(50) NOTNULL,
bid_coins number NOTNULL,
bonus_coins number NULL,
cost_in_currency number NOTNULL,
CONSTRAINT bid_coin_bag_pk PRIMARY KEY (id)
);
— Table: bid_coin_transaction_log
CREATETABLE bid_coin_transaction_log (
id number NOTNULL,
bidder_id number NOTNULL,
bid_coin_bag_id number NULL,
bid_order_bidding_log_id number NULL,
transaction_type char(1) NOTNULL,
transaction_date date NOTNULL,
coins_count number NOTNULL,
CONSTRAINT bid_coin_transaction_log_pk PRIMARY KEY (id)
);
— Table: bid_order
CREATETABLE bid_order (
id number NOTNULL,
product_id number NOTNULL,
bid_start_time timestamp NOTNULL,
bid_end_time timestamp NOTNULL,
bid_chair_cost_in_bid_coin number NOTNULL,
number_of_chairs_allowed number NOTNULL,
base_price_in_currency number NOTNULL,
bidding_cost_in_bid_coin number NOTNULL,
increment_in_price_per_bid number NOTNULL,
increment_in_time_per_bid number NULL,
CONSTRAINT bid_order_pk PRIMARY KEY (id)
);
— Table: bid_order_bidding_log
CREATETABLE bid_order_bidding_log (
id number NOTNULL,
bidder_bid_registration_id number NOTNULL,
bid_timestamp timestamp NOTNULL,
CONSTRAINT bid_order_bidding_log_pk PRIMARY KEY (id)
);
— Table: bidder
CREATETABLE bidder (
id number NOTNULL,
first_name varchar2(50) NOTNULL,
last_name varchar2(50) NULL,
date_of_birth date NOTNULL,
email varchar2(255) NOTNULL,
user_name varchar2(20) NOTNULL,
pwd_enc varchar2(100) NOTNULL,
joining_date date NOTNULL,
current_bid_coins number NOTNULL,
CONSTRAINT bidder_pk PRIMARY KEY (id)
);
— Table: bidder_bid_registration
CREATETABLE bidder_bid_registration (
id number NOTNULL,
bidder_id number NOTNULL,
bid_order_id number NOTNULL,
registration_date date NOTNULL,
is_active char(1) NOTNULL,
CONSTRAINT bidder_bid_registration_pk PRIMARY KEY (id)
);
— Table: product
CREATETABLE product (
id number NOTNULL,
product_name varchar2(100) NOTNULL,
product_category_id number NOTNULL,
product_specification varchar2(4000) NOTNULL,
actual_cost_in_currency number NOTNULL,
CONSTRAINT product_pk PRIMARY KEY (id)
);
— Table: product_category
CREATETABLE product_category (
id number NOTNULL,
product_category varchar2(50) NOTNULL,
CONSTRAINT product_category_pk PRIMARY KEY (id)
);
— foreign keys
— Reference: bid_coin_trx_log_bid_coin_bag (table: bid_coin_transaction_log)
ALTERTABLE bid_coin_transaction_log ADDCONSTRAINT bid_coin_trx_log_bid_coin_bag
FOREIGN KEY (bid_coin_bag_id)
REFERENCES bid_coin_bag (id)
NOT DEFERRABLE
INITIALLY IMMEDIATE
;
— Reference: bid_coin_trx_log_bob_log (table: bid_coin_transaction_log)
ALTERTABLE bid_coin_transaction_log ADDCONSTRAINT bid_coin_trx_log_bob_log
FOREIGN KEY (bid_order_bidding_log_id)
REFERENCES bid_order_bidding_log (id)
NOT DEFERRABLE
INITIALLY IMMEDIATE
;
— Reference: bid_registration_product (table: bid_order)
ALTERTABLE bid_order ADDCONSTRAINT bid_registration_product
FOREIGN KEY (product_id)
REFERENCES product (id)
NOT DEFERRABLE
INITIALLY IMMEDIATE
;
— Reference: bidder_bid_registration_bidder (table: bidder_bid_registration)
ALTERTABLE bidder_bid_registration ADDCONSTRAINT bidder_bid_registration_bidder
FOREIGN KEY (bidder_id)
REFERENCES bidder (id)
NOT DEFERRABLE
INITIALLY IMMEDIATE
;
— Reference: bidder_bid_rgs_bid_rgs (table: bidder_bid_registration)
ALTERTABLE bidder_bid_registration ADDCONSTRAINT bidder_bid_rgs_bid_rgs
FOREIGN KEY (bid_order_id)
REFERENCES bid_order (id)
NOT DEFERRABLE
INITIALLY IMMEDIATE
;
— Reference: bob_log_bidder_bid_rgs (table: bid_order_bidding_log)
ALTERTABLE bid_order_bidding_log ADDCONSTRAINT bob_log_bidder_bid_rgs
FOREIGN KEY (bidder_bid_registration_id)
REFERENCES bidder_bid_registration (id)
NOT DEFERRABLE
INITIALLY IMMEDIATE
;
— Reference: product_product_category (table: product)
ALTERTABLE product ADDCONSTRAINT product_product_category
FOREIGN KEY (product_category_id)
REFERENCES product_category (id)
NOT DEFERRABLE
INITIALLY IMMEDIATE
;
— Reference: user_bid_credit_log_bidder (table: bid_coin_transaction_log)
ALTERTABLE bid_coin_transaction_log ADDCONSTRAINT user_bid_credit_log_bidder
FOREIGN KEY (bidder_id)
REFERENCES bidder (id)
NOT DEFERRABLE
INITIALLY IMMEDIATE
;
— End of file.
