PostgreSQL for Application Developers
Duration: 5 Days
Intended Audience
The course is aimed at those who already have some experience of working with relational databases and who need to get up to speed with PostgreSQL quickly. PostgreSQL is a powerful open source relational database that can be extended by adding functions to it, supports triggers and stored procedures, and , which, also supports object relational features via table inheritance. It is especially useful for Web designers who already have some experience with e.g. Access or MySQL and who wish to incorporate PostgreSQL into their web applications.
Course Overview
This is an intensive course for PostgresSQL application developers. It covers the fundamentals of relational database theory and design and the use of the Data Manipulation Language (DML) features of SQL to retrieve, delete and update information, and the Data Definition Language (DDL) features of SQL create databases, tables and indexes. The table inheritance - object relational features of PostgresSQL are also introduced. The course will use PostgresSQL running on Linux.
The course includes an introductory discussion (with demonstrations) of how PostgresSQL is used in Web applications, and how it can be accessed from programming languages such as Perl, Java, PHP and Python and C. ODBC and JDBC data access are described.
Key Skills
- Revision of the relational model , relational database terminology and SQL
- Overview of drawing entity relationship models using UML notation
- Object Relational aspects of PostgreSQL
- Overview of Normalisation and Normal Forms
- Implement basic SQL queries
- Implement complex SQL queries
- Understand transactions
- Implement constraints
- Specify and deploy triggers and stored procedures
Practical Work
About 50% of the course time will be allocated to practical exercises. The exercises will be built around a specific case study ... and so will cover the entire process from design, to database implementation, to application development. These will include the following activities.
- Identifying the entities, attributes and relationships for a given business application
- Carrying out normalisation to third normal form of a database that is (partly) in second normal form
- Interacting with a pre-built database using SQL - both retrieving and modifying the data
- Adding new tables to the database and populating them with data
- Writing PL/PGSQL code to enforce referential data integrity constraints
- Implementing simple reporting applications
Course Contents
- The uses of databases
- The advantages of databases over files
- The history of PostgresSQL
- Overview of obtaining and installing PostgresSQL on Linux/Unix
- TCP/IP - Client/Server
- Unix Domain Sockets
- Command line utilities
- Graphical clients
- ODBC and JDBC
- Web interfaces
- Relational Algebra
- Tables, Rows and Columns
- Keys
- Relationships
- Relational Operations
- SQL-92 features not supported by PostgresSQL
- Data definition
- Inserting rows
- Updating rows
- Deleting rows
- Queries
- Joins
- Ordering
- Grouping
- Limiting
- Altering a table
- Requirements and Design specifications
- Entity Relation(ER) diagrams (and their UML equivalents)
- Reasons for Normalising
- First, Second and Third Normal Forms
- Boyce-Codd Normal Form
- Fourth Normal Form
- Denormalisation
- Transaction Control - COMMIT and ROLLBACK
- Inner and Outer JOINs
- Views
- Combining Queries with Set operations
- Correlated Sub-Queries
- Conditional expressions - CASE .. WHEN
- EXCEPT, INTERSECT, EXISTS
- Constraints - NOT NULL, UNIQUE, PRIMARY KEY, Foreign Key/REFERENCES, CHECK
- Table management - ALTER, GRANT, REVOKE
- Table management - Inheritance, Views, Rules
- Table management - LISTEN, NOTIFY
- SQL Functions
- PL/pgSQL Functions
- Triggers
- C programs and the PostgresSQL C API
- Java programs and JDBC
- PHP, Python
- C++ and the postgresSQL C++ API
- ODBC