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