Tables and columns and rows! Oh my!

Intro to storing your data in a database

You’ve got data. Like people’s names, phones, and email addresses. Or customers’ orders. Or animals making visits to your veterinary office. Or a history of timestamped events.

You want to keep that data organized. Available for searching. And sorting.

So now it is time for a database to store and organize all that data. Let's take a breezy tour of how to make a row for each person or order, how to define columns to store the date of each animal’s visit, and how to organize all those rows and columns into a table capable of searching and sorting.

Get a taste for using the simple but powerful *SQL* language as a way to add records, search and sort those records, and delete them too.

Get a sense of database design, how to translate your business problem into a database solution.

Learn the meaning of “primary key” and “foreign key” to see how they link tables.

We will be using the excellent *Postgres* database engine, but the concepts presented here apply to any relational database including MySQL, H2, Microsoft SQL Server, and Oracle.

« fin »

Transcript

1. 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
2. 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
3. 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
4. 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
5. 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
6. 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
7. 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
8. Postgres SQL • Language for interacting with database engine • ‘black box’ – Don't know the internals; don’t care*. • Declarative vs Imperative magic 8
9. 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
10. 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
11. 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
12. 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
13. 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
14. SELECT column WHERE book_ title_ Fruit at the Bottom SELECT title_ FROM book_ WHERE title LIKE 'Fruit%' ; 14
15. from database to app app driver / library database engine 15
16. from Postgres to console app driver / library database engine console terminal ‘psql’ + library database engine 16
17. from Postgres to pgAdmin app driver / library database engine ‘pgAdmin’ Python + library database engine 17
18. from Postgres to Vaadin app app driver / library database engine Vaadin web app JDBC driver Postgres Java objects DB types 18
19. Vaadin Grid 19
20. 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
21. 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
22. 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
23. 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
24. 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
25. 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
26. example data 26 Acme Corp MegaMart Beta Company # 1 # 3 # 2 # 102 # 101 # 103
27. 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
28. 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
29. 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
30. 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
31. hide pkey/fkey in UI name_ inv_number_ Acme Corp 101 Acme Corp 103 MegaMart 102 31
32. LEFT OUTER JOIN result name_ inv_number_ Acme Corp 101 Acme Corp 103 Beta Company NULL MegaMart 102 32
33. 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
34. 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
35. 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
36. 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
37. 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
38. 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
39. 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
40. 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
41. Why Postgres ? The world’s most advanced open-source database « fin » 41 Basil Bourque LinkedIn: basilbourque