C.S Projects

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 “;