Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

from database to app app driver / library database engine 15

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

Vaadin Grid 19

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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