create database Shop
CREATE TABLE category_master
(
cate_id Int,
cat_type Varchar(50),
PRIMARY KEY(cate_id)
);
CREATE TABLE checkout_master
(
checkout_id Int,
checkout_cust_id Int,
checkout_date Date,
checkout_amount Int,
checkout_payment_success Int,
checout_trnsaction_id Varchar(50),
PRIMARY KEY(checkout_id),
FOREIGN KEY(checkout_cust_id) REFERENCES Categories(checkout_master)
);
CREATE TABLE checkout_transcation
(
ct_id Int,
ct_checkout_id Int,
ct_product_id Date,
ct_quantity Int,
ct_rate Int
PRIMARY KEY(ct_id),
FOREIGN KEY (checkout_id) REFERENCES (checkout_master) ,
FOREIGN KEY (prod_id) REFERENCES (product_master)
);
CREATE TABLE customer_master
(
cust_id Int,
cust_name Int,
cust_user_name Varchar(50),
cust_password Varchar(50),
cust_email_id Varchar(50),
cust_last_login_date1 Date,
cust_last_login_date2 Date,
cust_pass_chg_date Date,
PRIMARY KEY(cust_id),
);
CREATE TABLE member_shipping
(
mb_id Int,
mb_cust_id Int,
mb_address_1 Varchar(150),
mb_address_2 Varchar(150),
mb_city Varchar(50),
mb_state Varchar(50),
mb_postal_code Varchar(6),
mb_contact_no Varchar(50),
PRIMARY KEY(mb_id),
FOREIGN KEY(cust_id) REFERENCES Categories(customer_master)
);
CREATE TABLE product_master
(
prod_id Int,
prod_cat_id Int,
prod_name Varchar(50),
prod_description Varchar(150),
prod_price Int,
prod_tax Int,
prod_create_by Int,
prod_create_date Date,
PRIMARY KEY(prod_id),
FOREIGN KEY (cat_id) REFERENCES (category_master),
FOREIGN KEY (user_id) REFERENCES (user_master)
);
CREATE TABLE shipping_master
(
ship_id Int,
ship_vendor_id Int,
ship_checkout_id Int,
ship_cust_id Int,
ship_address1 Varchar(100),
ship_address2 Varchar(100),
ship_city Varchar(100),
ship_state Varchar(100),
ship_postal_code Varchar(6),
ship_contct_no Varchar(50),
PRIMARY KEY(ship_id),
FOREIGN KEY (vendor_id) REFERENCES (vendor_master)
FOREIGN KEY (check_id) REFERENCES (checkout_master)
FOREIGN KEY (cust_id) REFERENCES (customer_master)
FOREIGN KEY (user_id) REFERENCES (user_master)
);
CREATE TABLE shipping_trnsaction
(
st_id Int,
st_ship_id Int,
st_prod_id Int,
st_quantity Int,
PRIMARY KEY(st_id),
FOREIGN KEY (ship_id) REFERENCES (shipping_master),
FOREIGN KEY (prod_id) REFERENCES (product_master)
);
CREATE TABLE shopping_cart_master
(
sc_id Int,
sc_cust_id Int,
sc_date date,
sc_check_out Int,
PRIMARY KEY(sc_id),
FOREIGN KEY (cust_id) REFERENCES (customer_master)
);
CREATE TABLE shopping_cart_transcation
(
sct_id Int,
sct_sc_id Int,
sct_product_id Int,
sct_quatity Int,
PRIMARY KEY(sct_id),
FOREIGN KEY (sc_id), REFERENCES (shopping_cart_master),
FOREIGN KEY (prod_id) REFERENCES (product_master)
);
CREATE TABLE user_master
(
user_id Int,
user_name Varchar(50),
user_password Varchar(50),
user_type Varchar(50),
PRIMARY KEY(user_id)
);
CREATE TABLE vendor_master
(
vendor_id Int,
vendor_name Varchar(50),
vendor_address_1 Varchar(100),
vendor_address_2 Varchar(100),
vendor_city Varchar(50),
vendor_state Varchar(50),
vendor_postal_code Varchar(6),
vendor_contact_no Varchar(50),
vendor_email Varchar(50),
vendor_create_by int,
vendor_create_date Date,
PRIMARY KEY(vendor_id),
FOREIGN KEY (user_id) REFERENCES (user_master)
);