• Database Systems 17.01.2020

    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)

    AttributedatatypeDefaultkeyReason
    usernamevarcharNot Nullprimary/foreignUniquely identifies each user and therefore each ambassador 
    brandintNot NullforeignEvery ambassador is specific to one brand

    Brand

    brandid→ {brandName}

    Brand(brandid, brandName)

    AttributedatatypeDefaultkeyReason
    brandidintNot NullprimaryUnique for every brand and practical to use on queries(names can be too long 
    brandNamevarcharNot Null

    Cart

    cartid→ {productid,quantity,customerid}

    Cart(cartid, productid, quantity, customerid)

    Foreign Key productid references Product(productid) On Delete Cascade On Update Restrict

    AttributedatatypedefaultkeyReason
    cartidintNot nullprimaryUniquely identifies all rows
    productidintNot nullforeignTo perform join queries with product table
    quantityintNot null

    customeridvarcharNot nullforeignEvery item in the cart belongs to a specific customer
    orderedbooleanfalse

    Category

    categoryid→ categoryname

    Category(categoryid, categoryname)

    AttributedatatypedefaultkeyReason
    categoryidintNot nullprimaryUniquely identifies each row
    categorynamevarcharNot null

    CreditCard

    cardnumber→ {nameOnCard,expiryDate}

    CreditCard(cardNumber, expirydate, NameOnCard)

    AttributedatatypedefaultkeyReason
    cardnumbervarcharNot nullprimaryUnique in real life 
    expirydateDateNot null

    NameoncardvarcharNot 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

    AttributedatatypedefaultkeyReason
    usernamevarcharNot nullprimary/foreignUnique for every user hence also unique for every customer, a customer should be a user
    cardnumbervarcharNot nullforeignEvery 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

    AttributedatatypedefaultkeyReason
    usernamevarcharNot nullprimary/foreignUniqely defines each role,Influencer is a user
    skintonevarcharnull

    typeOfmakeupvarcharnull

    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

    AttributedatatypedefaultkeyReason
    orderidintNot nullprimaryUniquely identifies each order
    customeridvarcharNot nullforeignEvery order is made by a customer
    cardnumbervarcharNot nullforeignEvery purchase requires a payment
    DateDateCurrent 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

    AttributedatatypedefaultkeyReason
    productidintNot nullprimaryUniquely identifies every product
    productNamevarcharNot null

    brandintNot nullforeignEvery product has a specific brand
    categoryintNot nullforeignEvery product has  a category
    productpricedoubleNot null

    imageblobnull

         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

    AttributedatatypedefaultkeyReason
    tutorialidintNot nullprimaryUnique for each tutorial
    productidintNot nullforeignEach tutorial is for one product
    influenceridvarcharNot nullforeignInfluencer makes the tutorial
    VideoUrlvarcharNot null

    TipsvarcharNo Tips

    Recommendedbooleanfalse

    Users

    username → {firstname,lastname,password,role}

    AttributedatatypedefaultkeyReason
    firstnamevarcharNot null

    lastnamevarcharNot null

    usernamevarcharNot nullprimaryUniquely identifies each role and similar to real life
    isAdminboolean0

    isInfluencerboolean0

    isAmbassadorboolean0

    isCustomerboolean0

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

    Posted by beauty27 @ 1:15 am

  • Leave a Reply

    Your email address will not be published. Required fields are marked *