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
- The uses of databases
- The advantages of databases over files
- The history of PostgreSQL
- Obtaining and installing PostgreSQL
- TCP/IP - Client/Server
- Unix Domain Sockets
- Command line utilities
- Graphical clients
- JDBC and ODBC
- Web interfaces
- Relational Algebra
- Tables, Rows and Columns
- Keys
- Relationships
- Relational Operations
- Transactions
- SQL-92 features not supported by PostgreSQL
- 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
- C programs and the PostgreSQL C API
- Java programs and JDBC
- PHP, Python
- Importing PostgreSQL query results into spreadsheets
- Querying a PostgreSQL database from Access (Optional)
- Creating reports using PostgreSQL and Crystal Reports (Optional)
- How PostgreSQL handles binary large objects
- Strategies for classifying images
- Strategies for classifying documents
- Example case studies