项目作者: Hyorm

项目描述 :
Customized Drink Recommentder
高级语言: Java
项目地址: git://github.com/Hyorm/CDR.git
创建时间: 2021-01-17T05:54:10Z
项目社区:https://github.com/Hyorm/CDR

开源协议:

下载


CDR

Summary

  • Database Group Project
    • Complete collaboration
    • Communicate perfectly with four team members and distribute work perfectly
    • Development and Engineering
  • Customized Drink Recommentder

Problem Statement

  1. Name: CDR(Customized Drink Recommender)
  2. Problem Statement
    There is no system in the cafe drink ordering system that takes into
    account the individual’s illnesses such as allergies. A more effective
    method is needed, rather than merely indicating the beverage material
    small.

E/R Diagram

image

Scenario

image

Figure 1. Frow Overview with First Page of CDR

Register(SignUp)

Figure 2. Register Page of CDR

  • Query
    1. INSERT INTO userList VALUES(“ID", “Name", Age", “Gender", PW")

LogIn(SignIn)


Figure 3. SignIn Page of CDR



Figure 4. Incorrect ID & PW



Figure 5. Correct ID & PW

  • Query
    1. SELECT user_id, user_name, age, gender FROMuserList WHERE user_id=“ID" AND passwd=“PW"

My Page


Figure 6. User My Page of CDR

  • Query
    • My Info
    • Picked List
      1. SELECT b.drink_id, a.drink_name FROM drink a INNER JOIN pickedList b ON a.drink_id=b.drink_id INNER JOIN userList c ON c.user_id=b.user_id WHERE (c.user_name= name’)
    • Ordered List
    • Add Disease
      1. INSERT INTO sufferList(user_id, disease_id) VALUES( ID’, diseaseID’)
    • Check Disease
      1. SELECT disease_id, disease_name FROM diseasetable WHERE disease_id IN (SELECT disease_id FROM sufferList WHERE user_id=‘ID’)
    • Delete Disease
      1. DELETE FROM sufferList WHERE disease_id=‘diseaseID AND user_id=‘ID
    • Change Password
      1. UPDATE userList SET passwd='newPW' WHERE user_id='ID'

Choose Drink


Figure 7. Choose Drink Page of CDR

  • Query

    • Cafe
      1. SELECT * FROM drink INNER JOIN drinkandcafe ON drink.drink_id = drinkandcafe.drink_id WHERE drinkanSELECT * FROM drink INNER JOIN drinkandcafe ON drink.drink_id = drinkandcafe.drink_id WHERE drinkandcafe.cafe_id = 1;
    • New
      1. SELECT drink_name, price, temparature FROM drink WHERE release_date LIKE ‘%18/5%’ OR release_date LIKE ‘%18/4%’ OR release_date LIKE ‘%18/3%’ ;
    • Drink Recommand
      1. Next Page Selection of Search Condition

      Figure 8. Choose Custome Drink Page of CDR

  • Query

    1. SELECT * FROM drink
    2. JOIN recipe ON recipe.drink_id = drink.drink_i JOIN ingredientslist
    3. ON recipe.ingredient_name = ingredientslist.ingredient_name
    4. JOIN orderedList ON orderedList.drink_id = drink.drink_id
    5. JOIN userList ON orderedList.user_id = userList.user_id
    6. WHERE season = 'Winter' AND drink.price >=1500 AND drink.price
    7. <= 5000 AND userList.gender = 'F' AND userList.age >= 50 AND
    8. userList.age < 60 AND recipe.ingredient_name = 'Espresso'
    9. GROUP BY orderedList.drink_id ORDER BY count DESC

    Choose Option

  • Choose Options using disease filter or not

    Figure 9. Choose Options Page of CDR

Figure 10. Choose Options Case 1 Page of CDR

  • Case 1) User choose options by their own preference

    • If disease filter is OFF
      1. SELECT choose_id FROM chooseList WHERE option1=‘caramelSyrup AND option2=’javaChip AND option3=’whippingCream’;
    • Else if disease filter if ON
      1. SELECT option_name FROM optionCauseList WHERE disease_id=1;
      1. SELECT * FROM optionList WHERE option_name <> shot AND option_name <> cinnamonSyrup’;
      1. CREATE OR REPLACE VIEW myDisease AS SELECT disease_id FROM sufferList WHERE user_id=1;
  • Case 2) user choose options among the recommend List

    Figure 11. Choose Options Case 2(If disease filter is OFF) Page of CDR

    • If disease filter is OFF,

      1. SELECT choose_id from (SELECT * FROM orderedList ORDER BY count DESC)t WHERE drink_id=4 LIMIT 5;

      Figure 12. Choose Options Case 2(If disease filter is ON) Page of CDR

    • Else if disease filter in ON

      1. CREATE OR REPLACE VIEW descOrderedListView AS SELECT * FROM
      2. orderedList ORDER BY count DESC;
      1. CREATE OR REPLACE VIEW tempChooseListView AS SELECT choose_id,
      2. option1, option2, option3, FROM chooseList WHERE choose_id=1 OR choose_id=5;
      1. SELECT choose_id FROM tempChooseListView WHERE option1<>’shot AND
      2. option1<>’cinnamonSyrup' AND option2<>'shot' AND option2<>'cinnamonSyrup'
      3. AND option3<>'shot' AND option3<>'cinnamonSyrup

    Order Drink

    Figure 13. Choose Options Case 2(If disease filter is ON) Page of CDR

  • Query

    1. CREATE OR REPLACE VIEW myDisease AS SELECT disease_id FROM sufferList WHERE user_id=‘ID
    1. SELECT SUM(IF("null" IN (option1), 0, 1))+SUM(IF("null" IN (option2), 0, 1))+SUM(IF("null" IN (option3), 0, 1)) AS SUM FROM chooseList WHERE choose_id=‘ChooseID’;
    1. UPDATE orderedList SET count=‘count+1 WHERE user_id=‘ID AND drink_id=‘DrinkID AND choose_id=‘ChooseID’;