April 25

Questions about anything?

Today's assignment was to try "by hand" some of the graph "shortest" algorithms we looked at last week: Dijkstra's & Floyd-Warshall (shortest path) and Prim's & Kruskal (min spanning tree).

We didn't spend much time on Prim or Kruskal last week - depending on interest we may do a "by hand" example today ... though since we're online and don't have the whiteboard that'll be more awkward - I can draw on my iPad and share that.

For this week, we'll look at a classic data structure and look at what it's used for: one of the most common database technologies, the "SQL" database.

SQL

This week I'd like to at least expose you to relational databases and SQL. We aren't going to do much more than see some of the basic ideas, and I want you to be starting to work on your final project ... so the assignment is fairly open ended.

SQL stands for "structured query language"; it's a programming language (yes, another one) used to manage and search relational databases. But before looking at the SQL language, I want to look at the data structure used to store the information in a typical database.

How : the "B-tree" data structure

An m-way (max number of children for children per node) balanced tree, with O(log n) cost to search, insert, or delete.

• wikipedia: B-tree
• geeksforgeeks: intro to B-tree

The search is similar to the binary search tree we looked at earlier this term, but each node can have more than two children. This means that the tree can have many more nodes for a given height.

B-trees are often used for databases and file systems. Besides doing all operations fairly fast (log(n)), often the higher nodes can be kept within faster memory, leaving only the bottom tier of nodes on the (slower) disk. There are also variants targeted at specific use cases. For example, a typical database is tuned with "indices" (an auxiliary b-tree that clumps some of the original data together to give a smaller tree), giving faster lookup for some of the database keys, at the cost of maintaining the index.

The short description is that B-tree is a "height balanced m-way tree", together with algorithms for search, insert, delete that ensure it remains balanced.

I'm bringing all this up at this point in our course because (a) these sorts of databases are common and important, and (b) the algorithm uses trees, which are a kind of graph, which is our most recent topic.

main points :

• B-trees are made up of nodes & leaf-nodes (terminal)
• each node has some number (up to n, depends on settings) of children and some number of keys , which define where to look for children
• "self balancing" : cannot turn into --- but instead stays spread out
• examples are usually 2-3 (two or three children) but in practical implementations may be many more (100 or so)
• the point is to keep the top parts in main memory, for fast access
• SQL databases may also have multiple b-trees for one table, as specified with an "index" command, to get fast lookup for some columns.
• splitting a node : promote a key upwards; divide into two.
  • so tree depth only increases if root splits, promoting something up to new root

Here's a python search & insert code example.

And here's a "real code" implementation, in SQLite (which we'll look at more later)

• architecture of SQLite
• SQLite B-tree file format
• C source code
• testing it - 640 times as much test code as implementation (!)

Also be aware that there are other similar "balanced" trees with differing specifics: Red-Black trees, AVL trees, 2-3 trees, etc. Some courses on algorithms spend a lot of time on the details of these things. We're not going to ... though you could explore any of these for a final project.

What : SQL implementation models

• client/server systems (MySQL, PostgreSQL) are common parts of backend web applications
  • the web app client "connects" (internet or local process communication) to the sql server
  • good for scaling to large systems with distributed components
  • all the complexities of authentication (who has access to what database)
• NoSQL - alternative types databases ; usually big distributed key:value stores
• SQLite
  • wikipedia: sqlite - summary and background
  • free , open source , database=file (so not client/server model, as many others like MySQL and PostgreSQL are )
  • authentication is same as file access
  • local read/write from a process ... simple but some scaling issues
  • sql language spec
  • python API
  • ... or wrap python interface into an ORM like peewee (object relational model; one python class per database table)

concepts

• tables linked with foreign keys
• security : insert values into queries correctly ; see "parameter substitution" and defense against the dark arts . (Don't be that xkcd Mom
• transactions : making sure that a batch of changes either all succeed or all fail

talking the talk

SQL is not one language - each application (SQLite, MySQL, PostgreSQL, ...) has their own variations, though the basics are the same.

Here are some of the most important commands :

   TABLE    - define a table
   SELECT   - fetch some data
   INSERT   - add a new row of data
   UPDATE   - modify some data

A few quirks :

• sqlite3 syntax ignores case in commands and table names : "SELECT * FROM Person;" is the same as "select * from person;".
• sqlite3 has special "user" commands that start with a period, such as .tables. Most of these are for information or settings.
• All SQL statements end with a semicolon (;).
• The comment charcter is two dashes : -- this is a comment. The C syntax /* ... */ also works.

Combining information from several tables is where the syntax can get tricky. The options are :

• JOIN : connect tables using a primary=foreign key
• subquery : next a SELECT within another SELECT

There are a number of types of joins (left, right, inner, outer), differing mostly in what to do for data which is in one of the two tables but not the other.

an example - umber (this course management system)

• diagram
• sql
• umber on github
  • database model (umber's ORM)

practice

There are some nice lessons at software carpentry ; I think that lesson 1 (selecting), 7 (combining), and 10 (python) look interesting.

To get started with these on jupyter.bennington.college :

   $ wget https://swcarpentry.github.io/sql-novice-survey/files/survey.db
   $ sqlite3 survey.db
   sqlite>

I'll walk us through some what's on the resources page.

---

I uploaded the files that I used to explain things during class - min spanning tree , sql tables.

attachments