e_commerce_script.sql

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS User;
DROP TABLE IF EXISTS Address;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Cart;
DROP TABLE IF EXISTS Cart_content;
DROP TABLE IF EXISTS Payment_method;
DROP TABLE IF EXISTS Card;
DROP TABLE IF EXISTS Command;
DROP TABLE IF EXISTS Invoices;
DROP TABLE IF EXISTS Photo;
DROP TABLE IF EXISTS Photo_type;
DROP TABLE IF EXISTS Rate;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE User(
    ID INTEGER NOT NULL AUTO_INCREMENT, 
    First_name TEXT NOT NULL,
    Last_name TEXT NOT NULL, 
    Email TEXT NOT NULL, 
    Password TEXT NOT NULL, 
    PRIMARY KEY(ID)
);

CREATE TABLE Address(
    ID INTEGER NOT NULL AUTO_INCREMENT, 
    FK_User_ID INTEGER NOT NULL,
    Address TEXT NOT NULL, 
    Extra_info TEXT, 
    Postal_code INT NOT NULL,
    City TEXT NOT NULL,
    Country TEXT NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE Product(
    ID INTEGER NOT NULL AUTO_INCREMENT,
    Name TEXT NOT NULL,
    Description TEXT NOT NULL, 
    Category TEXT NOT NULL,
    Price FLOAT NOT NULL, 
    PRIMARY KEY(ID)
);

CREATE TABLE Cart(
    ID INTEGER NOT NULL AUTO_INCREMENT,
    FK_user_id INTEGER NOT NULL,
    PRIMARY KEY(ID)
);

CREATE TABLE Cart_content(
    ID INTEGER NOT NULL AUTO_INCREMENT, 
    FK_cart_ID INTEGER NOT NULL,
    FK_product_ID INTEGER NOT NULL, 
    Quantity INTEGER NOT NULL, 
    PRIMARY KEY(ID)
);

CREATE TABLE Payment_method(
    ID INTEGER NOT NULL AUTO_INCREMENT,
    Type TEXT NOT NULL,
    PRIMARY KEY(ID)
);

CREATE TABLE Card(
    ID Integer NOT NULL AUTO_INCREMENT,
    FK_user_ID INTEGER NOT NULL,
    Card_owner TEXT NOT NULL,
    Card_number Varchar(16) NOT NULL,
    Expiration_date TEXT NOT NULL, 
    Cvc Varchar(3) NOT NULL,
    PRIMARY KEY(ID)
);

CREATE TABLE Command(
    ID INTEGER NOT NULL AUTO_INCREMENT,
    FK_cart_ID INTEGER NOT NULL, 
    FK_user_ID INTEGER NOT NULL,
    FK_address_ID INTEGER NOT NULL,
    FK_method_ID INTEGER NOT NULL,
    FK_card_ID INTEGER NOT NULL, 
    Order_date DATE NOT NULL,
    Total_price float NOT NULL,
    PRIMARY KEY(ID)
);

CREATE TABLE Invoices(
    ID INTEGER NOT NULL AUTO_INCREMENT,
    FK_user_ID INTEGER NOT NULL,
    FK_command_ID INTEGER NOT NULL,
    PRIMARY KEY(ID)
);

CREATE TABLE Photo(
    ID INTEGER NOT NULL AUTO_INCREMENT,
    FK_id_type INTEGER NOT NULL, 
    Entity_ID INTEGER NOT NULL,
    url TEXT NOT NULL,
    PRIMARY KEY(ID)
);

CREATE TABLE Photo_type(
    ID INTEGER NOT NULL AUTO_INCREMENT,
    Type TEXT NOT NULL,
    PRIMARY KEY(ID)    
);

CREATE TABLE Rate(
    ID INTEGER NOT NULL AUTO_INCREMENT,
    FK_product_ID INTEGER NOT NULL,
    FK_user_ID INTEGER NOT NULL,
    Rating FLOAT, 
    Review TEXT,
    PRIMARY KEY(ID)
);

INSERT INTO Payment_method (Type) VALUES ("product");
INSERT INTO Payment_method (Type) VALUES ("user");

INSERT INTO Photo_type (Type) VALUES ("product");
INSERT INTO Photo_type (Type) VALUES ("user");


ALTER TABLE User ENGINE=InnoDB;
ALTER TABLE Address ENGINE=InnoDB;
ALTER TABLE Product ENGINE=InnoDB;
ALTER TABLE Cart ENGINE=InnoDB;
ALTER TABLE Cart_content ENGINE=InnoDB;
ALTER TABLE Payment_method ENGINE=InnoDB;
ALTER TABLE Card ENGINE=InnoDB;
ALTER TABLE Command ENGINE=InnoDB;
ALTER TABLE Invoices ENGINE=InnoDB;
ALTER TABLE Photo ENGINE=InnoDB;
ALTER TABLE Photo_type ENGINE=InnoDB;
ALTER TABLE Rate ENGINE=InnoDB;


ALTER TABLE Address ADD FOREIGN KEY (FK_User_ID) REFERENCES User(ID) ON DELETE CASCADE;
ALTER TABLE Cart ADD FOREIGN KEY(FK_user_ID) REFERENCES User(ID) ON DELETE CASCADE;
ALTER TABLE Cart_content ADD FOREIGN KEY(FK_cart_ID) REFERENCES Cart(ID) ON DELETE CASCADE;
ALTER TABLE Cart_content ADD FOREIGN KEY(FK_product_ID) REFERENCES Product(ID) ON DELETE CASCADE;
ALTER TABLE Card ADD FOREIGN KEY(FK_user_ID) REFERENCES User(ID) ON DELETE CASCADE;
ALTER TABLE Command ADD FOREIGN KEY(FK_user_ID) REFERENCES User(ID) ON DELETE CASCADE;
ALTER TABLE Command ADD FOREIGN KEY(FK_address_ID) REFERENCES Address(ID) ON DELETE CASCADE;
ALTER TABLE Command ADD FOREIGN KEY(FK_cart_ID) REFERENCES Cart(ID) ON DELETE CASCADE;
ALTER TABLE Command ADD FOREIGN KEY(FK_method_ID) REFERENCES Payment_method(ID) ON DELETE CASCADE;
ALTER TABLE Command ADD FOREIGN KEY(FK_card_ID) REFERENCES Card(ID) ON DELETE CASCADE;
ALTER TABLE Invoices ADD FOREIGN KEY(FK_user_ID) REFERENCES User(ID) ON DELETE CASCADE;
ALTER TABLE Invoices ADD FOREIGN KEY(FK_command_ID) REFERENCES Command(ID) ON DELETE CASCADE;
ALTER TABLE Photo ADD FOREIGN KEY(FK_id_type) REFERENCES Photo_type(ID) ON DELETE CASCADE;
ALTER TABLE Rate ADD FOREIGN KEY(FK_product_id) REFERENCES Product(ID) ON DELETE CASCADE;
ALTER TABLE Rate ADD FOREIGN KEY(FK_user_ID) REFERENCES User(ID) ON DELETE CASCADE;