Spring 2021
-->

# May 2

This Thursday : I'm getting my 2nd vaccination on Wed, and so may be out of the loop the dafter after ... and so you may get the day off. I'll send out a note Wed night either way.

... start recording.

Search projects due today ... how did that go?

Final project thoughts?

# 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.

## How : B-trees

"height balanced m-way tree"

main points :

• 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 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.

## 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
• free , open source , database=file (so not client/server)
• authentication is same as file access
• local read/write from a process ... simple but some scaling issues

## 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.

## 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.

For your homework for next Monday, I'd like you to try some of these and describe what you did.

https://cs.bennington.college /courses /spring2021 /algorithms /notes /sql1