Table_creation2.sql

-- Creating the LOGIN table
CREATE TABLE LOGINS (
    EMAIL VARCHAR(255) PRIMARY KEY,
    PASSWORD VARCHAR(255) NOT NULL
);

-- Creating the CUSTOMERS table with a starting value for CUSTOMER_ID
CREATE TABLE CUSTOMERS (
    CUSTOMER_ID INT AUTO_INCREMENT PRIMARY KEY,
    FIRST_NAME VARCHAR(255) NOT NULL,
    LAST_NAME VARCHAR(255),
    EMAIL VARCHAR(255) NOT NULL UNIQUE,
    PHONE CHAR(10) NOT NULL,
    ADDRESS1 VARCHAR(255) NOT NULL,
    CITY VARCHAR(255) NOT NULL,
    PROVINCE VARCHAR(255) NOT NULL,
    POSTAL_CODE CHAR(6) NOT NULL,
    FOREIGN KEY (EMAIL) REFERENCES LOGINS(EMAIL)
) AUTO_INCREMENT = 1000; -- Starting value set to 1000

-- Creating the PRODUCTS table with a starting value for PRODUCT_ID
CREATE TABLE PRODUCTS (
    PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
    PRODUCT_NAME VARCHAR(255) NOT NULL,
    PRODUCT_DESCRIPTION VARCHAR(255),
    PRICE DECIMAL(10, 2) NOT NULL,
    CATEGORY VARCHAR(255) NOT NULL,
    STOCK DECIMAL(10,0) NOT NULL
) AUTO_INCREMENT = 5000; -- Starting value set to 5000

-- Creating the ORDERS table with a starting value for ORDER_ID
CREATE TABLE ORDERS (
    ORDER_ID INT AUTO_INCREMENT PRIMARY KEY,
    CUSTOMER_ID INT NOT NULL,
    ORDER_DATE DATE NOT NULL,
    ORDER_VALUE DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID)
) AUTO_INCREMENT = 10000; -- Starting value set to 10000

-- Creating the SIMILAR_PRODUCTS table
CREATE TABLE SIMILAR_PRODUCTS (
    RELATION_ID INT AUTO_INCREMENT PRIMARY KEY,
    PRODUCT_ID_1 INT NOT NULL,
    PRODUCT_ID_2 INT NOT NULL,
    FOREIGN KEY (PRODUCT_ID_1) REFERENCES PRODUCTS(PRODUCT_ID),
    FOREIGN KEY (PRODUCT_ID_2) REFERENCES PRODUCTS(PRODUCT_ID),
    CHECK (PRODUCT_ID_1 < PRODUCT_ID_2)
) AUTO_INCREMENT = 1; -- Starting value set to 1

-- Creating the CARTS table with a starting value for CART_ID
CREATE TABLE CARTS (
    CART_ID INT AUTO_INCREMENT PRIMARY KEY,
    CUSTOMER_ID INT NOT NULL,
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID)
) AUTO_INCREMENT = 20000; -- Starting value set to 20000

-- Creating the ORDER_ITEMS table with a starting value for ORDER_ITEM_ID
CREATE TABLE ORDER_ITEMS (
    ORDER_ITEM_ID INT AUTO_INCREMENT PRIMARY KEY,
    ORDER_ID INT NOT NULL,
    PRODUCT_ID INT NOT NULL,
    QUANTITY INT NOT NULL,
    FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID),
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
) AUTO_INCREMENT = 30000; -- Starting value set to 30000

-- Creating the CART_ITEMS table with a starting value for CART_ITEM_ID
CREATE TABLE CART_ITEMS (
    CART_ITEM_ID INT AUTO_INCREMENT PRIMARY KEY,
    CART_ID INT NOT NULL,
    PRODUCT_ID INT NOT NULL,
    QUANTITY INT NOT NULL,
    FOREIGN KEY (CART_ID) REFERENCES CARTS(CART_ID),
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
) AUTO_INCREMENT = 40000; -- Starting value set to 40000