Project 2
1. Design your ecommerce database in MongoDB with the specifications provided Below :
use ecommerce; (Switch the database to ecommerce)
a. Every Customer would register on the ecommerce website with all their personal information
Answers:
db.customer.insertMany([
{
customer_id: 1,
customer_name:'Vikas soni',
customer_dob:2002/10/10,
customer_address:'123, abc area',
customer_city:'Jaipur',
customer_contactno:9782250356,
customer_email:'[email protected]',
gender:'male'
},
{
customer_id: 2,
customer_name:'Person_2',
customer_dob:2000/11/11,
customer_address:'Address_2',
customer_city:'Jodhpur',
customer_contactno:123456789,
customer_email:'[email protected]',
gender:'female'
},
{
customer_id: 3,
customer_name:'Perosn_3',
customer_dob:2000/11/11,
customer_address:'123/3 abcd area, city road',
customer_city:'Maharastra',
customer_contactno:9876543210,
customer_email:'[email protected]',
gender:'male'
}
]);
b. Each Customer has a type of being normal or premium member
db.customer.insertMany([
{
customer_id: 4,
customer_name:'Perosn_4',
customer_dob:2002/11/11,
customer_address:'Address_4',
customer_city:'Jaipur',
member_type:'premium_member',
customer_contactno:1234657890,
customer_email:'[email protected]',
gender:'male'
},
{
customer_id: 5,
customer_name:'Perosn_5',
customer_dob:2010/12/12,
customer_address:'Address_5',
customer_city:'Bhanjanagar',
member_type:'normal_member',
customer_contactno:2345678901,
customer_email:'[email protected]',
gender:'female'
}
]);
c. The customer should be able to view products and add them to cart
db.cart.insertMany([
{
cart_id:1,
customer_id: 1,
product_id:[1,3]
},
{
cart_id:2,
customer_id: 2,
product_id:[2,1]
},
{
cart_id:3,
customer_id: 3,
product_id:[2,3,3]
}
]);
d. Products can belong to multiple categories, brands and segments.
db.product.insertMany([
{
product_id:1,
categroy_name:'Home Applicances',
brand_name:'LG',
segements:'Refrigeraor'
},
{
product_id:2,
categroy_name:'Books',
brand_name:'jungle book',
segements:'mogle'
},
{
product_id:3,
categroy_name:'Fasion',
brand_name:'mogle',
segements:'shirts'
}
]);
e. Each category can have subcategories as well.
db.categories.insertMany([
{
_id:1,
categroy_name: 'Home Applicances',
sub_categories: ['Refrigeraor', 'cleaning appliance']
},
{
_id:2,
categroy_name: 'Computers, Laptop and Mobiles',
sub_categories: ['Computers', 'Laptops', 'Mobiles']
},
{
_id:3,
categroy_name: 'Books',
sub_categories: ['Fiction', 'Biography', 'Fantacy Fiction','Non Fiction']
},
{
_id:4,
categroy_name: 'Fasion',
sub_categories: ['mens', 'womens']
},
{
_id:5,
categroy_name: 'Games and Consoles',
sub_categories: ['titles', 'console']
}
]);
f. Each products can also have multiple tags associated with it.
db.products.insertMany([
{
_id:1,
product_name: 'Laptop',
product_model: 'C201',
product_brand: 'HP',
tags: ['Computers', 'Laptops'],
product_cost: 100000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:2,
product_name: 'Smart Phone',
product_model: 'C202',
product_brand: 'Google',
tags: ['Mobile', 'Android'],
product_cost: 60000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:3,
product_name: 'Iphone',
product_model: '13',
tags: ['Mobile', 'iphone'],
product_cost: 115000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:4,
product_name: 'Highlaner black jogger',
product_model: 'fit jogger',
tags: ['pant', 'wearabless'],
product_cost: 900,
categroy_name: 'clothes and wearabless'
}
]);
g. From the cart, the customer should be able to check out and make payment and his/her order would be booked which the customer can track
db.cart.insertMany([
{
cart_id:1,
customer_id: 1,
product_id:1,
product_name:'shoes',
add_cart:'1',
check_out:'yes',
make_payment:'pay'
},
{
cart_id:2,
customer_id:2,
product_id:2,
product_name:'clothes',
add_cart:'2',
check_out:'no',
make_payment:'pay'
},
{
cart_id:3,
customer_id: 2,
product_id:3,
product_name:'phone',
add_cart:'1',
check_out:'yes',
make_payment:'pay'
},
{
cart_id:4,
customer_id: 4,
product_id:4,
product_name:'T-shirt',
add_cart:'3',
check_out:'yes',
make_payment:'not pay'
}
]);
h. The admin of the ecommerce and the seller both can add products under multiple categories, segments and tags
db.admin.insertMany([
{
admin_id:1,
admin_name:'Admin_1',
admin_password:'abcde1234'
},
{
admin_id:2,
admin_name:'Admin_2',
admin_password:'abcde1234'
},
{
admin_id:2,
admin_name:'Admin_2',
admin_password:'abcde1234'
}
]);
db.seller.insertMany([
{
seller_id:1,
seller_name:'Seller_1',
seller_address:'Seller_address_1',
seller_city:'Jaipur',
seller_mobileno:123456789,
number_of_prducts:2
},
{
seller_id:2,
seller_name:'Seller_2',
seller_address:'Seller_address_2',
seller_city:'Mumbai',
seller_mobileno:123456789,
number_of_prducts:4
},
{
seller_id:3,
seller_name:'Seller_3',
seller_address:'Seller_address_3',
seller_city:'Jodhpur',
seller_mobileno:123456789,
number_of_prducts:10
}
]);
db.products.insertMany([
{
_id:1,
seller_id:1,
product_name: 'Laptop',
product_model: 'C201',
product_brand: 'HP',
tags: ['Computers', 'Laptops'],
product_cost: 100000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:2,
seller_id:2,
product_name: 'Smart Phone',
product_model: 'C202',
product_brand: 'Google',
tags: ['Mobile', 'Android'],
product_cost: 60000,
categroy_name: 'Computers, Laptop and Mobiles'
}
]);
i. The seller would receive the orders and should be able to fulfil them.
db.orders.insertMany([
{
_id: 1,
product_name: 'Winter Clothes',
customer_id:1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-11'),
order:'receive'
},
{
_id: 2,
product_name: 'Laptops',
customer_id: 2,
order_qty: 2,
order_status: 'Dispatched',
order_date: new Date('2022-11-22'),
order:' Not receive'
},
{
_id: 3,
product_name: 'IPhone-13',
customer_id: 3,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-13'),
order:'receive'
},
{
_id: 4,
product_name: 'Smart Phone',
customer_id: 1,
order_qty: 1,
order_status: 'cancelled',
order_date: new Date('2022-11-18'),
order:'cancel'
}
]);
j. The order shipping details and the billing information are very important things to be stored in the database as well.
db.orders.insertMany([
{
_id: 1,
product_name: 'Winter Clothes',
customer_id:1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-11'),
order:'receive'
},
{
_id: 2,
product_name: 'Laptops',
customer_id: 2,
order_qty: 2,
order_status: 'Dispatched',
order_date: new Date('2022-11-22'),
order:' Not receive'
},
{
_id: 3,
product_name: 'IPhone-13',
customer_id: 3,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-13'),
order:'receive'
},
{
_id: 4,
product_name: 'Smart Phone',
customer_id: 1,
order_qty: 1,
order_status: 'cancelled',
order_date: new Date('2022-11-18'),
order:'cancel'
}
]);
k. Every Seller would register on the ecommerce website with all their personal information.
db.seller.insertMany([
{
_id: 1,
seller_name: 'Seller_101',
seller_business: 'Shop_1',
seller_mobileno: 8181818181,
seller_address: 'ABCD area',
seller_city: 'Jodhpur'
},
{
_id: 2,
seller_name: 'Seller_102',
seller_business: 'Shop_2',
seller_mobileno: 9191919191,
seller_address: '23/4 sector 5',
seller_city: 'Jaipur'
}
]);
## Simple Queries to perform on ecommerce db:
1.Write a query to find the average order value for all Customers.
Answers:
db.products.aggregate({$group:{_id:'order',average:{$avg:'$order'}}});
2. Query your database to get top 10 customers whose orders have the highest value and who have been customers since less than 2 months
Answers:
db.orders.find().sort({order:1}).limit(10);
3. Query your database to find the sum of all orders received in this month
Answers:
db.order.aggregate([{$group:{_id:'Total_order', Totl_sum : {$sum:'$order'}}}]);
4. Query your database to find customers count in various cities, group them by names of cities
Answers:
db.customers.aggregate([{$group:{_id:'customer_city',count:{$sum:1}}}]);
5. Query your database to get all the sellers who have base in the same city in which the customers who ordered their products. Order them in the order of highest number of products sold to customers in same city to lowest.
Answers:
db.sellers.aggregate([$group:{_id:'seller_name':{$match:'customer_city'}}}])
db.orders.aggregate([$sort :{order: 1}:{$match:'customer_city'}}}])
Also attached ecommerce database in MongoDB with the specifications in Project_2.txt file.