🏛️ CampusBazaar

A Campus Marketplace Web Application

Database Systems Project — BCS-4B | NUCES Faisalabad

---

Project Name	CampusBazaar
Course	Database Systems
Institution	FAST-NUCES, Faisalabad
Program	BS Computer Science — BCS-4B
Members	Ali Amir · Muhammad Zain
Database	PostgreSQL (hosted on Supabase)
Frontend	HTML · CSS · React (via CDN)
Deployment	Vercel

---

📋 Table of Contents

1. Project Overview
2. Features & Capabilities
3. Database Architecture
4. UI Walkthrough
5. Setup & Installation
6. Running the Project
7. Database Setup on Supabase
8. Execution Guide
9. Trigger Demonstrations
10. Team Members

---

🎯 Project Overview

CampusBazaar is a full-stack web application designed as a marketplace exclusively for university campus members — students, faculty, and staff. It allows users to buy and sell everyday campus essentials such as textbooks, stationery, clothing, food, electronics, and utilities.

The core focus of this project is demonstrating real-world database concepts including:

• Relational schema design with proper normalisation
• IS-A inheritance pattern using shared primary keys
• Referential integrity through foreign key constraints
• Domain integrity through CHECK constraints
• Automated business logic using PL/pgSQL triggers
• Multi-table JOIN queries for data retrieval
• Row-level data filtering and sorting

This is not merely a demo — every user action (register, post listing, buy item, leave review) translates directly into real SQL operations executing on a live PostgreSQL database hosted on Supabase.

---

✨ Features & Capabilities

👤 User Management

• Register as Student, Faculty, or Staff — each stored in their own sub-table
• IS-A inheritance: one master users table with three specialised child tables sharing the same primary key
• Secure login that checks both email AND password against the database
• Separate admin login through the admins table
• User profile page showing personal listing statistics pulled live from the database

🛍️ Listings

• Post a listing with title, description, price, condition, category, and optional image URL
• Browse all active listings with real-time data from PostgreSQL
• Filter listings by category (Books, Food, Clothing, Utilities, Electronics, Stationery)
• Sort listings by newest, price ascending, or price descending
• Search listings by title or description
• Listing cards display condition badges (New / Like New / Used / Heavily Used)
• Sold listings visually greyed out and marked — cannot be purchased again

💳 Transactions

• Complete a purchase by selecting a meetup point
• Every purchase inserts a row into the transactions table with status completed
• Database trigger automatically marks the listing as sold upon transaction completion
• Transaction history page showing all purchases with buyer, listing, amount, and date
• Protection against buying your own listing — enforced at database level via trigger
• Protection against buying an already-sold listing — enforced at database level via trigger

⭐ Reviews

• Leave a review only after a completed transaction — no fake reviews possible
• Star rating from 1 to 5 with an optional comment
• Reviews linked to transactions, not listings — full audit trail
• Reviews page displays all reviews with reviewer name, listing name, and date

👑 Admin Dashboard

• Tabbed view of all listings, all users, and all transactions
• Summary statistics: total listings, active listings, registered users, total revenue
• Real-time data pulled from the database with JOIN queries
• Status badges for at-a-glance monitoring

🗄️ Database Schema Page

• Visual documentation of all 9 tables with column names, types, PK and FK labels
• All 4 PL/pgSQL trigger functions displayed with their actual SQL code
• Relationship map showing all foreign key connections between tables
• Designed specifically for instructor demonstration and academic review

---

🗃️ Database Architecture

Entity Relationship Summary

                    ADMIN
                      |
                  moderates
                      |
         USER ─────────────────────────── MESSAGE
          |  \──IS-A──► STUDENT
          |   \─IS-A──► FACULTY          REVIEW
          |    \─IS-A─► STAFF              |
          |                             generates
          │ (seller)                       |
          └──────────► LISTING ──────► TRANSACTION ◄── (buyer) ── USER
                          |
                     belongs to
                          |
                       CATEGORY

Tables

Table	Purpose	Key Constraint
users	Master table for all campus members	email UNIQUE
students	Student-specific details	Shared PK with users
faculty	Faculty-specific details	Shared PK with users
staff	Staff-specific details	Shared PK with users
admins	Platform administrators	Separate from users
categories	Listing categories	category_name UNIQUE
listings	Items for sale	price >= 0, status CHECK
transactions	Purchase records	amount >= 0, status CHECK
reviews	Post-purchase reviews	rating BETWEEN 1 AND 5

IS-A Inheritance Pattern

