How do databases work?

Introduction to how relational databases store data, keys and indices, and B-trees.

Appropriate for someone who recently learned Rails.

Transcript

1. How do databases work?
2. Why do we store information?
3. Why do we store information? Remember Find it later Analyze it
4. Why do we store information? Remember Find it later Analyze it Doctor’s Appointment on Thursday Test Results How much did I spend on medical bills this year?
5. Where do you store your information?
6. Where do you store your information?
7. Where do you store your information? FILES!
8. How does your Rails app store information?
9. How does your Rails app store information? Database
10. Relational Database Management System Think: Excel
11. Relational Database Management System Table Rows Columns Think: Excel
12. Relational Database Management System Table Rows Columns Think: Excel
13. Relational Database Management System Table Rows Columns Think: Excel
14. Relational Database Management System Table Rows Columns Think: Excel
15. Family Directory
16. Family Directory
20. the ‘relation’ in relational databases!
21. Rails
22. Rails Table
23. Rails Column
24. Rails Relation
25. Rails Row
26. How do I find ID #4? person.find(4)
27. How do I find ID #4? person.find(4)
29. Primary Key
30. Foreign Key
31. Array of numbers [1,2,4,5,6]
32. 6 5 numbers Sorted Where is 6? 5 4 2 1
33. Divide & Conquer 6 5 numbers Sorted Where is 6? 5 4 2 1
34. Divide & Conquer 6 5 numbers Sorted Where is 6? 5 4 2 1 6 > 4
35. Divide & Conquer 6 5 numbers Sorted Where is 6? 5 4 2 1 6 > 5
36. Divide & Conquer 6 5 numbers Sorted Where is 6? 5 4 2 1 6 == 6
37. Divide & Conquer 6 5 numbers Sorted Where is 6? 5 4 2 1
38. Divide & Conquer 6 5 numbers Sorted Where is 6? 5 4 2 1
39. Algorithm Step-by-step procedure
40. Algorithm Step-by-step procedure Data Structure Way to store and organize data
41. Algorithm Step-by-step procedure Data Structure Way to store and organize data Efficient Fast(er) or uses less memory Expensive Takes a lot of time and/or memory
42. How does a database store data?
43. 5 6 2 1 4 Tree
44. What happens if we delete a row?
45. What happens if we delete a row?
46. B-Tree 1 2 5 6 7 16 9 12 18 21
47. Database Files of Data 63 Rebecca B-tree to organize Data Organized by Primary Key 63 17 89
48. How do we find by name? person.find_by_name(‘Rebecca’)
49. Index
50. Database Primary Key 63 17 89 Index on first_name 3782 1829 9373 63 Rebecca
51. Should we create an index on every column?
52. Add an index in Rails In migration
53. Add foreign key in Rails Gems! foreigner In migration Data Integrity
54. Primary Key Foreign Key Database Index Algorithm Data Structure Efficient/Expensive How database stores data