E-commerce Processing System — Course
Project (MySQL + PHP + HTML/CSS/JS)
This scaffold contains a complete project outline, database schema, SQL
setup, PHP back-end snippets, front-end templates, transaction examples,
stored procedures, triggers, testing checklist and deployment notes tailored
to a DBMS course project.
Project Overview
Build a small but complete e-commerce processing system demonstrating
DBMS concepts: normalized schema, transactions, constraints, indexes,
stored procedures, triggers, views, backup/restore, and sample queries for
evaluation. Users can browse products, add to cart, checkout (order
processing), manage profile, and view order history. Admin can add/update
products and view orders.
Technologies: MySQL 8+, PHP 8+, HTML5, CSS3, JavaScript (vanilla), Apache
(XAMPP/LAMP)
Files included (suggested project structure)
/ecs_project/
├─ sql/ # SQL scripts
│ ├─ [Link]
│ ├─ sample_data.sql
│ └─ stored_procs_triggers.sql
├─ public/ # web root
│ ├─ [Link]
│ ├─ [Link]
│ ├─ [Link]
│ ├─ [Link]
│ ├─ order_success.php
│ ├─ [Link]
│ ├─ [Link]
│ ├─ admin/ # admin pages
│ │ ├─ [Link]
│ │ └─ [Link]
│ └─ assets/
│ ├─ css/[Link]
│ └─ js/[Link]
├─ src/
│ ├─ [Link] # DB connection + common helpers
│ ├─ [Link]
│ └─ [Link] # simplified REST-like endpoints
└─ [Link]
ER Diagram (textual)
Entities: User, Product, Category, CartItem, Order, OrderItem, Payment,
Address
Relationships: - User 1—* Address - User 1—* Order - User 1—* CartItem -
Category 1—* Product - Product 1—* OrderItem - Order 1—* OrderItem -
Order 1—1 Payment
Notes: CartItem is transient (can be stored server-side in DB for persistence).
[Link] (core DDL)
-- Create database
CREATE DATABASE IF NOT EXISTS ecs_db CHARACTER SET utf8mb4 COLLATE
utf8mb4_unicode_ci;
USE ecs_db;
-- Users
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
role ENUM('customer','admin') NOT NULL DEFAULT 'customer',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Addresses
CREATE TABLE addresses (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
line1 VARCHAR(255) NOT NULL,
line2 VARCHAR(255),
city VARCHAR(100) NOT NULL,
state VARCHAR(100),
postal_code VARCHAR(30),
country VARCHAR(100) DEFAULT 'India',
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Categories
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
-- Products
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
image_url VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET
NULL
);
-- Cart items (server-side persistent cart)
CREATE TABLE cart_items (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
qty INT NOT NULL CHECK (qty > 0),
added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY user_product_unique (user_id, product_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);
-- Orders
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
address_id INT,
total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),
status ENUM('pending','paid','shipped','completed','cancelled') NOT
NULL DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (address_id) REFERENCES addresses(id) ON DELETE SET NULL
);
-- Order items
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
qty INT NOT NULL CHECK (qty > 0),
unit_price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);
-- Payments
CREATE TABLE payments (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
amount DECIMAL(12,2) NOT NULL,
method ENUM('cod','card','upi') DEFAULT 'cod',
status ENUM('initiated','successful','failed') DEFAULT 'initiated',
transaction_ref VARCHAR(255),
processed_at TIMESTAMP NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
-- Indexes for common queries
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_user ON orders(user_id);
sample_data.sql (small dataset)
USE ecs_db;
INSERT INTO categories (name) VALUES ('Books'),('Electronics'),
('Clothing');
INSERT INTO users (name,email,password_hash,role) VALUES
('Alice','alice@[Link]',SHA2('alicepass',256),'customer'),
('Admin','admin@[Link]',SHA2('adminpass',256),'admin');
INSERT INTO products
(category_id,name,description,price,stock,image_url) VALUES
(1,'Programming in C','Intro to C programming',399.00,10,NULL),
(2,'Wireless Mouse','2.4GHz wireless mouse',599.00,25,NULL),
(3,'Plain T-Shirt','Cotton t-shirt',249.00,50,NULL);
Stored procedures & triggers (stored_procs_triggers.sql)
USE ecs_db;
-- Stored procedure: attempt to place an order inside a transaction
DELIMITER $$
CREATE PROCEDURE place_order(IN p_user_id INT, IN p_address_id INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_prod_id INT;
DECLARE v_qty INT;
DECLARE v_price DECIMAL(10,2);
DECLARE v_total DECIMAL(12,2) DEFAULT 0.0;
-- Start transaction
START TRANSACTION;
-- create order row with total 0 for now
INSERT INTO orders (user_id, address_id, total_amount) VALUES
(p_user_id, p_address_id, 0.0);
SET @order_id = LAST_INSERT_ID();
-- cursor through cart items
DECLARE cur CURSOR FOR SELECT product_id, qty FROM cart_items WHERE
user_id = p_user_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_prod_id, v_qty;
IF done = 1 THEN
LEAVE read_loop;
END IF;
SELECT price INTO v_price FROM products WHERE id = v_prod_id FOR
UPDATE;
-- check stock
IF (SELECT stock FROM products WHERE id = v_prod_id) < v_qty THEN
-- insufficient stock; rollback and return
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock
for product';
END IF;
-- reduce stock
UPDATE products SET stock = stock - v_qty WHERE id = v_prod_id;
SET v_total = v_total + v_price * v_qty;
-- insert order item
INSERT INTO order_items (order_id, product_id, qty, unit_price)
VALUES (@order_id, v_prod_id, v_qty, v_price);
END LOOP;
CLOSE cur;
-- update order total
UPDATE orders SET total_amount = v_total WHERE id = @order_id;
-- clear cart
DELETE FROM cart_items WHERE user_id = p_user_id;
COMMIT;
END$$
DELIMITER ;
-- Trigger example: log stock changes
CREATE TABLE stock_audit (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
old_stock INT,
new_stock INT,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER $$
CREATE TRIGGER trg_products_stock_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
IF [Link] != [Link] THEN
INSERT INTO stock_audit (product_id, old_stock, new_stock) VALUES
([Link], [Link], [Link]);
END IF;
END$$
DELIMITER ;
PHP: [Link] (DB connection + helper)
<?php
// src/[Link]
$DB_HOST = '[Link]';
$DB_NAME = 'ecs_db';
$DB_USER = 'root';
$DB_PASS = '';
try {
$pdo = new
PDO("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=utf8mb4", $DB_USER,
$DB_PASS, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
} catch (PDOException $e) {
die('DB Connection failed: ' . $e->getMessage());
}
function require_login() {
session_start();
if (!isset($_SESSION['user_id'])) {
header('Location: /[Link]');
exit;
}
}
?>
PHP: simple checkout flow ([Link])
This file shows server-side handling using a transaction (without using the
stored procedure) to illustrate DBMS transaction handling in app code.
<?php
// public/[Link]
require_once __DIR__ . '/../src/[Link]';
session_start();
if (!isset($_SESSION['user_id'])) { header('Location: /[Link]');
exit; }
$user_id = $_SESSION['user_id'];
$address_id = $_POST['address_id'] ?? null;
try {
$pdo->beginTransaction();
// create order placeholder
$stmt = $pdo->prepare('INSERT INTO orders (user_id, address_id,
total_amount) VALUES (?, ?, 0)');
$stmt->execute([$user_id, $address_id]);
$order_id = $pdo->lastInsertId();
// fetch cart items with FOR UPDATE to lock product rows
$stmt = $pdo->prepare('SELECT c.product_id, [Link], [Link], [Link]
FROM cart_items c JOIN products p ON c.product_id = [Link] WHERE
c.user_id = ? FOR UPDATE');
$stmt->execute([$user_id]);
$items = $stmt->fetchAll();
$total = 0.0;
foreach ($items as $it) {
if ($it['stock'] < $it['qty']) {
throw new Exception('Insufficient stock for product ' .
$it['product_id']);
}
// reduce stock
$stmtUp = $pdo->prepare('UPDATE products SET stock = stock - ?
WHERE id = ?');
$stmtUp->execute([$it['qty'], $it['product_id']]);
// insert order item
$stmtOI = $pdo->prepare('INSERT INTO order_items (order_id,
product_id, qty, unit_price) VALUES (?, ?, ?, ?)');
$stmtOI->execute([$order_id, $it['product_id'], $it['qty'],
$it['price']]);
$total += $it['price'] * $it['qty'];
}
// update order total
$stmt = $pdo->prepare('UPDATE orders SET total_amount = ? WHERE id =
?');
$stmt->execute([$total, $order_id]);
// empty cart
$stmt = $pdo->prepare('DELETE FROM cart_items WHERE user_id = ?');
$stmt->execute([$user_id]);
$pdo->commit();
header('Location: /order_success.php?order_id=' . $order_id);
exit;
} catch (Exception $e) {
$pdo->rollBack();
// log error and show friendly message
error_log($e->getMessage());
echo 'Checkout failed: ' . htmlspecialchars($e->getMessage());
exit;
}
Authentication ([Link] & [Link]) — key notes
Store password hashes using password_hash() and verify with
password_verify().
Use prepared statements to prevent SQL injection.
Use sessions for login state; regenerate session id after login.
Front-end snippets
public/[Link] (product listing skeleton)
<?php
require_once __DIR__ . '/../src/[Link]';
$products = $pdo->query('SELECT p.*, [Link] AS category FROM products
p LEFT JOIN categories c ON p.category_id=[Link]')->fetchAll();
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Store</title>
<link rel="stylesheet" href="/assets/css/[Link]">
</head>
<body>
<h1>Products</h1>
<div class="products">
<?php foreach($products as $p): ?>
<div class="product">
<h3><?=htmlspecialchars($p['name'])?></h3>
<p>₹<?=number_format($p['price'],2)?></p>
<a href="/[Link]?id=<?=$p['id']?>>">View</a>
</div>
<?php endforeach; ?>
</div>
</body>
</html>
assets/js/[Link] — sample add-to-cart via fetch
async function addToCart(productId, qty=1){
const res = await fetch('/src/[Link]?action=add_to_cart', {
method:'POST',
headers: {'Content-Type':'application/json'},
body: [Link]({product_id: productId, qty})
});
const j = await [Link]();
if([Link]) alert('Added'); else alert('Error: '+[Link]);
}
Example queries for DBMS report (to demonstrate skills)
1. Top 5 selling products (aggregate order_items).
2. Monthly revenue view (use DATE_FORMAT(orders.created_at, '%Y-
%m')).
3. Create a view customer_orders showing user, order total, status.
4. Use transaction logs and stock_audit to analyze stock changes.
Example: Top 5 selling
SELECT [Link], [Link], SUM([Link]) AS sold_qty
FROM order_items oi JOIN products p ON oi.product_id = [Link]
GROUP BY [Link] ORDER BY sold_qty DESC LIMIT 5;
Testing and evaluation checklist (for viva / report)
Schema normalization: show ER diagram and justify normalization
(3NF).
Constraints: demonstrate CHECK, FOREIGN KEY, UNIQUE, NOT NULL.
Transactions: checkout uses transaction with proper locking;
demonstrate rollback on insufficient stock.
Stored procedures/triggers: show place_order() and
trg_products_stock_update.
Indexes: show query plan with and without index (EXPLAIN) for product
lookup.
Backup/restore: mysqldump command examples.
Security: password hashing, prepared statements, session handling.
Edge cases: concurrent checkout on same product -> explain locking
strategy.
Deployment & run instructions (local)
1. Install XAMPP (or LAMP) with PHP 8+. Start Apache and MySQL.
2. Copy ecs_project/public to htdocs/ecs_project (XAMPP) or configure
your vhost.
3. Put src outside web root; adjust [Link] DB credentials.
4. Import schema and sample data:
o mysql -u root -p < sql/[Link]
o mysql -u root -p ecs_db < sql/sample_data.sql
o mysql -u root -p ecs_db < sql/stored_procs_triggers.sql
5. Open [Link] and test.
Example mysqldump backup
mysqldump -u root -p ecs_db > ecs_db_backup_$(date +%F).sql
Grading rubric suggestions (what examiners look for)
Database design and normalization: 25%
Correct use of transactions, locks, and concurrency control: 20%
Implementation of stored procedures/triggers/views: 15%
Security and input validation: 10%
Front-end usability and completeness: 10%
Testing, documentation, and demo: 20%
Next steps I can do for you (pick any and I’ll implement):
Generate complete code for one of the PHP pages (e.g., register/login
with secure hashing).
Implement full cart workflow (API + JS + UI).
Create full admin product CRUD pages with image upload.
Create a PDF report template with ER diagram and queries for viva.
Good luck — this scaffold is intentionally minimal and focused on DBMS
features your course will evaluate. If you want, tell me which file to expand
and I’ll generate full code for it next.