PostgreSQL - An Intensive Overview
Course
In Carshalton
Description
-
Type
Course
-
Location
Carshalton
-
Duration
5 Days
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. Attendees will be given a CD containing the compiled and source code for the most recent stable release of PostgresSQL, as well as a. Suitable for: 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
Facilities
Location
Start date
Start date
Reviews
Course programme
Course DB582
PostgreSQL: An Intensive Overview
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 either Solaris or 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
- 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.
Attendees will be given a CD containing the compiled and source code for the most recent stable release of PostgresSQL, as well as a collection of applications that enhance the usefulness of PostgresSQL. The course exercises and solutions, plus further exercises (without solutions -- though these can be requested via email .... on presentation of evidence that a serious attempt to tackle them has been made) will also be on the CD.
- Identifying the entities, attributes and relationships for a given business application
- Carry out the normalisation to third normal form of a database that is (partly) in second normal form
- Interact 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
Introduction to PostgreSQL
- 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
- 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
- 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 (libpq)
- Java programs and JDBC
- PHP, Perl, Python
- C++ and the postgresSQL C++ API (libpq++)
- ODBC
- Tcl/TK
PostgreSQL - An Intensive Overview