users (user_id PK)
  ├── students (user_id PK + FK → users)
  ├── faculty  (user_id PK + FK → users)
  └── staff    (user_id PK + FK → users)

A student does not get a separate ID. Their user_id in the students table is the same as in users — this is the shared primary key pattern implementing IS-A inheritance in a relational database.

PL/pgSQL Triggers

Trigger	Event	Purpose
trg_mark_listing_sold	AFTER INSERT OR UPDATE ON transactions	Auto-marks listing as sold when transaction completes
trg_prevent_self_purchase	BEFORE INSERT ON transactions	Rejects purchase if buyer is the seller
trg_prevent_duplicate_purchase	BEFORE INSERT ON transactions	Rejects purchase if listing already sold
trg_update_listing_timestamp	BEFORE UPDATE ON listings	Auto-updates last_updated timestamp on any edit

---

🖥️ UI Walkthrough

Home Page

The landing page features a full-width hero section with the CampusBazaar branding, a live search bar, and three key statistics pulled from the database (active listings, items sold, categories). Below the hero, a horizontal category filter lets users browse by type. The listings grid displays all active items as cards, each showing the item emoji icon, condition badge, title, description, price, seller name and role, and a View button. The grid supports sorting and search filtering in real time.

Login & Register Pages

The login page queries the admins table first, then the users table, matching on both email and password. If neither matches, an error message is shown in red — no automatic login for wrong credentials. The register page dynamically shows additional fields based on the selected role (degree program for students, department for faculty, role title for staff), then inserts into users and the appropriate sub-table in a single flow.

Listing Detail Modal

Clicking any listing card opens a full-detail modal showing the item image placeholder, category, title, price, condition and availability badges, full description, and a seller info card with name and role. If the item is available and the user is logged in, a meetup point input and Confirm Purchase button appear. Submitting the purchase triggers a real database INSERT into the transactions table, which fires the trigger that marks the listing as sold. The modal updates instantly.

Post a Listing Page

A clean form with fields for title, description, price, category dropdown, condition dropdown, and optional image URL. Submitting the form inserts a new row into the listings table with the logged-in user's user_id as seller_id. Validation errors from the database (such as CHECK constraint violations) surface directly on screen.

Profile Page

Displays the logged-in user's name, role badge, email, and three statistics — active listings, sold listings, and total listings — all computed from a live database query filtered by seller_id. A refresh button re-fetches data from the database so sold items appear updated immediately after a transaction completes.

Transaction History Page

A full-width table showing every row in the transactions table, joined with listing titles and buyer names. Columns include transaction ID, listing, buyer, amount, meetup point, date, and status. This page directly demonstrates JOIN queries running on real data.

Reviews Page

Shows all reviews from the reviews table joined with reviewer names and listing titles. Logged-in users who have completed transactions can submit a new review by selecting their transaction, choosing a star rating, and writing a comment. The review INSERT is rejected if the transaction ID does not belong to the reviewer — referential integrity in action.

Database Schema Page

A dedicated page for academic demonstration. Displays all 9 tables as visual cards with column names, data types, and PK/FK labels. Below the table grid, a relationships section maps all foreign key connections. Below that, all 4 trigger functions are shown with their actual PL/pgSQL source code and plain-English explanations of what each one does and when it fires.

Admin Dashboard

Accessible only when logged in as admin. Shows four summary stat cards, then a tabbed table viewer for listings, users, and transactions. All data is fetched live from the database using JOIN queries. Status values display as colour-coded pills (green for active/completed, red for sold/cancelled, orange for pending).

---

⚙️ Setup & Installation

Prerequisites

You need the following before starting:

• A modern web browser (Chrome, Firefox, Edge)
• A Supabase account (free)
• A Vercel or Netlify account (free) for deployment
• No Node.js installation required — the project runs as a single HTML file

Project Files

CampusBazaar/
├── index.html          ← Complete frontend application
├── script.js
├── style.css
├── campusbazaar.sql ← Complete database setup script
└── README.md                 ← This file

---

🗄️ Database Setup on Supabase

Step 1 — Create Supabase Project

1. Go to supabase.com and sign in with GitHub
2. Click New Project
3. Fill in the following:
   • Name: campusbazaar
   • Database Password: choose a strong password and save it
   • Region: Singapore or Mumbai (closest to Pakistan)
   • Plan: Free
4. Click Create new project and wait 1-2 minutes for setup

Step 2 — Run the SQL Setup Script

1. In your Supabase project, click SQL Editor in the left sidebar
2. Click New Query
3. Open campusbazaar_complete.sql in any text editor
4. Select all content (Ctrl+A) and copy it
5. Paste into the Supabase SQL Editor
6. Click Run (or press Ctrl+Enter)
7. You should see a success message at the bottom

This single script does everything in order:

• Drops any existing tables (clean slate)
• Creates all 9 tables with constraints
• Disables Row Level Security on all tables
• Creates all 4 trigger functions and attaches the triggers
• Inserts seed data (users, categories, listings)

Step 3 — Verify Setup

After running the script, run this verification query in a new SQL Editor tab:

SELECT 'users'        AS tbl, COUNT(*) AS rows FROM users
UNION ALL
SELECT 'students',    COUNT(*) FROM students
UNION ALL
SELECT 'faculty',     COUNT(*) FROM faculty
UNION ALL
SELECT 'staff',       COUNT(*) FROM staff
UNION ALL
SELECT 'admins',      COUNT(*) FROM admins
UNION ALL
SELECT 'categories',  COUNT(*) FROM categories
UNION ALL
SELECT 'listings',    COUNT(*) FROM listings
UNION ALL
SELECT 'transactions',COUNT(*) FROM transactions
UNION ALL
SELECT 'reviews',     COUNT(*) FROM reviews;

Expected results:

tbl	rows
users	4
students	2
faculty	1
staff	1
admins	1
categories	6
listings	8
transactions	0
reviews	0

Step 4 — Get API Keys

1. In Supabase left sidebar, click Settings (gear icon)
2. Click API
3. Copy the following two values:
   • Project URL — looks like https://xxxxxxxxxxxxxxxx.supabase.co
   • anon public key — long JWT string starting with eyJ...

---

🚀 Running the Project

Step 1 — Configure API Keys

Open index_fixed.html in any text editor. Find these two lines near the top of the <script> tag:

const SUPABASE_URL = 'YOUR_SUPABASE_URL_HERE';
const SUPABASE_KEY = 'YOUR_SUPABASE_ANON_KEY_HERE';

Replace with your actual values:

const SUPABASE_URL = 'https://xxxxxxxxxxxxxxxx.supabase.co';
const SUPABASE_KEY = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...';

Save the file.

Step 2A — Run Locally

Simply open index_fixed.html in your browser. No server needed. No npm install. No build step. Double-click the file and it opens directly.

Note: Some browsers block external API calls from local files. If listings don't load, use the deployment method below instead.

Step 2B — Deploy to Netlify (Recommended)

1. Go to netlify.com/drop
2. Drag and drop index_fixed.html onto the page
3. Netlify generates a live URL instantly — no account required
4. Share the URL with your instructor

Step 2C — Deploy to Vercel

1. Go to vercel.com and sign in
2. Click Add New Project
3. Instead of importing from GitHub, look for the drag-and-drop option
4. If using GitHub: create a repo with only index_fixed.html in the root, then:
   • Framework Preset: Other
   • Build Command: (leave empty)
   • Output Directory: ./
5. Deploy

---

🎬 Execution Guide

Full Walkthrough for Demonstration

Follow these steps in order to demonstrate the complete system to your instructor:

1. Open the Schema Page first Navigate to the DB Schema tab in the navbar. Show your instructor the table cards, the relationship map, and the trigger source code. This establishes that the database design is complete and intentional.

2. Show the database in Supabase Open Supabase Dashboard → Table Editor → click listings. Show the instructor the live rows in the database. Then show users and categories.

3. Register a new user Click Join Free. Fill in all fields, select Student, enter a degree program. Click Create Account. Then open Supabase → users table — the new row appears instantly. Also check students table — the sub-table row also exists.

4. Post a listing While logged in, click + List Item. Fill in title, price, condition, category. Submit. Open Supabase → listings table — the new row appears with status active.

5. Buy a listing Log out. Log in as a different user (e.g. Sara). Click any active listing. Enter a meetup point. Click Confirm Purchase. Then:

• Open Supabase → transactions table — new row with status completed
• Open Supabase → listings table — that listing's status changed to sold
• This proves the trigger fired automatically

6. Demonstrate trigger protection While logged in as Sara, try to buy the same listing again. The purchase button is disabled because status is sold. Explain that even if attempted via SQL directly, the trigger trg_prevent_duplicate_purchase would reject it.

7. Leave a review Navigate to Reviews. Select your completed transaction from the dropdown. Give a star rating and comment. Submit. Open Supabase → reviews table — the new row appears.

