First Technology Transfer

Standard and Advanced Technical Training, Consultancy and Mentoring

Introduction to SQL and Relational Databases via PostgreSQL

Duration: 5 Days

Intended Audience

The hands-on course is FTT's main introductory course to Relational Databases. It is suitable for anyone that has a basic understanding of computers who wishes to understand relational databases and how they work. It is especially useful for Web designers who will be implementing database driven web applications and who need to understand Relational Database technology from scratch.

Course Overview

This is an entry level course for PostgreSQL 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 to create databases, tables and indexes The course will use PostgreSQL running on Linux. It is generic in the sense that it does not depend on any particular operating system and that it concentrates on the most commonly used standard parts of SQL. The course includes an introductory discussion (with demonstrations) of howPostgreSQL is used in Web applications, and how it can be accessed from programming languages such as Java, PHP, Python and C.

Key Skills Learned

  • Understanding of the relational model and relational database terminology
  • Ability to run SQL commands
  • Understand entity relationship modeling and be able to draw entity relationship models using UML notation
  • Be able to create databases and populate them with tables and data
  • Implement basic SQL queries
  • Implement complex SQL queries
  • Understand transactions

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. This will include practical activities on the following topics, that are not always covered well in other courses.

  • Identifying the entities, attributes and relationships for a given business application
  • Carrying out the 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 pseudo code to enforce referential data integrity constraints
  • Implementing simple reporting applications

Detailed Course Outline

  • Introduction to PostgreSQL
    • The uses of databases
    • The advantages of databases over files
    • The history of PostgreSQL
    • Obtaining and installing PostgreSQL
  • Connecting to PostgreSQL
    • TCP/IP - Client/Server
    • Unix Domain Sockets
    • Command line utilities
    • Graphical clients
    • JDBC and ODBC
    • Web interfaces
  • The Relational Model
    • Relational Algebra
    • Tables, Rows and Columns
    • Keys
    • Relationships
    • Relational Operations
    • Transactions
    • SQL-92 features not supported by PostgreSQL
  • Structured Query Language - SQL
    • Data definition
    • Inserting rows
    • Updating rows
    • Deleting rows
    • Queries
    • Joins
    • Ordering
    • Grouping
    • Limiting
    • Altering a table
  • Design and Normalisation
    • 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
  • Overview of Ways of implementing PostgreSQL Clients
    • C programs and the PostgreSQL C API
    • Java programs and JDBC
    • PHP, Python
  • Data Analysis and Reporting with PostgreSQL - an overview
    • Importing PostgreSQL query results into spreadsheets
    • Querying a PostgreSQL database from Access (Optional)
    • Creating reports using PostgreSQL and Crystal Reports (Optional)
  • Using PostgreSQL to store and organise images and documents - an introduction (Optional)
    • How PostgreSQL handles binary large objects
    • Strategies for classifying images
    • Strategies for classifying documents
    • Example case studies