How the Web Works: Lecture 5

This talk was designed for a class (98-135) taught at Carnegie Mellon University in Spring 2010.

Transcript

2. Third Time Ever!
4. Spring Break!
5. Spring Break! May have to combine
6. Lecture 5 Databases and MySQL
8. Why the Fuss?
9. Students
10. Students Professors
11. Students Courses Professors
13. Constraint Satisfaction To build a schedule, the algorithm asks...
14. Database Queries How many students? What professors can teach? Course Prereqs? Rooms of correct size? Days per week? etc......
15. Idea 1 The Big Table!
16. Student Wanted Courses Allowed Units Alpha 251 213 66 Beta 272 211 59 Gamma 410 437 66
17. But Wait! There might be other courses offered...
18. Student Wanted Courses Allowed Units All Courses Alpha 251 213 66 251 211 213 272 212 410 437 381 451 Beta 272 211 59 251 211 213 272 212 410 437 381 451 Gamma 410 437 66 251 211 213 272 212 410 437 381 451
19. Student Wanted Courses Allowed Units All Courses Alpha 251 213 66 251 211 213 272 212 410 437 381 451 Beta 272 211 59 251 211 213 272 212 410 437 381 451 Gamma 410 437 66 251 211 213 272 212 410 437 381 451 Repetition Fail
20. Idea 2 Multiple Tables
21. Student Wanted Courses Allowed Units Alpha 251 213 66 Beta 272 211 59 ... ... ... Course Units Space 410 12 50 211 12 80 ... ... ... Students Courses
22. Database Queries How many students? What professors can teach? Course Prereqs? Rooms of correct size? Days per week? etc......
23. Database Queries SELECT COUNT(*) FROM STUDENTS SELECT PROFS FROM COURSES WHERE COURSE = 410 SELECT PREREQS FROM COURSES WHERE COURSE = 251 SELECT RSIZE FROM COURSES WHERE COURSE = 410 SELECT ROOM FROM ROOMS WHERE SIZE = RSIZE SELECT DAYS FROM COURSES WHERE COURSE = 211 SELECT ROOM FROM ROOMS WHERE AVAIL = DAYS
24. Database Queries SELECT COUNT(*) FROM STUDENTS SELECT PROFS FROM COURSES WHERE COURSE = 410 SELECT PREREQS FROM COURSES WHERE COURSE = 251 SELECT RSIZE FROM COURSES WHERE COURSE = 410 SELECT ROOM FROM ROOMS WHERE SIZE = RSIZE SELECT DAYS FROM COURSES WHERE COURSE = 211 SELECT ROOM FROM ROOMS WHERE AVAIL = DAYS Useful, Right?
25. Structured Query Language
26. MySQL Is a Query Language Interfaces User and DB SQL is a general idea MySQL is an implementation Free, Open Source The ‘M’ in LAMP
27. Creating & Deleting CREATE TABLE STUDENTS ( name VARCHAR(20), gpa FLOAT, college VARCHAR(3), );
28. Creating & Deleting name gpa college
29. Creating & Deleting DROP TABLE STUDENTS; name gpa college
30. Creating & Deleting DROP TABLE STUDENTS;
31. Insertion INSERT INTO STUDENTS VALUES (‘Alpha’, 3, ‘scs’); name gpa college Alpha 3 scs
32. Insertion INSERT INTO STUDENTS VALUES (‘Beta’, 3.2, ‘cit’); name gpa college Alpha 3 scs Beta 3.2 cit
33. Insertion INSERT INTO STUDENTS VALUES (‘Gamma’, 4, ‘hss’); name gpa college Alpha 3 scs Beta 3.2 cit Gamma 4 hss
34. Updating UPDATE STUDENTS SET gpa = 4, college = “mcs” WHERE name = ‘Alpha’; name gpa college Alpha 3 scs Beta 3.2 cit Gamma 4 hss
35. Updating UPDATE STUDENTS SET gpa = 4, college = “mcs” WHERE name = ‘Alpha’; name gpa college Alpha 4 mcs Beta 3.2 cit Gamma 4 hss
36. Selecting SELECT * FROM STUDENTS; name gpa college Alpha 4 scs Beta 3.2 cit Gamma 4 hss
37. Selecting SELECT (college, gpa) FROM STUDENTS; name gpa college Alpha 4 scs Beta 3.2 cit Gamma 4 hss
38. Selecting SELECT (college, gpa) FROM STUDENTS; name gpa college Alpha 4 scs Beta 3.2 cit Gamma 4 hss
39. Selecting SELECT (college, gpa) FROM STUDENTS WHERE name = “Alpha” OR name = “Beta”; name gpa college Alpha 4 scs Beta 3.2 cit Gamma 4 hss
40. Deleting DELETE FROM STUDENTS WHERE name = “Alpha”; name gpa college Alpha 4 scs Beta 3.2 cit Gamma 4 hss
41. Deleting DELETE FROM STUDENTS WHERE name = “Alpha”; name gpa college Alpha 4 scs Beta 3.2 cit Gamma 4 hss
42. Storing Lists
43. Storing Lists Technical Term: Serialize Not-So-Technical: Hack Understandable: Stringify
44. Storing Lists Technical Term: Serialize Not-So-Technical: Hack Understandable: Stringify
45. Storing Lists Technical Term: Serialize Not-So-Technical: Hack Understandable: Stringify
46. [1, 2, 3, 4, 5]
47. [1, 2, 3, 4, 5]
48. [1, 2, 3, 4, 5] [‘1’, ‘2’, ‘3’, ‘4’, ‘5’]
49. [1, 2, 3, 4, 5] [‘1’, ‘2’, ‘3’, ‘4’, ‘5’] implode
50. [1, 2, 3, 4, 5] [‘1’, ‘2’, ‘3’, ‘4’, ‘5’] implode
51. [1, 2, 3, 4, 5] [‘1’, ‘2’, ‘3’, ‘4’, ‘5’] implode “1-2-3-4-5”
52. [1, 2, 3, 4, 5] [‘1’, ‘2’, ‘3’, ‘4’, ‘5’] implode “1-2-3-4-5” explode
53. [1, 2, 3, 4, 5] [‘1’, ‘2’, ‘3’, ‘4’, ‘5’] implode “1-2-3-4-5” explode
54. Implode & Explode
55. There’s More
56. There’s More .... much, much, much more
57. MySQL Just Bare Essentials < 5% of what MySQL can do but you can be useful! Read Up Online (Check Blog) Take 15-415 http://w3schools.com/sql/
58. Still Boring, Eh? Think Performance Lookup in Big Table vs. Small Space Saved from Repetition Means to and end It gets better...
59. PhpMyAdmin Manage MySQL Databases with a GUI
60. PhpMyAdmin Made in PHP GUI Interface to Tables Useful for Setting Up Can Generate Query Code Comes with XAMPP Beware!
62. steroids( )
63. Demo
64. Remember http://www.w3schools.com/sql/
66. PHP & MySQL Super Popular Easy to Use - MySQLi Host User, Password, Database Tables
67. PHP: www.cmuwebworks.com (202.1.1.4) SQL: www.cmuwebworks.com (202.1.1.4) Hostname would be ‘localhost’
68. PHP: www.cmuwebworks.com (202.1.1.4) SQL: sql.cmuwebworks.com (202.1.1.5) Hostname would be ‘sql.cmuwebworks.com’
69. Connect $conn = new mysqli($hostname, $username, $password, $dbname);
70. Prepare a Query $query = $conn->prepare(“SELECT * FROM users WHERE name=?”);
71. Binding Parameters $query = $bind_param($types, $value1, $value2, $value3, ...)
72. Execute! $query->execute()
73. Process the Data $query->bind_result($col1, $col2, ...); $query->fetch()
74. Wrap Up $query->close(); $conn->close();
75. SQL Injections Ooooooh, security holes!
77. Why Bother?
78. SELECT * FROM users WHERE name = “ $name ”;
79. SELECT * FROM users WHERE name = “ dummy”;DROP TABLE users;“ ”;
82. Something Random Collaborative Filtering
83. Why? Because I’m too quick! SQL is dull =( I like this stuff I couldn’t wait All over the web... Don’t believe me?
86. Umm, not so good..
89. In Fact... PageRank is Collaborative Filtering (with no explicit effort)
92. Awesome, Right? But How?
93. Dumfucus Filtering
94. Dumfucus Filtering Because I invented it... this morning! Me Critics
95. Who Do I Believe? Measure of Similarity, Use Collected Data
99. Lets Rank the Critics! Based on how much we like them...
103. Its Showtime Consider Movie Ranking and Critic Similarity
104. The General Idea Define Similarity Find Similar People What do they like? You probably like it too!
105. More Seriously... Naive Bayes Clustering k-Nearest Neighbours Take 15-381 or 10-601 Read the Books (PCI is easier) ML is Awesome Netflix Prize: $1 Million
106. The Missing Link...
107. The Missing Link...
109. Aardvark We’ve only begun exploiting the social graph...
110. Mini 3,4
111. Week 6 Week 7 Javascript
112. Week 6.5? Javascript
115. http://www.chromeexperiments.com/
116. Photo Credits http://www.txgrp.com/images/data%20center.jpg http://upload.wikimedia.org/wikipedia/commons/9/95/PhpMyAdmin_logo.png http://xkcd.com/327/ http://www.elite-alliance2u.com/news/home/images/flash_logo.png http://images.amazon.com/images/P/1933988665.01._SCLZZZZZZZ_.jpg http://covers.oreilly.com/images/9780596529321/lrg.jpg http://www.nolanhergert.com/wordpressblog/wp-content/uploads/2008/08/405686.png