Customized Drink Recommentder
Figure 1. Frow Overview with First Page of CDR
Figure 2. Register Page of CDR
INSERT INTO userList VALUES(“ID", “Name", “Age", “Gender", “PW")
Figure 3. SignIn Page of CDR
Figure 4. Incorrect ID & PW
Figure 5. Correct ID & PW
SELECT user_id, user_name, age, gender FROMuserList WHERE user_id=“ID" AND passwd=“PW"
Figure 6. User My Page of CDR
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’)
INSERT INTO sufferList(user_id, disease_id) VALUES( ‘ID’, ‘diseaseID’)
SELECT disease_id, disease_name FROM diseasetable WHERE disease_id IN (SELECT disease_id FROM sufferList WHERE user_id=‘ID’)
DELETE FROM sufferList WHERE disease_id=‘diseaseID’ AND user_id=‘ID’
UPDATE userList SET passwd='newPW' WHERE user_id='ID'
Figure 7. Choose Drink Page of CDR
Query
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;
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%’ ;
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
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
SELECT choose_id FROM chooseList WHERE option1=‘caramelSyrup’ AND option2=’javaChip’ AND option3=’whippingCream’;
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
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’;