https://github.com/beautytasara27/Database-Project-Online-shop
Problem Description
Our project is an online shop that sells makeup . The basic idea is that people can buy products without having to physically go to shops or malls and they can get other additional information like tutorials on how to use the products they buy without exhausting searches on google. Real life examples include Sephora, Ulta.
There are four different types of users that can access the system and they all have different interfaces specific to their needs.
Customers
They can browse through the products available in the shop, choose the products the want and make a purchase.
Influencer
They can browse through products , choose a product they want to promote and create tutorials and tips.
Brand Ambassador
They manage products specific to their brand, they can add new product as well as delete the product specified. They can also recommend tutorials for their products.
Admin
Manages all the users, products, brands, tutorials. Basically they can see all the data in the database .
Team Roles
Beauty Tatenda Tasara 2201849402
Designing the database and documentation
Michael Stanely Chinaza 2201853785
Database security and backend classes
Rachel Hera 2201841671
Designing user Interface
Isabella Virgin 2201808144
Managing queries and demo video
ERD
Relation Schema
Ambassador
ambassodorid→ brand
Ambassador(username,brand)
Attribute | datatype | Default | key | Reason |
username | varchar | Not Null | primary/foreign | Uniquely identifies each user and therefore each ambassador |
brand | int | Not Null | foreign | Every ambassador is specific to one brand |
Brand
brandid→ {brandName}
Brand(brandid, brandName)
Attribute | datatype | Default | key | Reason |
brandid | int | Not Null | primary | Unique for every brand and practical to use on queries(names can be too long |
brandName | varchar | Not Null |
Cart
cartid→ {productid,quantity,customerid}
Cart(cartid, productid, quantity, customerid)
Foreign Key productid references Product(productid) On Delete Cascade On Update Restrict
Attribute | datatype | default | key | Reason |
cartid | int | Not null | primary | Uniquely identifies all rows |
productid | int | Not null | foreign | To perform join queries with product table |
quantity | int | Not null | ||
customerid | varchar | Not null | foreign | Every item in the cart belongs to a specific customer |
ordered | boolean | false |
Category
categoryid→ categoryname
Category(categoryid, categoryname)
Attribute | datatype | default | key | Reason |
categoryid | int | Not null | primary | Uniquely identifies each row |
categoryname | varchar | Not null |
CreditCard
cardnumber→ {nameOnCard,expiryDate}
CreditCard(cardNumber, expirydate, NameOnCard)
Attribute | datatype | default | key | Reason |
cardnumber | varchar | Not null | primary | Unique in real life |
expirydate | Date | Not null | ||
Nameoncard | varchar | Not null |
Customer
customerid→ creditCardNum
Customer(Username, cardnumber)
Foreign Key cardnumber references CreditCard(cardNumber) On Delete Cascade On Update Cascade
Foreign Key Username references Users(Username) On Delete Cascade On Update Restrict
Attribute | datatype | default | key | Reason |
username | varchar | Not null | primary/foreign | Unique for every user hence also unique for every customer, a customer should be a user |
cardnumber | varchar | Not null | foreign | Every customer has a card to make purchases |
Influencer
influencerid→ {skinTone,TypeOfMakeup}
Influencer(username, skintone, typeOfMakeup)
Foreign Key username references users(username) On Delete Cascade On Update Restrict
Attribute | datatype | default | key | Reason |
username | varchar | Not null | primary/foreign | Uniqely defines each role,Influencer is a user |
skintone | varchar | null | ||
typeOfmakeup | varchar | null |
Order
orderid→ {cartid,creditCardNumber}
Orders(orderid, customerid, cardnumber, Date)
Foreign Key customerid references customers (username) On Delete Cascade On Update Restrict
Foreign Key cardnumber references creditcard(cardnumber) On Delete Cascade On Update Cascade
Attribute | datatype | default | key | Reason |
orderid | int | Not null | primary | Uniquely identifies each order |
customerid | varchar | Not null | foreign | Every order is made by a customer |
cardnumber | varchar | Not null | foreign | Every purchase requires a payment |
Date | Date | Current Date |
Product
productid→ {productName,brand,category}
Product(brand, productid, productName, productpprice, image, category)
Foreign Key brand references brand(brandid) On Delete Cascade On Update Restrict
Foreign Key category references category(categoryid) On Delete Cascade On Update Restrict
Attribute | datatype | default | key | Reason |
productid | int | Not null | primary | Uniquely identifies every product |
productName | varchar | Not null | ||
brand | int | Not null | foreign | Every product has a specific brand |
category | int | Not null | foreign | Every product has a category |
productprice | double | Not null | ||
image | blob | null |
Tutorials
tutorialid → {influencerid, productid, video, tips}
Tutorials(influencerId, tutorialid, productid, url, tips, recommended)
Foreign Key influencerId references influencer(username) On Delete Cascade On Update Restrict
Foreign Key productid references Product(productid) On Delete Cascade On Update Restrict
Attribute | datatype | default | key | Reason |
tutorialid | int | Not null | primary | Unique for each tutorial |
productid | int | Not null | foreign | Each tutorial is for one product |
influencerid | varchar | Not null | foreign | Influencer makes the tutorial |
VideoUrl | varchar | Not null | ||
Tips | varchar | No Tips | ||
Recommended | boolean | false |
Users
username → {firstname,lastname,password,role}
Attribute | datatype | default | key | Reason |
firstname | varchar | Not null | ||
lastname | varchar | Not null | ||
username | varchar | Not null | primary | Uniquely identifies each role and similar to real life |
isAdmin | boolean | 0 | ||
isInfluencer | boolean | 0 | ||
isAmbassador | boolean | 0 | ||
isCustomer | boolean | 0 |
Sample Queries
To show subtotal in the Cart
SELECT SUM(PRODUCT.PRODUCTPRICE* CART.QUANTITY ) AS TOTAL FROM CART INNER JOIN PRODUCT ON CART.PRODUCTID= PRODUCT.PRODUCTID AND CART.CUSTOMERID = ‘”+currentUserId+”‘”;
To show Cart items of the customer
SELECT * FROM CART INNER JOIN PRODUCT ON CART.PRODUCTID= PRODUCT.PRODUCTID AND CART.CUSTOMERID = ‘”+currentUserId+”‘”
To insert Products into Product Table
“INSERT INTO PRODUCT(BRAND,PRODUCTID,PRODUCTNAME,PRODUCTPRICE,CATEGORY) VALUES(?,?,?,?,?)”
To delete Users by userid perfomed by admin
“DELETE FROM USERS WHERE USERNAME='”+userid+”‘”
To insert tutorials into Tutorials table
“INSERT INTO TUTORIALS(TUTORIALID,PRODUCTID,URL,TIPS,INFLUENCERID) VALUES(?,?,?,?,?)”
Security
Our system uses traditional authentication of password and username.If the user enters credentials conforming to the admin then they will be directed to the admin page else there will be directed to their respective pages depending on their roles.
User Interface
Register as a new user form.
INSERT INTO USERS(FIRSTNAME,LASTNAME,USERNAME,PASSWORD,ISADMIN,ISCUSTOMER,
ISINFLUENCER,ISAMBASSADOR) VALUES(?,?,?,?,?,?,?,?)”;
Register as a customer form
INSERT INTO CUSTOMERS(USERNAME,CARDNUMBER) VALUES(?,?)
Login form
Customer dashboard
SELECT * FROM PRODUCT INNER JOIN CATEGORY WHERE PRODUCT.CATEGORY = CATEGORY.CATEGORYID “
Customer Cart
SELECT * FROM CART INNER JOIN PRODUCT ON CART.PRODUCTID= PRODUCT.PRODUCTID AND CART.CUSTOMERID = ‘”+query”‘”;
Customer Orders
“SELECT * FROM ORDERS WHERE CUSTOMERID ='”+customerid+”‘”
Brand Ambassador Dashboard
SELECT * FROM PRODUCT INNER JOIN CATEGORY WHERE PRODUCT.CATEGORY = CATEGORY.CATEGORYID “
Add New Product
“INSERT INTO PRODUCT(BRAND,PRODUCTID,PRODUCTNAME,PRODUCTPRICE,CATEGORY) VALUES(?,?,?,?,?)”
Influencer Dashboard
“SELECT * FROM TUTORIALS WHERE INFLUENCERID = ‘“+query”’”;
“SELECT * FROM PRODUCT”;
“INSERT INTO TUTORIALS(TUTORIALID,PRODUCTID,URL,TIPS,INFLUENCERID) VALUES(?,?,?,?,?)”;
Add new Tutorial
Admin Dashboard
“SELECT * FROM USERS”;
“SELECT * FROM PRODUCT INNER JOIN CATEGORY WHERE PRODUCT.CATEGORY = CATEGORY.CATEGORYID “;
Leave a Reply