Campus
Buy • Sell • Reuse
Carts
- Database System Concepts + Web Technology
-oreo maggi
DATABASE DESIGN METHODOLOGY
DESCRIPTION
Campus Cart is an online marketplace designed to help students buy and sell
essential items within their college community. Students frequently need
affordable textbooks, notes, electronics, calculators, bags, and hostel
supplies. At the same time, many others have items they no longer use but
are still in excellent condition. Without a proper system, these resources
often go to waste. Campus Cart addresses this problem by offering a
dedicated platform where students can easily exchange items in a safe,
convenient, and student-exclusive environment.
The platform is built using PHP for backend development and MySQL for
database management, both running through XAMPP to provide a simple
and efficient local development setup. These technologies make it easy to
implement core functionalities such as user registration and login, product
posting, displaying item details, and handling database operations. Since
PHP and MySQL are widely used, reliable, and beginner-friendly, they ensure
that the platform remains stable, responsive, and easy to maintain.
Campus Cart focuses on usability and accessibility. Students can quickly
create an account, add products with images and descriptions, and set their
own prices. Buyers can browse items through categories or keyword search,
making it simple to find what they need. The system also enables direct
communication between buyers and sellers, encouraging smoother
interaction and faster decision-making. Restricting access to campus
students increases trust and reduces the chances of misuse.
A key aim of Campus Cart is to support affordability and sustainability on
campus. Buying second-hand items helps students save money, while selling
unused items reduces waste and encourages reuse. This promotes a more
resource-efficient environment where students support one another.
Overall, Campus Cart enhances convenience and strengthens community
connections within the campus. By using straightforward, effective
technologies such as PHP, MySQL, and XAMPP, the project delivers a practical
and meaningful solution that benefits the entire student body.
Relation Schema
STRUCTURAL CONSTRAINT OF THE ER
DIAGRAM
1. Student – Product Relationship
Relationship: A Student sells many Products
Constraints:
• Minimum: 0 (a student may register but never sell anything)
• Maximum: Many
• Type: (1, N) → One-to-Many
Meaning:
Each product is posted by exactly one student (seller), but a
student can upload multiple products to sell.
2. Student – Order (as Buyer) Relationship
Relationship: A Student can place many Orders
Constraints:
• Minimum: 0 (a student may not buy anything yet)
• Maximum: Many
• Type: (1, N) → One-to-Many
Meaning:
Each order is placed by one student (buyer), but one
student can place multiple different orders.
3. Student – Order (as Seller) Relationship
Relationship: A Student can receive orders for their products
Constraints:
• Minimum: 0 (a seller may not have any sales yet)
• Maximum: Many
• Type: (1, N) → One-to-Many
Meaning:
Each order has one seller, but one seller can receive many
orders for different products.
4. Product – Order Relationship
Relationship: A Product can appear in many Orders
Constraints:
• Minimum: 0 (a product may not be purchased yet)
• Maximum: Many
• Type: (1, N) → One-to-Many
Meaning:
Each order refers to one specific product, but the same
product can be ordered multiple times by different buyers.
5. Order – Product Relationship
(reverse of #4 – included for clarity)
Relationship: Each Order is linked to one Product
Constraints:
• Minimum: 1
• Maximum: 1
• Type: (N, 1) → Many-to-One
Meaning:
Many orders may refer to the same product (if multiple
students buy it), but each order always belongs to one
product.
6. Order – Student (as Buyer)
Already covered in #2, but included formally:
Relationship: Each Order must have one Buyer
• Minimum: 1
• Maximum: 1
• Type: (N, 1)
Meaning:
Each order is always tied to one student who placed it.
CONVERSION OF ER DIAGRAM TO
RELATIONAL SCHEMA
Steps to Convert ER Diagram → Relational Schema
1. Identify Entities
From the ER diagram, the core entities are:
• Students (act as buyers and sellers)
• Products (items posted for sale)
• Orders (transactions between students)
These form the foundation of the relational schema.
2. Assign Primary Keys
Each entity requires a unique identifier:
• students.student_id
• products.product_id
• orders.order_id
Primary keys ensure each record can be uniquely referenced.
i. One-to-Many (Student → Products)
3. Map Relationships
A student can upload many products.
products.seller_id → students.student_id
• Add foreign key:
ii. One-to-Many (Product → Orders)
orders.product_id → products.product_id
A product may be ordered multiple [Link] foreign key:
iii. One-to-Many (Student → Orders as Buyer)
A student may place many orders.
orders.buyer_id → students.student_id
• Add foreign key:
iv. One-to-Many (Student → Orders as Seller)
A seller may receive many orders for their products.
orders.seller_id → students.student_id
• Add foreign key:
4. Define Attributes
Each table is populated with appropriate attributes:
Students: name, email, college, branch, year, password, phone
Products: category, price, quantity, condition, image_url, description, date_posted
Orders: quantity, total_price, order_date, status
Attributes are kept atomic to maintain normalization.
5. Add Constraints
i. Primary and Foreign Keys
• PKs: student_id, product_id, order_id
• FKs: seller_id, buyer_id, product_id
ii. NOT NULL & UNIQUE
• Email should be UNIQUE
• Essential fields (name, password, price, etc.) should be NOT NULL
iii. Referential Integrity
• ON DELETE SET NULL → keeps product/order history intact
Foreign keys use:
• ON UPDATE CASCADE → maintains consistency on key updates
6. Normalize Data
The schema avoids redundancy and satisfies 3rd Normal Form (3NF):
• Each student’s data stored once
• Product details stored only in Products
• Orders reference only IDs, not full records
7. Add Indexes
Improves query performance:
• Index email in students table (used for login)
• Index category in products (used for filtering)
• Index all foreign keys: seller_id, buyer_id, product_id
8. Final Relational Schema
i. Students
students(
student_id PK, name, email UNIQUE, college, branch, year, password, phone
)
ii. Products
product_id PK, seller_id FK → students.student_id,
products(
name, category, price, quantity, condition, image_url, description, date_posted
)
iii. Orders
order_id PK, buyer_id FK → students.student_id,
orders(
seller_id FK → students.student_id,
product_id FK → products.product_id,
quantity, total_price, order_date, status
)
ER DIAGRAM
DATABASE QUERY
-- Create database
CREATE DATABASE student_marketplace;
USE student_marketplace;
-- Students Table
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(200),
email VARCHAR(200),
college VARCHAR(200),
branch VARCHAR(100),
year VARCHAR(50),
password VARCHAR(200),
phone VARCHAR(30)
);
-- Products Table
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY
KEY,
seller_id INT,
name VARCHAR(300),
category VARCHAR(100),
price INT,
quantity INT,
`condition` VARCHAR(50),
description TEXT,
date_posted DATE,
FOREIGN KEY (seller_id) REFERENCES
students(student_id)
);
-- Orders Table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
buyer_id INT,
seller_id INT,
product_id INT,
quantity INT,
total_price INT,
order_date DATE,
status VARCHAR(50),
FOREIGN KEY (buyer_id) REFERENCES
students(student_id),
FOREIGN KEY (seller_id) REFERENCES
students(student_id),
FOREIGN KEY (product_id) REFERENCES
products(product_id)
);
-- Sample Insert
INSERT INTO students VALUES
(1, 'Aarav Naik', '[Link]@[Link]', 'St.
Xavier''s College', 'ECE', '3rd Year', 'pass123',
'9859447379');
INSERT INTO products (seller_id, name, category, price,
quantity, `condition`, description, date_posted)
VALUES (1, 'Data Structures Book', 'Books', 500, 1,
'Used', 'Good condition', '2025-01-12');
INSERT INTO orders VALUES
(1, 1, 1, 1, 1, 500, '2025-01-15', 'Delivered');
IMPLEMENTATION
HOME PAGE
LOGIN PAGE
REGISTER PAGE
PRODUCTS PAGE
P
RODUCT DETAILS
S
ELL ITEM PAGE
Conclusion
The Campus Cart system successfully provides a convenient and reliable
platform for students to buy and sell essential items within their campus.
By combining PHP for backend processing and MySQL for efficient
data management, the project delivers a smooth and user-friendly
experience. Students can easily register, log in, post products, and
browse available items, making the entire exchange process simple and
accessible.
The platform also supports affordability and sustainability by promoting
the reuse of student resources instead of letting them go to waste.
Overall, Campus Cart meets its objective of connecting students,
improving convenience, and creating a practical solution that benefits
the entire campus community.