Course DB580
Introduction to Relational Databases and PostgreSQL
Introduction to Relational Databases and 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 Perl, Java, PHP, Python and C.
Key Skills
- 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.
Attendees will be given a CD containing the compiled and source code for the most recent stable release of PostgreSQL, as well as a collection of applications that enhance the usefulness of PostgreSQL. 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 pseudo 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 PostgreSQL
- Obtaining and installing PostgreSQL
Connecting to PostgreSQL
- TCP/IP - Client/Server
- Command line utilities
- Graphical clients
- Perl DBI, JDBC
- 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, Perl, Python
Data Analysis and Reporting with PostgreSQL - an overview
- Importing PostgreSQL query results into spreadsheets
- Querying a PostgreSQL database from Access
- Creating reports using PostgreSQL and Crystal Reports
Using PostgreSQL to store and organise images and documents - an introduction
- How PostgreSQL handles binary large objects
- Strategies for classifying images
- Strategies for classifying documents
- Example case studies