Spring 2017, CS-160 Lecture: Introduction to Postgres relational database management system

Introduction to Postgres relational database management system
CS-160: Software Engineering
Instructor: Robert Bruce
Spring 2017

SLIDE 1: Introduction to Postgres

Postgres is an open source, ANSI SQL compliant relational database management system.
Additional information about Postgres is at the web address http://www.postgresql.org/about/

SLIDE 2: Postgres: command line admin interface

As database admin for Postgres, you will need to log into the command line interface to create, update, and manage databases and their accompanying tables.

Logging into Postgres from the command line:

rob@laptop ~ $ psql -h localhost -U postgres
Password for user postgres:
psql (9.3.10)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.
postgres=#

SLIDE 3: Postgres: create database

This command creates a new database called cs160:
create database cs160

SLIDE 4: Postgres tables: structure management

Postgres provides standard SQL commands for tables:

create table: creates a new table.
alter table: updates the structure of a table.
drop table: deletes the structure of a table (any data in the table is also deleted).

SLIDE 5: Postgres: columns types we will use

Postgres contains a variety of fields (columns). We'll be using the following types:

bigserial: auto-incrementing eight-byte integer.
integer: integer data type.
varchar: variable length character.
timestamp: date and time.

SLIDE 6: Postgres tables: data management

Postgres provides standard SQL commands for managing the data stored in tables. These commands do not change the structure of the tables.

select from table: retrieves a row of data from an existing table.
insert into table: adds a new row of data to an existing table.
update table: updates data in an existing table.
delete from table: deletes data from a table.

SLIDE 7: Primary keys and foreign keys

STUDENTS
student_id	bigserial
first_name	varchar(100)
last_name	varchar(100)

GRADES
student_id	bigint
course_id	bigint
grade	varchar(2)

COURSES
course_id	bigserial
course_name	varchar(255)
course_description	varchar(1024)

SLIDE 8: Primary keys and foreign keys

	STUDENTS
PRIMARY KEY➞	student_id	bigserial
	first_name	varchar(100)
	last_name	varchar(100)

SLIDE 9: Primary keys and foreign keys

	STUDENTS
	student_id	bigserial
	first_name	varchar(100)
	last_name	varchar(100)

	GRADES
PRIMARY KEY{	student_id	bigint
PRIMARY KEY{	course_id	bigint
	grade	varchar(2)

SLIDE 10: Primary keys and foreign keys

	STUDENTS
	student_id	bigserial
	first_name	varchar(100)
	last_name	varchar(100)

	GRADES
	student_id	bigint
	course_id	bigint
	grade	varchar(2)

	COURSES
PRIMARY KEY➞	course_id	bigserial
	course_name	varchar(255)
	course_description	varchar(1024)

SLIDE 11: Primary keys and foreign keys

The yellow highlighted columns (fields) below form a relation. This relation ties the STUDENTS and GRADES tables together.

	STUDENTS
	student_id	bigserial
	first_name	varchar(100)
	last_name	varchar(100)

	GRADES
	student_id	bigint
	course_id	bigint
	grade	varchar(2)

	COURSES
	course_id	bigserial
	course_name	varchar(255)
	course_description	varchar(1024)

SLIDE 12: Primary keys and foreign keys

The yellow highlighted columns (fields) below form a relation. This relation ties the GRADES and COURSES tables together.

	STUDENTS
	student_id	bigserial
	first_name	varchar(100)
	last_name	varchar(100)

	GRADES
	student_id	bigint
	course_id	bigint
	grade	varchar(2)

	COURSES
	course_id	bigserial
	course_name	varchar(255)
	course_description	varchar(1024)

SLIDE 13: SQL WHERE clause

The WHERE clause is a powerful SQL command for defining a condition. This condition must be satisfied (evaluated to Boolean TRUE) before the appropriate action is taken. The WHERE clause can be applied to SELECT, INSERT, UPDATE, and DELETE commands. Examples:

SELECT student_id FROM students WHERE last_name = "BRUCE";
SELECT first_name, last_name FROM students, grades WHERE student.student_id = grades.student_id AND grades.grade = "A";
SELECT course_id, course_description FROM courses;
UPDATE courses SET course_description = "Software engineering" WHERE course_id = 160;

SLIDE 14: SQL ORDER clause

The ORDER clause follows a WHERE clause. It only applies to SELECT statements. The ORDER clause provides a means of sorting the output in ASCending or DESCending order based on the columns SELECTed. Examples:

SELECT student_id FROM students WHERE last_name = "BRUCE" ORDER BY student_id ASC;
SELECT first_name, last_name FROM students, grades WHERE student.student_id = grades.student_id AND grades.grade = "A" ORDER BY last_name DESC;
SELECT course_id, course_description FROM courses ORDER BY course_id ASC;

SLIDE 15: SQL DISTINCT clause

The DISTINCT clause is used in SELECT statements. It removes duplicate rows of data. Each row returned is thus unique or DISTINCT from each other. Example:

SELECT DISTINCT last_name FROM students ORDER BY last_name;

SLIDE 16: Retrieving auto incremented bigserial column from last INSERT

The BIGSERIAL datatype is an unsigned integer datatype that automatically increments its column value atomically when a new row of data is INSERTed. This is a very useful operation when the bigserial column needs to be unique and be the primary key. Primary keys, by definition, must be unique!

Since this operation is atomic, no race conditions can occur. This is very important when developing applications in highly scaled environments with many concurrent users!

Retrieving the last INSERTed serial number for an INSERT operation is easy. Here's how to do so: