JAYPEE INSTITUTE OF INFORMATION TECHNOLOGY,
NOIDA
BSc Computer Science
4th Semester
Data Base Management System Lab (24B55CS244)
Project Based Learning
1. Shivansh Saraswat 23203014
2. Vaibhav Suryavanshi 23203009
3. Aadi Sharma 23203022
TOPIC: E-Marketplace with Hierarchical Categories and
Personalized Notifications
Introduction :
In the digital age, online marketplaces have transformed how individuals
and businesses engage in buying and selling goods. However, many
platforms lack intelligent categorization and user-centric alerting systems.
This project introduces an E-Marketplace with Hierarchical Categories
and Personalized Notifications, a web-based application that enables
users to browse and list items across structured, multi-level categories and
receive alerts when items matching their registered interests are posted.
The system offers an intuitive frontend interface, a robust backend
database, and a personalized experience that caters to both sellers and
buyers.
Objective :
The main objectives of this project, focused on a database-level
implementation using MySQL, are:
1. Design a Hierarchical Category Structure: Use relational database
tables to model multi-level item categories (e.g., Electronics >
Laptops > Gaming Laptops).
2. Implement Item Listings: Store item listings in a structured way,
associating them with their respective categories and metadata (title,
description, price, etc.).
3. Enable Interest Registration: Allow users to register private
interests in certain categories, along with specific conditions like a
maximum acceptable price.
4. Generate Personalized Notifications: Use database queries or
triggers to identify and notify users when new items match their
registered interests.
5. Maintain Data Integrity and Relationships: Use primary and foreign
keys to manage relationships between users, categories, items, and
interests efficiently.
6. Support Query-Based Interaction: Enable all marketplace
operations (listing, interest registration, alert generation) through SQL
queries and logic within the database.
Here is the Complete My Sql Code :
Output :
Explanation :
This SQL script creates and initializes the core structure of a basic
electronic marketplace database called emarketplace_db. The schema
is designed with five main tables: em_users to store user accounts,
em_categories to manage hierarchical item categories, em_items to
represent items listed for sale, em_interests to allow users to register
interest in certain types of items within a budget, and em_notifications
to track when a user is notified about relevant items. Sample data is
inserted for three users: Alice, Bob, and Carol. A nested category structure
is also defined—starting with "Electronics" as the root category, under
which "Mobile Phones" is added, followed by a subcategory
"Smartphones." Carol expresses interest in smartphones priced under
₹600, which is recorded in the em_interests table. A sample SQL query
is then provided to find items that match a user's interest by joining the
interests with the listed items, filtered by category and price. Finally, the
script includes a command to describe the structure of the em_items table
and to retrieve metadata about its columns using INFORMATION_SCHEMA,
helping verify the schema's correctness before further data manipulation or
queries are performed.
Conclusion :
This electronic marketplace project successfully demonstrates the design
and implementation of a structured, category-driven item listing system with
user interest tracking and notification features. The database schema
effectively supports hierarchical categories, user and item management,
and dynamic interest matching. With the ability to notify users when items
matching their preferences (such as price and category) are listed, the
project simulates a real-world e-commerce alert system. Overall, it lays a
strong foundation for building a scalable marketplace platform that can be
expanded further with features like bidding, messaging, or admin control.
