{"id":81,"date":"2020-01-17T01:15:25","date_gmt":"2020-01-16T18:15:25","guid":{"rendered":"http:\/\/beauty27.blog.binusian.org\/?p=81"},"modified":"2020-04-09T19:37:50","modified_gmt":"2020-04-09T12:37:50","slug":"documentation","status":"publish","type":"post","link":"https:\/\/beauty27.blog.binusian.org\/?p=81","title":{"rendered":"Documentation"},"content":{"rendered":"\n

https:\/\/github.com\/beautytasara27\/Database-Project-Online-shop<\/a><\/p>\n\n\n\n

Problem Description<\/h3>\n\n\n\n

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. 
<\/p>\n\n\n\n

There are four different types of users that can access the system and they all have different interfaces specific to their needs.
<\/p>\n\n\n\n

Customers<\/strong><\/p>\n\n\n\n

They can browse through the products available in the shop, choose the products the want and make a purchase.
<\/p>\n\n\n\n

Influencer<\/strong><\/p>\n\n\n\n

They can browse through products , choose a product they want to promote and create tutorials and tips.
<\/p>\n\n\n\n

Brand Ambassador<\/strong><\/p>\n\n\n\n

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.
<\/p>\n\n\n\n

Admin<\/strong><\/p>\n\n\n\n

Manages all the users, products, brands, tutorials. Basically they can see all the data in the database .
<\/p>\n\n\n\n

Team Roles<\/strong><\/p>\n\n\n\n

Beauty Tatenda Tasara  2201849402<\/strong><\/p>\n\n\n\n

Designing the database and documentation
<\/p>\n\n\n\n

Michael Stanely Chinaza 2201853785 <\/strong><\/p>\n\n\n\n

Database security and backend classes
<\/p>\n\n\n\n

Rachel Hera 2201841671<\/strong><\/p>\n\n\n\n

Designing user Interface
<\/p>\n\n\n\n

Isabella Virgin 2201808144<\/strong><\/p>\n\n\n\n

Managing queries and demo video<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

ERD<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Relation Schema<\/h3>\n\n\n\n

           <\/h4>\n\n\n\n

\t     Ambassador<\/strong><\/p>\n\n\n\n

ambassodorid\u2192 brand<\/p>\n\n\n\n

Ambassador<\/strong>(username,brand)
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>Default<\/td>key<\/td>Reason<\/td><\/tr>
username<\/td>varchar<\/td>Not Null<\/td>primary\/foreign<\/td>Uniquely identifies each user and therefore each ambassador <\/td><\/tr>
brand<\/td>int<\/td>Not Null<\/td>foreign<\/td>Every ambassador is specific to one brand<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Brand<\/strong><\/p>\n\n\n\n

brandid\u2192 {brandName}<\/p>\n\n\n\n

Brand<\/strong>(brandid, brandName)
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>Default<\/td>key<\/td>Reason<\/td><\/tr>
brandid<\/td>int<\/td>Not Null<\/td>primary<\/td>Unique for every brand and practical to use on queries(names can be too long <\/td><\/tr>
brandName<\/td>varchar<\/td>Not Null<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

<\/p>\n\n\n\n

Cart<\/strong><\/p>\n\n\n\n

cartid\u2192 {productid,quantity,customerid}<\/p>\n\n\n\n

Cart<\/strong>(cartid, productid, quantity, customerid)<\/p>\n\n\n\n

Foreign Key productid references Product(productid) On Delete Cascade On Update Restrict
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
cartid<\/td>int<\/td>Not null<\/td>primary<\/td>Uniquely identifies all rows<\/td><\/tr>
productid<\/td>int<\/td>Not null<\/td>foreign<\/td>To perform join queries with product table<\/td><\/tr>
quantity<\/td>int<\/td>Not null<\/td>
<\/td>
<\/td><\/tr>
customerid<\/td>varchar<\/td>Not null<\/td>foreign<\/td>Every item in the cart belongs to a specific customer<\/td><\/tr>
ordered<\/td>boolean<\/td>false<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Category<\/strong><\/p>\n\n\n\n

categoryid\u2192 categoryname<\/p>\n\n\n\n

Category<\/strong>(categoryid, categoryname)
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
categoryid<\/td>int<\/td>Not null<\/td>primary<\/td>Uniquely identifies each row<\/td><\/tr>
categoryname<\/td>varchar<\/td>Not null<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

<\/p>\n\n\n\n

CreditCard<\/strong><\/p>\n\n\n\n

cardnumber\u2192 {nameOnCard,expiryDate}<\/p>\n\n\n\n

CreditCard<\/strong>(cardNumber, expirydate, NameOnCard)
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
cardnumber<\/td>varchar<\/td>Not null<\/td>primary<\/td>Unique in real life <\/td><\/tr>
expirydate<\/td>Date<\/td>Not null<\/td>
<\/td>
<\/td><\/tr>
Nameoncard<\/td>varchar<\/td>Not null<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Customer<\/strong><\/p>\n\n\n\n

customerid\u2192 creditCardNum<\/p>\n\n\n\n

Customer<\/strong>(Username, cardnumber)<\/p>\n\n\n\n

Foreign Key cardnumber references CreditCard(cardNumber) On Delete Cascade On Update Cascade<\/p>\n\n\n\n

Foreign Key Username references Users(Username) On Delete Cascade On Update Restrict
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
username<\/td>varchar<\/td>Not null<\/td>primary\/foreign<\/td>Unique for every user hence also unique for every customer, a customer should be a user<\/td><\/tr>
cardnumber<\/td>varchar<\/td>Not null<\/td>foreign<\/td>Every customer has a card to make purchases<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

<\/p>\n\n\n\n

Influencer<\/strong><\/p>\n\n\n\n

influencerid\u2192 {skinTone,TypeOfMakeup}<\/p>\n\n\n\n

Influencer<\/strong>(username, skintone, typeOfMakeup)<\/p>\n\n\n\n

Foreign Key username references users(username) On Delete Cascade On Update Restrict
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
username<\/td>varchar<\/td>Not null<\/td>primary\/foreign<\/td>Uniqely defines each role,Influencer is a user<\/td><\/tr>
skintone<\/td>varchar<\/td>null<\/td>
<\/td>
<\/td><\/tr>
typeOfmakeup<\/td>varchar<\/td>null<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Order<\/strong><\/p>\n\n\n\n

orderid\u2192 {cartid,creditCardNumber}<\/p>\n\n\n\n

Orders<\/strong>(orderid, customerid, cardnumber, Date)<\/p>\n\n\n\n

Foreign Key customerid references customers (username) On Delete Cascade On Update Restrict<\/p>\n\n\n\n

Foreign Key cardnumber references creditcard(cardnumber) On Delete Cascade On Update Cascade
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
orderid<\/td>int<\/td>Not null<\/td>primary<\/td>Uniquely identifies each order<\/td><\/tr>
customerid<\/td>varchar<\/td>Not null<\/td>foreign<\/td>Every order is made by a customer<\/td><\/tr>
cardnumber<\/td>varchar<\/td>Not null<\/td>foreign<\/td>Every purchase requires a payment<\/td><\/tr>
Date<\/td>Date<\/td>Current Date<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

<\/p>\n\n\n\n

Product <\/strong><\/p>\n\n\n\n

productid\u2192 {productName,brand,category}<\/p>\n\n\n\n

Product<\/strong>(brand, productid, productName, productpprice, image, category)<\/p>\n\n\n\n

Foreign Key brand references brand(brandid) On Delete Cascade  On Update Restrict<\/p>\n\n\n\n

Foreign Key category references category(categoryid) On Delete Cascade  On Update Restrict
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
productid<\/td>int<\/td>Not null<\/td>primary<\/td>Uniquely identifies every product<\/td><\/tr>
productName<\/td>varchar<\/td>Not null<\/td>
<\/td>
<\/td><\/tr>
brand<\/td>int<\/td>Not null<\/td>foreign<\/td>Every product has a specific brand<\/td><\/tr>
category<\/td>int<\/td>Not null<\/td>foreign<\/td>Every product has  a category<\/td><\/tr>
productprice<\/td>double<\/td>Not null<\/td>
<\/td>
<\/td><\/tr>
image<\/td>blob<\/td>null<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

     Tutorials<\/strong><\/p>\n\n\n\n

tutorialid \u2192 {influencerid, productid, video, tips}<\/p>\n\n\n\n

Tutorials<\/strong>(influencerId, tutorialid, productid, url, tips, recommended)<\/p>\n\n\n\n

Foreign Key influencerId references influencer(username) On Delete Cascade On Update Restrict<\/p>\n\n\n\n

Foreign Key productid references Product(productid) On Delete Cascade On Update Restrict<\/p>\n\n\n\n

<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
tutorialid<\/td>int<\/td>Not null<\/td>primary<\/td>Unique for each tutorial<\/td><\/tr>
productid<\/td>int<\/td>Not null<\/td>foreign<\/td>Each tutorial is for one product<\/td><\/tr>
influencerid<\/td>varchar<\/td>Not null<\/td>foreign<\/td>Influencer makes the tutorial<\/td><\/tr>
VideoUrl<\/td>varchar<\/td>Not null<\/td>
<\/td>
<\/td><\/tr>
Tips<\/td>varchar<\/td>No Tips<\/td>
<\/td>
<\/td><\/tr>
Recommended<\/td>boolean<\/td>false<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Users<\/strong><\/p>\n\n\n\n

username \u2192 {firstname,lastname,password,role}
<\/p>\n\n\n\n

Attribute<\/td>datatype<\/td>default<\/td>key<\/td>Reason<\/td><\/tr>
firstname<\/td>varchar<\/td>Not null<\/td>
<\/td>
<\/td><\/tr>
lastname<\/td>varchar<\/td>Not null<\/td>
<\/td>
<\/td><\/tr>
username<\/td>varchar<\/td>Not null<\/td>primary<\/td>Uniquely identifies each role and similar to real life<\/td><\/tr>
isAdmin<\/td>boolean<\/td>0<\/td>
<\/td>
<\/td><\/tr>
isInfluencer<\/td>boolean<\/td>0<\/td>
<\/td>
<\/td><\/tr>
isAmbassador<\/td>boolean<\/td>0<\/td>
<\/td>
<\/td><\/tr>
isCustomer<\/td>boolean<\/td>0<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Sample Queries<\/h3>\n\n\n\n

To show subtotal in the Cart <\/p>\n\n\n\n

SELECT SUM(PRODUCT.PRODUCTPRICE* CART.QUANTITY ) AS TOTAL FROM CART INNER JOIN PRODUCT ON CART.PRODUCTID= PRODUCT.PRODUCTID AND CART.CUSTOMERID = ‘”+currentUserId+”‘”;<\/p>\n\n\n\n

To show Cart items of the customer<\/p>\n\n\n\n

SELECT * FROM CART INNER JOIN PRODUCT ON CART.PRODUCTID= PRODUCT.PRODUCTID AND CART.CUSTOMERID = ‘”+currentUserId+”‘”<\/p>\n\n\n\n

To insert Products into Product Table<\/p>\n\n\n\n

“INSERT INTO PRODUCT(BRAND,PRODUCTID,PRODUCTNAME,PRODUCTPRICE,CATEGORY) VALUES(?,?,?,?,?)”<\/p>\n\n\n\n

To delete Users by userid perfomed by admin<\/p>\n\n\n\n

“DELETE FROM USERS WHERE USERNAME='”+userid+”‘” <\/p>\n\n\n\n

To insert tutorials into Tutorials table<\/p>\n\n\n\n

“INSERT INTO TUTORIALS(TUTORIALID,PRODUCTID,URL,TIPS,INFLUENCERID) VALUES(?,?,?,?,?)”<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Security<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

<\/p>\n\n\n\n

User Interface<\/h3>\n\n\n\n

Register as a new user form.
<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

INSERT INTO USERS(FIRSTNAME,LASTNAME,USERNAME,PASSWORD,ISADMIN,ISCUSTOMER,<\/p>\n\n\n\n

ISINFLUENCER,ISAMBASSADOR) VALUES(?,?,?,?,?,?,?,?)”;<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Register as a customer form<\/p>\n\n\n\n

INSERT INTO CUSTOMERS(USERNAME,CARDNUMBER) VALUES(?,?)
<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Login form<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Customer dashboard<\/p>\n\n\n\n

SELECT * FROM PRODUCT INNER JOIN CATEGORY WHERE PRODUCT.CATEGORY = CATEGORY.CATEGORYID “<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Customer Cart<\/p>\n\n\n\n

SELECT * FROM CART INNER JOIN PRODUCT ON CART.PRODUCTID= PRODUCT.PRODUCTID AND CART.CUSTOMERID = ‘”+query”‘”;<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Customer Orders<\/p>\n\n\n\n

“SELECT * FROM  ORDERS WHERE CUSTOMERID ='”+customerid+”‘”<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Brand Ambassador Dashboard<\/p>\n\n\n\n

SELECT * FROM PRODUCT INNER JOIN CATEGORY WHERE PRODUCT.CATEGORY = CATEGORY.CATEGORYID “<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Add New Product<\/p>\n\n\n\n

“INSERT INTO PRODUCT(BRAND,PRODUCTID,PRODUCTNAME,PRODUCTPRICE,CATEGORY) VALUES(?,?,?,?,?)”<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Influencer Dashboard<\/p>\n\n\n\n

\u201cSELECT * FROM TUTORIALS WHERE INFLUENCERID = \u2018\u201c+query\u201d\u2019\u201d;<\/p>\n\n\n\n

\u201cSELECT * FROM PRODUCT\u201d;<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

“INSERT INTO TUTORIALS(TUTORIALID,PRODUCTID,URL,TIPS,INFLUENCERID) VALUES(?,?,?,?,?)”;<\/p>\n\n\n\n

Add new Tutorial<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Admin Dashboard<\/p>\n\n\n\n

“SELECT * FROM  USERS”;<\/p>\n\n\n\n

“SELECT * FROM PRODUCT INNER JOIN CATEGORY WHERE PRODUCT.CATEGORY = CATEGORY.CATEGORYID “;<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":9231,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-81","post","type-post","status-publish","format-standard","hentry","category-db_systems"],"_links":{"self":[{"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/posts\/81","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/users\/9231"}],"replies":[{"embeddable":true,"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=81"}],"version-history":[{"count":2,"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/posts\/81\/revisions"}],"predecessor-version":[{"id":84,"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=\/wp\/v2\/posts\/81\/revisions\/84"}],"wp:attachment":[{"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=81"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=81"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beauty27.blog.binusian.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=81"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}