SQLize | PHPize | SQLtest

CREATE TABLE Customers ( customer_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, address VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'), phone VARCHAR(20) NOT NULL CHECK (phone ~ '^[0-9]+$') ); CREATE TABLE Orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ); CREATE TABLE Categories ( category_id INT PRIMARY KEY, name VARCHAR(255) not null ); CREATE TABLE Manufacturers ( manufacturer_id INT PRIMARY KEY, name VARCHAR(255), country VARCHAR(255) not null, email VARCHAR(100) NOT NULL CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'), phone VARCHAR(20) NOT NULL CHECK (phone ~ '^[0-9]+$') ); CREATE TABLE Products ( product_id INT PRIMARY KEY, name VARCHAR(255), description TEXT, price DECIMAL(10,2) not null check (price >= 0), manufacturer VARCHAR(255) not null, FOREIGN KEY (product_id) REFERENCES Manufacturers(manufacturer_id) ); CREATE TABLE Reviews ( review_id INT PRIMARY KEY, product_id INT not null, rating INT CHECK (rating >= 0), comment VARCHAR(255), FOREIGN KEY (product_id) REFERENCES Products(product_id) ); CREATE TABLE Order_Items ( order_id INT, product_id INT not null, quantity INT check (quantity >= 0), FOREIGN KEY (order_id) REFERENCES Orders(order_id), FOREIGN KEY (product_id) REFERENCES Products(product_id) ); CREATE TABLE Product_Categories ( product_id INT, category_id INT not null, FOREIGN KEY (product_id) REFERENCES Products(product_id), FOREIGN KEY (category_id) REFERENCES Categories(category_id) ); CREATE INDEX idx_customers_email ON Customers (email); -- This index can speed up queries that involve searching or filtering customers by email. CREATE INDEX idx_orders_customer_id ON Orders (customer_id); -- This index can improve the performance of queries that involve joining the Orders table with the Customers table using the customer_id. CREATE INDEX idx_products_manufacturer ON Products (manufacturer); -- This index can be beneficial for queries that involve searching or filtering products by manufacturer. CREATE INDEX idx_reviews_product_id ON Reviews (product_id); -- This index can enhance the performance of queries that involve joining the Reviews table with the Products table using the product_id. CREATE INDEX idx_order_items_order_id ON Order_Items (order_id); -- This index can speed up queries that involve joining the Order_Items table with the Orders table using the order_id. CREATE INDEX idx_order_items_product_id ON Order_Items (product_id); -- This index can improve the performance of queries that involve joining the Order_Items table with the Products table using the product_id. CREATE INDEX idx_product_categories_product_id ON Product_Categories (product_id); -- This index can be beneficial for queries that involve joining the Product_Categories table with the Products table using the product_id. CREATE INDEX idx_product_categories_category_id ON Product_Categories (category_id); -- This index can enhance the performance of queries that involve joining the Product_Categories table with the Categories table using the category_id. CREATE VIEW Customers_Public AS SELECT customer_id, name, address FROM Customers; CREATE VIEW Customers_Contact AS SELECT customer_id, name, address, regexp_replace(email, '([A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+)', '***') AS email, regexp_replace(phone, '([0-9]+)', '***') AS phone FROM Customers; CREATE VIEW Sales_Stats AS SELECT c.customer_id, c.name, oi.product_id, p.name AS product_name, SUM(oi.quantity) AS total_quantity, SUM(oi.quantity * p.price) AS total_amount FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id JOIN Order_Items oi ON o.order_id = oi.order_id JOIN Products p ON oi.product_id = p.product_id GROUP BY c.customer_id, c.name, oi.product_id, p.name; CREATE VIEW Order_Frequency AS SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name; CREATE VIEW Product_Category AS SELECT p.product_id, p.name AS product_name, pc.category_id, c.name AS category_name FROM Products p JOIN Product_Categories pc ON p.product_id = pc.product_id JOIN Categories c ON pc.category_id = c.category_id; CREATE OR REPLACE PROCEDURE AddProduct( p_product_id INT, p_name VARCHAR(255), p_description TEXT, p_price DECIMAL(10, 2), p_manufacturer VARCHAR(255) ) AS $$ BEGIN INSERT INTO Products (product_id, name, description, price, manufacturer) VALUES (p_product_id, p_name, p_description, p_price, p_manufacturer); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION GetProductCount(p_category_id INT) RETURNS INT AS $$ DECLARE product_count INT; BEGIN SELECT COUNT(*) INTO product_count FROM Product_Categories WHERE category_id = p_category_id; RETURN product_count; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION UpdateProductPrice() RETURNS TRIGGER AS $$ BEGIN IF NEW.price <> OLD.price THEN -- Perform necessary actions when the price of a product is updated -- For example, you can log the price change or update related records. -- Replace the following line with your desired actions. RAISE NOTICE 'Product price updated: Old price = %, New price = %', OLD.price, NEW.price; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER product_price_update_trigger AFTER UPDATE ON Products FOR EACH ROW EXECUTE FUNCTION UpdateProductPrice();