DBschema

DB:

CREATE DATABASE AUCTOMATIC:

CREATE TABLE BUYER(
     buyer_ID int primary key auto_increment,
     buyer_name VARCHAR(20) not null,
     buyer_email VARCHAR(20) not null unique,
     buyer_username VARCHAR(20) not null unique,
     buyer_password VARCHAR(15) not null,
     buyer_mobile VARCHAR(15) not null
     );

CREATE TABLE SELLER(
     seller_ID int primary key auto_increment,
     seller_name VARCHAR(20) not null,
     seller_email VARCHAR(20) not null unique,
     seller_username VARCHAR(20) not null unique,
     seller_password VARCHAR(15) not null,
     seller_mobile VARCHAR(15) not null
     );

    
CREATE TABLE CATEGORY(
    category_ID int primary key auto_increment,
    category_name VARCHAR(20)
    );
     
mysql> DESC BUYER; DESC SELLER; DESC CATEGORY;

Table BUYER:
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| buyer_ID       | int         | NO   | PRI | NULL    | auto_increment |
| buyer_name     | varchar(20) | NO   |     | NULL    |                |
| buyer_email    | varchar(20) | NO   | UNI | NULL    |                |
| buyer_password | varchar(15) | NO   |     | NULL    |                |
| buyer_location | varchar(15) | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
5 rows in set (0.20 sec)

Table SELLER:
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| seller_ID       | int         | NO   | PRI | NULL    | auto_increment |
| seller_name     | varchar(20) | NO   |     | NULL    |                |
| seller_email    | varchar(20) | NO   | UNI | NULL    |                |
| seller_password | varchar(15) | NO   |     | NULL    |                |
| seller_location | varchar(15) | YES  |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+

Table CATEGORY:
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| category_ID   | int         | NO   | PRI | NULL    | auto_increment |
| category_name | varchar(20) | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+



mysql> INSERT INTO CATEGORY (category_name) VALUES ("Clothing"),("Electronics"),("Grocery"),("Miscellaneous");
mysql> select * from category;
+-------------+---------------+
| category_ID | category_name |
+-------------+---------------+
|        1001 | Clothing      |
|        1002 | Electronics   |
|        1003 | Grocery       |
|        1004 | Miscellaneous |
+-------------+---------------+
by providing item_id, name, price, quantity, description,
sold_status (0 for unsold, 1 for sold)

CREATE TABLE product (
		product_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
		product_name VARCHAR(25),
		category VARCHAR(30),
		price DOUBLE,
		buyer_ID int,
		seller_ID int,
		sold_status TINYINT,
		description VARCHAR(255),
		PRIMARY KEY (product_ID)
		) AUTO_INCREMENT=100;

mysql> DESC PRODUCT;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| product_ID   | int unsigned | NO   | PRI | NULL    | auto_increment |
| product_name | varchar(25)  | YES  |     | NULL    |                |
| category     | varchar(30)  | YES  |     | NULL    |                |
| price        | double       | YES  |     | NULL    |                |
| buyer_ID     | int          | YES  |     | NULL    |                |
| seller_ID    | int          | YES  |     | NULL    |                |
| sold_status  | tinyint      | YES  |     | NULL    |                |
| description  | varchar(255) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+



CREATE TABLE order_table(
    order_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    buyer_ID int NOT NULL,
    seller_ID int NOT NULL,
    product_ID int NOT NULL,
    bid_price double NOT NULL,
    order_status tinyint
);