Tables and columns and rows! Oh my! tables, columns, rows, types, SQL, CRUD, storing & retrieving data, primary key, foreign key Introduction to database © 2018 Basil Bourque 1 2018-04-27 Basil Bourque LinkedIn: basilbourque

me • Basil Bourque • LinkedIn: basilbourque • Decades as Software Developer • Building custom-crafted database-backed client-server apps (4D) • Shipped iOS apps (Objective-C) • Micro-startups (Java, Vaadin, Postgres) • Got an idea? Talk to me. SeaPUG

data represents things in the world Veterinary Customer Animal Doctor Visit Publisher Book Author Royalty Invoice Customer Invoice Line Item Dormitory Student Room Assignment Term 3

column = attribute of entity book_ author_ title_ isbn_ description_ name_ phone_ address_ Atomic = Smallest chunk of info you ever want to search for or sort on room_ student_ unit_number_ capacity_ first_name_ last_name_ phone_ gender_ birth_ 4

row = each entity author_ name_ phone_ address_ Lisa Coleman (206) 555-1354 101 Alphabet St Wendy Melvoin (425) 555-7655 202 Chanhassen St Jesse Johnson (360) 555-7878 303 Guitar St book_ title_ isbn_ desc_ Fruit at the Bottom 978-3-16-148410-0 Poetic meditation Revue 876-3-17-242350-0 Minimalist story 5

data type • Data type = Something the database engine can make sense of • Compare: Searching & Sorting characters integers text varchar char clob smallint int bigint exact fraction other numeric decimal boolean date-time uuid geometry network array bits JSON & XML geo (PostGIS) … approx. fraction real double float 6

make your own data type • Some databases such as Postgres allow you to define your own type • CREATE TYPE: composite, enum, range, base, or shell • Domain = limited set of possible values • Examples:
 Postal codes for states in US (56)
 DayOfWeek… ‘MON’, ‘WED’, ‘FRI’, ‘unknown’ • CREATE DOMAIN color VARCHAR(20)
 CHECK ( VALUE IN ( 'pumpkin' , 'periwinkle' , 'seafoam' ) ) ; • Enforced by the database engine – Invalid data rejected
 INSERT INTO tbl ( color_ ) VALUES( 'dusty-rose' ) ; 7

Postgres SQL • Language for interacting with database engine • ‘black box’ – Don't know the internals; don’t care*. • Declarative vs Imperative magic 8

define db structure • Make a table, drop a table • Add a column, drop a column • Add an index, drop an index • Define users-and-roles (security) • ‘DDL’ = Data Definition Language • Like defining fields on a paper form CREATE TABLE dorm_room_ ( unit_ text capacity_ int ) ; CREATE TABLE book_ ( title_ text isbn_ text desc_ text ) ; 9

entering data • CRUD • Create, Read, Update, Delete • SQL: INSERT, SELECT, UPDATE, DELETE & TRUNCATE & DROP • ‘DML’ = Data Manipulation Language • ‘transaction’ = undo ( roll-back if anything bad happens, else commit ) 10

add data INSERT INTO book_ ( title_ , isbn_ , desc_ ) VALUES ( 'Fruit at the Bottom', '978-3-16-148410-0', 'Poetic meditation’) , ( 'Eroica', ‘876-3-17-242350-0', ‘A study of female heroism') ; 11

query data TABLE book_ ; SELECT * FROM book_ ; book_ title_ isbn_ desc_ Fruit at the Bottom 978-3-16-148410-0 Poetic meditation Eroica 876-3-17-242350-0 A study of female heroism 12

SELECT … WHERE SELECT * FROM book_ WHERE title = 'Fruit at the Bottom' ; book_ title_ isbn_ desc_ Fruit at the Bottom 978-3-16-148410-0 Poetic meditation SELECT * FROM book_ WHERE title LIKE 'Fruit%' ; 13 Fruit at the Bottom Fruitcake Fruitition

SELECT column WHERE book_ title_ Fruit at the Bottom SELECT title_ FROM book_ WHERE title LIKE 'Fruit%' ; 14

from database to app app driver / library database engine 15

from Postgres to console app driver / library database engine console terminal ‘psql’ + library database engine 16

from Postgres to pgAdmin app driver / library database engine ‘pgAdmin’ Python + library database engine 17

from Postgres to Vaadin app app driver / library database engine Vaadin web app JDBC driver Postgres Java objects DB types 18

Vaadin Grid 19

identity • Primary Key = Unique identifier • Column to distinguish each row from all others • Use what you have • Use data already existing in your entity (pick a column) • Ex: Employee ID for person, ISBN for book • ‘Natural’ key • Add extra column • Artificially generated value • Sequence = 1, 2, 3, 4… • UUID = 128-bit value
 current moment + MAC + random • ‘Surrogate’ key 20

CREATE TABLE revisited 21 CREATE TABLE dorm_room ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
 unit_ text capacity_ int ) ; CREATE TABLE book_ ( title_ text isbn_ text PRIMARY KEY, desc_ text ) ; * SERIAL now outmoded

connecting tables • Veterinary: Each animal belongs to a customer. Each visit serves one animal. • Dorm: Each student is assigned one room. Each room has 0, 1, or x students. Each dorm room belongs to a dormitory building. • Invoices: Each invoice is sent to a single customer. Each line item belongs to only one invoice. • Publisher: Each book is written by one or more authors. Each author might write 0, 1, or more books. 22

foreign key customer_ invoice_ pkey_ pkey_ fkey_customer_ line_item_ pkey_ fkey_invoice_ name_ phone_ address_ inv_number_ generated_ when_sent_ when_due_ total_ product_ quantity_ price_ extended_cost_ One-to-Many One-to-Many 23

