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

Scenario

Figure 1. Frow Overview with First Page of CDR

Register(SignUp)

Figure 2. Register Page of CDR

• Query

  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

  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

    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

    INSERT INTO sufferList(user_id, disease_id) VALUES( ‘ID’, ‘diseaseID’)

  • Check Disease

    SELECT disease_id, disease_name FROM diseasetable WHERE disease_id IN (SELECT disease_id FROM sufferList WHERE user_id=‘ID’)

  • Delete Disease

    DELETE FROM sufferList WHERE disease_id=‘diseaseID’ AND user_id=‘ID’

  • Change Password

    UPDATE userList SET passwd='newPW' WHERE user_id='ID'

Choose Drink

Figure 7. Choose Drink Page of CDR

• Query

  • Cafe

    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

    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

    Next Page – Selection of Search Condition

    

    Figure 8. Choose Custome Drink Page of CDR

• Query

  SELECT * FROM drink 
  JOIN recipe ON recipe.drink_id = drink.drink_i JOIN ingredientslist
  ON recipe.ingredient_name = ingredientslist.ingredient_name 
  JOIN orderedList ON orderedList.drink_id = drink.drink_id
  JOIN userList ON orderedList.user_id = userList.user_id
  WHERE season = 'Winter' AND drink.price >=1500 AND drink.price
  <= 5000 AND userList.gender = 'F' AND userList.age >= 50 AND
  userList.age < 60 AND recipe.ingredient_name = 'Espresso'
  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

    SELECT choose_id FROM chooseList WHERE option1=‘caramelSyrup’ AND option2=’javaChip’ AND option3=’whippingCream’;

  • Else if disease filter if ON

    SELECT option_name FROM optionCauseList WHERE disease_id=1;

    SELECT * FROM optionList WHERE option_name <> ‘shot’ AND option_name <> ‘cinnamonSyrup’;

    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,

    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

    CREATE OR REPLACE VIEW descOrderedListView AS SELECT * FROM
    orderedList ORDER BY count DESC;

    CREATE OR REPLACE VIEW tempChooseListView AS SELECT choose_id,
    option1, option2, option3, FROM chooseList WHERE choose_id=1 OR choose_id=5;

    SELECT choose_id FROM tempChooseListView WHERE option1<>’shot’ AND
    option1<>’cinnamonSyrup' AND option2<>'shot' AND option2<>'cinnamonSyrup'
    AND option3<>'shot' AND option3<>'cinnamonSyrup

  Order Drink

  

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

• Query

  CREATE OR REPLACE VIEW myDisease AS SELECT disease_id FROM sufferList WHERE user_id=‘ID’

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

  UPDATE orderedList SET count=‘count+1’ WHERE user_id=‘ID’ AND drink_id=‘DrinkID’ AND choose_id=‘ChooseID’;