8. Show the Admin Dashboard Log out. Log in as admin@campusbazaar.pk with password admin123. Navigate to Admin in the navbar. Show the statistics cards and switch between the Listings, Users, and Transactions tabs. All data is live from the database.

9. Show Transaction History Navigate to Transactions in the navbar. Show the table with all purchase records, joined with listing and buyer information. Explain this is a multi-table JOIN query running on PostgreSQL.

Resetting the Database for a Fresh Demo

Run this in Supabase SQL Editor to clear transactions and reset listings:

DELETE FROM reviews;
DELETE FROM transactions;
UPDATE listings SET status = 'active';

---

⚡ Trigger Demonstrations

Run these queries directly in Supabase SQL Editor to prove triggers work independently of the frontend:

Test 1 — Normal Purchase (should succeed)

INSERT INTO transactions (listing_id, buyer_id, amount, status, meetup_point)
VALUES (1, 2, 800.00, 'completed', 'Library Entrance');

-- Immediately verify listing is now sold
SELECT listing_id, title, status FROM listings WHERE listing_id = 1;
-- Expected: status = 'sold'

Test 2 — Buy Already-Sold Listing (trigger blocks it)

INSERT INTO transactions (listing_id, buyer_id, amount, status, meetup_point)
VALUES (1, 2, 800.00, 'completed', 'Cafeteria');
-- Expected: ERROR — This listing is already sold!

Test 3 — Buy Your Own Listing (trigger blocks it)

INSERT INTO transactions (listing_id, buyer_id, amount, status, meetup_point)
VALUES (2, 1, 150.00, 'completed', 'Block A');
-- Expected: ERROR — You cannot buy your own listing!

Test 4 — Timestamp Auto-Update (trigger fires)

-- Note the current last_updated
SELECT listing_id, price, last_updated FROM listings WHERE listing_id = 2;

-- Update the listing
UPDATE listings SET price = 100.00 WHERE listing_id = 2;

-- last_updated should be different now
SELECT listing_id, price, last_updated FROM listings WHERE listing_id = 2;

Test 5 — Full JOIN Query

SELECT
    l.listing_id,
    l.title,
    l.price,
    l.status,
    c.category_name,
    u.name  AS seller_name,
    u.role  AS seller_role
FROM
    listings    l
    JOIN categories c ON l.category_id = c.category_id
    JOIN users      u ON l.seller_id   = u.user_id
ORDER BY
    l.posted_date DESC;

---

👨‍💻 Team Members

Ali Amir

BS Computer Science — BCS-4B FAST-NUCES Faisalabad Roll No: 24F-0622

Muhammad Zain

BS Computer Science — BCS-4B FAST-NUCES Faisalabad Roll No: 24F-0531

---

🛠️ Technology Stack

Layer	Technology	Purpose
Database	PostgreSQL	Core relational database engine
Cloud DB Host	Supabase	PostgreSQL hosting + REST API + Dashboard
Stored Procedures	PL/pgSQL	Trigger functions for business logic
Frontend	React 18 (CDN)	UI component rendering
Styling	Pure CSS	Custom design system
Fonts	Google Fonts	Playfair Display + DM Sans
Deployment	Vercel	Static file hosting

---

📚 Key Database Concepts Demonstrated

Concept	Where Used
Primary Keys	Every table — SERIAL auto-increment
Foreign Keys	listings → users, transactions → listings, reviews → transactions
Shared Primary Key (IS-A)	students, faculty, staff all inherit from users
UNIQUE Constraint	email in users, category_name in categories
CHECK Constraint	price >= 0, status IN (...), rating BETWEEN 1 AND 5
ON DELETE CASCADE	Sub-tables cascade delete when user is removed
ON DELETE SET NULL	listings.category_id becomes NULL if category deleted
DEFAULT Values	join_date, posted_date, status all have defaults
BEFORE Trigger	Self-purchase prevention, duplicate purchase prevention, timestamp update
AFTER Trigger	Auto-mark listing as sold after transaction completes
DECLARE Variables	Used in trigger functions to hold intermediate query results
RAISE EXCEPTION	Trigger rejection mechanism — cancels the DML operation
Multi-table JOIN	Used on Home, Transactions, Admin, Reviews pages
Aggregation	COUNT, SUM used on Admin dashboard stats

---

CampusBazaar — Built for learning. Powered by PostgreSQL. FAST-NUCES Faisalabad · Database Systems · BCS-4B · 2024