Join customer_ & invoice_ name_ inv_number_ when_sent_ Acme Corp 10012 2017-01-23 MegaMart 10013 2017-01-24 SELECT customer_.name_ , invoice_.inv_number_ , invoice_.when_sent_ FROM customer_ , invoice_ WHERE customer_.id_ = invoice_.fkey_customer_ ; customer_ invoice_ 24 JOIN = Drawing data from across 2 or more tables tables being joined

ephemeral table • Result Set = a new pretend table (transient) • Conceptually: Built as taking every row of ‘customer_’ table and combining with each and every row of ‘invoice_’, then filtering out the new combo-rows to those meeting the criteria of the WHERE clause • ‘Cartesian Product’
 3 customers * 3 invoices = 9 combo-rows • ‘Inner Join’= filter no-match. ‘Left Outer Join’ retains first-table no-match. ‘Right’ 2nd. combo-rows pkey_ name_ phone_ address_ pkey_ fkey_customer_ inv_numb er_ generated _ when_sen t_ when_due_ total_ Acme 10012 2017-01 customer_ invoice_ 25

example data 26 Acme Corp MegaMart Beta Company # 1 # 3 # 2 # 102 # 101 # 103

example tables invoice_ inv_number_ fkey_customer_ 101 1 102 3 103 1 27 customer_ pkey_ name_ 1 Acme Corp 2 Beta Company 3 MegaMart

Cartesian Product 3 customers, 2 of which own our 3 invoices pkey_ name_ inv_number_ fkey_customer_ 1 Acme Corp 101 1 1 Acme Corp 102 3 1 Acme Corp 103 1 2 Beta Company 101 1 2 Beta Company 102 3 2 Beta Company 103 1 3 MegaMart 101 1 3 MegaMart 102 3 3 MegaMart 103 1 28

JOIN syntax 29 SELECT customer_.name_ , invoice_.inv_number_ , invoice_.when_sent_ FROM customer_ , invoice_ WHERE customer_.id_ = invoice_.fkey_customer_ ; SELECT customer_.name_ , invoice_.inv_number_ , invoice_.when_sent_ FROM customer_ INNER JOIN invoice_ ON ( customer_.id_ = invoice_.fkey_customer_ ) ; implicit explicit

INNER JOIN filters out no-match pkey_ name_ inv_number_ fkey_customer_ 1 Acme Corp 101 1 1 Acme Corp 102 3 1 Acme Corp 103 1 2 Beta Company 101 1 2 Beta Company 102 3 2 Beta Company 103 1 3 MegaMart 101 1 3 MegaMart 102 3 3 MegaMart 103 1 30

hide pkey/fkey in UI name_ inv_number_ Acme Corp 101 Acme Corp 103 MegaMart 102 31

LEFT OUTER JOIN result name_ inv_number_ Acme Corp 101 Acme Corp 103 Beta Company NULL MegaMart 102 32

sort SELECT customer_.name_ , invoice_.inv_number_ , invoice_.total_ FROM customer_ , invoice_ WHERE customer_.id_ = invoice_.fkey_customer_ ORDER BY invoice_.total_ DESC ; See biggest invoice amounts at the top, along with each customer name & invoice number. 33

aggregate SELECT customer_.name_ , SUM( invoice_.total_ ) AS sum_invoices_ FROM customer_ INNER JOIN invoice_ ON ( customer_.id_ = invoice_.fkey_customer_ ) GROUP BY customer_.name_ ; Each customer listed once, with the total of all their invoices, if they had any invoices ( = INNER ) 34

many-to-many • Book can be written by no author, one author, or multiple authors • Each author can write no book, one book, or multiple books book_ author_ pkey_ title_ isbn_ desc_ Many-to-Many problem pkey_ name_ phone_ address_ 35

many-to-many • Always resolved by adding third table • Transformed into a pair of One-to-Many relationships book_ author_ pkey_ title_ isbn_ desc_ pkey_ name_ phone_ address_ authorship_ pkey_ fkey_book_ fkey_author_ royalty_ One-to-Many Many-to-One 36

many-to-many example 37 title royalty name phone Fruit at the Bottom 14% Wendy Melvoin (425) 555-7655 Fruit at the Bottom 22% Lisa Coleman (206) 555-1354 Revue 18% Jesse Johnson (360) 555-7878

hierarchy • computer / host OS • cluster = one installation of Postgres (versions: 9.5, 9.6, etc.) • catalog = database. Highest container of data. • schema = namespace. A grouping of tables. Security boundary. • table = list of your entities, contains rows of data. • rows 38

null • Means “no value given” or “no value yet determined” • Work of the Devil • See Dr. Chris Date, Guide to the SQL Standard • Define your columns as NOT NULL • Set DEFAULT value for missing field on INSERT/UPDATE • Remember: null ≠ empty string Icon by: http://iconka.com/ 39

advantages of db server • Enforce rules when inserting/updating data (CHECK constraint) • Server-side code ( ‘stored procedures’, write in PL/pgSQL, Java, Perl, etc. ) • Triggers – Code run before/after each INSERT, UPDATE, DELETE (audit trail) • Concurrency – Coordinates multiple people reading/writing ( A.C.I.D. ) • Safety – Protects your data against crashing and partial writes ( W. A. L. ) • Efficiency – Manages RAM cache vs storage; Plans best route to access data 40

Why Postgres ? The world’s most advanced open-source database « fin » 41 Basil Bourque LinkedIn: basilbourque