Upgrade to Pro — share decks privately, control downloads, hide ads and more …

How do databases work?

How do databases work?

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

Appropriate for someone who recently learned Rails.

A9a491b0fcbe0fbce3d64063a37add99?s=128

Rebecca Miller-Webster

November 04, 2013
Tweet

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

  17. None
  18. None
  19. None
  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)

  28. None
  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