P2: DDL.sql

---------------------------------------------------------------------------
-- ===========================================================
-- DDL Script for deleting the tables and relationship tables
-- ===========================================================

-- * 'CASCADE' is added after each table that has foreign key, which means that when you drop the "parent" table, it will automatically drop the "child" tables that have foreign key references to it.


DROP TABLE IF EXISTS WishlistBook;
DROP TABLE IF EXISTS WishlistCustomer;
DROP TABLE IF EXISTS Wishlist;
DROP TABLE IF EXISTS FeedbackCustomer;
DROP TABLE IF EXISTS Feedback;
DROP TABLE IF EXISTS CartBook;
DROP TABLE IF EXISTS CartCustomer;
DROP TABLE IF EXISTS Cart;
DROP TABLE IF EXISTS ReviewCustomer;
DROP TABLE IF EXISTS ReviewBook;
DROP TABLE IF EXISTS Review;
DROP TABLE IF EXISTS RecommendationCustomer;
DROP TABLE IF EXISTS RecommendationBook;
DROP TABLE IF EXISTS Recommendation;
DROP TABLE IF EXISTS ShippingOrder;
DROP TABLE IF EXISTS PaymentOrder;
DROP TABLE IF EXISTS Shipping;
DROP TABLE IF EXISTS Payment;
DROP TABLE IF EXISTS OrderTable;
DROP TABLE IF EXISTS CustomerOrder;
DROP TABLE IF EXISTS Customer CASCADE;
DROP TABLE IF EXISTS Author CASCADE;
DROP TABLE IF EXISTS Genre CASCADE;
DROP TABLE IF EXISTS Publisher CASCADE;
DROP TABLE IF EXISTS BookAuthor CASCADE;
DROP TABLE IF EXISTS BookPublisher CASCADE;
DROP TABLE IF EXISTS BookGenre CASCADE;
DROP TABLE IF EXISTS Book CASCADE;




---------------------------------------------------------------------------
-- ============================
-- CREATE TABLES  (DDL Script)
-- ============================


CREATE TABLE Book (
    BookID INT PRIMARY KEY,
    Price DECIMAL(10, 2) NOT NULL,
    Title VARCHAR(255) NOT NULL,
    StockQuantity INTEGER NOT NULL,
    PublishedDate DATE NOT NULL
);

CREATE TABLE Publisher (
    PublisherID INT PRIMARY KEY,
    PublisherName VARCHAR(255) NOT NULL
);

CREATE TABLE Genre (
    GenreID INT PRIMARY KEY,
    GenreName VARCHAR(255) NOT NULL
);

CREATE TABLE Cart (
    CartID INT PRIMARY KEY,
    Quantity INTEGER NOT NULL
);

CREATE TABLE Author (
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL
);

CREATE TABLE Recommendation (
    RecommendationID INT PRIMARY KEY,
    DateRecommended DATE NOT NULL
);

CREATE TABLE Review (
    ReviewID INT PRIMARY KEY,
    Rating DECIMAL(3, 2) NOT NULL,
    Comment VARCHAR(255) NOT NULL,
    DatePosted DATE NOT NULL
);

CREATE TABLE OrderTable (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    TotalAmount DECIMAL(10, 2) NOT NULL
);

CREATE TABLE Shipping (
    ShippingID INT PRIMARY KEY,
    ShipDate DATE NOT NULL,
    TrackingNumber VARCHAR(50) NOT NULL,
    Status BOOLEAN NOT NULL
);

CREATE TABLE Payment (
    PaymentID INT PRIMARY KEY,
    PaymentDate DATE NOT NULL,
    PaymentMethod VARCHAR(255) NOT NULL,
    Amount DECIMAL(10, 2) NOT NULL
);

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Username VARCHAR(255) NOT NULL,
    Password VARCHAR(255) NOT NULL,
    Address VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    Phone VARCHAR(20) NOT NULL
);

CREATE TABLE Feedback (
    FeedbackID INT PRIMARY KEY,
    FeedbackText VARCHAR(255) NOT NULL,
    DateSubmitted DATE NOT NULL
);

CREATE TABLE Wishlist (
    WishlistID INT PRIMARY KEY
);





----------------------------------------------------------------------------
-- ===============================
-- CREATE RELATIONS  (DDL Script)
-- ===============================




CREATE TABLE BookGenre (
    BookID INT,
    GenreID INT,
    PRIMARY KEY (BookID, GenreID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (GenreID) REFERENCES Genre(GenreID)
);

CREATE TABLE BookAuthor (
    BookID INT,
    AuthorID INT,
    PRIMARY KEY (BookID, AuthorID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);

CREATE TABLE BookPublisher (
    BookID INT,
    PublisherID INT,
    PRIMARY KEY (BookID, PublisherID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)
);

CREATE TABLE CustomerOrder (
    CustomerID INT,
    OrderID INT,
    PRIMARY KEY (CustomerID, OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    FOREIGN KEY (OrderID) REFERENCES OrderTable(OrderID)
);

CREATE TABLE ReviewBook (
    ReviewID INT,
    BookID INT,
    PRIMARY KEY (ReviewID, BookID),
    FOREIGN KEY (ReviewID) REFERENCES Review(ReviewID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

CREATE TABLE ReviewCustomer (
    ReviewID INT,
    CustomerID INT,
    PRIMARY KEY (ReviewID, CustomerID),
    FOREIGN KEY (ReviewID) REFERENCES Review(ReviewID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE RecommendationBook (
    RecommendationID INT,
    BookID INT,
    PRIMARY KEY (RecommendationID, BookID),
    FOREIGN KEY (RecommendationID) REFERENCES Recommendation(RecommendationID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

CREATE TABLE RecommendationCustomer (
    RecommendationID INT,
    CustomerID INT,
    PRIMARY KEY (RecommendationID, CustomerID),
    FOREIGN KEY (RecommendationID) REFERENCES Recommendation(RecommendationID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE FeedbackCustomer (
    FeedbackID INT,
    CustomerID INT,
    PRIMARY KEY (FeedbackID, CustomerID),
    FOREIGN KEY (FeedbackID) REFERENCES Feedback(FeedbackID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE CartCustomer (
    CartID INT,
    CustomerID INT,
    PRIMARY KEY (CartID, CustomerID),
    FOREIGN KEY (CartID) REFERENCES Cart(CartID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE CartBook (
    CartID INT,
    BookID INT,
    PRIMARY KEY (CartID, BookID),
    FOREIGN KEY (CartID) REFERENCES Cart(CartID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

CREATE TABLE WishlistCustomer (
    WishlistID INT,
    CustomerID INT,
    PRIMARY KEY (WishlistID, CustomerID),
    FOREIGN KEY (WishlistID) REFERENCES Wishlist(WishlistID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE WishlistBook (
    WishlistID INT,
    BookID INT,
    PRIMARY KEY (WishlistID, BookID),
    FOREIGN KEY (WishlistID) REFERENCES Wishlist(WishlistID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

CREATE TABLE PaymentOrder (
    PaymentID INT,
    OrderID INT,
    PRIMARY KEY (PaymentID, OrderID),
    FOREIGN KEY (PaymentID) REFERENCES Payment(PaymentID),
    FOREIGN KEY (OrderID) REFERENCES OrderTable(OrderID)
);

CREATE TABLE ShippingOrder (
    ShippingID INT,
    OrderID INT,
    PRIMARY KEY (ShippingID, OrderID),
    FOREIGN KEY (ShippingID) REFERENCES Shipping(ShippingID),
    FOREIGN KEY (OrderID) REFERENCES OrderTable(OrderID)
);