1 / 17

PostgreSQL and relational databases

PostgreSQL and relational databases. As well as assignment 4…. Reflections on relational DBs. Reasons for using a relational database Data fits nicely on one server and is used locally

hila
Download Presentation

PostgreSQL and relational databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

More Related