PostgreSQL and relational databases

1. PostgreSQL and relational databases As well as assignment 4…

2. Reflections on relational DBs • Reasons for using a relational database • Data fits nicely on one server and is used locally • We need absolute correctness for our data – remember, once there is any error at all, we cannot provide a quantitative description of just how bad it might be • We have a pile of programmers who know and love SQL • We need our queries/updates to be carefully vetted and stored for security on the server, with no other queries/updates running • Our data is highly and repetitively structured • We need to heavily optimize our queries for very high volume repetition • Our database will be fairly stable structurally and there is downtime that can be used to make schema and mass data updates • We don’t want to use two different database technologies and we already know we need a relational database for at least part of our data • We want a db technology that is widely used and trusted and that is well understood by the software community as a whole • We don’t want to risk using a db technology that might change or go away the day after tomorrow

3. PostgreSQL vs. MySQL • PostgreSQL is a generation newer • It has nice UDT capabilities • There are libraries of UDTs that can be imported and used • Both PostgreSQL and MySQL • Full text search • XML data types • To some degree free • MySQL • Never underestimate the value of a heavily understood piece of software • Lots of stacks and development environments come configured to work with it (but to a lesser extent, this is true of PostgreSQL, too). • It is a “core” SQL database, in that we can move pretty much to any other server-based DBMS is we start with MySQL

4. Installing PostgreSQL • http://bitnami.org/stacks • http://bitnami.org/stack/wapp • http://bitnami.org/stack/mapp

5. PostgreSQL install, continued • Go to: http://127.0.0.1/phppgadmin/

6. Using PostgreSQL

7. Using PostgreSQL, continued

8. Assignment 4: Overview • You will build an application using PostgreSQLand Cassandra • The application will consist of a handful of operations that you will perform on each database – you can run your operations manually and have no app • PostgreSQL will hold your schema based, tabular data • But only the most recent data • Cassandra will hold your schema-variable data and will be comprehensive over time • The operations you run on the Cassandra table will make use of timestamps • This is due at the beginning of class on Feb. 25.

9. Assignment 4: Tables • There will be two tables in PostgreSQL • The first holds customers who are buying items • Key for customer, customer name, item purchased for each row (FK of primary key of second table) • Note that will only keep the most recent purchase in the table. • The second will hold the items for purchase • Key for item; price for item; an overview of the item, which contains two fields, an item description and the market where the item is sold (U.S., Colorado, or something like that). • Note we only keep the most recent price for each item. • Cassandra will hold the buying history of each customer • What items purchased • How many of each item • Price paid all of the instances of a given item – prices can change over time

10. Assignment 4: Operations • http://www.postgresql.org/docs/9.0/static/sql.html • You will need to: • create 2 tables in PostgreSQL • create a table in Cassandra • search the Cassandra table by customer and return the total amount spent to date for a given customer • search the Cassandra table by customer and return the price paid for a given item and the date it was bought; note that an item can be bought more than once and at different prices • create rows for tables in PostgreSQL, making sure to always replace a previous tuple if the primary key is already in the table

11. UDTs in PostgreSQL • For extra credit, put a Two important operators: • Create domain • Does not support the making of complex types • You can create an alias for a built-in data type and specify the rang • You can also specify a default value and null/not null. • Create Type • Does not support nulls or default values • Either of them can be used to create enumerated types • A composite type contains more than one field

12. Example Create statements CREATE DOMAIN addresss VARCHAR(50) NOT NULL CREATE DOMAIN age INT CHECK (VALUE > 0 AND VALUE < 120) Note: you can add check constraints to a create domain specification, but not to a type definition CREATE TYPE addresses AS ( city VARCHAR(10), street VARCHAR(40), zip INTEGER)

13. Example inserts INSERT INTO people VALUES (‘t1', ‘wash car‘); INSERT INTO myhobbies VALUES (myid, (“raise chickens”, “eat chickens”));

14. Postgis • Geographic extensions for PostgreSQL • http://postgis.net/windows_downloads • Download 32 bit for windows • Provides • Basic 2d types: points, lines, polygons, etc. • Spatial operators: area, distance, etc. • Some 3d support • Lets us ask questions like: do these two places overlap?

15. GIS

16. Assignment 5… • Use the GIS database …..

17. Questions on midterm • The two books and the posted slides are important • No code writing • Questions will be conceptual, but each will have a specific answer • No essays or subjective material • We will review for the midterm on March 6 • Example questions • 1. You are going to build an application to manage the movement of energy on a grid. Of the db systems we have discussed in class, which would you use and why? • 2. What is polyglot persistence? • 3. When we say there is a tradeoff between correctness and speed (or throughput), what do we mean? • 4. What is a database “migration”? • 5. What is the difference between a key-value db and a key-document db? • 6. Take the following relational table and put it in 1st normal form (I would give you the attributes of the table, the PK, the FDs and the MVDs).